Forum Discussion
Query Alert Status and Assigned User
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?
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
- how do you join the incident id with the alert via kql?
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
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/
Thanks GaryBushey 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.
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.
