heartbeat query specific hours on workweek and excluding weekends

%3CLINGO-SUB%20id%3D%22lingo-sub-638019%22%20slang%3D%22en-US%22%3Eheartbeat%20query%20specific%20hours%20on%20workweek%20and%20excluding%20weekends%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-638019%22%20slang%3D%22en-US%22%3E%3CP%3EHello%2C%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI%20am%20trying%20to%20gather%20heartbeat%20result%20of%20all%20computers%2C%20but%20for%20specific%20hours%20on%20workweek%20and%20excluding%20the%20weekends%20and%20holidays.%20So%20far%2C%20I%20am%20able%20to%20aggregate%20total%20hours%20of%20time%20and%20total%20uptime%20of%20all%20vm%20%2C%20but%20due%20to%20schedule%20off%20time%2C%20the%20result%20coming%20with%20low%20%25%20uptime%20value.%20I%20would%20like%20to%20filter%20the%20production%20hours..%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CDIV%3E%3CDIV%3E%3CSPAN%3Elet%20StartTime%20%3Dstartofday(ago(%3C%2FSPAN%3E%3CSPAN%3E30%3C%2FSPAN%3E%3CSPAN%3Ed))%3B%3C%2FSPAN%3E%3C%2FDIV%3E%3CDIV%3E%3CSPAN%3Elet%20EndTime%20%3Dendofday(ago(%3C%2FSPAN%3E%3CSPAN%3E10%3C%2FSPAN%3E%3CSPAN%3Em))%3B%3C%2FSPAN%3E%3C%2FDIV%3E%3CDIV%3E%3CSPAN%3EHeartbeat%3C%2FSPAN%3E%3C%2FDIV%3E%3CDIV%3E%3CSPAN%3E%7C%20%3C%2FSPAN%3E%3CSPAN%3Ewhere%3C%2FSPAN%3E%3CSPAN%3E%20TimeGenerated%20%26gt%3B%20StartTime%20%3C%2FSPAN%3E%3CSPAN%3Eand%3C%2FSPAN%3E%3CSPAN%3E%20TimeGenerated%20%26lt%3B%20EndTime%3C%2FSPAN%3E%3C%2FDIV%3E%3CDIV%3E%3CSPAN%3E%7C%20%3C%2FSPAN%3E%3CSPAN%3Eextend%3C%2FSPAN%3E%3CSPAN%3E%20TimeStart%20%3D%20startofday(TimeGenerated)%2B%3C%2FSPAN%3E%3CSPAN%3E7%3C%2FSPAN%3E%3CSPAN%3Eh%3C%2FSPAN%3E%3C%2FDIV%3E%3CDIV%3E%3CSPAN%3E%7C%20%3C%2FSPAN%3E%3CSPAN%3Eextend%3C%2FSPAN%3E%3CSPAN%3E%20TimeEnd%20%3D%20startofday(TimeGenerated)%2B%3C%2FSPAN%3E%3CSPAN%3E19%3C%2FSPAN%3E%3CSPAN%3Eh%3C%2FSPAN%3E%3C%2FDIV%3E%3CDIV%3E%3CSPAN%3E%7C%20%3C%2FSPAN%3E%3CSPAN%3Ewhere%3C%2FSPAN%3E%3CSPAN%3E%20TimeGenerated%20between%20(TimeStart..TimeEnd)%3C%2FSPAN%3E%3C%2FDIV%3E%3CDIV%3E%3CSPAN%3E%7C%20%3C%2FSPAN%3E%3CSPAN%3Esummarize%3C%2FSPAN%3E%3CSPAN%3E%20heartbeat_per_hour%3D%3C%2FSPAN%3E%3CSPAN%3Ecount%3C%2FSPAN%3E%3CSPAN%3E()%20%3C%2FSPAN%3E%3CSPAN%3Eby%3C%2FSPAN%3E%3CSPAN%3E%20bin_at(TimeGenerated%2C%20%3C%2FSPAN%3E%3CSPAN%3E1%3C%2FSPAN%3E%3CSPAN%3Eh%2C%20StartTime)%2C%20Computer%3C%2FSPAN%3E%3C%2FDIV%3E%3CDIV%3E%3CSPAN%3E%7C%20%3C%2FSPAN%3E%3CSPAN%3Eextend%3C%2FSPAN%3E%3CSPAN%3E%20available_per_hour%3Diff(heartbeat_per_hour%26gt%3B%3C%2FSPAN%3E%3CSPAN%3E0%3C%2FSPAN%3E%3CSPAN%3E%2C%20%3C%2FSPAN%3E%3CSPAN%3Etrue%3C%2FSPAN%3E%3CSPAN%3E%2C%20%3C%2FSPAN%3E%3CSPAN%3Efalse%3C%2FSPAN%3E%3CSPAN%3E)%3C%2FSPAN%3E%3C%2FDIV%3E%3CDIV%3E%3CSPAN%3E%7C%20%3C%2FSPAN%3E%3CSPAN%3Esummarize%3C%2FSPAN%3E%3CSPAN%3E%20total_available_hours%3Dcountif(available_per_hour%3D%3D%3C%2FSPAN%3E%3CSPAN%3Etrue%3C%2FSPAN%3E%3CSPAN%3E)%20%3C%2FSPAN%3E%3CSPAN%3Eby%3C%2FSPAN%3E%3CSPAN%3E%20Computer%3C%2FSPAN%3E%3C%2FDIV%3E%3CDIV%3E%3CSPAN%3E%7C%20%3C%2FSPAN%3E%3CSPAN%3Eextend%3C%2FSPAN%3E%3CSPAN%3E%20total_number_of_buckets%3Dround((EndTime-StartTime)%2F%3C%2FSPAN%3E%3CSPAN%3E1%3C%2FSPAN%3E%3CSPAN%3Eh)%3C%2FSPAN%3E%3C%2FDIV%3E%3CDIV%3E%3CSPAN%3E%7C%20%3C%2FSPAN%3E%3CSPAN%3Eextend%3C%2FSPAN%3E%3CSPAN%3E%20UnAvailable%3D%20total_number_of_buckets%20-%20total_available_hours%3C%2FSPAN%3E%3C%2FDIV%3E%3CDIV%3E%3CSPAN%3E%7C%20%3C%2FSPAN%3E%3CSPAN%3Eextend%3C%2FSPAN%3E%3CSPAN%3E%20Uptime_percentage%20%3D(total_available_hours*%3C%2FSPAN%3E%3CSPAN%3E100%3C%2FSPAN%3E%3CSPAN%3E)%2Ftotal_number_of_buckets%3C%2FSPAN%3E%3C%2FDIV%3E%3C%2FDIV%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-638019%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EAzure%20Log%20Analytics%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-643347%22%20slang%3D%22en-US%22%3ERe%3A%20heartbeat%20query%20specific%20hours%20on%20workweek%20and%20excluding%20weekends%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-643347%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F347449%22%20target%3D%22_blank%22%3E%40tazzking22%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3ESnip%2C%20to%20show%20how%20to%20only%20get%20data%20from%20between%209%3A00%20and%2017%3A00%20as%20an%20example%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CPRE%3Elet%20StartTime%20%3Dstartofday(ago(30d))%3B%0Alet%20EndTime%20%3Dendofday(ago(10m))%3B%0Alet%20prodhourstart%20%3D%209%3B%0Alet%20prodhourend%20%3D%2017%3B%0AHeartbeat%0A%7C%20where%20TimeGenerated%20%26gt%3B%20StartTime%20and%20TimeGenerated%20%26lt%3B%20EndTime%0A%7C%20extend%20bythehour%20%3D%20datetime_part(%22hour%22%2C%20TimeGenerated)%0A%7C%20where%20bythehour%20between%20(prodhourstart%20..%20prodhourend)%3C%2FPRE%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E
Highlighted
Occasional Visitor

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
Highlighted

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