May 25 2020 09:37 AM - edited May 25 2020 12:53 PM
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
Jun 14 2020 06:20 AM
@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.
Jun 15 2020 12:39 AM
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.
Jun 15 2020 02:02 AM
@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.
Jun 17 2020 07:22 AM
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
Oct 07 2020 08:35 PM
@akefallonitis hello akefallonitis I have same problem. If you are successful, can you share your query?
Apr 20 2021 06:57 PM
Apr 21 2021 03:53 AM
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
Apr 21 2021 06:12 AM
Excellent answer @CliveWatson.
THANK YOU!
Jan 03 2023 01:32 PM
May 19 2023 07:47 AM
Not to necro an ancient post, but this seems to be the most prominent page talking about this.
Here's the solution I came up with: load the data into a table (or return it from a function). Then join as leftouter on the dataset.
let theAlertName = "Some Alert in SecurityAlert";
let days = 1d;
let Entities_File = SecurityAlert
| where TimeGenerated > ago(days)
| where AlertName has theAlertName
| extend Entities = iff(isempty(Entities), todynamic('[{"dummy" : ""}]'), todynamic(Entities))
| mv-apply Entities on (
where Entities.Type == "file" //and isnotempty(Entities.ParentProcess)
| extend File_Directory_ = tostring(Entities.Directory)
| extend File_FileName_ = tostring(Entities.Name)
| extend File_Hash_MD5_ = tostring(Entities.ImageFile.FileHashes[1].Value)
| extend File_Hash_SHA1_ = tostring(Entities.ImageFile.FileHashes[0].Value)
)
| project SystemAlertId, File_Directory_, File_FileName_, File_Hash_MD5_, File_Hash_SHA1_;
SecurityAlert
| where TimeGenerated > ago(days)
| where AlertName == theAlertName and Status == 'New'
| join kind=leftouter Entities_File on SystemAlertId
| order by SystemAlertId desc
You can then do the same with other entity types, for example to get user-related entity information, substitute this instead:
| mv-apply Entities on (
where Entities.Type == "account"
| extend ActorName_ = tostring(Entities.Name)
| extend ActorDnsDomain_ = tostring(Entities.DnsDomain)
| extend ActorSid_ = tostring(Entities.Sid)
)
| project SystemAlertId, ActorName_, ActorDnsDomain_, ActorSid_
When using a method like this, it's a good way to pull out all related entities for creating an incident. If there are more than one users or files or processes, they should get included in the incident graph this way...