[KQL] Entity extraction for Azure Security Alerts

security-alert-entity-extraction.kql

// Extract the entities for high severity AATP alerts
SecurityAlert
| where ProviderName == "Azure Advanced Threat Protection"
| where AlertSeverity == "High"
| extend Ent = parse_json(Entities)
| mvexpand Ent
| extend AccountCustomEntity = tostring(Ent.Name)
| extend HostCustomEntity = tostring(Ent.HostName)
| extend IPCustomEntity = tostring(Ent.Address)

//
// Aggregate entities to roll up and de-dupe alerts
SecurityAlert
// Converting Entities into dynamic data type and use mv-expand to unpack the array
| extend EntitiesDynamicArray = parse_json(Entities) 
| mv-expand EntitiesDynamicArray
// Parsing relevant entity column extract hostname and IP address
| extend EntityType = tostring(parse_json(EntitiesDynamicArray).Type), EntityAddress = tostring(EntitiesDynamicArray.Address), EntityHostName = tostring(EntitiesDynamicArray.HostName), EntityAccountName = tostring(EntitiesDynamicArray.Name)
| extend HostName = iif(EntityType == 'host', EntityHostName, '')
| extend IPAddress = iif(EntityType == 'ip', EntityAddress, '')
| extend Account = iif(EntityType == 'account', EntityAccountName, '')
| where isnotempty(IPAddress) or isnotempty(Account) or isnotempty(HostName)
// Aggregating by Time and Alert to consolidate all entities per Alert
| summarize AccountList=make_set(Account), IPList = make_set(IPAddress), HostList = make_set(HostName) by TimeGenerated, AlertName