Expanded Entities Combined in one alert/incident

[akefallonitis]

Hi,

 I am trying to figure out how the default Create incidents based on Microsoft Defender Advanced Threat Protection alerts works with entities expanding them and correlated them in one incident.

 So i am trying to reproduce it by enabling a scheduled query rule which expands all the entities of a MDATP alert using something similar to this:

SecurityAlert
| where ProviderName == 'MDATP'
| extend Entities = iff(isempty(Entities), todynamic('[{"dummy" : ""}]'), todynamic(Entities))
| mvexpand Entities
| evaluate bag_unpack(Entities) 

and map the fields like:

| extend HostCustomEntity = iif(EntityType == 'host', EntityHostName, '')
| extend IPCustomEntity =   iif(EntityType == 'ip', EntityAddress, '')
| extend AccountCustomEntity = iif(EntityType == 'account', EntityAccount, '')
| extend URLCustomEntity = iif(EntityType == 'url', EntityUrl, '')

But it does not seem to work as the expansion leads to multiple events expanded per type.

If i use :

| summarize arg_max(TimeGenerated, *) by SystemAlertId

I lose all the expanded info.

Does anyone knows how to use this correctly to combine and created a schedule query rule that will create an incident with all the Entities extracted from one SystemAlertId? Is there a way to auto-expand all Entities using KQL the map them correctly in the rule ?

So the basic result of auto expanding the entities i want to reproduce is similar to this screenshot but i want to do it manually with a scheduled query rule as it will be nice and customizable

[#2]

@akefallonitis : the fact that mv-expand produced multiple rows should not matter. Each generates a value for the entity and those are all included in the list of values for an entity. 

A few KQL notes:

- mvexpand should be replaced by mv-expand

- You can use case instead of the multiple iff

- For me bag_unpack did not work since one of the dynamic fields names is "Type". I had to use the dynamic fields directly.

[#3]

Hi @Ofer_Shezaf  and thanks for your response and feedback.

So basically the answer is that somehow auto-expansion and similar results to the built-in Azure Sentinel Analytics for Microsoft Products can not be re-produced and the only way is to match all the cases in a huge KQL query.

That is my workaround also but i was thinking of a more no so "hackie" method to do so. Probably using an external function to aggregate and parse json or KQL make_set could also be used.

[#4]

@akefallonitis : I may have mislead you. I tried to help with your workaround. Microsoft rules automatically assign all entities, even those not available for alert rules.

[#5]

@Ofer_Shezaf 

Hi Ofer i understand the point of your comment for the workaround and thank your for that, i am actually doing something similar with mv-apply - mv-expand.

The only problem is to correctly use make_set and summarize so i can extend all needed properties by SystemAlertId so i can write a generic scheduled rule similar to the Microsoft ones and aggregated all the values needed in one result for all MS products

[#6]

@akefallonitis hello akefallonitis I have same problem. If you are successful, can you share your query?

[#7]

Hi @Ofer_Shezaf or anyone,
I'm not seeing an answer here on how to extract values from the Entities field.
I can do it with regex:
|extend MCASDomainName= extract("DnsDomai[^\"]+\"\\: \"([^\"]+)\",",1,Entities)
But I'd love to see an example of this with mv-expand.
Here's an example Entities string.
My challenge is with fields that may show up in any of the array fields.
Entities
[ { "$id": "4", "DnsDomain": "google.com", "HostName": "bob", "OSFamily": "Windows", "OSVersion": "1909", "Type": "host", "MdatpDeviceId": "abcde", "FQDN": "google.com", "AadDeviceId": "abcde", "RiskScore": "Informational", "HealthStatus": "Active", "LastSeen": "2021-04-19T22:11:06.7753511Z", "LastExternalIpAddress": "172.74.8.164", "LastIpAddress": "192.168.86.31", "Tags": [] },

[#8]

@bobsyouruncle 

What about?

SecurityAlert
| where ProviderName == 'MDATP'
| extend Entities = iff(isempty(Entities), todynamic('[{"dummy" : ""}]'), todynamic(Entities))
| mv-expand Entities
| extend id_ = tostring(Entities.["$id"]),
         DnsDomain_ = tostring(Entities.DnsDomain),
         FQDN_ = tostring(Entities.FQDN),
         HostName_ = tostring(Entities.HostName),
         LastExternalIpAddress_ = tostring(Entities.LastExternalIpAddress)
// add more here
| summarize arg_max(TimeGenerated,*) by SystemAlertId
// optional syntax to just show the expanded columsn and SystemAlertId
| project-keep *_, SystemAlertId

[#9]

Excellent answer @CliveWatson.

THANK YOU!

[#10]

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