azure kql assistant
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. CliveWatson helped me with this last time