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 genera...
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
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.
Hi GaryBushey 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
