The query behind the Sentinel Open | New | Active incident widget

Laszlo Pal 20

Hi,

We are trying to figure out what query produces the following numbers in Sentinel

User's image

We've been trying to produce the same numbers using the SecurityIncident and SecurityAlert table, but the number of incidents are much less than showed here.

I'm thinking how this widget interprets an incident which can have multiple alerts, so maybe it counts -incorrectly- each alert as a separated incident.

Did everyone figured this out already?

Thx

Givary-MSFT 28,321 Reputation points Microsoft Employee

2024-04-23T03:45:25.5633333+00:00

@Laszlo Pal Just checking in to see if below information was helpful. If you have any further updates on this issue, please feel free to post back.

1 answer

Clive Watson 5,716 MVP

This is the closest I've been able to achieve. I do get some only drift in my Sentinel which shows a few incidents closed in the UI but showing as Active in the query.

Note, the UI does look back longer for SecurityAlerts (hence the 180days)

SecurityIncident
| where TimeGenerated > ago(1d)
| where Status in('New','Active')
| extend Alerts = extract("\\[(.*?)\\]", 1, tostring(AlertIds))
| mv-expand AlertIds to typeof(string), Labels to typeof(string), Comments to typeof(string), AdditionalData to typeof(string)
| join kind=inner
(
    SecurityAlert
    | where TimeGenerated > ago(180d)   
    ) on $right.SystemAlertId == $left.AlertIds
    | summarize AlertCount=dcount(AlertIds),
                arg_max
                (
                 TimeGenerated, *)
                by IncidentNumber

Laszlo Pal 20 Reputation points

2024-04-23T07:06:07.7833333+00:00

Thank you. It is even stranger than my simple query. When I query the last 30 days, the result is close to the one displayed on the builtin widget (1893 vs. 2400) but when I increase the time range for both to 114 days (or anything bigger) it still shows 1893 :)
Andrew Blumhardt 9,496 Reputation points Microsoft Employee

2024-04-30T03:26:26.4433333+00:00
Maybe something simple like this:

SecurityIncident | summarize arg_max(TimeGenerated, *) by IncidentNumber | summarize count() by Status
Laszlo Pal 20 Reputation points

2024-04-30T06:32:31.88+00:00

No, it is not simple like this. The query above produces ~3K and the builtin widget still shows 9.4K. I have a meeting scheduled with MS this Thursday, so I hope I'll have the answer :)
Givary-MSFT 28,321 Reputation points Microsoft Employee

2024-04-30T08:51:30.61+00:00

@Laszlo Pal Please help me with the case number to track this internally.

Clive Watson 5,716 MVP

Can you try this one? I had the wrong join in the original query.

SecurityIncident


SecurityIncident
| where TimeGenerated > ago(1d)  
    | extend Alerts = extract("\\[(.*?)\\]", 1, tostring(AlertIds))
    | mv-expand AlertIds to typeof(string), Labels to typeof(string), Comments to typeof(string), AdditionalData to typeof(string)
    | join kind=leftouter
    (
        SecurityAlert
        | where TimeGenerated > ago(90d)   
        ) on $right.SystemAlertId == $left.AlertIds
        | summarize AlertCount=dcountif(AlertIds, isnotempty(AlertIds)),
                    arg_max
                    (
                     TimeGenerated, *
                    )
                    by IncidentNumber

Laszlo Pal 20

Modified like this. Result is 2903, in builtin dashboard it is 9.4K, so it is still a huge difference. I'll have a meeting later today with MS engineer, so I hope we can figure out the reason of the difference

SecurityIncident
| where TimeGenerated > ago(365d)  
    | extend Alerts = extract("\\[(.*?)\\]", 1, tostring(AlertIds))
    | mv-expand AlertIds to typeof(string), Labels to typeof(string), Comments to typeof(string), AdditionalData to typeof(string)
    | join kind=leftouter
    (
        SecurityAlert
        | where TimeGenerated > ago(365d)   
        ) on $right.SystemAlertId == $left.AlertIds
        | summarize AlertCount=dcountif(AlertIds, isnotempty(AlertIds)),
                    arg_max
                    (
                     TimeGenerated, *
                    )
                    by IncidentNumber