Oct 06 2020 07:49 AM
Oct 06 2020 09:02 AM
Would mv-expand help?
SigninLogs
| project ConditionalAccessPolicies
| mv-expand ConditionalAccessPolicies
| summarize by tostring(ConditionalAccessPolicies.displayName)
Simple example above, and the docs:https://docs.microsoft.com/en-us/azure/data-explorer/kusto/query/mvexpandoperator
Oct 07 2020 04:54 AM
Thank you Clive for the suggestion, I believe the mv-extend will help with structuring the display of the results but unfortunately not with initial issue I am trying to overcome.
When looking at the call records where type = groupCall there is more then 2 participants under participants_s, these are represented by integers [#] for each participant. What I am hoping to do is be able to query the Call records and parse the unknown number of partiicpants using a wildcard or loop condition if possible. As you can see below an extend has been used for each individual participant to extract and map there username to a field, but this is only because I happen to know there were 4 participants in this case. In all other cases the number would be unknown.
TEAMSGraphCallRecords_CL
| extend caller0 = parse_json(tostring(parse_json(participants_s)[0].user)).displayName
| extend caller1 = parse_json(tostring(parse_json(participants_s)[1].user)).displayName
| extend caller2 = parse_json(tostring(parse_json(participants_s)[2].user)).displayName
| extend caller3 = parse_json(tostring(parse_json(participants_s)[3].user)).displayName
Oct 07 2020 06:31 AM
So you have this today (using our demo data):
Go to Log Analytics and run query
SecurityAlert
| project Entities
| extend Name_ = tostring(parse_json(Entities)[1].Name)
| extend Name_2 = tostring(parse_json(Entities)[2].Name)
| extend Name_3 = tostring(parse_json(Entities)[3].Name)
| extend Name_4 = tostring(parse_json(Entities)[4].Name)
| project Name_, Name_2, Name_3, Name_4
Result
Name_ | Name_2 | Name_3 | Name_4 |
---|---|---|---|
cmd.exe | Victim00$ | mimikatz.exe | |
jeleonar |
|
How about?
SecurityAlert
| project Entities
| mv-expand todynamic(Entities)
| project Entities.Name
| where isnotempty(Entities_Name)
| serialize
| extend caller_ = strcat("caller ",row_number(),"="), Entities_Name
| project strcat(caller_,Entities_Name)
Result
Go to Log Analytics and run query
Column1 |
---|
caller 1=cmd.exe |
caller 2=Victim00$ |
caller 3=mimikatz.exe |
caller 4=jeleonar |