azure kql assistant

%3CLINGO-SUB%20id%3D%22lingo-sub-1567665%22%20slang%3D%22en-US%22%3Eazure%20kql%20assistant%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1567665%22%20slang%3D%22en-US%22%3E%3CDIV%3E%3CSPAN%3ECan%20you%20please%20have%20a%20look%20at%20my%20query%20below%20and%20try%20to%20assist%20me%20with%20this%26nbsp%3B%3C%2FSPAN%3E%3C%2FDIV%3E%3CDIV%3E%26nbsp%3B%3C%2FDIV%3E%3CDIV%3E%3CSPAN%3E%2F%2F%20please%20add%20a%20list%20of%20your%20servers%20here%2C%20these%20ones%20are%20the%20ones%20that%20are%20*shutdown*%20overnight%3C%2FSPAN%3E%3C%2FDIV%3E%3CDIV%3E%3CSPAN%3Elet%20shutdownComputers%20%3D%20dynamic(%5B%3C%2FSPAN%3E%3CSPAN%3E%22machines%3C%2FSPAN%3E%3CSPAN%3E%22%3C%2FSPAN%3E%3CSPAN%3E%5D)%3B%3C%2FSPAN%3E%3C%2FDIV%3E%3CDIV%3E%3CSPAN%3E%2F%2F%20always%20exclude%20these%20computers%3C%2FSPAN%3E%3C%2FDIV%3E%3CDIV%3E%3CSPAN%3Elet%20excludeComputers%20%3D%20dynamic(%5B%3C%2FSPAN%3E%3CSPAN%3E%22machines%3C%2FSPAN%3E%3CSPAN%3E%22%3C%2FSPAN%3E%3CSPAN%3E%5D)%3B%3C%2FSPAN%3E%3C%2FDIV%3E%3CDIV%3E%3CSPAN%3E%2F%2F%20config%20the%20hours%20to%20exclude%3C%2FSPAN%3E%3C%2FDIV%3E%3CDIV%3E%3CSPAN%3Elet%20startHour%20%3D%20%3C%2FSPAN%3E%3CSPAN%3E1900%3C%2FSPAN%3E%3CSPAN%3E%3B%20%3C%2FSPAN%3E%3CSPAN%3E%2F%2F%2007%3A00%20PM%3C%2FSPAN%3E%3C%2FDIV%3E%3CDIV%3E%3CSPAN%3Elet%20endHour%20%3D%20%3C%2FSPAN%3E%3CSPAN%3E06%3C%2FSPAN%3E%3CSPAN%3E%3B%20%3C%2FSPAN%3E%3CSPAN%3E%2F%2F%2006%3A00%20Am%3C%2FSPAN%3E%3C%2FDIV%3E%3CDIV%3E%3CSPAN%3EHeartbeat%3C%2FSPAN%3E%3C%2FDIV%3E%3CDIV%3E%3CSPAN%3E%2F%2F%20Get%20just%20the%20excluded%20Servers%3C%2FSPAN%3E%3C%2FDIV%3E%3CDIV%3E%3CSPAN%3E%7C%20%3C%2FSPAN%3E%3CSPAN%3Ewhere%3C%2FSPAN%3E%3CSPAN%3E%20TimeGenerated%20%26gt%3B%20startofday(ago(%3C%2FSPAN%3E%3CSPAN%3E24%3C%2FSPAN%3E%3CSPAN%3Eh))%3C%2FSPAN%3E%3C%2FDIV%3E%3CDIV%3E%3CSPAN%3E%7C%20%3C%2FSPAN%3E%3CSPAN%3Ewhere%3C%2FSPAN%3E%3CSPAN%3E%20Computer%20in%20(shutdownComputers)%3C%2FSPAN%3E%3C%2FDIV%3E%3CDIV%3E%3CSPAN%3E%7C%20%3C%2FSPAN%3E%3CSPAN%3Esummarize%3C%2FSPAN%3E%3CSPAN%3E%20LastCall%20%3D%20arg_max(%20TimeGenerated%2C%20datetime_part(%3C%2FSPAN%3E%3CSPAN%3E%22hour%22%3C%2FSPAN%3E%3CSPAN%3E%2C%20TimeGenerated)%20between(%20startHour%20..%20endHour)%20)%3C%2FSPAN%3E%3C%2FDIV%3E%3CDIV%3E%3CSPAN%3Eby%3C%2FSPAN%3E%3CSPAN%3E%20Computer%2C%20sComputer%20%3D%20strcat(%3C%2FSPAN%3E%3CSPAN%3E%22Computer%20goes%20offline%20between%20%22%3C%2FSPAN%3E%3CSPAN%3E%2C%20startHour%2C%3C%2FSPAN%3E%3CSPAN%3E%22%20to%20%22%3C%2FSPAN%3E%3CSPAN%3E%2C%20endHour%2C%3C%2FSPAN%3E%3CSPAN%3E%22%20%3A%22%3C%2FSPAN%3E%3CSPAN%3E%2CComputer)%2C%20ComputerEnvironment%3C%2FSPAN%3E%3C%2FDIV%3E%3CDIV%3E%3CSPAN%3E%7C%20%3C%2FSPAN%3E%3CSPAN%3Ewhere%3C%2FSPAN%3E%3CSPAN%3E%20isnotempty(LastCall)%3C%2FSPAN%3E%3C%2FDIV%3E%3CDIV%3E%3CSPAN%3E%7C%20%3C%2FSPAN%3E%3CSPAN%3Eproject%3C%2FSPAN%3E%3CSPAN%3E%20Computer%20%2C%20LastCall%2C%20sComputer%3C%2FSPAN%3E%3C%2FDIV%3E%3CDIV%3E%3CSPAN%3E%2F%2F%20Now%20join%20those%20excluded%20servers%20with%20the%20others...%3C%2FSPAN%3E%3C%2FDIV%3E%3CDIV%3E%3CSPAN%3E%7C%20%3C%2FSPAN%3E%3CSPAN%3Ejoin%3C%2FSPAN%3E%3CSPAN%3E%20kind%3D%20fullouter%3C%2FSPAN%3E%3C%2FDIV%3E%3CDIV%3E%3CSPAN%3E(%3C%2FSPAN%3E%3C%2FDIV%3E%3CDIV%3E%3CSPAN%3EHeartbeat%3C%2FSPAN%3E%3C%2FDIV%3E%3CDIV%3E%3CSPAN%3E%7C%20%3C%2FSPAN%3E%3CSPAN%3Ewhere%3C%2FSPAN%3E%3CSPAN%3E%20TimeGenerated%20%26gt%3B%20startofday(ago(%3C%2FSPAN%3E%3CSPAN%3E24%3C%2FSPAN%3E%3CSPAN%3Eh))%3C%2FSPAN%3E%3C%2FDIV%3E%3CDIV%3E%3CSPAN%3E%7C%20%3C%2FSPAN%3E%3CSPAN%3Ewhere%3C%2FSPAN%3E%3CSPAN%3E%20Computer%20!in%20(shutdownComputers)%20%3C%2FSPAN%3E%3CSPAN%3Eand%3C%2FSPAN%3E%3CSPAN%3E%20Computer%20!in(excludeComputers)%3C%2FSPAN%3E%3C%2FDIV%3E%3CDIV%3E%3CSPAN%3E%7C%20%3C%2FSPAN%3E%3CSPAN%3Esummarize%3C%2FSPAN%3E%3CSPAN%3E%20LastCall%20%3D%20arg_max(TimeGenerated%2C*)%20%3C%2FSPAN%3E%3CSPAN%3Eby%3C%2FSPAN%3E%3CSPAN%3E%20Computer%3C%2FSPAN%3E%3C%2FDIV%3E%3CDIV%3E%3CSPAN%20style%3D%22color%3A%20%23000000%3B%22%3E%3CSPAN%3E)%3C%2FSPAN%3E%3CSPAN%3E%20%3C%2FSPAN%3E%3CSPAN%20style%3D%22color%3A%20%230000ff%3B%22%3Eo%3C%2FSPAN%3E%3CSPAN%3En%3C%2FSPAN%3E%3CSPAN%20style%3D%22color%3A%20%23000000%3B%22%3EComputer%3C%2FSPAN%3E%3C%2FSPAN%3E%3C%2FDIV%3E%3CDIV%3E%3CSPAN%3E%2F%2F%20This%20bit%20can%20probably%20be%20improved%20if%20I%20get%20time%3C%2FSPAN%3E%3C%2FDIV%3E%3CDIV%3E%3CSPAN%3E%7C%20%3C%2FSPAN%3E%3CSPAN%3Eextend%3C%2FSPAN%3E%3CSPAN%3E%20Computer%20%3D%20iif(isempty(Computer)%2CComputer1%2CComputer)%2C%3C%2FSPAN%3E%3C%2FDIV%3E%3CDIV%3E%3CSPAN%3ELastCall%20%3D%20iif(isempty(LastCall)%2CLastCall1%2CLastCall)%3C%2FSPAN%3E%3C%2FDIV%3E%3CDIV%3E%3CSPAN%3E%7C%20%3C%2FSPAN%3E%3CSPAN%3Esummarize%3C%2FSPAN%3E%20%3CSPAN%3Eby%3C%2FSPAN%3E%3CSPAN%3E%20LastCall%2C%20Computer%2C%20sComputer%3C%2FSPAN%3E%3C%2FDIV%3E%3CDIV%3E%3CSPAN%3E%7C%20%3C%2FSPAN%3E%3CSPAN%3Ewhere%3C%2FSPAN%3E%3CSPAN%3E%20LastCall%20%26lt%3B%20ago(%3C%2FSPAN%3E%3CSPAN%3E10%3C%2FSPAN%3E%3CSPAN%3Em)%3C%2FSPAN%3E%3C%2FDIV%3E%3CDIV%3E%26nbsp%3B%3C%2FDIV%3E%3CDIV%3E%3CDIV%20class%3D%22mceNonEditable%20lia-copypaste-placeholder%22%3E%26nbsp%3B%3C%2FDIV%3E%3CP%3EAzure%20vm's%26nbsp%3B%20heartbeat%20alert%20not%20working%20as%20expected%20shown%20in%20the%20screenshot%20below.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThere%20are%20some%20machines%2C%20which%20are%20not%20being%20reported.%20%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%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%3Bhelped%20me%20with%20this%20last%20time%3C%2FP%3E%3C%2FDIV%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-1567665%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3Eazure%20monitor%20alerts%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1578955%22%20slang%3D%22en-US%22%3ERe%3A%20azure%20kql%20assistant%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1578955%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F643248%22%20target%3D%22_blank%22%3E%40Arslan11%3C%2FA%3E%26nbsp%3Byou%20say%20heartbeat%20is%20not%20working%20as%20expected%2C%20have%20you%20validated%20the%20connection%20of%20those%20VMs%20to%20the%20service%20to%20ensure%20the%20agent%20is%20installed%20properly%20and%20they%20can%20communicate%20with%20Log%20Analytics%3F%3C%2FP%3E%3C%2FLINGO-BODY%3E
Contributor
Can you please have a look at my query below and try to assist me with this 
 
