Forum Discussion
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
- SocInABoxMay 04, 2021Iron Contributorhow do you join the incident id with the alert via kql?
- GaryBusheyMay 05, 2021Bronze Contributor
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
- SocInABoxMay 05, 2021Iron Contributor
Thanks GaryBushey!
I was struggling with that join, VERY much appreciated!