Query level parsing numerous call participants

%3CLINGO-SUB%20id%3D%22lingo-sub-1749536%22%20slang%3D%22en-US%22%3EQuery%20level%20parsing%20numerous%20call%20participants%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1749536%22%20slang%3D%22en-US%22%3E%3CDIV%3E%3CDIV%3E%3CSPAN%3EHello%3C%2FSPAN%3E%3C%2FDIV%3E%3CDIV%3E%26nbsp%3B%3C%2FDIV%3E%3CDIV%3E%3CSPAN%3EShort%20of%20properly%20parsing%20the%20data%20prior%26nbsp%3Bto%20hitting%20Log%20Analytics%2FSentinel%20(That's%20the%20next%20step)%20would%20anyone%20happen%20to%20know%20how%20to%20leverage%20a%20wildcard%20of%20sorts%20to%20ensure%20no%20matter%20how%20many%20participants%20are%20on%20a%20call%20it%20can%20be%20queried%20without%20having%20to%20use%20the%20logic%20as%20seen%20below%20and%20so%20that%20all%20participants%26nbsp%3Bare%20written%20to%20the%20same%20field%20-%20so%20one%20parser%20line%20to%20cover%20any%20number%20of%20potential%20participants.%20I%20know%20I%20can%20move%20the%20data%20to%20a%20merged%20field%20but%20it%20is%20the%20integer%20%5B0%5D%20%5B1%5D%20%5B2%5D%2C%20etc.%20representation%20of%20the%20participants%20that%20causes%20issues%20with%20queries.%3C%2FSPAN%3E%3C%2FDIV%3E%3CDIV%3E%26nbsp%3B%3C%2FDIV%3E%3CDIV%3E%3CSPAN%3E%7C%20%3C%2FSPAN%3E%3CSPAN%3Eextend%3C%2FSPAN%3E%3CSPAN%3E%20caller0%20%3D%20parse_json(tostring(parse_json(participants_s)%5B%3C%2FSPAN%3E%3CSTRONG%3E0%3C%2FSTRONG%3E%3CSPAN%3E%5D.user)).displayName%3C%2FSPAN%3E%3C%2FDIV%3E%3CDIV%3E%3CSPAN%3E%7C%20%3C%2FSPAN%3E%3CSPAN%3Eextend%3C%2FSPAN%3E%3CSPAN%3E%20caller1%20%3D%20parse_json(tostring(parse_json(participants_s)%5B%3C%2FSPAN%3E%3CSTRONG%3E1%3C%2FSTRONG%3E%3CSPAN%3E%5D.user)).displayName%3C%2FSPAN%3E%3C%2FDIV%3E%3CDIV%3E%3CSPAN%3E%7C%20%3C%2FSPAN%3E%3CSPAN%3Eextend%3C%2FSPAN%3E%3CSPAN%3E%20caller2%20%3D%20parse_json(tostring(parse_json(participants_s)%5B%3C%2FSPAN%3E%3CSTRONG%3E2%3C%2FSTRONG%3E%3CSPAN%3E%5D.user)).displayName%3C%2FSPAN%3E%3C%2FDIV%3E%3CDIV%3E%3CSPAN%3E%7C%20%3C%2FSPAN%3E%3CSPAN%3Eextend%3C%2FSPAN%3E%3CSPAN%3E%20caller3%20%3D%20parse_json(tostring(parse_json(participants_s)%5B%3C%2FSPAN%3E%3CSTRONG%3E3%3C%2FSTRONG%3E%3CSPAN%3E%5D.user)).displayName%3C%2FSPAN%3E%3C%2FDIV%3E%3CDIV%3E%26nbsp%3B%3C%2FDIV%3E%3CDIV%3E%3CSPAN%3Ecaller0%3C%2FSPAN%3E%3C%2FDIV%3E%3CDIV%3E%3CSPAN%3E%3CA%20href%3D%22mailto%3Alarrybird%40somecompany.com%22%20target%3D%22_blank%22%20rel%3D%22nofollow%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%22%3Elarrybird%40somecompany.com%3C%2FA%3E%3C%2FSPAN%3E%3C%2FDIV%3E%3CDIV%3E%26nbsp%3B%3C%2FDIV%3E%3CDIV%3E%3CSPAN%3Ecaller1%3C%2FSPAN%3E%3C%2FDIV%3E%3CDIV%3E%3CSPAN%3E%3CA%20href%3D%22mailto%3Ajon.b%40yahoo.ca%22%20target%3D%22_blank%22%20rel%3D%22nofollow%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%22%3Ejon.b%40something.uk%3C%2FA%3E%3C%2FSPAN%3E%3C%2FDIV%3E%3CDIV%3E%26nbsp%3B%3C%2FDIV%3E%3CDIV%3E%26nbsp%3B%3C%2FDIV%3E%3C%2FDIV%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1749783%22%20slang%3D%22en-US%22%3ERe%3A%20Query%20level%20parsing%20numerous%20call%20participants%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1749783%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F787244%22%20target%3D%22_blank%22%3E%40TheriumSec1940%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EWould%20mv-expand%20help%3F%3CBR%20%2F%3E%3CBR%20%2F%3E%3C%2FP%3E%0A%3CPRE%20class%3D%22lia-code-sample%20language-cpp%22%3E%3CCODE%3ESigninLogs%0A%7C%20project%20ConditionalAccessPolicies%0A%7C%20mv-expand%20ConditionalAccessPolicies%0A%7C%20summarize%20by%20tostring(ConditionalAccessPolicies.displayName)%3C%2FCODE%3E%3C%2FPRE%3E%0A%3CP%3ESimple%20example%20above%2C%20and%20the%20docs%3A%3CA%20href%3D%22https%3A%2F%2Fdocs.microsoft.com%2Fen-us%2Fazure%2Fdata-explorer%2Fkusto%2Fquery%2Fmvexpandoperator%22%20target%3D%22_blank%22%20rel%3D%22noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%22%3Ehttps%3A%2F%2Fdocs.microsoft.com%2Fen-us%2Fazure%2Fdata-explorer%2Fkusto%2Fquery%2Fmvexpandoperator%3C%2FA%3E%26nbsp%3B%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1753535%22%20slang%3D%22en-US%22%3ERe%3A%20Query%20level%20parsing%20numerous%20call%20participants%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1753535%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F239477%22%20target%3D%22_blank%22%3E%40Clive%20Watson%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThank%20you%20Clive%20for%20the%20suggestion%2C%20I%20believe%20the%20mv-extend%20will%20help%20with%20structuring%20the%20display%20of%20the%20results%20but%20unfortunately%20not%20with%20initial%20issue%20I%20am%20trying%20to%20overcome.%3C%2FP%3E%3CP%3EWhen%20looking%20at%20the%20call%20records%20where%20type%20%3D%20groupCall%20there%20is%20more%20then%202%20participants%20under%20participants_s%2C%20these%20are%20represented%20by%20integers%20%5B%23%5D%20for%20each%20participant.%20What%20I%20am%20hoping%20to%20do%20is%20be%20able%20to%20query%20the%20Call%20records%20and%20parse%20the%20unknown%20number%20of%20partiicpants%20using%20a%20wildcard%20or%20loop%20condition%20if%20possible.%20As%20you%20can%20see%20below%20an%20extend%20has%20been%20used%20for%20each%20individual%20participant%20to%20extract%20and%20map%20there%20username%20to%20a%20field%2C%20but%20this%20is%20only%20because%20I%20happen%20to%20know%20there%20were%204%20participants%20in%20this%20case.%20In%20all%20other%20cases%20the%20number%20would%20be%20unknown.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3ETEAMSGraphCallRecords_CL%3CBR%20%2F%3E%7C%20extend%20caller0%20%3D%20parse_json(tostring(parse_json(participants_s)%5B0%5D.user)).displayName%3CBR%20%2F%3E%7C%20extend%20caller1%20%3D%20parse_json(tostring(parse_json(participants_s)%5B1%5D.user)).displayName%3CBR%20%2F%3E%7C%20extend%20caller2%20%3D%20parse_json(tostring(parse_json(participants_s)%5B2%5D.user)).displayName%3CBR%20%2F%3E%7C%20extend%20caller3%20%3D%20parse_json(tostring(parse_json(participants_s)%5B3%5D.user)).displayName%3C%2FP%3E%3C%2FLINGO-BODY%3E
Highlighted
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
Highlighted

@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  

Highlighted

@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