// please add a list of your servers here, these ones are the ones that are *shutdown* overnight
let shutdownComputers = dynamic(["machines"]);
// always exclude these computers
let excludeComputers = dynamic(["machines"]);
// config the hours to exclude
let startHour = 1900; // 07:00 PM
let endHour = 06; // 06:00 Am
Heartbeat
// Get just the excluded Servers
| where TimeGenerated > startofday(ago(24h))
| where Computer in (shutdownComputers)
| summarize LastCall = arg_max( TimeGenerated, datetime_part("hour", TimeGenerated) between( startHour .. endHour) )
by Computer, sComputer = strcat("Computer goes offline between ", startHour," to ", endHour," :",Computer), ComputerEnvironment
| where isnotempty(LastCall)
| project Computer , LastCall, sComputer
// Now join those excluded servers with the others...
| join kind= fullouter
(
Heartbeat
| where TimeGenerated > startofday(ago(24h))
| where Computer !in (shutdownComputers) and Computer !in(excludeComputers)
| summarize LastCall = arg_max(TimeGenerated,*) by Computer
) on Computer
// This bit can probably be improved if I get time
| extend Computer = iif(isempty(Computer),Computer1,Computer),
LastCall = iif(isempty(LastCall),LastCall1,LastCall)
| summarize by LastCall, Computer, sComputer
| where LastCall < ago(10m)
 
 

Azure vm's  heartbeat alert not working as expected shown in the screenshot below.

 

There are some machines, which are not being reported.  

 

@Clive Watson helped me with this last time

1 Reply

@Arslan11 you say heartbeat is not working as expected, have you validated the connection of those VMs to the service to ensure the agent is installed properly and they can communicate with Log Analytics?