Forum Discussion

ScottAllison's avatar
ScottAllison
Iron Contributor
Aug 05, 2019
Solved

Heartbeat query, show negative results

Greetings Community,

 

I'm trying to formulate a query whereby I provide a list of servers to check for a heartbeat in the last 6 hours, but I only want to return the servers THAT DO NOT HAVE A RECORD in the Heartbeat table.

 

For example in the following query, "SERVER123" and "SERVER456" are heartbeating, but "SERVER789" has never Heartbeat. How can I get the query below to only spit out "SERVER789" as not having a Heartbeat (or any entry at all for that matter)?

Heartbeat
| project TimeGenerated , Computer 
| where TimeGenerated >= ago(6h)
| where Computer in ("SERVER123","SERVER456","SERVER789")
| summarize arg_max(TimeGenerated, *) by Computer

 

Azure Log Analytics
azure monitor
  • CliveWatson's avatar
    CliveWatson
    Aug 06, 2019

    ScottAllison 

     

    Have a look at this example: Go to Log Analytics and Run Query

     

    You will need to add in your own myList values and make some other minor edits 

    //
// I have filtered on Computers starting with "A" to make the list smaller - remove or edit as required
//
// First, generarte a list of all computers from Heartbeat table into an array
let myServers = toscalar(Heartbeat 
| where Computer startswith "A" 
| distinct Computer
| summarize make_set(Computer));  
// Now define my list of computers as an array
let myList = dynamic(["aFakeComputer","ad-primary-dc.contoso.com","ad-secondary-dc.contoso.com","fakeComputer"]); 
Heartbeat
| where Computer startswith "A" 
| distinct Computer
// compare the two arrays, show only values that are not in the 2nd array 
| project ComputersNotInHeatBeat = set_difference(myList, myServers)
| distinct tostring(ComputersNotInHeatBeat)

       

    Results, look like this:

     

    ComputersNotInHeatBeat
    ["aFakeComputer","fakeComputer"]

     

     

8 Replies

  • Meir_Mendelovich's avatar
    Meir_Mendelovich
    Icon for Microsoft rankMicrosoft
    Aug 11, 2019

    Hi,

     

    The most performant way would be to utilize the built-in join operator in its anti flavors:

     

    let MyCompList = datatable(Computer:string)
    [
    "SERVER123",
    "SERVER456",
    "SERVER789"
    ];
    MyCompList
    | join kind= leftanti (
        Heartbeat | where TimeGenerated > ago(6h) | distinct Computer   
    ) on Computer 

     

    Thanks,

    Meir

    ScottAllison CliveWatson 

     

  • CliveWatson's avatar
    CliveWatson
    Former Employee
    Aug 06, 2019

    ScottAllison 

     

    Have a look at this example: Go to Log Analytics and Run Query

     

    You will need to add in your own myList values and make some other minor edits 

    //
// I have filtered on Computers starting with "A" to make the list smaller - remove or edit as required
//
// First, generarte a list of all computers from Heartbeat table into an array
let myServers = toscalar(Heartbeat 
| where Computer startswith "A" 
| distinct Computer
| summarize make_set(Computer));  
// Now define my list of computers as an array
let myList = dynamic(["aFakeComputer","ad-primary-dc.contoso.com","ad-secondary-dc.contoso.com","fakeComputer"]); 
Heartbeat
| where Computer startswith "A" 
| distinct Computer
// compare the two arrays, show only values that are not in the 2nd array 
| project ComputersNotInHeatBeat = set_difference(myList, myServers)
| distinct tostring(ComputersNotInHeatBeat)

       

    Results, look like this:

     

    ComputersNotInHeatBeat
    ["aFakeComputer","fakeComputer"]

     

     

    • ScottAllison's avatar
      ScottAllison
      Iron Contributor
      Aug 06, 2019

      CliveWatson This query is near perfect, but I need one final piece that I'm struggling with... taking that final array and converting it to a column with all the values:

      | distinct tostring(ComputersNotInHeatBeat)

      I've tried using "parse", "parse_json", "extractjson"--I can't seem to find a way to spit the individual results in the array to a column. 

      • CliveWatson's avatar
        CliveWatson
        Former Employee
        Aug 06, 2019

        ScottAllison 

         

        I meant to add, sending an array to a row (rather than column) is easy

        Go to Log Analytics and Run Query

         

        //
// I have filtered on Computers starting with "A" to make the list smaller - remove or edit as required
//
// First, generarte a list of all computers from Heartbeat table into an array
let myServers = toscalar(Heartbeat 
| where Computer startswith "A" 
| distinct Computer
| summarize make_set(Computer));  
// Now define my list of computers as an array
let myList = dynamic(["aFakeComputer","ad-primary-dc.contoso.com","ad-secondary-dc.contoso.com","fakeComputer"]); 
Heartbeat
| where Computer startswith "A" 
| distinct Computer
// compare the two arrays, show only values that are not in the 2nd array 
| extend  ComputersNotInHeatBeat = set_difference(myList, myServers)
| mvexpand todynamic(ComputersNotInHeatBeat)
| distinct tostring(ComputersNotInHeatBeat)

         

        result

         

        ComputersNotInHeatBeat
        aFakeComputer
        fakeComputer

         

Resources