SOLVED

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

%3CLINGO-SUB%20id%3D%22lingo-sub-3102751%22%20slang%3D%22en-US%22%3EHow%20to%20get%20the%20real%20count%20of%20incidents%20in%20Microsoft%20Sentinel%3F%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-3102751%22%20slang%3D%22en-US%22%3E%3CP%3EHi%20guys%2C%20when%20I%20do%20the%20monthly%20report%20for%20my%20customers%2C%20I%20found%20different%20counts%20of%20security%20incidents.%20I%20can%20not%20determine%20the%20real%20count%20of%20incidents%3F%3C%2FP%3E%3CP%3EPlease%20see%20the%20attached%20files%20for%20your%20reference.%26nbsp%3B%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20image-alt%3D%221.PNG%22%20style%3D%22width%3A%20690px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F344427i830A6787AC4B02E4%2Fimage-size%2Flarge%3Fv%3Dv2%26amp%3Bpx%3D999%22%20role%3D%22button%22%20title%3D%221.PNG%22%20alt%3D%221.PNG%22%20%2F%3E%3C%2FSPAN%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20image-alt%3D%222.PNG%22%20style%3D%22width%3A%20999px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F344428i95B14D5A048BE0AF%2Fimage-size%2Flarge%3Fv%3Dv2%26amp%3Bpx%3D999%22%20role%3D%22button%22%20title%3D%222.PNG%22%20alt%3D%222.PNG%22%20%2F%3E%3C%2FSPAN%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20image-alt%3D%223.PNG%22%20style%3D%22width%3A%20999px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F344429i1D4D0FA324A1C393%2Fimage-size%2Flarge%3Fv%3Dv2%26amp%3Bpx%3D999%22%20role%3D%22button%22%20title%3D%223.PNG%22%20alt%3D%223.PNG%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-3103740%22%20slang%3D%22en-US%22%3ERe%3A%20How%20to%20get%20the%20real%20count%20of%20incidents%20in%20Microsoft%20Sentinel%3F%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-3103740%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F795584%22%20target%3D%22_blank%22%3E%40zaylinhtun%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EYour%20second%20screen%20shot%20shows%207days%20selected%2C%20so%20if%20the%20UI%20is%20set%20to%2030days%2C%20then%20that's%20the%20difference%3F%3CBR%20%2F%3E%3CBR%20%2F%3E%3C%2FP%3E%3CPRE%20class%3D%22lia-code-sample%20language-cpp%22%3E%3CCODE%3E%2F%2F%20Show%20count%20for%20last%2030days%20%0ASecurityIncident%0A%7C%20where%20TimeGenerated%20%26gt%3B%20ago(30d)%0A%7C%20summarize%20count()%20by%20IncidentNumber%2C%20IncidentName%2C%20Title%20%2C%20Severity%0A%0A%2F%2F%20daily%20count%20from%20midnight%2030days%20ago%0ASecurityIncident%0A%7C%20where%20TimeGenerated%20%26gt%3B%20startofday(ago(30d))%0A%7C%20summarize%20count()%20by%20bin(TimeGenerated%2C1d)%0A%7C%20order%20by%20TimeGenerated%20asc%0A%2F%2F%7C%20render%20columnchart%20%3C%2FCODE%3E%3C%2FPRE%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EFor%20each%20%3CSTRONG%3ESecurityIncident%3C%2FSTRONG%3E%20there%20will%20be%20a%20%3CSTRONG%3ESecurityAlert%3C%2FSTRONG%3E%20entry%2C%20but%20there%20could%20be%20more%20than%20one%20Alert%20-%20so%20the%20%3CSTRONG%3ESecurityAlert%3C%2FSTRONG%3E%20count%20is%20often%20higher%3F%26nbsp%3B%20e.g.%20This%20single%20Incident%2010725%20has%20three%20Alerts%3CBR%20%2F%3E%3CBR%20%2F%3E%3C%2FP%3E%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20image-alt%3D%22Clive_Watson_0-1643793236614.png%22%20style%3D%22width%3A%20400px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F344479i2F6245CBB1831320%2Fimage-size%2Fmedium%3Fv%3Dv2%26amp%3Bpx%3D400%22%20role%3D%22button%22%20title%3D%22Clive_Watson_0-1643793236614.png%22%20alt%3D%22Clive_Watson_0-1643793236614.png%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThis%20KQL%20should%20match%20what%20you%20see%20in%20the%20UI%3CBR%20%2F%3E%3CBR%20%2F%3E%3C%2FP%3E%3CPRE%20class%3D%22lia-code-sample%20language-cpp%22%3E%3CCODE%3ESecurityIncident%0A%7C%20summarize%20IncidentCount%20%3D%20count()%20%20by%20IncidentNumber%2C%20tostring(AlertIds)%2C%20Title%0A%7C%20extend%20Alerts%20%3D%20extract(%22%5C%5C%5B(.*%3F)%5C%5C%5D%22%2C%201%2C%20tostring(AlertIds))%0A%7C%20mv-expand%20todynamic(AlertIds)%20to%20typeof(string)%0A%7C%20join%20%0A(%0A%20%20%20%20SecurityAlert%0A%20%20%20%20%7C%20summarize%20AlertCount%20%3D%20count()%20by%20AlertSeverity%2C%20SystemAlertId%2C%20AlertName%0A)%20on%20%24left.AlertIds%20%3D%3D%20%24right.SystemAlertId%0A%7C%20summarize%20sum(AlertCount)%2C%20make_set(AlertName)%20by%20IncidentNumber%2C%20Title%3C%2FCODE%3E%3C%2FPRE%3E%3CP%3E%3CBR%20%2F%3E%3CBR%20%2F%3E%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-3103991%22%20slang%3D%22en-US%22%3ERe%3A%20How%20to%20get%20the%20real%20count%20of%20incidents%20in%20Microsoft%20Sentinel%3F%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-3103991%22%20slang%3D%22en-US%22%3E%3CP%3EHello%26nbsp%3B%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F1210469%22%20target%3D%22_blank%22%3E%40Clive_Watson%3C%2FA%3E%2C%20My%20UI%20is%20set%20for%207%20days%20too.%20Not%2030%20days.%20But%20your%20last%20KQL%20is%20working%20and%20matches%20the%20count%20number%20with%20UI.%20Thanks%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-3104466%22%20slang%3D%22en-US%22%3ERe%3A%20How%20to%20get%20the%20real%20count%20of%20incidents%20in%20Microsoft%20Sentinel%3F%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-3104466%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F1210469%22%20target%3D%22_blank%22%3E%40Clive_Watson%3C%2FA%3E%26nbsp%3BThis%20is%20more%20for%20my%20knowledge%20than%20anything%20else.%26nbsp%3B%20%26nbsp%3BDoes%20your%20code%20guarantee%20that%20the%20latest%20entry%20in%20the%20SecurityIncident%20table%20is%20returned%20for%20each%20incident%3F%26nbsp%3B%20%26nbsp%3BIf%20I%20have%20an%20incident%20created%20and%20then%202%20hours%20later%20another%20alert%20is%20added%20to%20it%2C%20will%20I%20be%20certain%20to%20get%20all%20the%20alerts%20listed%20or%20should%20there%20be%20a%20line%20like%26nbsp%3B%3C%2FP%3E%3CPRE%20class%3D%22lia-code-sample%20language-applescript%22%3E%3CCODE%3E%7C%20summarize%20IncidentCount%20%3D%20arg_max(TimeGenerated%2C%20*)%20%20by%20IncidentNumber%2C%20tostring(AlertIds)%2C%20Title%3C%2FCODE%3E%3C%2FPRE%3E%3CP%3Eto%20make%20sure%20to%20get%20the%20latest%20entry%3F%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-3104665%22%20slang%3D%22en-US%22%3ERe%3A%20How%20to%20get%20the%20real%20count%20of%20incidents%20in%20Microsoft%20Sentinel%3F%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-3104665%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F46875%22%20target%3D%22_blank%22%3E%40Gary%20Bushey%3C%2FA%3E%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EIn%20this%20case%20my%20simple%20example%20was%20a%20simple%20count%20of%20all%20the%20Incidents%20and%20Alerts%20within%20(like%20in%20the%20UI).%20Depending%20on%20the%20Use%20Case%20there%20are%20quite%20a%20few%20other%20columns%20that%20could%20come%20into%20play%2C%20as%20well%20as%20TimeGenerated.%26nbsp%3B%26nbsp%3B%3CSTRONG%3ELastModifiedTime%3C%2FSTRONG%3E%20etc...%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20image-alt%3D%22Clive_Watson_0-1643804894355.png%22%20style%3D%22width%3A%20400px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F344565i89BCFA547A25F0B9%2Fimage-size%2Fmedium%3Fv%3Dv2%26amp%3Bpx%3D400%22%20role%3D%22button%22%20title%3D%22Clive_Watson_0-1643804894355.png%22%20alt%3D%22Clive_Watson_0-1643804894355.png%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI%20used%20%3CSTRONG%3Earg_max%3C%2FSTRONG%3E%20for%20a%20similar%20answer%20last%20week%3A%26nbsp%3B%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fmicrosoft-sentinel%2Fsearch-incidents-for-entries-from-an-ip-range%2Fm-p%2F3072435%22%20target%3D%22_blank%22%3ESearch%20Incidents%20for%20entries%20from%20an%20IP%20Range%20-%20Microsoft%20Tech%20Community%3C%2FA%3E%3C%2FP%3E%3CP%3EI%20have%20a%20few%20versions%20(code%20templates)%20and%20just%20happened%20to%20pick%20one%20that%20didn't%20do%20arg_max%20this%20time.%26nbsp%3B%20I%20think%20of%20this%20forum%20and%20my%20replies%20in%20the%20context%20of%26nbsp%3B%20'teach%20a%20person%20to%20fish'%20rather%20than%20cooking%20them%20the%20meal%20and%20feeding%20them%20the%20final%20result.%26nbsp%3B%26nbsp%3B%3CBR%20%2F%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E
Occasional Contributor

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. 1.PNG2.PNG3.PNG

4 Replies
best response confirmed by zaylinhtun (Occasional Contributor)
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

Clive_Watson_0-1643793236614.png

 

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



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

@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?

@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...

 

Clive_Watson_0-1643804894355.png

 

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.