SOLVED

Availability on OMS

%3CLINGO-SUB%20id%3D%22lingo-sub-145267%22%20slang%3D%22en-US%22%3EAvailability%20on%20OMS%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-145267%22%20slang%3D%22en-US%22%3E%3CP%3EHi%20everyone.%3C%2FP%3E%0A%3CP%3EI'm%20trying%20to%20find%20a%20way%20of%20getting%20Availability%20of%20servers%20on%20OMS%2C%20but%20I%20can't%20find%20any...%3C%2FP%3E%0A%3CP%3EBy%20Availability%20I%20mean%20the%20%25%20of%20uptime%20of%20a%20given%20server%20during%20a%20certain%20period%20of%20time.%3C%2FP%3E%0A%3CP%3ESo%2C%20if%20a%20server%20was%20up%2098%20of%20a%20total%20100%20hours%2C%20the%20availability%20for%20that%20period%20is%2098%25.%3C%2FP%3E%0A%3CP%3EI'm%20looking%20to%20do%20that%20in%20OMS%2C%20but%20I'm%20not%20sure%20it's%20possible.%3C%2FP%3E%0A%3CP%3EThanks%20in%20advance.%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-145267%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3ECustom%20Logs%20and%20Custom%20Fields%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-288879%22%20slang%3D%22en-US%22%3ERe%3A%20Availability%20on%20OMS%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-288879%22%20slang%3D%22en-US%22%3E%3CP%3EDoes%20anyone%20have%20a%20way%20to%20restrict%20the%20script%20to%20pull%20heartbeats%20Mon-Fri%20and%207am-6pm%3F%20I%20keep%20getting%20errors.%20This%20is%20what%20I%20have%3A%3C%2FP%3E%3CDIV%3E%3CDIV%3E%3CSPAN%3Elet%20start_time%3Dstartofday(%3C%2FSPAN%3E%3CSPAN%3Edatetime%3C%2FSPAN%3E%3CSPAN%3E(%3C%2FSPAN%3E%3CSPAN%3E%222018-06-1%2007%3A30%3A00%22%3C%2FSPAN%3E%3CSPAN%3E))%3B%3C%2FSPAN%3E%3C%2FDIV%3E%3CDIV%3E%3CSPAN%3Elet%20end_time%3Dendofday(%3C%2FSPAN%3E%3CSPAN%3Edatetime%3C%2FSPAN%3E%3CSPAN%3E(%3C%2FSPAN%3E%3CSPAN%3E%222018-06-30%2018%3A00%3A00%22%3C%2FSPAN%3E%3CSPAN%3E))%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%20start_time%20%3C%2FSPAN%3E%3CSPAN%3Eand%3C%2FSPAN%3E%3CSPAN%3E%20TimeGenerated%20%26lt%3B%20end_time%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%20start_time)%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((end_time-start_time)%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%20availability_rate%3Dtotal_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%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-280429%22%20slang%3D%22en-US%22%3ERe%3A%20Availability%20on%20OMS%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-280429%22%20slang%3D%22en-US%22%3E%3CP%3EAwesome%20Script%20Thanks%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-280428%22%20slang%3D%22en-US%22%3ERe%3A%20Availability%20on%20OMS%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-280428%22%20slang%3D%22en-US%22%3E%3CP%3Elet%20start_time%3Dstartofday(ago(30d))%3B%3CBR%20%2F%3Elet%20end_time%3Dstartofday(now())%3B%3CBR%20%2F%3EHeartbeat%3CBR%20%2F%3E%7C%20where%20TimeGenerated%20%26gt%3B%20start_time%20and%20TimeGenerated%20%26lt%3B%20end_time%3CBR%20%2F%3E%7C%20summarize%20heartbeat_per_hour%3Dcount()%20by%20bin_at(TimeGenerated%2C%201h%2C%20start_time)%2C%20Computer%3CBR%20%2F%3E%7C%20extend%20available_per_hour%3Diff(heartbeat_per_hour%26gt%3B0%2C%20true%2C%20false)%3CBR%20%2F%3E%7C%20summarize%20total_available_hours%3Dcountif(available_per_hour%3D%3Dtrue)%20by%20Computer%3CBR%20%2F%3E%7C%20extend%20total_number_of_buckets%3Dround((end_time-start_time)%2F1h)%3CBR%20%2F%3E%7C%20extend%20availability_rate%3Dtotal_available_hours*100%2Ftotal_number_of_buckets%3CBR%20%2F%3E%7C%20order%20by%20availability_rate%20desc%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-274712%22%20slang%3D%22en-US%22%3ERe%3A%20Availability%20on%20OMS%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-274712%22%20slang%3D%22en-US%22%3E%3CP%3ELove%20this%20query.%20I'm%20having%20trouble%20modifying%20it%20to%20meet%20my%20needs.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EIn%20addition%20to%20what%20this%20query%20provides%2C%20I'd%20also%20like%20to%20show%20the%20last%20TimeGenerated%20for%20each%20Computer.%20I%20can't%20seem%20to%20get%20the%20logic%20to%20work%20correctly.%20Any%20help%20is%20appreciated.%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-251804%22%20slang%3D%22en-US%22%3ERe%3A%20Availability%20on%20OMS%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-251804%22%20slang%3D%22en-US%22%3E%3CP%3EHi%20Gaurav%2C%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI%20got%20it%2C%26nbsp%3B%3C%2FP%3E%3CP%3EThank%20You%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-251545%22%20slang%3D%22en-US%22%3ERe%3A%20Availability%20on%20OMS%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-251545%22%20slang%3D%22en-US%22%3E%3CP%3EHi%20Prashant%2C%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EWe%20can%20get%20data%20by%20doing%20amendment%20in%20dates.%20But%20make%20sure%20you%20have%20data%20retention%20policy%20for%20last%20year%20to%20save%20data.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EYou%20can%20check%20here%3A-%3C%2FP%3E%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20style%3D%22width%3A%20999px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Fgxcuf89792.i.lithium.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F49543i67ECB080A574297F%2Fimage-size%2Flarge%3Fv%3D1.0%26amp%3Bpx%3D999%22%20alt%3D%22OMS%20data%20retention.PNG%22%20title%3D%22OMS%20data%20retention.PNG%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-251512%22%20slang%3D%22en-US%22%3ERe%3A%20Availability%20on%20OMS%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-251512%22%20slang%3D%22en-US%22%3E%3CP%3EThanks%20but%20I%20got%20the%20answer%3C%2FP%3E%3CDIV%3E%3CDIV%3E%3CDIV%3E%3CSPAN%3Elet%20month%20%3D%20startofday(ago(%3C%2FSPAN%3E%3CSPAN%3E3%3C%2FSPAN%3E%3CSPAN%3Ed))%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%26gt%3Bago(%3C%2FSPAN%3E%3CSPAN%3E3%3C%2FSPAN%3E%3CSPAN%3Ed)%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%20(ago(%3C%2FSPAN%3E%3CSPAN%3E3%3C%2FSPAN%3E%3CSPAN%3Ed)))%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%3D%20round((now()-month)%2F%3C%2FSPAN%3E%3CSPAN%3E1%3C%2FSPAN%3E%3CSPAN%3Eh)-%3C%2FSPAN%3E%3CSPAN%3E2%3C%2FSPAN%3E%3C%2FDIV%3E%3CDIV%3E%3CSPAN%3E%7C%20%3C%2FSPAN%3E%3CSPAN%3Eextend%3C%2FSPAN%3E%3CSPAN%3E%20availability_rate%3Dtotal_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%3C%2FDIV%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-251501%22%20slang%3D%22en-US%22%3ERe%3A%20Availability%20on%20OMS%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-251501%22%20slang%3D%22en-US%22%3E%3CP%3ECan%26nbsp%3B%20we%20Availabilty%20for%20past%2010%20days%20instead%20of%20add%20start%20date%20and%20End%20date%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-225362%22%20slang%3D%22en-US%22%3ERe%3A%20Availability%20on%20OMS%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-225362%22%20slang%3D%22en-US%22%3EHey%20Noa%2C%3CBR%20%2F%3E%3CBR%20%2F%3ECan%20we%20take%20the%201%20year%20details%20by%20this%20script.%3F%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-225247%22%20slang%3D%22en-US%22%3ERe%3A%20Availability%20on%20OMS%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-225247%22%20slang%3D%22en-US%22%3E%3CP%3EI%20am%20struggling%20to%20generate%20the%20report%20for%20Mon-Friday%20only%20and%20in%20my%20time%20zone.%20I%20just%20get%20errors.%20The%20script%20below%20works%20for%20me.%20Thanks%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CDIV%3E%3CDIV%3E%3CSPAN%3Elet%20start_time%3Dstartofday(%3C%2FSPAN%3E%3CSPAN%3Edatetime%3C%2FSPAN%3E%3CSPAN%3E(%3C%2FSPAN%3E%3CSPAN%3E%222018-07-1%2007%3A30%3A00%22%3C%2FSPAN%3E%3CSPAN%3E))%3B%3C%2FSPAN%3E%3C%2FDIV%3E%3CDIV%3E%3CSPAN%3Elet%20end_time%3Dendofday(%3C%2FSPAN%3E%3CSPAN%3Edatetime%3C%2FSPAN%3E%3CSPAN%3E(%3C%2FSPAN%3E%3CSPAN%3E%222018-07-31%2018%3A00%3A00%22%3C%2FSPAN%3E%3CSPAN%3E))%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%20start_time%20%3C%2FSPAN%3E%3CSPAN%3Eand%3C%2FSPAN%3E%3CSPAN%3E%20TimeGenerated%20%26lt%3B%20end_time%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%20start_time)%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((end_time-start_time)%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%20availability_rate%3Dtotal_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%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-225245%22%20slang%3D%22en-US%22%3ERe%3A%20Availability%20on%20OMS%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-225245%22%20slang%3D%22en-US%22%3E%3CP%3EI%20am%20struggling%20to%20generate%20the%20report%20for%20Mon-Friday%20only%20and%20in%20my%20time%20zone.%20I%20just%20get%20errors.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CDIV%3E%3CDIV%3E%3CSPAN%3Elet%20start_time%3Dstartofday(%3C%2FSPAN%3E%3CSPAN%3Edatetime%3C%2FSPAN%3E%3CSPAN%3E(%3C%2FSPAN%3E%3CSPAN%3E%222018-07-1%2007%3A30%3A00%22%3C%2FSPAN%3E%3CSPAN%3E))%3B%3C%2FSPAN%3E%3C%2FDIV%3E%3CDIV%3E%3CSPAN%3Elet%20end_time%3Dendofday(%3C%2FSPAN%3E%3CSPAN%3Edatetime%3C%2FSPAN%3E%3CSPAN%3E(%3C%2FSPAN%3E%3CSPAN%3E%222018-07-31%2018%3A00%3A00%22%3C%2FSPAN%3E%3CSPAN%3E))%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%20start_time%20%3C%2FSPAN%3E%3CSPAN%3Eand%3C%2FSPAN%3E%3CSPAN%3E%20TimeGenerated%20%26lt%3B%20end_time%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%20start_time)%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((end_time-start_time)%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%20availability_rate%3Dtotal_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%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-187457%22%20slang%3D%22en-US%22%3ERe%3A%20Availability%20on%20OMS%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-187457%22%20slang%3D%22en-US%22%3E%3CP%3EHey%2C%3C%2FP%3E%0A%3CP%3EThat's%20not%20powershell%20but%20our%20query%20language%2C%20that%20can%20be%20used%20%3CA%20href%3D%22https%3A%2F%2Fportal.loganalytics.io%2Fdemo%22%20target%3D%22_blank%22%20rel%3D%22nofollow%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%22%3Ehere%3C%2FA%3E%26nbsp%3Band%20through%20our%20%3CA%20href%3D%22https%3A%2F%2Fdev.loganalytics.io%2F%22%20target%3D%22_blank%22%20rel%3D%22nofollow%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%22%3EAPI%3C%2FA%3E.%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-187373%22%20slang%3D%22en-US%22%3ERe%3A%20Availability%20on%20OMS%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-187373%22%20slang%3D%22en-US%22%3Eit%20is%20a%20pwerShell%20Script%3F%3CBR%20%2F%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-180446%22%20slang%3D%22en-US%22%3ERe%3A%20Availability%20on%20OMS%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-180446%22%20slang%3D%22en-US%22%3E%3CP%3EHi%20Eric%2C%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3ETo%20adjust%20for%20the%20service%20agreement%2C%20you%20can%20calculate%20the%20start%20time%20and%20end%20time%20like%20this%3A%3C%2FP%3E%0A%3CPRE%3Elet%20raw_date%20%3D%20datetime(%222017-01-01%22)%3B%0Alet%20start_date%20%3D%20case(%22SLA%22%20in%20(%22S1%22%2C%20%22S2%22)%2C%20case(dayofweek(raw_date)%3D%3D0%2C%20startofday(raw_date%2B1d)%2B7h%2C%0A%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20dayofweek(raw_date)%3D%3D6%2C%20startofday(raw_date%2B2d)%2B7h%2C%20%0A%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20startofday(raw_date)%2B7h)%2C%0A%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20raw_date)%3B%3C%2FPRE%3E%0A%3CP%3EOn%20the%20intervals%20-%20it%20can%20adjusted%20any%20way%20you%20need%2C%20just%20use%20%60bin(fieldname%2C%2030m)%60%20instead%20of%20%60bin(fieldname%2C%201h)%60.%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-180443%22%20slang%3D%22en-US%22%3ERe%3A%20Availability%20on%20OMS%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-180443%22%20slang%3D%22en-US%22%3E%3CP%3EThanks%20George.%3C%2FP%3E%0A%3CP%3ETo%20adjust%20for%20the%20local%20time%20zone%20you%20can%20do%20this%3A%3C%2FP%3E%0A%3CDIV%3E%0A%3CPRE%3Elet%20midnight%3Dstartofday(now())-7h%3C%2FPRE%3E%0A%3C%2FDIV%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-164523%22%20slang%3D%22en-US%22%3ERe%3A%20Availability%20on%20OMS%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-164523%22%20slang%3D%22en-US%22%3E%3CP%3EHi%2C%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EThanks%20for%20a%20exellent%20code%20sample.%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EI%20would%20like%20to%20extend%20the%20Query%2C%20supporting%20also%20specified%20time%20intervals%20and%20smaller%20uptime%20checks%20(heartbeat)%3C%2FP%3E%0A%3CP%3E%23%20Service%20levels%3C%2FP%3E%0A%3CP%3EEx%3A%20Service%20agreements%20are%20based%20on%203%20categories%3C%2FP%3E%0A%3CP%3ES1%20%3D%2007%3A00%20-%2017%3A00%20Weekdays%3C%2FP%3E%0A%3CP%3ES2%20%3D%2007%3A00-%2022%3A00%20Weekdays%3C%2FP%3E%0A%3CP%3E365%2F7%20%3D%20Always%20(already%20supported%20by%20your%20query%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3E%3D%20Uptime%20should%20be%20calculated%20based%20on%20service%20agreement%20hours%2Fdays%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3ETime%20should%20also%20be%20converted%20to%20UTC%20%2B1%3C%2FP%3E%0A%3CP%3E-%20will%20this%20do%20the%20trick%20%3D%20%26gt%3B%20%26nbsp%3B%3C%2FP%3E%0A%3CDIV%3E%0A%3CDIV%3E%3CSPAN%3EHeartbeat%3C%2FSPAN%3E%3C%2FDIV%3E%0A%3CDIV%3E%3CSPAN%3E%7C%20%3C%2FSPAN%3E%3CSPAN%3Eextend%3C%2FSPAN%3E%3CSPAN%3E%20Timegenerated%20%3D%20TimeGenerated%20%2B%20%3C%2FSPAN%3E%3CSPAN%3E1%3C%2FSPAN%3E%3CSPAN%3Eh%3C%2FSPAN%3E%3C%2FDIV%3E%0A%3CDIV%3E%26nbsp%3B%3C%2FDIV%3E%0A%3CDIV%3E%0A%3CDIV%3E%3CSPAN%3EI%20checked%20the%20samples%20from%20endofday%2Fweek%2C%20but%20are%20unable%20to%20get%20it%20to%20work%20in%20your%20sample%3C%2FSPAN%3E%3C%2FDIV%3E%0A%3CDIV%3E%26nbsp%3B%3C%2FDIV%3E%0A%3C%2FDIV%3E%0A%3CDIV%3E%26nbsp%3B%3C%2FDIV%3E%0A%3CDIV%3E%3CSPAN%3E%23%20Intervals%3CBR%20%2F%3E%3C%2FSPAN%3E%3C%2FDIV%3E%0A%3CDIV%3E%0A%3CDIV%3E%0A%3CDIV%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%0A%3CDIV%3E%3CSPAN%3E%3D%20can%20this%20be%20adjusted%20to%20heartbeat%20per%2030%20min%20%2F%2015%20min%3C%2FSPAN%3E%3C%2FDIV%3E%0A%3C%2FDIV%3E%0A%3C%2FDIV%3E%0A%3CDIV%3E%26nbsp%3B%3C%2FDIV%3E%0A%3CDIV%3E%26nbsp%3B%3C%2FDIV%3E%0A%3CDIV%3E%3CSPAN%3EAny%20ideas%20%3F%3C%2FSPAN%3E%3C%2FDIV%3E%0A%3CDIV%3E%26nbsp%3B%3C%2FDIV%3E%0A%3CDIV%3E%3CSPAN%3EBr%3C%2FSPAN%3E%3C%2FDIV%3E%0A%3CDIV%3E%3CSPAN%3Eerik%3C%2FSPAN%3E%3C%2FDIV%3E%0A%3C%2FDIV%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-163939%22%20slang%3D%22en-US%22%3ERe%3A%20Availability%20on%20OMS%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-163939%22%20slang%3D%22en-US%22%3EExcellent%20query.%20%20How%20can%20%22let%20midnight%3Dstartofday(now())%22%20be%20altered%20to%20make%20it%20my%20local%20time%20zone%3F%20%20If%20I%20run%20this%20as%20is%2C%20it%20seems%20to%20be%20my%20time%20%2B7%2C%20and%20the%20amount%20of%20hours%20don't%20match%20up.%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-159448%22%20slang%3D%22en-US%22%3ERe%3A%20Availability%20on%20OMS%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-159448%22%20slang%3D%22en-US%22%3E%3CP%3ENoa%2C%20amazing%2C%20thank%20you%20so%20much.%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-156485%22%20slang%3D%22en-US%22%3ERe%3A%20Availability%20on%20OMS%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-156485%22%20slang%3D%22en-US%22%3E%3CP%3ESure.%20I%20tweaked%20it%20a%20bit%20to%20match%20what%20you%20ask%20for%3A%26nbsp%3B%3C%2FP%3E%0A%3CPRE%3Elet%20start_time%3Dstartofday(datetime(%222017-01-01%22))%3B%0Alet%20end_time%3Dendofday(datetime(%222017-01-31%22))%3B%0AHeartbeat%0A%7C%20where%20TimeGenerated%20%26gt%3B%20start_time%20and%20TimeGenerated%20%26lt%3B%20end_time%0A%7C%20summarize%20heartbeat_per_hour%3Dcount()%20by%20bin_at(TimeGenerated%2C%201h%2C%20start_time)%2C%20Computer%0A%7C%20extend%20available_per_hour%3Diff(heartbeat_per_hour%26gt%3B0%2C%20true%2C%20false)%0A%7C%20summarize%20total_available_hours%3Dcountif(available_per_hour%3D%3Dtrue)%20by%20Computer%20%0A%7C%20extend%20total_number_of_buckets%3Dround((end_time-start_time)%2F1h)%0A%7C%20extend%20availability_rate%3Dtotal_available_hours*100%2Ftotal_number_of_buckets%3C%2FPRE%3E%0A%3CP%3EThe%20first%202%20lines%20define%20variables%2C%20set%20to%20the%20start%20and%20end%20time%20you%20mentioned.%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3ENext%2C%20we%20use%20these%20variables%20to%20limit%20the%20query%20to%20that%20time%20range%3A%26nbsp%3B%3C%2FP%3E%0A%3CPRE%3E%7C%20where%20TimeGenerated%20%26gt%3B%20start_time%20and%20TimeGenerated%20%26lt%3B%20end_time%3C%2FPRE%3E%0A%3CP%3EThen%26nbsp%3Bwe%26nbsp%3Bcount%20the%20heartbeats%20reported%20from%20each%20computer%2C%20in%20buckets%20(bins)%20of%201%20hour%2C%20starting%20at%26nbsp%3Bthe%20start%20time%20you%20define%3A%26nbsp%3B%3C%2FP%3E%0A%3CPRE%3E%7C%20summarize%20heartbeat_per_hour%3Dcount()%20by%20bin_at(TimeGenerated%2C%201h%2C%20start_time)%2C%20Computer%3C%2FPRE%3E%0A%3CP%3ENow%20we%20can%20see%20how%20many%20heartbeats%20were%20reported%20by%20each%20computer%20each%20hour.%20If%20the%20number%20is%26nbsp%3B%200%20we%20understand%20the%20computer%20was%20probably%20offline%20at%20that%20time.%3C%2FP%3E%0A%3CP%3EWe%20use%20a%20new%20column%20to%20mark%20if%20a%20computer%20was%20available%20or%20not%20each%20hour%3A%26nbsp%3B%3C%2FP%3E%0A%3CPRE%3E%7C%20extend%20available_per_hour%3Diff(heartbeat_per_hour%26gt%3B0%2C%20true%2C%20false)%3C%2FPRE%3E%0A%3CP%3Eand%20then%20count%20the%20number%20of%20hours%20each%20computer%20was%20indeed%20%22alive%22%3A%26nbsp%3B%3C%2FP%3E%0A%3CPRE%3E%7C%20summarize%20total_available_hours%3Dcountif(available_per_hour%3D%3Dtrue)%20by%20Computer%3C%2FPRE%3E%0A%3CP%3ENote%20that%20this%20way%20we%20give%20a%20little%26nbsp%3Bleeway%20for%20missing%20heartbeat%20reports%20each%20hour.%20Instead%20of%20expecting%20a%20report%20every%205%20or%2010%20minutes%2C%20we%20only%20mark%20a%20computer%20as%20%22unavailable%22%20if%20we%20didn't%20get%20any%20report%20from%20it%20during%20a%20full%20hour.%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EAt%20this%20point%20we%20get%20a%20number%20for%20each%20computer%2C%20something%20like%20this%3A%26nbsp%3B%3C%2FP%3E%0A%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20style%3D%22width%3A%20595px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Fgxcuf89792.i.lithium.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F28426i10ECEFAB91DDD56C%2Fimage-size%2Flarge%3Fv%3D1.0%26amp%3Bpx%3D999%22%20alt%3D%22results1.png%22%20title%3D%22results1.png%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3ESo%20we%20know%20each%20computer%20was%20alive%2011%20hours%20in%20the%20select%20time%20range.%20But%20what%20does%20it%20mean%3F%20how%20many%20hours%20were%20there%20altogether%3F%20is%20this%2011%20out%20of%2011%20hours%20(100%25%20availability)%20or%20out%20of%20110%20hours%20(only%2010%25%20%3CSPAN%3Eavailability%3C%2FSPAN%3E)%3F%3C%2FP%3E%0A%3CP%3EHere's%20how%20we%20can%20calculate%20the%20total%20number%20of%20hours%20in%20the%20selected%20time%20range%3A%26nbsp%3B%3C%2FP%3E%0A%3CPRE%3E%7C%20extend%20total_number_of_buckets%3Dround((end_time-start_time)%2F1h)%2B1%3C%2FPRE%3E%0A%3CP%3EI%20admit%20it%20might%20not%20be%20the%20best%20calculation%20of%20buckets..%20there%20is%20probably%20a%20better%20way%20but%20I%20can't%20think%20of%20it%20now..%3C%2FP%3E%0A%3CP%3E%26nbsp%3Bfinally%20we%20calculate%20the%20ratio%20between%20available%20hours%20and%20total%20hours%3A%3C%2FP%3E%0A%3CPRE%3E%7C%20extend%20availability_rate%3Dtotal_available_hours*100%2Ftotal_number_of_buckets%3C%2FPRE%3E%0A%3CP%3Eand%20get%20this%3A%3C%2FP%3E%0A%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20style%3D%22width%3A%20958px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Fgxcuf89792.i.lithium.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F28431i5CF48473094748EE%2Fimage-size%2Flarge%3Fv%3D1.0%26amp%3Bpx%3D999%22%20alt%3D%22results2.png%22%20title%3D%22results2.png%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EHTH%2C%3C%2FP%3E%0A%3CP%3ENoa%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-155702%22%20slang%3D%22en-US%22%3ERe%3A%20Availability%20on%20OMS%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-155702%22%20slang%3D%22en-US%22%3E%3CP%3ENoa%2C%20your%20script%20is%20amazing%2C%20however%20i'm%20struggling%20to%20understand%20it%20and%20tweak%20it%20to%20my%20needs%20(30%20fixed%20days%2C%20for%20example%20from%201st%20to%2031%20of%20january)%3C%2FP%3E%0A%3CP%3ECould%20you%20gimme%20a%20hand%20to%20understand%20it%3F%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CPRE%3Elet%20midnight%3Dstartofday(now())%20%3B%20%3CSTRONG%3E%23%3CEM%3EFirst%20part.%20I%20need%20to%20change%20this%20to%20between((2018-01-01)%20..%20(2017-01-31))%3B%20am%20I%20correct%3F%3C%2FEM%3E%3C%2FSTRONG%3E%0AHeartbeat%0A%7C%20where%20TimeGenerated%26gt%3Bmidnight%0A%7C%20summarize%20heartbeat_per_hour%3Dcount()%20by%20bin_at(TimeGenerated%2C%201h%2C%20midnight)%20%3CSTRONG%3E%23im%20not%20sure%20i%20understand%20why%20do%20you%20use%20bin_at%20instead%20of%20just%20bin%3C%2FSTRONG%3E%2C%20Computer%0A%7C%20extend%20available_per_hour%3Diff(heartbeat_per_hour%26gt%3B0%2C%20true%2C%20false)%0A%7C%20summarize%20total_available_hours%3Dcountif(available_per_hour%3D%3Dtrue)%20by%20Computer%20%0A%7C%20extend%20number_of_buckets%3Dhourofday(now())%2B1%0A%7C%20extend%20availability_rate%3Dtotal_available_hours*100%2Fnumber_of_buckets%3C%2FPRE%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-145505%22%20slang%3D%22en-US%22%3ERe%3A%20Availability%20on%20OMS%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-145505%22%20slang%3D%22en-US%22%3E%3CP%3EThe%20last%20situation%20MMA%20agent%20not%20working%20properly%20or%20has%20stopped%20working%20is%20exactly%20what%20worries%20me%20in%20order%20to%20create%20the%20availability%20report%20based%20on%20heartbeats.%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-145478%22%20slang%3D%22en-US%22%3ERe%3A%20Availability%20on%20OMS%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-145478%22%20slang%3D%22en-US%22%3EHeartbeat%20should%20be%20running%20without%20issue%20however%20there%20are%20scenarios%20when%20you%20might%20not%20get%20data%3A%0A-%20Log%20Analytics%20service%20is%20down%0A-%20You%20machine%20has%20lost%20Internet%20connection%0A-%20MMA%20agent%20service%20is%20stopped%0A-%20MMA%20agent%20is%20not%20functioning%20properly%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-145477%22%20slang%3D%22en-US%22%3ERe%3A%20Availability%20on%20OMS%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-145477%22%20slang%3D%22en-US%22%3E%3CP%3EThank%20you%20for%20your%20help%2C%20im%20going%20to%20investigate%20a%20bit%20that%20query.%3C%2FP%3E%0A%3CP%3EHowever%2C%20i'm%20not%20sure%20about%20that%20approach%20because%20the%20heartbeat%20happens%20to%20stop%20working%20a%20lot%20even%20if%20the%20VM%20is%20perfectly%20fine.%3C%2FP%3E%0A%3CP%3EBut%20I%20understand%20the%20approach...%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-145441%22%20slang%3D%22en-US%22%3ERe%3A%20Availability%20on%20OMS%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-145441%22%20slang%3D%22en-US%22%3E%3CP%3EStanislav%20is%20right%2C%20it's%20possible%20%3A)%3C%2Fimg%3E%3C%2FP%3E%0A%3CP%3EHere's%20an%20example%20that%20calculates%20the%20availability%20rate%20of%20each%20computer%2C%20starting%20at%20midnight.%3C%2FP%3E%0A%3CPRE%3Elet%20midnight%3Dstartofday(now())%3B%0AHeartbeat%0A%7C%20where%20TimeGenerated%26gt%3Bmidnight%0A%7C%20summarize%20heartbeat_per_hour%3Dcount()%20by%20bin_at(TimeGenerated%2C%201h%2C%20midnight)%2C%20Computer%0A%7C%20extend%20available_per_hour%3Diff(heartbeat_per_hour%26gt%3B0%2C%20true%2C%20false)%0A%7C%20summarize%20total_available_hours%3Dcountif(available_per_hour%3D%3Dtrue)%20by%20Computer%20%0A%7C%20extend%20number_of_buckets%3Dhourofday(now())%2B1%0A%7C%20extend%20availability_rate%3Dtotal_available_hours*100%2Fnumber_of_buckets%3C%2FPRE%3E%0A%3CP%3E%3CA%20href%3D%22https%3A%2F%2Fportal.loganalytics.io%2FDemo%3Fq%3DH4sIAAAAAAAAA22Qz07DMAyH75P2Dj4mEIn2jLILB3gA7pGzOjQif6bUYRTx8Cygsk3d1fbv82cHYoh%252BSP5tZD0xFs5uwFmkfBRSPm43L3SqWULebr7hOFIhePWRnilRQaZht6Rbf6oxYvFfBOMSMwcqZsy16H2uiYUEO4P1ySCLK5CCflT%252FLlLBU46HylQamD6Z0gD4gT6gDXSmeufEetmuU8ClkgKHYSJ5LceZMZgzrCWmPz%252FvxI0lurF%252BzRcpuLBKNdrTZHbG1v078aRb6PKP9%252F36CB88z6adrm%252F63PVd97BC%252FwBe7kgPsAEAAA%253D%253D%22%20target%3D%22_blank%22%20rel%3D%22nofollow%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%22%3ERun%20it%20on%20our%20playground%3C%2FA%3E%26nbsp%3Band%20tweak%20it%26nbsp%3Bas%20makes%20sense%20to%20you.%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-145364%22%20slang%3D%22en-US%22%3ERe%3A%20Availability%20on%20OMS%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-145364%22%20slang%3D%22en-US%22%3EHi%0AThere%20is%20no%20out%20of%20the%20box%20solution%20for%20this.%20Probably%20one%20way%20to%20calculate%20this%20is%20by%20using%20Heartbeat%20events.%20For%20Windows%20machines%20they%20are%20logged%20every%201%20minute%20and%20for%20Linux%20every%205%20minutes.%20You%20could%20probably%20calculate%20what%20should%20be%20the%20sum%20of%20the%20events%20for%20each%20machine%20and%20also%20the%20maximum%20sum%20for%20specific%20time%20frame.%20From%20those%20two%20values%20you%20can%20calculate%20the%20percentage.%20The%20query%20language%20is%20pretty%20rich%20so%20this%20should%20be%20possible.%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-807939%22%20slang%3D%22en-US%22%3ERe%3A%20Availability%20on%20OMS%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-807939%22%20slang%3D%22en-US%22%3EI%20have%20used%20the%20query%2C%20there%20are%20some%20differences%20which%20is%20not%20reflecting%20for%20us%20in%20the%20log%20analytics.%3CBR%20%2F%3E%3CBR%20%2F%3EWe%20are%20doing%20the%20Monthly%20patches%20for%20the%20Virtual%20Machines.So%20when%20i%20tried%20this%20query%20it%20is%20showing%20the%20availability%20rate%20as%20100%25%20percentage.%3CBR%20%2F%3EBut%20we%20are%20rebooting%20the%20server%20after%20the%20patching%20activity%2C%20the%20Availability%20report%20percentage%20should%20differ%20but%20it%20is%20showing%20100%25%20percent%20for%20all%20the%20servers.%3CBR%20%2F%3E%3CBR%20%2F%3ECan%20anyone%20help%20us%20on%20this%20%3F%20how%20can%20we%20get%20the%20exact%20report%20%3F%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-814437%22%20slang%3D%22en-US%22%3ERe%3A%20Availability%20on%20OMS%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-814437%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F393401%22%20target%3D%22_blank%22%3E%40MaheshG%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EHi!%20I've%20update%20the%20above%20query%20to%20reflect%20your%20week%20days%20and%20hours%20(Mon-Fri%2C%2007%3A00-17%3A59).%3C%2FP%3E%0A%3CP%3E%3CSPAN%20style%3D%22font-family%3A%20inherit%3B%22%3EAlso%2C%20the%20above%20query%20considers%20every%20hour%20in%20which%20there%20was%20even%201%20heartbeat%20as%20%22up%20time%22%20(available)%2C%20so%20this%20is%20probably%20the%20part%20you%20want%20to%20tweak.%20The%20resolution%20here%20depends%20on%20your%20agent.%20If%20it%20reports%20a%20heartbeat%20every%205%20minutes%2C%20you%20can%20do%20this%3A%3C%2FSPAN%3E%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CPRE%20class%3D%22lia-code-sample%20language-markup%22%3E%3CCODE%3Elet%20start_time%3Dstartofday(datetime(%222019-08-01%2007%3A00%3A00%22))%3B%0Alet%20end_time%3Dendofday(datetime(%222019-08-30%2018%3A00%3A00%22))%3B%0AHeartbeat%0A%7C%20where%20TimeGenerated%20%26gt%3B%3D%20start_time%20and%20TimeGenerated%20%26lt%3B%3D%20end_time%0A%7C%20where%20dayofweek(TimeGenerated)%20%26gt%3B%3D%201d%20and%20dayofweek(TimeGenerated)%20%26lt%3B%3D%205d%20%20%2F%2F%20Monday-Friday%0A%7C%20where%20hourofday(TimeGenerated)%20%26gt%3B%3D%207%20and%20hourofday(TimeGenerated)%20%26lt%3B%3D17%20%20%20%2F%2F%207%3A00-17%3A59%0A%7C%20summarize%20heartbeat_per_5_minutes%3Dcount()%20by%20bin_at(TimeGenerated%2C%205m%2C%20start_time)%2C%20Computer%0A%7C%20extend%20available_per_5_min%3Diff(heartbeat_per_5_minutes%26gt%3B0%2C%20true%2C%20false)%0A%7C%20summarize%20total_available_buckets%3Dcountif(available_per_5_min%3D%3Dtrue)%0A%2C%20total_unavailable_buckets%3Dcountif(available_per_5_min%3D%3Dfalse)%20by%20Computer%0A%7C%20extend%20total_number_of_buckets%3Dround(total_available_buckets%2Btotal_unavailable_buckets)%0A%7C%20extend%20availability_rate%3Dtotal_available_buckets*100%2Ftotal_number_of_buckets%3C%2FCODE%3E%3C%2FPRE%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3ENote%20than%20anyway%2C%20if%20the%20reboot%20was%20quick%20and%20the%20agent%20sends%20a%20heartbeat%20every%205%20minutes%2C%20it%20might%20go%20unnoticed.%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EHTH%2C%3C%2FP%3E%0A%3CP%3ENoa%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-836225%22%20slang%3D%22en-US%22%3ERe%3A%20Availability%20on%20OMS%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-836225%22%20slang%3D%22en-US%22%3EHello%20Noa%2C%3CBR%20%2F%3ECan%20we%20get%20the%20output%20in%20the%20form%20of%20graph%20and%20chart%20on%20the%20Azure%20dashboard%3F%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-837141%22%20slang%3D%22en-US%22%3ERe%3A%20Availability%20on%20OMS%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-837141%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F141516%22%20target%3D%22_blank%22%3E%40Prashant%20Sharma%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EJust%20add%20a%20last%20line%20of%26nbsp%3B%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CPRE%20class%3D%22lia-code-sample%20language-markup%22%3E%3CCODE%3E%7C%20render%20barchart%20kind%3Dunstacked%20%3C%2FCODE%3E%3C%2FPRE%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3Eor%20if%20you%20want%20less%20data%2C%20pick%20the%20columns%2C%20using%20%3CSTRONG%3Eproject%3C%2FSTRONG%3E%3A%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CPRE%20class%3D%22lia-code-sample%20language-markup%22%3E%3CCODE%3E%7C%20project%20Computer%2C%20availability_rate%20%20%0A%7C%20render%20barchart%20kind%3Dunstacked%20title%20%3D%20%22Availability%20Rate%20per%20Computer%22%3C%2FCODE%3E%3C%2FPRE%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3E%3CA%20href%3D%22https%3A%2F%2Fportal.loganalytics.io%2FDemo%3Fq%3DH4sIAAAAAAAAA03MwQmAMAwF0LvgDh%252FPruBB3MAFJLYBY2stMQqCw6sHwQHeu5B1ndkZunXJu7HWoIMk0ihR7ByUjIGyuKCcPCtGUjeRGoIk3%252BxpM3KBPUwsMhpU7Y%252Bjf3l%252B2NdXN6V9JTRyAAAA%26amp%3Btimespan%3DP1D%22%20target%3D%22_blank%22%20rel%3D%22nofollow%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%22%3EGo%20to%20Log%20Analytics%20and%20Run%20Query%3C%2FA%3E%3CBR%20%2F%3E%3CBR%20%2F%3E%3C%2FP%3E%0A%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20style%3D%22width%3A%20400px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Fgxcuf89792.i.lithium.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F130104iFF2FF03F70F14EFD%2Fimage-size%2Fmedium%3Fv%3D1.0%26amp%3Bpx%3D400%22%20alt%3D%22clipboard_image_0.png%22%20title%3D%22clipboard_image_0.png%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-880529%22%20slang%3D%22en-US%22%3ERe%3A%20Availability%20on%20OMS%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-880529%22%20slang%3D%22en-US%22%3E%3CPRE%3EThis%20question%20for%26nbsp%3B%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F239477%22%20target%3D%22_blank%22%3E%40Clive%20Watson%3C%2FA%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F54923%22%20target%3D%22_blank%22%3E%40Noa%20Kuperberg%3C%2FA%3E%26nbsp%3B%3C%2FPRE%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI%20have%20used%20queries%20and%20in%20my%20workspace%20and%20got%20some%20discrepancies.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EFirst%2C%20I%20used%20below%20one.%3C%2FP%3E%3CDIV%3E%3CPRE%3E%3CSPAN%3Elet%20start_time%3Dstartofday(%3C%2FSPAN%3E%3CSPAN%3Edatetime%3C%2FSPAN%3E%3CSPAN%3E(%3C%2FSPAN%3E%3CSPAN%3E%222019-09-01%2000%3A00%3A00%22%3C%2FSPAN%3E%3CSPAN%3E))%3B%3C%2FSPAN%3E%3CBR%20%2F%3E%3CBR%20%2F%3E%3CSPAN%3Elet%20end_time%3Dendofday(%3C%2FSPAN%3E%3CSPAN%3Edatetime%3C%2FSPAN%3E%3CSPAN%3E(%3C%2FSPAN%3E%3CSPAN%3E%222019-09-27%2000%3A00%3A00%22%3C%2FSPAN%3E%3CSPAN%3E))%3B%3C%2FSPAN%3E%3CBR%20%2F%3E%3CBR%20%2F%3E%3CSPAN%3EHeartbeat%3C%2FSPAN%3E%3CBR%20%2F%3E%3CBR%20%2F%3E%3CSPAN%3E%7C%20%3C%2FSPAN%3E%3CSPAN%3Ewhere%3C%2FSPAN%3E%3CSPAN%3E%20TimeGenerated%20%26gt%3B%3D%20start_time%20%3C%2FSPAN%3E%3CSPAN%3Eand%3C%2FSPAN%3E%3CSPAN%3E%20TimeGenerated%20%26lt%3B%3D%20end_time%3C%2FSPAN%3E%3CBR%20%2F%3E%3CBR%20%2F%3E%3CSPAN%3E%7C%20%3C%2FSPAN%3E%3CSPAN%3Ewhere%3C%2FSPAN%3E%3CSPAN%3E%20dayofweek(TimeGenerated)%20%26gt%3B%3D%20%3C%2FSPAN%3E%3CSPAN%3E1%3C%2FSPAN%3E%3CSPAN%3Ed%20%3C%2FSPAN%3E%3CSPAN%3Eand%3C%2FSPAN%3E%3CSPAN%3E%20dayofweek(TimeGenerated)%20%26lt%3B%3D%20%3C%2FSPAN%3E%3CSPAN%3E5%3C%2FSPAN%3E%3CSPAN%3Ed%20%3C%2FSPAN%3E%3CSPAN%3E%2F%2F%20Monday-Friday%3C%2FSPAN%3E%3CBR%20%2F%3E%3CBR%20%2F%3E%3CSPAN%3E%7C%20%3C%2FSPAN%3E%3CSPAN%3Ewhere%3C%2FSPAN%3E%3CSPAN%3E%20hourofday(TimeGenerated)%20%26gt%3B%3D%20%3C%2FSPAN%3E%3CSPAN%3E7%3C%2FSPAN%3E%20%3CSPAN%3Eand%3C%2FSPAN%3E%3CSPAN%3E%20hourofday(TimeGenerated)%20%26lt%3B%3D%3C%2FSPAN%3E%3CSPAN%3E17%3C%2FSPAN%3E%20%3CSPAN%3E%2F%2F%207%3A00-17%3A59%3C%2FSPAN%3E%3CBR%20%2F%3E%3CBR%20%2F%3E%3CSPAN%3E%7C%20%3C%2FSPAN%3E%3CSPAN%3Esummarize%3C%2FSPAN%3E%3CSPAN%3E%20heartbeat_per_1_minutes%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%3Em%2C%20start_time)%2C%20Computer%3C%2FSPAN%3E%3CBR%20%2F%3E%3CBR%20%2F%3E%3CSPAN%3E%7C%20%3C%2FSPAN%3E%3CSPAN%3Eextend%3C%2FSPAN%3E%3CSPAN%3E%20available_per_1_min%3Diff(heartbeat_per_1_minutes%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%3CBR%20%2F%3E%3CBR%20%2F%3E%3CSPAN%3E%7C%20%3C%2FSPAN%3E%3CSPAN%3Esummarize%3C%2FSPAN%3E%3CSPAN%3E%20total_available_buckets%3Dcountif(available_per_1_min%3D%3D%3C%2FSPAN%3E%3CSPAN%3Etrue%3C%2FSPAN%3E%3CSPAN%3E)%3C%2FSPAN%3E%3CBR%20%2F%3E%3CBR%20%2F%3E%3CSPAN%3E%2C%20total_unavailable_buckets%3Dcountif(available_per_1_min%3D%3D%3C%2FSPAN%3E%3CSPAN%3Efalse%3C%2FSPAN%3E%3CSPAN%3E)%20%3C%2FSPAN%3E%3CSPAN%3Eby%3C%2FSPAN%3E%3CSPAN%3E%20Computer%3C%2FSPAN%3E%3CBR%20%2F%3E%3CBR%20%2F%3E%3CSPAN%3E%7C%20%3C%2FSPAN%3E%3CSPAN%3Eextend%3C%2FSPAN%3E%3CSPAN%3E%20total_number_of_buckets%3Dround(total_available_buckets%2Btotal_unavailable_buckets)%3C%2FSPAN%3E%3CBR%20%2F%3E%3CBR%20%2F%3E%3CSPAN%3E%7C%20%3C%2FSPAN%3E%3CSPAN%3Eextend%3C%2FSPAN%3E%3CSPAN%3E%20availability_rate%3Dtotal_available_buckets*%3C%2FSPAN%3E%3CSPAN%3E100%3C%2FSPAN%3E%3CSPAN%3E%2Ftotal_number_of_buckets%3C%2FSPAN%3E%3CBR%20%2F%3E%3CBR%20%2F%3E%3CSPAN%3E%7C%20%3C%2FSPAN%3E%3CSPAN%3Ewhere%3C%2FSPAN%3E%3CSPAN%3E%20Computer%20%3D%3D%20%3C%2FSPAN%3E%3CSPAN%3E%22vm83560609d9%22%3C%2FSPAN%3E%3C%2FPRE%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EAnd%20got%20result%20like%20this%3A%20-%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20style%3D%22width%3A%20400px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Fgxcuf89792.i.lithium.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F133937i91BE8995E068A5A4%2Fimage-size%2Fmedium%3Fv%3D1.0%26amp%3Bpx%3D400%22%20alt%3D%22KQL_AVAIL_1.JPG%22%20title%3D%22KQL_AVAIL_1.JPG%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3ENow%20concern%20is%20why%20availability_rate%20for%20this%20VM%20is%20100%20for%20September%201st%20to%2027th%20%2C%20when%20it%20is%20no%20longer%20available%20after%20few%20days.%3C%2FP%3E%3CP%3EAnd%20what%20is%20%3CSTRONG%3Etotal%20number%20of%20bucket%3C%2FSTRONG%3E%20and%20%3CSTRONG%3Etotal%20unavailable%20bucket%3C%2FSTRONG%3E%3F%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EAnd%20on%20the%20other%20hand%20when%20i%20used%20second%20query%2C%20below%20one%3A%20-%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CDIV%3E%3CPRE%3E%3CSPAN%3Elet%20start_time%3Dstartofday(%3C%2FSPAN%3E%3CSPAN%3Edatetime%3C%2FSPAN%3E%3CSPAN%3E(%3C%2FSPAN%3E%3CSPAN%3E%222019-09-01%22%3C%2FSPAN%3E%3CSPAN%3E))%3B%3C%2FSPAN%3E%3CBR%20%2F%3E%3CBR%20%2F%3E%3CSPAN%3Elet%20end_time%3Dendofday(%3C%2FSPAN%3E%3CSPAN%3Edatetime%3C%2FSPAN%3E%3CSPAN%3E(%3C%2FSPAN%3E%3CSPAN%3E%222019-09-27%22%3C%2FSPAN%3E%3CSPAN%3E))%3B%3C%2FSPAN%3E%3CBR%20%2F%3E%3CBR%20%2F%3E%3CSPAN%3EHeartbeat%3C%2FSPAN%3E%3CBR%20%2F%3E%3CBR%20%2F%3E%3CSPAN%3E%7C%20%3C%2FSPAN%3E%3CSPAN%3Ewhere%3C%2FSPAN%3E%3CSPAN%3E%20TimeGenerated%20%26gt%3B%20start_time%20%3C%2FSPAN%3E%3CSPAN%3Eand%3C%2FSPAN%3E%3CSPAN%3E%20TimeGenerated%20%26lt%3B%20end_time%3C%2FSPAN%3E%3CBR%20%2F%3E%3CBR%20%2F%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%20start_time)%2C%20Computer%3C%2FSPAN%3E%3CBR%20%2F%3E%3CBR%20%2F%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%3CBR%20%2F%3E%3CBR%20%2F%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%20%3C%2FSPAN%3E%3CBR%20%2F%3E%3CBR%20%2F%3E%3CSPAN%3E%7C%20%3C%2FSPAN%3E%3CSPAN%3Eextend%3C%2FSPAN%3E%3CSPAN%3E%20total_number_of_buckets%3Dround((end_time-start_time)%2F%3C%2FSPAN%3E%3CSPAN%3E1%3C%2FSPAN%3E%3CSPAN%3Eh)%3C%2FSPAN%3E%3CBR%20%2F%3E%3CBR%20%2F%3E%3CSPAN%3E%7C%20%3C%2FSPAN%3E%3CSPAN%3Eextend%3C%2FSPAN%3E%3CSPAN%3E%20availability_rate%3Dtotal_available_hours*%3C%2FSPAN%3E%3CSPAN%3E100%3C%2FSPAN%3E%3CSPAN%3E%2Ftotal_number_of_buckets%3C%2FSPAN%3E%3CBR%20%2F%3E%3CBR%20%2F%3E%3CSPAN%3E%7C%20%3C%2FSPAN%3E%3CSPAN%3Ewhere%3C%2FSPAN%3E%3CSPAN%3E%20Computer%20%3D%3D%20%3C%2FSPAN%3E%3CSPAN%3E%22vm83560609d9%22%3C%2FSPAN%3E%3C%2FPRE%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3ESeems%2C%20I%20am%20getting%20right%20results%3A%20-%3C%2FP%3E%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20style%3D%22width%3A%20400px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Fgxcuf89792.i.lithium.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F133938i6959F08808462664%2Fimage-size%2Fmedium%3Fv%3D1.0%26amp%3Bpx%3D400%22%20alt%3D%22KQL_AVAIL_2.JPG%22%20title%3D%22KQL_AVAIL_2.JPG%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EAnd%20in%20this%20section%2C%20why%20total%20number%20of%20bucket%20is%20too%20high%20(648).%20Same%20question%20again%20what%20is%20it%3F%3C%2FP%3E%3CP%3EAnd%20total%20number%20hours%20means%20%22the%20availability%20of%20system%22%2C%20if%20I%20am%20not%20wrong.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThanks%20in%20advance%20for%20the%20help.%3C%2FP%3E%3C%2FDIV%3E%3C%2FDIV%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-891038%22%20slang%3D%22en-US%22%3ERe%3A%20Availability%20on%20OMS%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-891038%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F184511%22%20target%3D%22_blank%22%3E%40Gourav%20Kumar%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EYou're%20right%2C%20there%20is%20a%20bug%20in%20this%20query.%26nbsp%3B%3C%2FP%3E%0A%3CP%3EI%20think%20this%20new%20query%20is%20the%20most%20precise%20one%2C%20taking%20into%20account%20off-hours%20as%20needed%20(e.g.%20using%20only%20work%20day%20hours)%20and%20easy%20to%20change%20from%20an%20hourly-based%20to%20a%20minute-by-minute%20calculation%20(change%20the%20grain%20in%20the%20make-series%20command%20from%201h%20to%201d).%20In%20the%20below%20query%2C%20total_buckets%20is%20the%20total%20number%20of%20buckets%20in%20the%20time%20range%20(if%20you%20use%201h%2C%20it's%20the%20number%20of%20hours%2C%20if%20you%20use%201m%2C%20it's%20the%20number%20of%20minutes%20etc.)%20and%20available_in_buckets%20is%20the%20number%20of%20buckets%20during%20which%20the%20vm%20sent%20at%20least%201%20heartbeat.%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CPRE%20class%3D%22lia-code-sample%20language-markup%22%3E%3CCODE%3Elet%20start_time%3Dstartofday(datetime(%222019-10-01%22))%3B%20%20%2F%2F%20UTC%0Alet%20end_time%3Dnow()%3B%0AHeartbeat%0A%7C%20make-series%20heartbeats_per_bucket%3Dcount()%20default%3D0%20on%20TimeGenerated%20from%20start_time%20to%20end_time%20step%201h%20by%20Computer%0A%7C%20mv-expand%20heartbeats_per_bucket%2C%20TimeGenerated%0A%7C%20project%20BucketTimeGenerated%3Dtodatetime(TimeGenerated)%2C%20Computer%2C%20heartbeats_per_bucket%0A%7C%20where%20BucketTimeGenerated%20%26gt%3B%3D%20start_time%20and%20BucketTimeGenerated%20%26lt%3B%3D%20end_time%0A%7C%20where%20dayofweek(BucketTimeGenerated)%20%26gt%3B%3D%201d%20and%20dayofweek(BucketTimeGenerated)%20%26lt%3B%3D%205d%20%2F%2F%20Monday-Friday%0A%7C%20where%20hourofday(BucketTimeGenerated)%20%26gt%3B%3D%207%20and%20hourofday(BucketTimeGenerated)%20%26lt%3B%3D17%20%2F%2F%207%3A00-17%3A59%0A%7C%20summarize%20total_buckets%3Dcount()%2C%20available_in_buckets%3Dcountif(heartbeats_per_bucket%26gt%3B0)%20by%20Computer%0A%7C%20project%20Computer%2C%20availability_rate%3Davailable_in_buckets*100%2Ftotal_buckets%3C%2FCODE%3E%3C%2FPRE%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-998245%22%20slang%3D%22en-US%22%3ERe%3A%20Availability%20on%20OMS%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-998245%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F54923%22%20target%3D%22_blank%22%3E%40Noa%20Kuperberg%3C%2FA%3E%26nbsp%3BJust%20curious%20if%20there%20is%20a%20way%20to%20find%2Fcalculate%20any%20windows%20service%20availability%20for%20a%20specific%20period%20using%20OMS%20query%3F%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1004782%22%20slang%3D%22en-US%22%3ERe%3A%20Availability%20on%20OMS%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1004782%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F453393%22%20target%3D%22_blank%22%3E%40SatyaParida%3C%2FA%3E%26nbsp%3B%20Windows%20events%20are%20logged%20in%20the%20Event%20table%2C%20as%20far%20as%20I%20know%2C%20but%20I%20am%20not%20familiar%20with%20the%20data%20reported%20by%20each%20service%20and%20application%20to%20create%20the%20query%20you%20ask%20for.%20If%20you%20have%20more%20information%20on%20the%20logged%20data%20I%20can%20help%20with%20the%20query%20syntax.%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1213416%22%20slang%3D%22en-US%22%3ERe%3A%20Availability%20on%20OMS%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1213416%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F54923%22%20target%3D%22_blank%22%3E%40Noa%20Kuperberg%3C%2FA%3E%26nbsp%3B%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F184511%22%20target%3D%22_blank%22%3E%40Gourav%20Kumar%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EHi%20Guys%2C%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI%20wanted%20to%20take%20a%20report%20for%20a%20list%20of%20Azure%20VMs%20the%2024%2F7%20availability%20report%20from%20the%20date%20of%20creation%2C%3C%2FP%3E%3CP%3ECan%20you%20please%20tweak%20the%20script%20and%20help%20me%20to%20get%20the%20report%3F%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1213438%22%20slang%3D%22en-US%22%3ERe%3A%20Availability%20on%20OMS%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1213438%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F576006%22%20target%3D%22_blank%22%3E%40snataraj1984%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EDo%20you%20mean%20the%20last%20query%20above%3F%26nbsp%3B%20Do%20you%20mean%20creation%20time%20of%20the%20computer%20(we'd%20only%20see%20that%20if%20you%20keep%20the%20logs%20from%20that%20time%20and%20its%20less%20than%20two%20years%20-%20max%20retention%20value%20in%20Log%20Analytics)%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CPRE%20class%3D%22lia-code-sample%20language-markup%22%3E%3CCODE%3Elet%20start_time%3Dstartofday(ago(360d))%3B%20%20%2F%2F%20UTC%0Alet%20end_time%3Dnow()%3B%0AHeartbeat%0A%7C%20make-series%20heartbeats_per_bucket%3Dcount()%20default%3D0%20on%20TimeGenerated%20from%20start_time%20to%20end_time%20step%201h%20by%20Computer%0A%7C%20mv-expand%20heartbeats_per_bucket%2C%20TimeGenerated%0A%7C%20project%20BucketTimeGenerated%3Dtodatetime(TimeGenerated)%2C%20Computer%2C%20heartbeats_per_bucket%0A%7C%20where%20BucketTimeGenerated%20%26gt%3B%3D%20start_time%20and%20BucketTimeGenerated%20%26lt%3B%3D%20end_time%0A%7C%20where%20dayofweek(BucketTimeGenerated)%20%26gt%3B%3D%201d%20and%20dayofweek(BucketTimeGenerated)%20%26lt%3B%3D%205d%20%2F%2F%20Monday-Friday%0A%7C%20where%20hourofday(BucketTimeGenerated)%20%26gt%3B%3D%207%20%20and%20hourofday(BucketTimeGenerated)%20%26lt%3B%3D17%20%20%2F%2F%207%3A00-17%3A59%0A%7C%20summarize%20total_buckets%3Dcount()%2C%20available_in_buckets%3Dcountif(heartbeats_per_bucket%26gt%3B0)%2C%20%20min(BucketTimeGenerated)%2C%20max(BucketTimeGenerated)%20by%20Computer%0A%7C%20project%20Computer%2C%20availability_rate%3Davailable_in_buckets*100%2Ftotal_buckets%3C%2FCODE%3E%3C%2FPRE%3E%0A%3CP%3EYou%20can%20adjust%20line%20%231%20to%20go%20back%20a%20number%20of%20days%20(above%20I%20show%201%20year%20(365days))%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1213458%22%20slang%3D%22en-US%22%3ERe%3A%20Availability%20on%20OMS%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1213458%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F239477%22%20target%3D%22_blank%22%3E%40Clive%20Watson%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThanks%20Clive%2C%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI%20will%20explain%20what%20i%20am%20trying%20to%20find%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E1.%26nbsp%3B%3CSPAN%3EFinding%20creation%20Time%2FDate%20for%20a%20list%20of%20Azure%20VMs%3C%2FSPAN%3E%3C%2FP%3E%3CP%3E%3CSPAN%3E2.%20Find%20the%20availability%20of%20those%20VMs%20from%20the%20date%20of%20creation%20if%20the%20log%20is%20overwritten%20we%20will%20consider%20the%20available%20logs%3C%2FSPAN%3E%3C%2FP%3E%3CP%3E%3CSPAN%3E3.%20Finding%20a%20way%20to%20get%20these%20results%20in%20a%20report%20resource%20group%20wise%2F%20Windows%20machines%20%2F%20Linux%20machines%3C%2FSPAN%3E%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EDo%20we%20already%20have%20a%20query%20to%20get%20this%20info%20by%20any%20chance%3F%3C%2FP%3E%3C%2FLINGO-BODY%3E
Highlighted
Contributor

