Query Alert Status and Assigned User

[ryanksmith]

Looking to query to alerts/incidents that have not been assigned/picked up or to look at the current status (New/In Progress) to detect and alert on stale events.  I use the following query to generate a list of all the SOC events the staff are looking at but I don't not see a User or Status field, anyone have a answer or work around to this one?

SecurityAlert | where ProviderName == 'ASI Scheduled Alerts' or ProviderName == 'CustomAlertRule'

[#2]

@ryanksmith What you are looking at is the listing of Alerts, not the Incidents.  Alerts do not get assigned to users, only the Incident will.

Unfortunately, you cannot query Incidents using KQL directly.  You have to use the Azure Sentinel REST API.  There have been a few posts about doing this lately, including mine on doing this through PowerShell : https://www.garybushey.com/2020/01/11/your-first-azure-sentinel-rest-api-call/  

[#3]

Thanks @Gary Bushey I'll take a look, very surprised we cant query this without to jump though a bunch of hoops, I have been able in every other SIEM I have worked with. 

[#4]

@ryanksmith I agree, but considering the API is still not G.A. we may be able to see something to make it easier once it does.  I would love to be able to have a workbook showing me the stats of my Incidents, but I guess that is more what something like ServiceNow is for.

[#5]

@ryanksmith I also have a blog post about getting the Incidents into PowerBI where you can create all sorts of nice reports.  https://www.garybushey.com/2020/01/20/azure-sentinel-incidents-in-powerbi

[#6]

how do you join the incident id with the alert via kql?

[#7]

@SocInABox Here is some code.  The good news is that since I last replied to this threat, the SecurityIncident table was created so you don't need to do the REST calls anymore.  I took some of the KQL from the "Incident Overview" workbook and added the join.  I have found that the workbooks and existing rules provide a wealth of useful KQL code.

Definitely not saying this code is perfect but it does work.  Have to convert the AlertIds into a string to use in the join and unfortunately you cannot do it in the join command itself.

SecurityIncident
| where IncidentNumber == '166'
| summarize arg_max(TimeGenerated,CreatedTime,Status, Severity, Owner, AdditionalData, IncidentUrl, Comments, Classification,ClassificationReason, ClassificationComment,Labels, Title, AlertIds) by IncidentNumber
| mv-expand AlertIds
| extend AlertIDstring = tostring(AlertIds)
| join SecurityAlert on $left.AlertIDstring == $right.SystemAlertId

[#8]

Thanks @Gary Bushey!

I was struggling with that join, VERY much appreciated!

[#9]

@SocInABox Just found that when you use mv-expand, you can specify the data type to expand into.  So the code could be written as

SecurityIncident
| where IncidentNumber == '166'
| summarize arg_max(TimeGenerated,CreatedTime,Status, Severity, Owner, AdditionalData, IncidentUrl, Comments, Classification,ClassificationReason, ClassificationComment,Labels, Title, AlertIds) by IncidentNumber
| mv-expand AlertIds to typeof(string)
| join SecurityAlert on $left.AlertIds == $right.SystemAlertId

 (mv-expand expands into a string type which eliminates the need for the expand command.

[#10]

good point, the error output is usually pretty good about telling me when that's wrong :).

ahh but the specific syntax you're showing I have not used before, cool.
Thanks for the tip.

[#11]

Hi @Gary Bushey can you write one kql to check if any particular entity(User account) present in any security alert/ incident.

Or one user account is associated with how many security alert for the past 7 days