Forum Discussion
Re: Query level parsing numerous call participants
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
2 Replies
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
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_4Result
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)
ResultGo to Log Analytics and run query
Column1 caller 1=cmd.exe caller 2=Victim00$ caller 3=mimikatz.exe caller 4=jeleonar