Hi everyone.

I'm trying to find a way of getting Availability of servers on OMS, but I can't find any...

By Availability I mean the % of uptime of a given server during a certain period of time.

So, if a server was up 98 of a total 100 hours, the availability for that period is 98%.

I'm looking to do that in OMS, but I'm not sure it's possible.

Thanks in advance.

36 Replies
Highlighted
Hi There is no out of the box solution for this. Probably one way to calculate this is by using Heartbeat events. For Windows machines they are logged every 1 minute and for Linux every 5 minutes. You could probably calculate what should be the sum of the events for each machine and also the maximum sum for specific time frame. From those two values you can calculate the percentage. The query language is pretty rich so this should be possible.
Highlighted

Stanislav is right, it's possible :)

Here's an example that calculates the availability rate of each computer, starting at midnight.

let midnight=startofday(now());
Heartbeat
| where TimeGenerated>midnight
| summarize heartbeat_per_hour=count() by bin_at(TimeGenerated, 1h, midnight), Computer
| extend available_per_hour=iff(heartbeat_per_hour>0, true, false)
| summarize total_available_hours=countif(available_per_hour==true) by Computer 
| extend number_of_buckets=hourofday(now())+1
| extend availability_rate=total_available_hours*100/number_of_buckets

Run it on our playground and tweak it as makes sense to you.

