Obtaining a Grouped Microsoft Sentinel Incident's Raw Events

I stumbled upon a question on the great Microsoft Tech Community board for Microsoft Sentinel.

Skip straight to the Solution section.

The question is very valid! Wouldn't it be great if we could have an overview of the raw events that lead to the alerts in an Incident? The standard Incident Overview interface does not support this functionality (and for good reason, as I'll discuss below). Luckily, we can implement this functionality ourselves using KQL.

Mechanism

To achieve this, we can utilize a property of the ExtendedProperties column of the SecurityAlert table. In the below example, you can see that the Query property contains some information relating the the original query that is responsible for triggering the alert.

What we see here is the mechanism that the official Incident Overview uses to display the Events for individual alerts. When the alert triggered, the analytic rule's results were packed and then compressed using zlib. The result is then encoded as base64. And lastly, a query is provided that will return decode, uncompress, and unpack the results when ran. Great! But how do we do this at scale?

Solution

Using a KQL query, we can find all the SecurityAlert events that correspond to the Security Incident that we are interested in, and find all the base64 encoded results at once.

The following is provided as a proof of concept and should be adapted by the user to fit their needs. The extraction methods used are opportunistic and may break when unexpected situations are encountered.

Users finding value in this solution should strongly consider to build on the mechanism used in the below example, rather than implementing the example as a complete solution.

let lookback = 90d;
SecurityIncident
| where TimeGenerated > ago(lookback) and IncidentNumber == 122
| summarize arg_max(TimeGenerated, AlertIds) by IncidentName
| mv-expand AlertId = AlertIds to typeof(string)
| join kind=inner (SecurityAlert
    | where TimeGenerated > ago(lookback * 2)
    | summarize arg_max(TimeGenerated, ExtendedProperties) by SystemAlertId
    | project AlertResults = tostring(parse_json(ExtendedProperties).Query), SystemAlertId)
    on $left.AlertId == $right.SystemAlertId
| project compressedRec = parse_json(replace_string(replace_string(replace_string(AlertResults, '// might contain sensitive data\nlet alertedEvent = datatable(compressedRec: string)\n', ''), '\n| extend raw = todynamic(zlib_decompress_from_base64_string(compressedRec)) | evaluate bag_unpack(raw) | project-away compressedRec;\nalertedEvent', ''), "'", '"'))
| where compressedRec startswith '["' // This will filter out alerts for which extraction failed, possibly because alert event grouping was enabled.
| mv-expand compressedRec to typeof(string)
| project raw = parse_json(zlib_decompress_from_base64_string(compressedRec))
| evaluate bag_unpack(raw)

If you provide the correct IncidentNumber to this query, it will find and display all the related raw events!

Warnings

Unfortunately, not all SecurityAlerts will contain a usable Query property in the ExtendedProperties column. Specifically, alerts generated by non-Sentinel providers, will not have this value. Note that this includes Microsoft Defender XDR.

Similarly, I've noticed that using "Event grouping" in Analytics Rules will break this functionality.

Beware of any other issues :).