Forum Discussion
Expanded Entities Combined in one alert/incident
Microsoftakefallonitis : 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.
- Ofer_Shezaf
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_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": [] },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
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?