Highlighted

Thank you for your help, im going to investigate a bit that query.

However, i'm not sure about that approach because the heartbeat happens to stop working a lot even if the VM is perfectly fine.

But I understand the approach...

 

Highlighted
Heartbeat should be running without issue however there are scenarios when you might not get data: - Log Analytics service is down - You machine has lost Internet connection - MMA agent service is stopped - MMA agent is not functioning properly
Highlighted

The last situation MMA agent not working properly or has stopped working is exactly what worries me in order to create the availability report based on heartbeats.

Highlighted

Noa, your script is amazing, however i'm struggling to understand it and tweak it to my needs (30 fixed days, for example from 1st to 31 of january)

Could you gimme a hand to understand it?

 

let midnight=startofday(now()) ; #First part. I need to change this to between((2018-01-01) .. (2017-01-31)); am I correct?
Heartbeat
| where TimeGenerated>midnight
| summarize heartbeat_per_hour=count() by bin_at(TimeGenerated, 1h, midnight) #im not sure i understand why do you use bin_at instead of just bin, Computer
| extend available_per_hour=iff(heartbeat_per_hour>0, true, false)
| summarize total_available_hours=countif(available_per_hour==true) by Computer 
| extend number_of_buckets=hourofday(now())+1
| extend availability_rate=total_available_hours*100/number_of_buckets

 

