Forum Discussion
Solved
How to get the real count of incidents in Microsoft Sentinel?
Hi guys, when I do the monthly report for my customers, I found different counts of security incidents. I can not determine the real count of incidents?
Please see the attached files for your reference.
Your second screen shot shows 7days selected, so if the UI is set to 30days, then that's the difference?
// Show count for last 30days SecurityIncident | where TimeGenerated > ago(30d) | summarize count() by IncidentNumber, IncidentName, Title , Severity // daily count from midnight 30days ago SecurityIncident | where TimeGenerated > startofday(ago(30d)) | summarize count() by bin(TimeGenerated,1d) | order by TimeGenerated asc //| render columnchartFor each SecurityIncident there will be a SecurityAlert entry, but there could be more than one Alert - so the SecurityAlert count is often higher? e.g. This single Incident 10725 has three Alerts
This KQL should match what you see in the UI
SecurityIncident | summarize IncidentCount = count() by IncidentNumber, tostring(AlertIds), Title | extend Alerts = extract("\\[(.*?)\\]", 1, tostring(AlertIds)) | mv-expand todynamic(AlertIds) to typeof(string) | join ( SecurityAlert | summarize AlertCount = count() by AlertSeverity, SystemAlertId, AlertName ) on $left.AlertIds == $right.SystemAlertId | summarize sum(AlertCount), make_set(AlertName) by IncidentNumber, Title
Your second screen shot shows 7days selected, so if the UI is set to 30days, then that's the difference?
// Show count for last 30days SecurityIncident | where TimeGenerated > ago(30d) | summarize count() by IncidentNumber, IncidentName, Title , Severity // daily count from midnight 30days ago SecurityIncident | where TimeGenerated > startofday(ago(30d)) | summarize count() by bin(TimeGenerated,1d) | order by TimeGenerated asc //| render columnchartFor each SecurityIncident there will be a SecurityAlert entry, but there could be more than one Alert - so the SecurityAlert count is often higher? e.g. This single Incident 10725 has three Alerts
This KQL should match what you see in the UI
SecurityIncident | summarize IncidentCount = count() by IncidentNumber, tostring(AlertIds), Title | extend Alerts = extract("\\[(.*?)\\]", 1, tostring(AlertIds)) | mv-expand todynamic(AlertIds) to typeof(string) | join ( SecurityAlert | summarize AlertCount = count() by AlertSeverity, SystemAlertId, AlertName ) on $left.AlertIds == $right.SystemAlertId | summarize sum(AlertCount), make_set(AlertName) by IncidentNumber, TitleClive_Watson This is more for my knowledge than anything else. Does your code guarantee that the latest entry in the SecurityIncident table is returned for each incident? If I have an incident created and then 2 hours later another alert is added to it, will I be certain to get all the alerts listed or should there be a line like
| summarize IncidentCount = arg_max(TimeGenerated, *) by IncidentNumber, tostring(AlertIds), Titleto make sure to get the latest entry?
In this case my simple example was a simple count of all the Incidents and Alerts within (like in the UI). Depending on the Use Case there are quite a few other columns that could come into play, as well as TimeGenerated. LastModifiedTime etc...
I used arg_max for a similar answer last week: Search Incidents for entries from an IP Range - Microsoft Tech Community
I have a few versions (code templates) and just happened to pick one that didn't do arg_max this time. I think of this forum and my replies in the context of 'teach a person to fish' rather than cooking them the meal and feeding them the final result.
Hello Clive_Watson, My UI is set for 7 days too. Not 30 days. But your last KQL is working and matches the count number with UI. Thanks
