Expanded Entities Combined in one alert/incident




 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:







| 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

8 Replies

@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.

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.

@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.



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

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

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.
[ { "$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": "", "LastIpAddress": "", "Tags": [] },



What about?


| 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



Excellent answer @CliveWatson.