Query level parsing numerous call participants

Occasional Visitor
Hello
 
Short of properly parsing the data prior to hitting Log Analytics/Sentinel (That's the next step) would anyone happen to know how to leverage a wildcard of sorts to ensure no matter how many participants are on a call it can be queried without having to use the logic as seen below and so that all participants are written to the same field - so one parser line to cover any number of potential participants. I know I can move the data to a merged field but it is the integer [0] [1] [2], etc. representation of the participants that causes issues with queries.
 
| 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
 
caller0
 
caller1
 
 
3 Replies

@TheriumSec1940 

 

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  

@Clive Watson 

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

@TheriumSec 


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