SOLVED

Heartbeat query, show negative results

Iron Contributor

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

 

8 Replies
best response confirmed by Scott Allison (Iron Contributor)
Solution

@Scott Allison 

 

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"]

 

 

@CliveWatson Exactly what I was looking for! Thank you!

@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. 

@Scott Allison 

 

You cant split arrays to columns, columns have to be named.  Closest I can do is, adding some named columns - this will probably only work if you have a few expected missing servers?  Add this amended code 

 

// compare the two arrays, show only values that are not in the 2nd array 
| extend  ComputersNotInHeatBeat = set_difference(myList, myServers)
| distinct tostring(ComputersNotInHeatBeat)
| extend txtArray = split(ComputersNotInHeatBeat,",")
| extend c1 = trim(@"[^\w]+", tostring(txtArray[0])) 
| extend c2 = trim(@"[^\w]+", tostring(txtArray[1])) 
| extend c3 = trim(@"[^\w]+", tostring(txtArray[3]))
| project-away ComputersNotInHeatBeat, txtArray  

 

Go to Log Analytics and Run Query

 

What values do you mean?  

@Scott Allison 

 

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

 

 

@Scott Allison 

 

I just realised (and I've looked at this so many times before); you can create columns, you just have to make a row first then turn a row into a column - very odd I know.

 

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)
// turn into rows
| mvexpand todynamic(ComputersNotInHeatBeat)
| distinct tostring(ComputersNotInHeatBeat)
// now turns rows into columns 
| evaluate pivot(ComputersNotInHeatBeat, dcount(ComputersNotInHeatBeat))

 

Results

 

aFakeComputer fakeComputer
1 1

 

See: 

https://docs.microsoft.com/en-us/azure/kusto/query/mvexpandoperator

https://docs.microsoft.com/en-us/azure/kusto/query/pivotplugin

 

 

I've learnt something new today, thank you :)

@CliveWatson Your last two responses got me exactly what I was looking for. Thank you!

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

@Scott Allison @CliveWatson 

 

1 best response

Accepted Solutions
best response confirmed by Scott Allison (Iron Contributor)
Solution

@Scott Allison 

 

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"]

 

 

View solution in original post