Aug 05 2019
08:46 AM
- last edited on
Apr 07 2022
06:02 PM
by
TechCommunityAP
Aug 05 2019
08:46 AM
- last edited on
Apr 07 2022
06:02 PM
by
TechCommunityAP
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
Aug 06 2019 02:08 AM
Solution
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"] |
Aug 06 2019 04:56 AM
@CliveWatson Exactly what I was looking for! Thank you!
Aug 06 2019 09:04 AM
@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.
Aug 06 2019 10:58 AM
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?
Aug 06 2019 11:09 AM
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 |
Aug 06 2019 11:22 AM
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 :)
Aug 08 2019 11:28 AM
@CliveWatson Your last two responses got me exactly what I was looking for. Thank you!
Aug 11 2019 05:37 AM
Hi,
The most performant way would be to utilize the built-in join operator in its anti flavors:
Thanks,
Meir
Aug 06 2019 02:08 AM
Solution
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"] |