May 04 2020
05:22 AM
- last edited on
Apr 08 2022
10:25 AM
by
TechCommunityAP
May 04 2020
05:22 AM
- last edited on
Apr 08 2022
10:25 AM
by
TechCommunityAP
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
May 06 2020 12:30 PM
May 06 2020 03:22 PM - edited May 06 2020 03:29 PM
@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
May 07 2020 05:58 AM
So the requirements are:
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?
May 07 2020 06:40 AM
@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.
May 07 2020 06:49 AM
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)
...
...
May 07 2020 09:09 AM
@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