Expanded Entities Combined in one alert/incident

I know this is an old thread but I wanted to put my solution in case anyone comes across it. Below I've put the query I created that allows you to extract all nested Entities no matter how far deep into a common top level column. The values can then be accessed by calling the top level field dot subfield, like so "PwnedEntities.Name" or "PwnedEntities.City", and so on.

You can change the top level field that they go to if you wish, and you can access all entities by their key name. I put this query on my github also, link for that is here: https://github.com/jostuffl/AzureSentinel_Stuff/blob/main/Queries/SecurityAlert_ParsedEntities.txt


// Created by Jonathon Stufflebeam - CSA-E @ Microsoft
// I've tried my best to make this query flexible enough to parse every Entity Type,
// however it may miss some values when parsing (whether because the regex doesn't match
// or because new entity values have been created.
// If you find any issues with this query please let me know
// This is query parses out the Entities field in the Security Alert table
SecurityAlert
| extend Entities = replace_regex(Entities,',("[a-zA-Z0-9]+":{)',",")
| extend Entities= replace_regex(Entities,@'[{}]+',"")
| extend Entities = replace_string(Entities,"$","")
| extend Entities = replace_string(Entities,", ",",")
| extend Entities = replace_string(Entities,'("id":"\\d",)+',",")
| extend Entities = replace_regex(Entities,',"([^"]+:)"[^"]+.":"[^"]','')
| extend Entities = todynamic(Entities)
| mv-apply Entities on (
summarize Entities= strcat_array(make_set(Entities), ", ")
)
| extend Entities = replace_regex(Entities,'[][]',"")
| mv-apply Entities = todynamic(Entities) on (
extend e = extract_all('(?:")([a-zA-Z0-9]+)(?:"):',tostring(Entities))
| extend w = extract_all(':(?:"{0,1})([^",]+)',tostring(Entities))
)
| mv-apply with_itemindex = i key = todynamic(e) to typeof(string) on (
summarize PwnedEntities = make_bag(pack(key, w[i]))
)
| project-away Entities, e, w