PinchasiShay-0466 avatar image
0 Votes"
PinchasiShay-0466 asked PinchasiShay-0466 commented

Complex query which track multipul process and VMs

Hey All,

I would like to know if it is possible to make this query below more advanced which will bring results of multiple process and it will query multiple VMs on one query?(this query tirgger an alert when the process is down )

 let process_tbl = datatable (computer: string, process: string, process_count: int) 
     [ "<Virtual machine name>", "bin/<process name>", 1, ]; 
       //Extract distinct list of computers 
     let comps = process_tbl | summarize by computer; 
       //Extract distinct list of process names 
     let procs = process_tbl | summarize by process; 
       //Extract the detailed process info from VMProcess Table 
       //that matches the multiple processes and multiple machines as defined in the process_tbl 
       //VMProcess collects live process information every 1hr but also catches a newly started process within 5 mins 
     let vm_procs = VMProcess 
     | extend process_id = tostring(Process) 
     | where TimeGenerated > ago(60m) 
     | where Computer in (comps) and CommandLine has_any (procs) 
     | project process_id, Computer, CommandLine, FirstPid, TimeGenerated, ExecutablePath 
     | order by TimeGenerated desc, CommandLine 
     | summarize arg_max(TimeGenerated, *) by CommandLine; 
       //Get the Live process Heartbeat data from the InsightsMetrics which is refreshed every min. 
     let foo = InsightsMetrics 
     | where Name == "Heartbeat" 
     | where Namespace == "Computer" 
     | where Origin == "" 
     | where TimeGenerated > ago(3m) 
     | where Computer in (comps) 
     | extend processObj = parse_json(Tags) 
     | extend process_id = parse_json(tostring(processObj.[""])) 
     | mv-expand process_id 
     | distinct tostring(process_id), Computer, TimeGenerated; 
       //Putting it all together 
       //Check for processes that are common and unique in VM Process and Heartbeat table 
     | join kind=leftanti (foo) on process_id, Computer 
     | summarize by process_id, Computer, CommandLine, FirstPid, TimeGenerated, ExecutablePath


· 2
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

Hi @PinchasiShay-0466,

It might be possible if you go with dynamic rather than string in the inital process_tbl datatable and then by using mv-expand / mv-apply operator so that we could expand the array and then apply the dynamic-property accessors foreach record and continue from there the next part of the query to check for multiple processes and VMs.

0 Votes 0 ·

Hi @tbgangav-MSFT
Thank you for the quick replay, Is it possible to take my example and to edit it by your suggestion?

0 Votes 0 ·

0 Answers