Forum Discussion
How to correlate Security Alert Entities further with a WorkList
Hi there, the best way to extract the individual entities from alerts within the SecurityAlerts table is using the mv-expand operator. There are some examples here - https://github.com/reprise99/Sentinel-Queries/tree/main/Security%20Alert
In your example there if you wanted to retrieve the IP address, you could do
| extend x = todynamic(Entities) | mv-expand x | parse-where x with * '$id":"4","' IPAddress '","Type' *That would create you a new column called IPAddress with everything between $id":"4"," and ","Type
You can then map them to other tables or watchlists etc like normal
Hi there, the best way to extract the individual entities from alerts within the SecurityAlerts table is using the mv-expand operator. There are some examples here - https://github.com/reprise99/Sentinel-Queries/tree/main/Security%20Alert
In your example there if you wanted to retrieve the IP address, you could do
| extend x = todynamic(Entities)
| mv-expand x
| parse-where x with * '$id":"4","' IPAddress '","Type' *That would create you a new column called IPAddress with everything between $id":"4"," and ","Type
You can then map them to other tables or watchlists etc like normal
- Thanks for the pointer, got it working:
let domain = "@";
let watchlist = (_GetWatchlist("SuspiciousUser") | project SearchKey);
SecurityAlert
| extend EntitiesDynamicArray=parse_json(Entities) | mvexpand EntitiesDynamicArray
| extend Entitytype = tostring(parse_json(EntitiesDynamicArray).Type)
| where Entitytype == "account"
| extend username=tostring(parse_json(EntitiesDynamicArray).Name)
| extend UPN=tostring(parse_json(EntitiesDynamicArray).UPNSuffix)
| extend useraccount = strcat(username, domain, UPN)
| where useraccount in (watchlist)
| project TimeGenerated, DisplayName, useraccount, Entities
