Forum Discussion

TheriumSec1940's avatar
TheriumSec1940
Copper Contributor
Oct 06, 2020

Query level parsing numerous call participants

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
 
 
    • TheriumSec's avatar
      TheriumSec
      Copper Contributor
      Oct 07, 2020

      CliveWatson 

      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

      • CliveWatson's avatar
        CliveWatson
        Icon for Microsoft rankMicrosoft
        Oct 07, 2020

        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

         

Resources