heartbeat query specific hours on workweek and excluding weekends

Copper Contributor

Hello,

 

I am trying to gather heartbeat result of all computers, but for specific hours on workweek and excluding the weekends and holidays. So far, I am able to aggregate total hours of time and total uptime of all vm , but due to schedule off time, the result coming with low % uptime value. I would like to filter the production hours..

 

let StartTime =startofday(ago(30d));
let EndTime =endofday(ago(10m));
Heartbeat
| where TimeGenerated > StartTime and TimeGenerated < EndTime
| extend TimeStart = startofday(TimeGenerated)+7h
| extend TimeEnd = startofday(TimeGenerated)+19h
| where TimeGenerated between (TimeStart..TimeEnd)
| summarize heartbeat_per_hour=count() by bin_at(TimeGenerated, 1h, StartTime), Computer
| extend available_per_hour=iff(heartbeat_per_hour>0, true, false)
| summarize total_available_hours=countif(available_per_hour==true) by Computer
| extend total_number_of_buckets=round((EndTime-StartTime)/1h)
| extend UnAvailable= total_number_of_buckets - total_available_hours
| extend Uptime_percentage =(total_available_hours*100)/total_number_of_buckets

 

 

1 Reply

@tazzking22 

 

Snip, to show how to only get data from between 9:00 and 17:00 as an example

 

let StartTime =startofday(ago(30d));
let EndTime =endofday(ago(10m));
let prodhourstart = 9;
let prodhourend = 17;
Heartbeat
| where TimeGenerated > StartTime and TimeGenerated < EndTime
| extend bythehour = datetime_part("hour", TimeGenerated)
| where bythehour between (prodhourstart .. prodhourend)