How to get the real count of incidents in Microsoft Sentinel?

[zaylinhtun]

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. 

[#2 · Accepted solution]

@zaylinhtun 

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 columnchart 

For 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

[#3]

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

[#4]

@Clive_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), Title

to make sure to get the latest entry?

[#5]

@Gary Bushey

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.