Defender XDR threat hunting - can you join SecurityIncident and AlertEvidence?

Question

I would have expected I could join SecurityIncident and AlertEvidence on the AlertIDs and AlertID fields.

I appreciate AlertIDs is an array but regardless I'm not finding that any of the alert IDs in the array match any of the AlertID values in the AlertEvidence table.

It it possible to join these 2 tables and if not, how can I query down from the Incident to the alerts?

Thanks!

Answer 1

Hi David, One way but its not at all scalable (and could be inaccurate) is to find and map an entity

SecurityIncident
| 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
    ) on $left.AlertIds == $right.SystemAlertId 
    | summarize AlertCount=dcount(AlertIds),
                arg_max
                (
                 TimeGenerated, *
                )
                by IncidentNumber
// start to map to individual entities, as an example I'm using IP                
| mv-expand todynamic(Entities) 
| where Entities.Type == "ip"
| extend ip_ = trim(@"[^\w]+",tostring(Entities.Address))
// now join to the IP in the Evidence Table 
| join //kind=inner
(
AlertEvidence
| where EntityType =~ "Ip"
| extend ip_ = tostring(RemoteIP)
) on ip_

Answer 2

@David Broggy Checked with my team regarding your ask, and here’s the suggested approach you can try:

You’ll need to include the SecurityAlert table to extract the VendorOriginalId.

The overall flow would be:

Retrieve SystemAlertIds from the SecurityIncident table

Use those SystemAlertIds to get the VendorOriginalId from the SecurityAlert table

Then, correlate the VendorOriginalId with the AlertEvidence table

Let me know if this helps or if you have any questions—feel free to reach out!