Highlighted
Solution

Sure. I tweaked it a bit to match what you ask for: 

let start_time=startofday(datetime("2017-01-01"));
let end_time=endofday(datetime("2017-01-31"));
Heartbeat
| where TimeGenerated > start_time and TimeGenerated < end_time
| summarize heartbeat_per_hour=count() by bin_at(TimeGenerated, 1h, start_time), 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((end_time-start_time)/1h)
| extend availability_rate=total_available_hours*100/total_number_of_buckets

The first 2 lines define variables, set to the start and end time you mentioned.

 

Next, we use these variables to limit the query to that time range: 

| where TimeGenerated > start_time and TimeGenerated < end_time

Then we count the heartbeats reported from each computer, in buckets (bins) of 1 hour, starting at the start time you define: 

| summarize heartbeat_per_hour=count() by bin_at(TimeGenerated, 1h, start_time), Computer

Now we can see how many heartbeats were reported by each computer each hour. If the number is  0 we understand the computer was probably offline at that time.

We use a new column to mark if a computer was available or not each hour: 

| extend available_per_hour=iff(heartbeat_per_hour>0, true, false)

and then count the number of hours each computer was indeed "alive": 

| summarize total_available_hours=countif(available_per_hour==true) by Computer

Note that this way we give a little leeway for missing heartbeat reports each hour. Instead of expecting a report every 5 or 10 minutes, we only mark a computer as "unavailable" if we didn't get any report from it during a full hour.

 

