Jan 30 2020 01:54 PM
Jan 30 2020 01:54 PM
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?
Jan 30 2020 02:55 PM
@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/
Jan 31 2020 05:33 AM
@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.
Jan 31 2020 05:37 AM
@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
May 05 2021 04:37 AM
@bobsyouruncle 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
May 06 2021 06:04 AM
@bobsyouruncle 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.
May 06 2021 06:17 AM - edited May 06 2021 06:19 AM
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.