SOLVED

Heartbeat query, show negative results

%3CLINGO-SUB%20id%3D%22lingo-sub-788372%22%20slang%3D%22en-US%22%3EHeartbeat%20query%2C%20show%20negative%20results%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-788372%22%20slang%3D%22en-US%22%3E%3CP%3EGreetings%20Community%2C%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI'm%20trying%20to%20formulate%20a%20query%20whereby%20I%20provide%20a%20list%20of%20servers%20to%20check%20for%20a%20heartbeat%20in%20the%20last%206%20hours%2C%20but%20I%20only%20want%20to%20return%20the%20servers%20THAT%20DO%20NOT%20HAVE%20A%20RECORD%20in%20the%20Heartbeat%20table.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EFor%20example%20in%20the%20following%20query%2C%20%22SERVER123%22%20and%20%22SERVER456%22%20are%20heartbeating%2C%20but%20%22SERVER789%22%20has%20never%20Heartbeat.%20How%20can%20I%20get%20the%20query%20below%20to%20only%20spit%20out%20%22SERVER789%22%20as%20not%20having%20a%20Heartbeat%20(or%20any%20entry%20at%20all%20for%20that%20matter)%3F%3C%2FP%3E%3CPRE%20class%3D%22lia-code-sample%20language-markup%22%3E%3CCODE%3EHeartbeat%0A%7C%20project%20TimeGenerated%20%2C%20Computer%20%0A%7C%20where%20TimeGenerated%20%26gt%3B%3D%20ago(6h)%0A%7C%20where%20Computer%20in%20(%22SERVER123%22%2C%22SERVER456%22%2C%22SERVER789%22)%0A%7C%20summarize%20arg_max(TimeGenerated%2C%20*)%20by%20Computer%3C%2FCODE%3E%3C%2FPRE%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-788372%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EAzure%20Log%20Analytics%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-789685%22%20slang%3D%22en-US%22%3ERe%3A%20Heartbeat%20query%2C%20show%20negative%20results%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-789685%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F151992%22%20target%3D%22_blank%22%3E%40Scott%20Allison%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EHave%20a%20look%20at%20this%20example%3A%26nbsp%3B%3CA%20href%3D%22https%3A%2F%2Fportal.loganalytics.io%2FDemo%3Fq%3DH4sIAAAAAAAAA52ST2vbQBDF7wJ%252Fh4dOMtgx5Fp8aAshgZJLjiGEiXYUbbt%252F3NmxjUo%252FfEcSkdOSU09id9%252B8eb8Z7XararfDHXo6MToflIUdcsLXHA9HOxUUJVGfXnH22qP%252BXEMzIv1gaM8IvihKpBBYsIVwzOaUBey8gord%252FDx6Mx0bTc1uvBTd4JUTizkzaDbJHcwF7dK4kxxxy6Z5YVIovQSGT9adEkiEhlUVWBGHB5bTWLG3aKWlQNJc6lbVb5x741qYZqSy8IwKZxF8anURjZflGCOJ%252F8UT73Nhbd6e1%252BtPwMRzn89w3PlkqmFBuWDYDP7N%252B20U7eGGRNG3zWNNN%252Bb%252FZl1vanLbg3jrPWxde9Vmgy7ZvnF%252BK2xX7sPX7r3Rk4VcVcso%252FnsSRjnykMw713OeecoGpTf8nMKAE4UjFxPYzEdlymrbmgquk5sLpgYHyd%252F5nX%252B5z3qXLKV%252BGfe1h8352fmus6Sp5Wae1%252Bay5%252FVfMQ1exf7P5mO%252FdfUH1YIneuUCAAA%253D%26amp%3Btimespan%3DP1D%22%20target%3D%22_blank%22%20rel%3D%22nofollow%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%22%3EGo%20to%20Log%20Analytics%20and%20Run%20Query%3C%2FA%3E%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EYou%20will%20need%20to%20add%20in%20your%20own%20%3CSTRONG%3EmyList%3C%2FSTRONG%3Evalues%20and%20make%20some%20other%20minor%20edits%26nbsp%3B%3C%2FP%3E%0A%3CPRE%20class%3D%22lia-code-sample%20language-markup%22%3E%3CCODE%3E%2F%2F%0A%2F%2F%20I%20have%20filtered%20on%20Computers%20starting%20with%20%22A%22%20to%20make%20the%20list%20smaller%20-%20remove%20or%20edit%20as%20required%0A%2F%2F%0A%2F%2F%20First%2C%20generarte%20a%20list%20of%20all%20computers%20from%20Heartbeat%20table%20into%20an%20array%0Alet%20myServers%20%3D%20toscalar(Heartbeat%20%0A%7C%20where%20Computer%20startswith%20%22A%22%20%0A%7C%20distinct%20Computer%0A%7C%20summarize%20make_set(Computer))%3B%20%20%0A%2F%2F%20Now%20define%20my%20list%20of%20computers%20as%20an%20array%0Alet%20myList%20%3D%20dynamic(%5B%22aFakeComputer%22%2C%22ad-primary-dc.contoso.com%22%2C%22ad-secondary-dc.contoso.com%22%2C%22fakeComputer%22%5D)%3B%20%0AHeartbeat%0A%7C%20where%20Computer%20startswith%20%22A%22%20%0A%7C%20distinct%20Computer%0A%2F%2F%20compare%20the%20two%20arrays%2C%20show%20only%20values%20that%20are%20not%20in%20the%202nd%20array%20%0A%7C%20project%20ComputersNotInHeatBeat%20%3D%20set_difference(myList%2C%20myServers)%0A%7C%20distinct%20tostring(ComputersNotInHeatBeat)%3C%2FCODE%3E%3C%2FPRE%3E%0A%3CP%3E%26nbsp%3B%20%26nbsp%3B%3C%2FP%3E%0A%3CP%3EResults%2C%20look%20like%20this%3A%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CDIV%3E%0A%3CTABLE%20cellspacing%3D%221%22%20cellpadding%3D%225%22%3E%0A%3CTBODY%3E%0A%3CTR%3E%0A%3CTH%3EComputersNotInHeatBeat%3C%2FTH%3E%0A%3C%2FTR%3E%0A%3CTR%3E%0A%3CTD%3E%5B%22aFakeComputer%22%2C%22fakeComputer%22%5D%3C%2FTD%3E%0A%3C%2FTR%3E%0A%3C%2FTBODY%3E%0A%3C%2FTABLE%3E%0A%3C%2FDIV%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-789918%22%20slang%3D%22en-US%22%3ERe%3A%20Heartbeat%20query%2C%20show%20negative%20results%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-789918%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F239477%22%20target%3D%22_blank%22%3E%40Clive%20Watson%3C%2FA%3E%26nbsp%3BExactly%20what%20I%20was%20looking%20for!%20Thank%20you!%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-790465%22%20slang%3D%22en-US%22%3ERe%3A%20Heartbeat%20query%2C%20show%20negative%20results%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-790465%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F239477%22%20target%3D%22_blank%22%3E%40Clive%20Watson%3C%2FA%3E%26nbsp%3BThis%20query%20is%20near%20perfect%2C%20but%20I%20need%20one%20final%20piece%20that%20I'm%20struggling%20with...%20taking%20that%20final%20array%20and%20converting%20it%20to%20a%20column%20with%20all%20the%20values%3A%3C%2FP%3E%3CPRE%3E%7C%20distinct%20tostring(ComputersNotInHeatBeat)%3C%2FPRE%3E%3CP%3EI've%20tried%20using%20%22parse%22%2C%20%22parse_json%22%2C%20%22extractjson%22--I%20can't%20seem%20to%20find%20a%20way%20to%20spit%20the%20individual%20results%20in%20the%20array%20to%20a%20column.%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-790655%22%20slang%3D%22en-US%22%3ERe%3A%20Heartbeat%20query%2C%20show%20negative%20results%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-790655%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F151992%22%20target%3D%22_blank%22%3E%40Scott%20Allison%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EYou%20cant%20split%20arrays%20to%20columns%2C%20columns%20have%20to%20be%20named.%26nbsp%3B%20Closest%20I%20can%20do%20is%2C%20adding%20some%20named%20columns%20-%20this%20will%20probably%20only%20work%20if%20you%20have%20a%20few%20expected%20missing%20servers%3F%26nbsp%3B%20Add%20this%20amended%20code%26nbsp%3B%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CPRE%20class%3D%22lia-code-sample%20language-markup%22%3E%3CCODE%3E%2F%2F%20compare%20the%20two%20arrays%2C%20show%20only%20values%20that%20are%20not%20in%20the%202nd%20array%20%0A%7C%20extend%20%20ComputersNotInHeatBeat%20%3D%20set_difference(myList%2C%20myServers)%0A%7C%20distinct%20tostring(ComputersNotInHeatBeat)%0A%7C%20extend%20txtArray%20%3D%20split(ComputersNotInHeatBeat%2C%22%2C%22)%0A%7C%20extend%20c1%20%3D%20trim(%40%22%5B%5E%5Cw%5D%2B%22%2C%20tostring(txtArray%5B0%5D))%20%0A%7C%20extend%20c2%20%3D%20trim(%40%22%5B%5E%5Cw%5D%2B%22%2C%20tostring(txtArray%5B1%5D))%20%0A%7C%20extend%20c3%20%3D%20trim(%40%22%5B%5E%5Cw%5D%2B%22%2C%20tostring(txtArray%5B3%5D))%0A%7C%20project-away%20ComputersNotInHeatBeat%2C%20txtArray%20%20%3C%2FCODE%3E%3C%2FPRE%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3E%3CA%20href%3D%22https%3A%2F%2Fportal.loganalytics.io%2FDemo%3Fq%3DH4sIAAAAAAAAA42POwvCQBCEe8H%252FsKRK8MRXLfhoFMTG0hfHZdWT5C7crcaAP96NIgZRSLHNMN%252FMTqcDyqaZdAh04sstSOdk4QX4k83BmqSAq0wu6NkgCUqnsQTaPIG%252BiV8ANBt3wBshCzDlyAuh80tLczNDSRM%252BGIJH2sf6cECHRmGYFgvtSUBarNBd2R%252BVKTFr2igCsp6cNsfwd15UqaQbjZ9vcEeWaPqDiEAEVUz1GOCONBwF690m37YC8al9h6672yiqDlT9WljvGxvUwgaMlVTm7BkVtWXOu%252F7s%252BQyHB7s5renLAQAA%26amp%3Btimespan%3DP1D%22%20target%3D%22_blank%22%20rel%3D%22nofollow%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%22%3EGo%20to%20Log%20Analytics%20and%20Run%20Query%3C%2FA%3E%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EWhat%20values%20do%20you%20mean%3F%26nbsp%3B%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-790673%22%20slang%3D%22en-US%22%3ERe%3A%20Heartbeat%20query%2C%20show%20negative%20results%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-790673%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F151992%22%20target%3D%22_blank%22%3E%40Scott%20Allison%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EI%20meant%20to%20add%2C%20sending%20an%20array%20to%20a%20row%20(rather%20than%20column)%20is%20easy%3C%2FP%3E%0A%3CP%3E%3CA%20href%3D%22https%3A%2F%2Fportal.loganalytics.io%2FDemo%3Fq%3DH4sIAAAAAAAAA52ST0sjQRDF7wP5Do85JZAY8Co5uIIoiBePIlJO1ziN%252FSd2VxJn2Q9v9QyZuIt78TRM9atX9Xvd6%252FWsWq9xi472jNY64cQGMeAq%252Bu1O%252FzKyUBIbXnGw0qG%252BrCERnt4Y0jGczYLsyTlOWCGxj%252BoUE9hYAWWtvO%252BsmpZBw7Brm7Is8cqBkzozaDSJLdQFzTS4TdHjhlXzwiQQenEMG3Q6BVBK1M8qxwLfP3Dal46NrpYbcpTmp75Z9QeHTrkmphEpTzxFYXQFGxqZRKWYd95Tsr954H3OLPPj8WJxAQw89%252FEAw60NquonlBOGZvDvvndFtIHpA3nbzB9rulb%252Fo3W9rMmstsnq7H5lmrMmKnSO%252BvXjWWYtmW9P269GT7rkrJqi%252BHESSll4KI13Loc48uQlcqf4Mbgee3I7zirQzIsyRNHbGhrOgxkbhgH8IayF0xO7j3IbdEv5Ve5rA8352di21U1Dw%252FMxr%252BXpnhfFxe%252F5Y0vqI%252FGY4%252FeGi7%252BgNCpJ%252Bpr%252FJ64%252BAdgNS78TAwAA%26amp%3Btimespan%3DP1D%22%20target%3D%22_blank%22%20rel%3D%22nofollow%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%22%3EGo%20to%20Log%20Analytics%20and%20Run%20Query%3C%2FA%3E%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CPRE%20class%3D%22lia-code-sample%20language-markup%22%3E%3CCODE%3E%2F%2F%0A%2F%2F%20I%20have%20filtered%20on%20Computers%20starting%20with%20%22A%22%20to%20make%20the%20list%20smaller%20-%20remove%20or%20edit%20as%20required%0A%2F%2F%0A%2F%2F%20First%2C%20generarte%20a%20list%20of%20all%20computers%20from%20Heartbeat%20table%20into%20an%20array%0Alet%20myServers%20%3D%20toscalar(Heartbeat%20%0A%7C%20where%20Computer%20startswith%20%22A%22%20%0A%7C%20distinct%20Computer%0A%7C%20summarize%20make_set(Computer))%3B%20%20%0A%2F%2F%20Now%20define%20my%20list%20of%20computers%20as%20an%20array%0Alet%20myList%20%3D%20dynamic(%5B%22aFakeComputer%22%2C%22ad-primary-dc.contoso.com%22%2C%22ad-secondary-dc.contoso.com%22%2C%22fakeComputer%22%5D)%3B%20%0AHeartbeat%0A%7C%20where%20Computer%20startswith%20%22A%22%20%0A%7C%20distinct%20Computer%0A%2F%2F%20compare%20the%20two%20arrays%2C%20show%20only%20values%20that%20are%20not%20in%20the%202nd%20array%20%0A%7C%20extend%20%20ComputersNotInHeatBeat%20%3D%20set_difference(myList%2C%20myServers)%0A%7C%20mvexpand%20todynamic(ComputersNotInHeatBeat)%0A%7C%20distinct%20tostring(ComputersNotInHeatBeat)%3C%2FCODE%3E%3C%2FPRE%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3E%3CSTRONG%3Eresult%3C%2FSTRONG%3E%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CDIV%3E%0A%3CTABLE%20cellspacing%3D%221%22%20cellpadding%3D%225%22%3E%0A%3CTBODY%3E%0A%3CTR%3E%0A%3CTH%3EComputersNotInHeatBeat%3C%2FTH%3E%0A%3C%2FTR%3E%0A%3CTR%3E%0A%3CTD%3EaFakeComputer%3C%2FTD%3E%0A%3C%2FTR%3E%0A%3CTR%3E%0A%3CTD%3EfakeComputer%3C%2FTD%3E%0A%3C%2FTR%3E%0A%3C%2FTBODY%3E%0A%3C%2FTABLE%3E%0A%3C%2FDIV%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-790715%22%20slang%3D%22en-US%22%3ERe%3A%20Heartbeat%20query%2C%20show%20negative%20results%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-790715%22%20slang%3D%22en-US%22%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F151992%22%20target%3D%22_blank%22%3E%40Scott%20Allison%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EI%20just%20realised%20(and%20I've%20looked%20at%20this%20so%20many%20times%20before)%3B%20you%20%3CSTRONG%3Ecan%3C%2FSTRONG%3Ecreate%20columns%2C%20you%20just%20have%20to%20make%20a%20row%20first%20then%20turn%20a%20row%20into%20a%20column%20-%20very%20odd%20I%20know.%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3E%3CA%20href%3D%22https%3A%2F%2Fportal.loganalytics.io%2FDemo%3Fq%3DH4sIAAAAAAAAA52STYvcMAyG74H5DyKnCWQ60GuZQ1tYulD20mMpi9ZWNqa2NbWVZFP64ysnTKYtO5eeQvTxvnpkHY%252B76niEe%252BhxJOicF0pkgSN85HAe9C9DFkzi4jNMTnqo39cgDAG%252FE0hP4F0WyAG9pwQHSBRYlTgBWSeAWSM%252FBqeixWgxu3MpSwvPFCmpMgGuItyBqoDZjLvEAT6R1jwRCgg%252BeQIX1R0jYEo47ypPAmH%252BQmksHScdLRv0mPbXvl31C6ZeuTamFSlvPKXC6gguGtmKSjAPIWByP2nhfcwk%252B0u6ad4BLDwPPIGlzkWtmjeUK4bu4N95P5eiE9g5YnBm%252F7XGO9W%252FSNdtjfZwTk6954M1bwwrdGb9hjWXSUP21Wz3p9A3HXJXbav4700oZeHBtL65TLzy5BZyr%252Fgc%252FQwj%252BoGyFujOS2Vk0ddaGt5GuzYsBvQipIHriT2w3EedUj6U9zqB7vnRuq7TSaOh%252Fbqv9vrOzTKRDCmu55B4ykU4jPRyRpUWvqz2dY%252FmL07dniQ98NvF6hYVszjmxW31NeyHoJEFquCjXvPZjSw3pFqwhod4K9001W%252BxWXk2kQMAAA%253D%253D%26amp%3Btimespan%3DP1D%22%20target%3D%22_blank%22%20rel%3D%22nofollow%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%22%3EGo%20to%20Log%20Analytics%20and%20Run%20Query%3C%2FA%3E%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CPRE%20class%3D%22lia-code-sample%20language-markup%22%3E%3CCODE%3E%2F%2F%0A%2F%2F%20I%20have%20filtered%20on%20Computers%20starting%20with%20%22A%22%20to%20make%20the%20list%20smaller%20-%20remove%20or%20edit%20as%20required%0A%2F%2F%0A%2F%2F%20First%2C%20generarte%20a%20list%20of%20all%20computers%20from%20Heartbeat%20table%20into%20an%20array%0Alet%20myServers%20%3D%20toscalar(Heartbeat%20%0A%7C%20where%20Computer%20startswith%20%22A%22%20%0A%7C%20distinct%20Computer%0A%7C%20summarize%20make_set(Computer))%3B%20%20%0A%2F%2F%20Now%20define%20my%20list%20of%20computers%20as%20an%20array%0Alet%20myList%20%3D%20dynamic(%5B%22aFakeComputer%22%2C%22ad-primary-dc.contoso.com%22%2C%22ad-secondary-dc.contoso.com%22%2C%22fakeComputer%22%5D)%3B%20%0AHeartbeat%0A%7C%20where%20Computer%20startswith%20%22A%22%20%0A%7C%20distinct%20Computer%0A%2F%2F%20compare%20the%20two%20arrays%2C%20show%20only%20values%20that%20are%20not%20in%20the%202nd%20array%20%0A%7C%20extend%20%20ComputersNotInHeatBeat%20%3D%20set_difference(myList%2C%20myServers)%0A%2F%2F%20turn%20into%20rows%0A%7C%20mvexpand%20todynamic(ComputersNotInHeatBeat)%0A%7C%20distinct%20tostring(ComputersNotInHeatBeat)%0A%2F%2F%20now%20turns%20rows%20into%20columns%20%0A%7C%20evaluate%20pivot(ComputersNotInHeatBeat%2C%20dcount(ComputersNotInHeatBeat))%3C%2FCODE%3E%3C%2FPRE%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3E%3CSTRONG%3EResults%3C%2FSTRONG%3E%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CDIV%3E%0A%3CTABLE%20cellspacing%3D%221%22%20cellpadding%3D%225%22%3E%0A%3CTBODY%3E%0A%3CTR%3E%0A%3CTH%3EaFakeComputer%3C%2FTH%3E%0A%3CTH%3EfakeComputer%3C%2FTH%3E%0A%3C%2FTR%3E%0A%3CTR%3E%0A%3CTD%3E1%3C%2FTD%3E%0A%3CTD%3E1%3C%2FTD%3E%0A%3C%2FTR%3E%0A%3C%2FTBODY%3E%0A%3C%2FTABLE%3E%0A%3C%2FDIV%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3E%3CSTRONG%3ESee%3A%26nbsp%3B%3C%2FSTRONG%3E%3C%2FP%3E%0A%3CP%3E%3CA%20href%3D%22https%3A%2F%2Fdocs.microsoft.com%2Fen-us%2Fazure%2Fkusto%2Fquery%2Fmvexpandoperator%22%20target%3D%22_blank%22%20rel%3D%22noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%22%3Ehttps%3A%2F%2Fdocs.microsoft.com%2Fen-us%2Fazure%2Fkusto%2Fquery%2Fmvexpandoperator%3C%2FA%3E%3C%2FP%3E%0A%3CP%3E%3CA%20href%3D%22https%3A%2F%2Fdocs.microsoft.com%2Fen-us%2Fazure%2Fkusto%2Fquery%2Fpivotplugin%22%20target%3D%22_blank%22%20rel%3D%22noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%22%3Ehttps%3A%2F%2Fdocs.microsoft.com%2Fen-us%2Fazure%2Fkusto%2Fquery%2Fpivotplugin%3C%2FA%3E%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EI've%20learnt%20something%20new%20today%2C%20thank%20you%20%3A)%3C%2Fimg%3E%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-795852%22%20slang%3D%22en-US%22%3ERe%3A%20Heartbeat%20query%2C%20show%20negative%20results%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-795852%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F239477%22%20target%3D%22_blank%22%3E%40Clive%20Watson%3C%2FA%3E%26nbsp%3BYour%20last%20two%20responses%20got%20me%20exactly%20what%20I%20was%20looking%20for.%20Thank%20you!%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-798970%22%20slang%3D%22en-US%22%3ERe%3A%20Heartbeat%20query%2C%20show%20negative%20results%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-798970%22%20slang%3D%22en-US%22%3E%3CP%3EHi%2C%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EThe%20most%20performant%20way%20would%20be%20to%20utilize%20the%20built-in%20join%20operator%20in%20its%20anti%20flavors%3A%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CDIV%20style%3D%22color%3A%20%23000000%3B%20background-color%3A%20%23fffffe%3B%20font-family%3A%20Consolas%2C%20'Courier%20New'%2C%20monospace%3B%20font-weight%3A%20normal%3B%20font-size%3A%2014px%3B%20line-height%3A%2016px%3B%20white-space%3A%20pre%3B%22%3E%0A%3CDIV%3E%3CFONT%20face%3D%22Consolas%22%3E%3CSPAN%20style%3D%22color%3A%20%23000000%3B%22%3Elet%20MyCompList%20%3D%20datatable(Computer%3C%2FSPAN%3E%3CSPAN%20style%3D%22color%3A%20%23cd3131%3B%22%3E%3A%3C%2FSPAN%3E%3CSPAN%20style%3D%22color%3A%20%230000ff%3B%22%3Estring%3C%2FSPAN%3E%3CSPAN%20style%3D%22color%3A%20%23000000%3B%22%3E)%3C%2FSPAN%3E%20%3C%2FFONT%3E%3C%2FDIV%3E%0A%3CDIV%3E%3CSPAN%20style%3D%22color%3A%20%23000000%3B%22%3E%3CFONT%20face%3D%22Consolas%22%3E%5B%3C%2FFONT%3E%3C%2FSPAN%3E%3C%2FDIV%3E%0A%3CDIV%3E%3CFONT%20face%3D%22Consolas%22%3E%3CSPAN%20style%3D%22color%3A%20%23a31515%3B%22%3E%22SERVER123%22%3C%2FSPAN%3E%3CSPAN%20style%3D%22color%3A%20%23000000%3B%22%3E%2C%3C%2FSPAN%3E%3C%2FFONT%3E%3C%2FDIV%3E%0A%3CDIV%3E%0A%3CDIV%20style%3D%22background-color%3A%20transparent%3B%20box-sizing%3A%20border-box%3B%20color%3A%20%23333333%3B%20font-family%3A%20%26amp%3Bquot%3B%20segoeui%26amp%3Bquot%3B%2C%26amp%3Bquot%3Blato%26amp%3Bquot%3B%2C%26amp%3Bquot%3Bhelvetica%20neue%26amp%3Bquot%3B%2Chelvetica%2Carial%2Csans-serif%3B%20font-size%3A%2016px%3B%20font-style%3A%20normal%3B%20font-variant%3A%20normal%3B%20font-weight%3A%20300%3B%20letter-spacing%3A%20normal%3B%20line-height%3A%2027.42px%3B%20orphans%3A%202%3B%20text-align%3A%20left%3B%20text-decoration%3A%20none%3B%20text-indent%3A%200px%3B%20text-transform%3A%20none%3B%20-webkit-text-stroke-width%3A%200px%3B%20white-space%3A%20normal%3B%20word-spacing%3A%200px%3B%22%3E%3CFONT%20face%3D%22Consolas%22%20style%3D%22box-sizing%3A%20border-box%3B%22%3E%3CSPAN%20style%3D%22box-sizing%3A%20border-box%3B%20color%3A%20%23a31515%3B%20font-family%3A%20%26amp%3Bquot%3B%22%3E%22SERVER456%22%3C%2FSPAN%3E%3CSPAN%20style%3D%22box-sizing%3A%20border-box%3B%20color%3A%20%23000000%3B%20font-family%3A%20%26amp%3Bquot%3B%22%3E%2C%3C%2FSPAN%3E%3C%2FFONT%3E%3C%2FDIV%3E%0A%3C%2FDIV%3E%0A%3CDIV%3E%3CSPAN%20style%3D%22color%3A%20%23a31515%3B%22%3E%3CFONT%20face%3D%22Consolas%22%3E%22SERVER789%22%3C%2FFONT%3E%3C%2FSPAN%3E%3C%2FDIV%3E%0A%3CDIV%3E%3CSPAN%20style%3D%22color%3A%20%23000000%3B%22%3E%3CFONT%20face%3D%22Consolas%22%3E%5D%3B%3C%2FFONT%3E%3C%2FSPAN%3E%3C%2FDIV%3E%0A%3CDIV%3E%3CSPAN%20style%3D%22color%3A%20%23000000%3B%22%3E%3CFONT%20face%3D%22Consolas%22%3EMyCompList%3C%2FFONT%3E%3C%2FSPAN%3E%3C%2FDIV%3E%0A%3CDIV%3E%3CFONT%20face%3D%22Consolas%22%3E%3CSPAN%20style%3D%22color%3A%20%23000000%3B%22%3E%7C%20%3C%2FSPAN%3E%3CSPAN%20style%3D%22color%3A%20%230000ff%3B%22%3Ejoin%3C%2FSPAN%3E%3CSPAN%20style%3D%22color%3A%20%23000000%3B%22%3Ekind%3D%20leftanti%20(%3C%2FSPAN%3E%3C%2FFONT%3E%3C%2FDIV%3E%0A%3CDIV%3E%3CFONT%20face%3D%22Consolas%22%3E%26nbsp%3B%26nbsp%3B%26nbsp%3B%20%3CSPAN%20style%3D%22color%3A%20%23000000%3B%22%3EHeartbeat%20%7C%20%3C%2FSPAN%3E%3CSPAN%20style%3D%22color%3A%20%230000ff%3B%22%3Ewhere%3C%2FSPAN%3E%3CSPAN%20style%3D%22color%3A%20%23000000%3B%22%3ETimeGenerated%20%26gt%3B%20ago(%3C%2FSPAN%3E%3CSPAN%20style%3D%22color%3A%20%2309885a%3B%22%3E6%3C%2FSPAN%3E%3CSPAN%20style%3D%22color%3A%20%23000000%3B%22%3Eh)%20%7C%20%3C%2FSPAN%3E%3CSPAN%20style%3D%22color%3A%20%230000ff%3B%22%3Edistinct%3C%2FSPAN%3E%3CSPAN%20style%3D%22color%3A%20%23000000%3B%22%3EComputer%3C%2FSPAN%3E%26nbsp%3B%26nbsp%3B%26nbsp%3B%20%3C%2FFONT%3E%3C%2FDIV%3E%0A%3CDIV%3E%3CFONT%20face%3D%22Consolas%22%3E%3CSPAN%20style%3D%22color%3A%20%23000000%3B%22%3E)%20%3C%2FSPAN%3E%3CSPAN%20style%3D%22color%3A%20%230000ff%3B%22%3Eon%3C%2FSPAN%3E%3CSPAN%20style%3D%22color%3A%20%23000000%3B%22%3EComputer%3C%2FSPAN%3E%26nbsp%3B%20%3C%2FFONT%3E%3C%2FDIV%3E%0A%3C%2FDIV%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EThanks%2C%3C%2FP%3E%0A%3CP%3EMeir%3C%2FP%3E%0A%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F151992%22%20target%3D%22_blank%22%3E%40Scott%20Allison%3C%2FA%3E%26nbsp%3B%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F239477%22%20target%3D%22_blank%22%3E%40Clive%20Watson%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E
Highlighted
Frequent 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
Highlighted
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"]

 

 

Highlighted

@Clive Watson Exactly what I was looking for! Thank you!

Highlighted

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

Highlighted

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

Highlighted

@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

 

Highlighted

 

@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 :)

Highlighted

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

Highlighted

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 @Clive Watson