Machine not sedning pings

CliveWatson Thanks for all the help you gave me and keeping up with me, my query is finally working

And it is doing the right thing, excluding those machines and I will see if I don't get alert tonight that means it is also avoiding the ones which shutdown at night at 10:00 pm.

As you described - let start =Hour  7 when the machines are started and 10:00pm when machines are stopped.

let startHour = 07;   // 7am
let endHour   = 22;   // 10pm

I have also removed the last line, as it was used for testing the query

| where LastCall < ago(10m)

Thanks, finally getting the logic

Arslan11 

Like this maybe?

// please add a list of your servers here, these ones are the ones that are *shutdown* overnight
let shutdownComputers = dynamic(["rancher-node-1","rancher-node-2","rancher-node-3"]);
// always exclude these computera
let excludeComputers = dynamic(["demo1","demo2","demo3","node-4"]);
// config the hours to exclude
let startHour = 07;   // 7am
let endHour   = 22;   // 10pm
Heartbeat
// Get just the excluded Servers
| where TimeGenerated > startofday(ago(1d)) 
| where Computer in (shutdownComputers) 
| summarize LastCall = arg_max( TimeGenerated, datetime_part("hour", TimeGenerated) between( startHour .. endHour) )
            by Computer, sComputer = strcat("Computer in OFFLINE list from ", 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(1d)) 
    | 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)

...

...

CliveWatson  Prefect, KQL working as expected, Final thing to be done, then it's all done.

All the machines specified in the screenshot, is stopped forever, how can i stop those reporting in my existing query

// config the hours to exclude
let startHour = 06;
let endHour = 22;
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 in OFFLINE list from ", 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))
| 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)

Should I add another joinkind= fulloter

then add this

Heartbeat

| where TimeGenerated > ago(24h)

| where Computer != "computer to be excluded"

// or  Computer  != "aaaa"

| summarize LastCall = max(TimeGenerated) by Computer, ComputerEnvironment

| where LastCall < ago(10m)

or there is any other way to do it, final thing to be done.

Arslan11 

So the requirements are:

1. I would like to know, if any machine is not sending pings:  All Computers 
2. except the machines that shut down at 10:00pm and start at 6:00am,  See list 
3. it should still report if not sending pings between 7:00 am to 9:00p 
   
   So for #3, is that all machines, including those excluded by #2?

The Query returns all servers, and the last record received (unless they are excluded within certain hours).

Have you added this back as the last line?

CliveWatsonI was unable to send private message, that's why I have put it over here

Sorry for confusing you, what I wanted exactly in my query to be set up as alert.

I would like to know, if any machine is not sending pings, expect  machines  that shut down at 10:00pm and start at 6:00am, but it should still report if not sending pings between 7:00 am to 9:00pm.

Machines that shut down.

But, the query is really confusing, it is displaying several machines, which should not be as those machines are turned on and sending pings.

Query

let shutdownComputers = dynamic(["NET-CCWALLBOARD.networkhg.org.uk","NET-FS3.networkhg.org.uk","NET-GISAPP1.networkhg.org.uk","NET-GISSQL1.networkhg.org.uk","NET-OVUAT2.networkhg.org.uk","NET-P2PTESTAPP1.networkhg.org.uk"]);
// config the hours to exclude
let startHour = 06;
let endHour = 22;
Heartbeat
// Get just the excluded Servers
| where TimeGenerated > startofday(ago(1h))
| where Computer in (shutdownComputers)
| summarize LastCall = arg_max( TimeGenerated, datetime_part("hour", TimeGenerated) between( startHour .. endHour) )
by Computer, sComputer = strcat("Computer in OFFLINE list from ", 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(1h))
| 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

Results 

CliveWatson   I did query accroding to my need.

Still not working, please let me know, where I went wrong.

// please add a list of your servers here, these ones are the ones that are *shutdown* overnight

Arslan11 

I think I'm understanding your requirements a bit more now.  This now does the work in two phases, the first part deals with the shutdown servers in the time windows you specified.  I then join those with all the other servers, to show the lastCall for both (but none of the ones in the shutdown window).   I that right?  Please test and adjust the KQL yourself to suit your expected outcome.   

// please add a list of your servers here, these ones are the ones that are *shutdown* overnight
let shutdownComputers = dynamic(["rancher-node-1","rancher-node-2","rancher-node-3"]);
// config the hours to exclude
let startHour = 07;   // 7am
let endHour   = 22;   // 10pm
Heartbeat
// Get just the excluded Servers
| where TimeGenerated > startofday(ago(1d)) 
| where Computer in (shutdownComputers)
| summarize LastCall = arg_max( TimeGenerated, datetime_part("hour", TimeGenerated) between( startHour .. endHour) )
            by Computer, sComputer = strcat("Computer in OFFLINE list from ", 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(1d)) 
    | where Computer !in (shutdownComputers)
    | 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

Go to Log Analytics and run query

CliveWatson Not an alert, just a query that  I run to see if there were any machines that weren't sending the  pings , and one machine came up at this time.

Can you please have a look at this query again, I still want to be alerted about other machines which is not sending the pings,  expect the one's which get's turn off at 10:00 pm and turn back on at 6:00 am as shown in the query below, which you helped

Heartbeat existing query

Heartbeat
| where TimeGenerated > ago(24h)
| where Computer != "NH-CMVMAAZ.networkhg.org.uk" and Computer != "UAT-WVD-REL86-0.networkhg.org.uk"
| where Computer == "NET-CCWALLBOARD.networkhg.org.uk" and Computer == "NET-FS3.networkhg.org.uk" and Computer == "NET-GISAPP1.networkhg.org.uk" and Computer == "NET-GISSQL1.networkhg.org.uk" and Computer == "NET-OVUAT2.networkhg.org.uk" and Computer == "NET-P2PTESTAPP1.networkhg.org.uk" and Computer == "NH-AAHW2.networkhg.org.uk" and Computer == "NH-ADAPPP-02.networkhg.org.uk" and Computer == "VM-WVD-REL86-0.networkhg.org.uk" and Computer == "VM-WVD-REL86-1.networkhg.org.uk" and Computer == "VM-WVD-REL86-2.networkhg.org.uk" and Computer == "VM-WVD-REL86-3.networkhg.org.uk" and Computer == "VM-WVD-REL86-4.networkhg.org.uk"
| extend hour = datetime_part("hour", TimeGenerated)
| where hour between (06 .. 22)
| summarize LastCall = max(TimeGenerated) by Computer, ComputerEnvironment