Forum Discussion
Heartbeat query, show negative results
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"]
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 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.
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 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!
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, txtArrayGo to Log Analytics and Run Query
What values do you mean?