Expanded Entities Combined in one alert/incident

%3CLINGO-SUB%20id%3D%22lingo-sub-1416356%22%20slang%3D%22en-US%22%3EExpanded%20Entities%20Combined%20in%20one%20alert%2Fincident%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1416356%22%20slang%3D%22en-US%22%3E%3CP%3EHi%2C%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3BI%20am%20trying%20to%20figure%20out%20how%20the%20default%20%3CSTRONG%3ECreate%20incidents%20based%20on%20Microsoft%20Defender%20Advanced%20Threat%20Protection%20alerts%20%3C%2FSTRONG%3Eworks%20with%20entities%20expanding%20them%20and%20correlated%20them%20in%20one%20incident.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3BSo%20i%20am%20trying%20to%20reproduce%20it%20by%20enabling%20a%20%3CSTRONG%3Escheduled%20query%20rule%3C%2FSTRONG%3E%20which%20expands%20all%20the%20entities%20of%20a%20MDATP%20alert%20using%20something%20similar%20to%20this%3A%3C%2FP%3E%3CP%3E%26nbsp%3B%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CPRE%20class%3D%22lia-code-sample%20language-applescript%22%3E%3CCODE%3ESecurityAlert%0A%7C%20where%20ProviderName%20%3D%3D%20'MDATP'%0A%7C%20extend%20Entities%20%3D%20iff(isempty(Entities)%2C%20todynamic('%5B%7B%22dummy%22%20%3A%20%22%22%7D%5D')%2C%20todynamic(Entities))%0A%7C%20mvexpand%20Entities%0A%7C%20evaluate%20bag_unpack(Entities)%20%3C%2FCODE%3E%3C%2FPRE%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3Eand%20map%20the%20fields%20like%3A%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CPRE%20class%3D%22lia-code-sample%20language-applescript%22%3E%3CCODE%3E%7C%20extend%20HostCustomEntity%20%3D%20iif(EntityType%20%3D%3D%20'host'%2C%20EntityHostName%2C%20'')%0A%7C%20extend%20IPCustomEntity%20%3D%20%20%20iif(EntityType%20%3D%3D%20'ip'%2C%20EntityAddress%2C%20'')%0A%7C%20extend%20AccountCustomEntity%20%3D%20iif(EntityType%20%3D%3D%20'account'%2C%20EntityAccount%2C%20'')%0A%7C%20extend%20URLCustomEntity%20%3D%20iif(EntityType%20%3D%3D%20'url'%2C%20EntityUrl%2C%20'')%3C%2FCODE%3E%3C%2FPRE%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EBut%20it%20does%20not%20seem%20to%20work%20as%20the%20expansion%20leads%20to%20multiple%20events%20expanded%20per%20type.%3C%2FP%3E%3CP%3EIf%20i%20use%20%3A%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CPRE%20class%3D%22lia-code-sample%20language-applescript%22%3E%3CCODE%3E%7C%20summarize%20arg_max(TimeGenerated%2C%20*)%20by%20SystemAlertId%3C%2FCODE%3E%3C%2FPRE%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI%20lose%20all%20the%20expanded%20info.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EDoes%20anyone%20knows%20how%20to%20use%20this%20correctly%20to%20combine%20and%20created%20a%20%3CSTRONG%3Eschedule%20query%20rule%3C%2FSTRONG%3E%20that%20will%20create%20an%20incident%20with%20all%20the%20Entities%20extracted%20from%20one%20SystemAlertId%3F%20Is%20there%20a%20way%20to%20auto-expand%20all%20Entities%20using%20KQL%20the%20map%20them%20correctly%20in%20the%20rule%20%3F%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20image-alt%3D%22akefallonitis_0-1590436197931.png%22%20style%3D%22width%3A%20400px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Fgxcuf89792.i.lithium.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F194320iA607BEC2442BAB69%2Fimage-size%2Fmedium%3Fv%3D1.0%26amp%3Bpx%3D400%22%20title%3D%22akefallonitis_0-1590436197931.png%22%20alt%3D%22akefallonitis_0-1590436197931.png%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3ESo%20the%20basic%20result%20of%20auto%20expanding%20the%20entities%20i%20want%20to%20reproduce%20is%20similar%20to%20this%20screenshot%20but%20i%20want%20to%20do%20it%20manually%20with%20a%20scheduled%20query%20rule%20as%20it%20will%20be%20nice%20and%20customizable%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1461639%22%20slang%3D%22en-US%22%3ERe%3A%20Expanded%20Entities%20Combined%20in%20one%20alert%2Fincident%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1461639%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F634199%22%20target%3D%22_blank%22%3E%40akefallonitis%3C%2FA%3E%26nbsp%3B%3A%20the%20fact%20that%20mv-expand%20produced%20multiple%20rows%20should%20not%20matter.%20Each%20generates%20a%20value%20for%20the%20entity%20and%20those%20are%20all%20included%20in%20the%20list%20of%20values%20for%20an%20entity.%26nbsp%3B%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EA%20few%20KQL%20notes%3A%3C%2FP%3E%0A%3CP%3E-%20mvexpand%20should%20be%20replaced%20by%20mv-expand%3C%2FP%3E%0A%3CP%3E-%20You%20can%20use%20case%20instead%20of%20the%20multiple%20iff%3C%2FP%3E%0A%3CP%3E-%20For%20me%20bag_unpack%20did%20not%20work%20since%20one%20of%20the%20dynamic%20fields%20names%20is%20%22Type%22.%20I%20had%20to%20use%20the%20dynamic%20fields%20directly.%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1462716%22%20slang%3D%22en-US%22%3ERe%3A%20Expanded%20Entities%20Combined%20in%20one%20alert%2Fincident%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1462716%22%20slang%3D%22en-US%22%3E%3CP%3EHi%20%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F293879%22%20target%3D%22_blank%22%3E%40Ofer_Shezaf%3C%2FA%3E%26nbsp%3B%20and%20thanks%20for%20your%20response%20and%20feedback.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3ESo%20basically%20the%20answer%20is%20that%20somehow%20auto-expansion%20and%20similar%20results%20to%20the%20built-in%20Azure%20Sentinel%20Analytics%20for%20Microsoft%20Products%20can%20not%20be%20re-produced%20and%20the%20only%20way%20is%20to%20match%20all%20the%20cases%20in%20a%20huge%20KQL%20query.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThat%20is%20my%20workaround%20also%20but%20i%20was%20thinking%20of%20a%20more%20no%20so%20%22hackie%22%20method%20to%20do%20so.%20Probably%20using%20an%20external%20function%20to%20aggregate%20and%20parse%20json%20or%20KQL%20make_set%20could%20also%20be%20used.%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1462861%22%20slang%3D%22en-US%22%3ERe%3A%20Expanded%20Entities%20Combined%20in%20one%20alert%2Fincident%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1462861%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F634199%22%20target%3D%22_blank%22%3E%40akefallonitis%3C%2FA%3E%26nbsp%3B%3A%20I%20may%20have%20mislead%20you.%20I%20tried%20to%20help%20with%20your%20workaround.%20Microsoft%20rules%20automatically%20assign%20all%20entities%2C%20even%20those%20not%20available%20for%20alert%20rules.%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1470801%22%20slang%3D%22en-US%22%3ERe%3A%20Expanded%20Entities%20Combined%20in%20one%20alert%2Fincident%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1470801%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F293879%22%20target%3D%22_blank%22%3E%40Ofer_Shezaf%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EHi%20Ofer%20i%20understand%20the%20point%20of%20your%20comment%20for%20the%20workaround%20and%20thank%20your%20for%20that%2C%20i%20am%20actually%20doing%20something%20similar%20with%20mv-apply%20-%20mv-expand.%3C%2FP%3E%3CP%3EThe%20only%20problem%20is%20to%20correctly%20use%20make_set%20and%20summarize%20so%20i%20can%20extend%20all%20needed%20properties%20by%20SystemAlertId%20so%20i%20can%20write%20a%20generic%20scheduled%20rule%20similar%20to%20the%20Microsoft%20ones%20and%20aggregated%20all%20the%20values%20needed%20in%20one%20result%20for%20all%20MS%20products%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1757052%22%20slang%3D%22en-US%22%3ERe%3A%20Expanded%20Entities%20Combined%20in%20one%20alert%2Fincident%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1757052%22%20slang%3D%22en-US%22%3E%3CP%20data-unlink%3D%22true%22%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F634199%22%20target%3D%22_blank%22%3E%40akefallonitis%3C%2FA%3E%26nbsp%3Bhello%26nbsp%3Bakefallonitis%26nbsp%3BI%20have%20same%20problem.%26nbsp%3BIf%20you%20are%20successful%2C%20can%20you%20share%20your%20query%3F%3C%2FP%3E%3C%2FLINGO-BODY%3E
Contributor

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 ?

 

akefallonitis_0-1590436197931.png

 

 

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.

@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

@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.
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": [] },

@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

 

 

Excellent answer @Clive Watson.

THANK YOU!

:cool::cool::cool::cool::cool: