Machine not sedning pings

[Arslan11]

Kusto query 

Heartbeat
| where TimeGenerated > ago(24h)
| where Computer != "NH-CMVMAAZ.networkhg.org.uk" and Computer != "UAT-WVD-REL86-0.networkhg.org.uk"
| summarize LastCall = max(TimeGenerated) by Computer, ComputerEnvironment
| where LastCall < ago(10m

I need assistance with this query, I don't want to be reported for the following servers in not sending pings, those severs get shutdown at 10:00pm UK time and starts at 6:00am uk time.

I don't want those servers to be reported from 10:00pm to 6:00am, how can I amend my existing query and make this possible

[#2]

Hello, You only needed to change line 1, not the 2nd to last line as well. I cannot tell what is not working without the results or error. This thread is probably getting too long. Maybe private message me the results, screenshot or csv file?

[#3]

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

NET-CCWALLBOARD1

Net-fs3

NET-GISAPP1

NET-GISSQL1

NET-OVUAT2

NET-P2PTESTAPP1

NH-AAHW2

NH-ADAPPP-02

NH-CMVMAAZ

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 

[#4]

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

| where LastCall < ago(10m)

[#5]

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

[#6]

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

// 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 computers

let excludeComputers = dynamic(["demo1","demo2","demo3","node-4"]);

...

...

Heartbeat

| where TimeGenerated > startofday(ago(1d))

| where Computer !in (shutdownComputers) and Computer !in(excludeComputers)

| summarize LastCall = arg_max(TimeGenerated,*) by Computer

[#7]

@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