At this point we get a number for each computer, something like this: 

results1.png

 

So we know each computer was alive 11 hours in the select time range. But what does it mean? how many hours were there altogether? is this 11 out of 11 hours (100% availability) or out of 110 hours (only 10% availability)?

Here's how we can calculate the total number of hours in the selected time range: 

| extend total_number_of_buckets=round((end_time-start_time)/1h)+1

I admit it might not be the best calculation of buckets.. there is probably a better way but I can't think of it now..

 finally we calculate the ratio between available hours and total hours:

| extend availability_rate=total_available_hours*100/total_number_of_buckets

and get this:

results2.png

 

HTH,

Noa

Highlighted

Noa, amazing, thank you so much.

Highlighted
Excellent query. How can "let midnight=startofday(now())" be altered to make it my local time zone? If I run this as is, it seems to be my time +7, and the amount of hours don't match up.
Highlighted

Hi,

 

Thanks for a exellent code sample.

 

I would like to extend the Query, supporting also specified time intervals and smaller uptime checks (heartbeat)

# Service levels

Ex: Service agreements are based on 3 categories

S1 = 07:00 - 17:00 Weekdays

S2 = 07:00- 22:00 Weekdays

365/7 = Always (already supported by your query

 

= Uptime should be calculated based on service agreement hours/days

 

Time should also be converted to UTC +1

- will this do the trick = >  

Heartbeat
| extend Timegenerated = TimeGenerated + 1h
 
I checked the samples from endofday/week, but are unable to get it to work in your sample
 
 
# Intervals
extend available_per_hour=iff(heartbeat_per_hour>0, true, false)
= can this be adjusted to heartbeat per 30 min / 15 min
 
 
Any ideas ?
 
Br
erik
Highlighted

Thanks George.

To adjust for the local time zone you can do this:

let midnight=startofday(now())-7h
Highlighted

Hi Eric,

 

To adjust for the service agreement, you can calculate the start time and end time like this:

let raw_date = datetime("2017-01-01");
let start_date = case("SLA" in ("S1", "S2"), case(dayofweek(raw_date)==0, startofday(raw_date+1d)+7h,
                                            dayofweek(raw_date)==6, startofday(raw_date+2d)+7h, 
                                            startofday(raw_date)+7h),
                raw_date);

On the intervals - it can adjusted any way you need, just use `bin(fieldname, 30m)` instead of `bin(fieldname, 1h)`.

Highlighted
it is a pwerShell Script?
Highlighted

Hey,

That's not powershell but our query language, that can be used here and through our API.

Highlighted

I am struggling to generate the report for Mon-Friday only and in my time zone. I just get errors.

 

let start_time=startofday(datetime("2018-07-1 07:30:00"));
let end_time=endofday(datetime("2018-07-31 18:00:00"));
Heartbeat
| where TimeGenerated > start_time and TimeGenerated < end_time
| summarize heartbeat_per_hour=count() by bin_at(TimeGenerated, 1h, start_time), 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((end_time-start_time)/1h)
| extend availability_rate=total_available_hours*100/total_number_of_buckets
Highlighted

I am struggling to generate the report for Mon-Friday only and in my time zone. I just get errors. The script below works for me. Thanks

 

let start_time=startofday(datetime("2018-07-1 07:30:00"));
let end_time=endofday(datetime("2018-07-31 18:00:00"));
Heartbeat
| where TimeGenerated > start_time and TimeGenerated < end_time
| summarize heartbeat_per_hour=count() by bin_at(TimeGenerated, 1h, start_time), 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((end_time-start_time)/1h)
| extend availability_rate=total_available_hours*100/total_number_of_buckets
Highlighted
Hey Noa,

Can we take the 1 year details by this script.?
Highlighted

Can  we Availabilty for past 10 days instead of add start date and End date

Highlighted

Thanks but I got the answer

let month = startofday(ago(3d));
Heartbeat
| where TimeGenerated>ago(3d)
| summarize heartbeat_per_hour=count() by bin_at(TimeGenerated, 1h, (ago(3d))), 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((now()-month)/1h)-2
| extend availability_rate=total_available_hours*100/total_number_of_buckets