Forum Discussion
Query level parsing numerous call participants
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_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 |