SOLVED

Machines availability on specific period of time

%3CLINGO-SUB%20id%3D%22lingo-sub-2043501%22%20slang%3D%22en-US%22%3EMachines%20availability%20on%20specific%20period%20of%20time%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2043501%22%20slang%3D%22en-US%22%3E%3CP%3EHello%2C%3C%2FP%3E%3CP%3EI%20am%20working%20on%20query%20where%20it%20should%20show%20the%20monthly%20availability%20according%20to%20buckets.%20But%20it%20should%20skip%20the%20maintenance%20days%20(which%20is%20the%204th%20weekend%20of%20the%20month)%20and%20calculate%20the%20rest.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI%20have%20written%20the%20following%20attempt%2C%20but%20I%20am%20struggling%20with%20TimeGenerate%20where%20it%20basically%20should%20set%20the%20value%20as%3A%3C%2FP%3E%3CP%3Estart_month%20until%20saturday%3C%2FP%3E%3CP%3Eand%20(maintenance)%3C%2FP%3E%3CP%3Esunday%20until%20end_month.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3Ethis%20maintenance%20should%20not%20be%20included%2C%20so%20I%20can%20see%20the%20rest%20696%20hours%20of%20availability.%26nbsp%3B%3C%2FP%3E%3CP%3EThank%20you.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CDIV%3E%3CDIV%3E%3CSPAN%3Elet%26nbsp%3Bend_month%26nbsp%3B%3D%26nbsp%3B(startofmonth(now())%26nbsp%3B-%26nbsp%3B%3C%2FSPAN%3E%3CSPAN%3E1%3C%2FSPAN%3E%3CSPAN%3Eh)%3B%3C%2FSPAN%3E%3C%2FDIV%3E%3CDIV%3E%3CSPAN%3Elet%26nbsp%3Bstart_month%26nbsp%3B%3D%26nbsp%3Bstartofmonth(end_month)%3B%3C%2FSPAN%3E%3C%2FDIV%3E%3CDIV%3E%3CSPAN%3Elet%26nbsp%3Bsaturday%26nbsp%3B%3D%26nbsp%3Bendofweek(start_month%2C%26nbsp%3B%3C%2FSPAN%3E%3CSPAN%3E3%3C%2FSPAN%3E%3CSPAN%3E)-%3C%2FSPAN%3E%3CSPAN%3E1%3C%2FSPAN%3E%3CSPAN%3Ed%3B%3C%2FSPAN%3E%3C%2FDIV%3E%3CDIV%3E%3CSPAN%3Elet%26nbsp%3Bsunday%26nbsp%3B%3D%26nbsp%3Bstartofweek(end_month%2C%26nbsp%3B%3C%2FSPAN%3E%3CSPAN%3E0%3C%2FSPAN%3E%3CSPAN%3E)%2B%3C%2FSPAN%3E%3CSPAN%3E1%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%26nbsp%3B%3C%2FSPAN%3E%3CSPAN%3Ewhere%3C%2FSPAN%3E%3CSPAN%3E%26nbsp%3BTimeGenerated%26nbsp%3B%26gt%3B%26nbsp%3Bstart_month%26nbsp%3B%3C%2FSPAN%3E%3CSPAN%3Eand%3C%2FSPAN%3E%3CSPAN%3E%26nbsp%3BTimeGenerated%26nbsp%3B%26lt%3B%26nbsp%3Bend_month%3C%2FSPAN%3E%3C%2FDIV%3E%3CDIV%3E%3CSPAN%3E%7C%26nbsp%3B%3C%2FSPAN%3E%3CSPAN%3Ewhere%3C%2FSPAN%3E%3CSPAN%3E%26nbsp%3BComputer%26nbsp%3B%3C%2FSPAN%3E%3CSPAN%3Econtains%3C%2FSPAN%3E%3CSPAN%3E%26nbsp%3B%3C%2FSPAN%3E%3CSPAN%3E%22TEST%22%3C%2FSPAN%3E%3C%2FDIV%3E%3CDIV%3E%3CSPAN%3E%7C%26nbsp%3B%3C%2FSPAN%3E%3CSPAN%3Esummarize%3C%2FSPAN%3E%3CSPAN%3E%26nbsp%3Bheartbeat_per_hour%3D%3C%2FSPAN%3E%3CSPAN%3Ecount%3C%2FSPAN%3E%3CSPAN%3E()%26nbsp%3B%3C%2FSPAN%3E%3CSPAN%3Eby%3C%2FSPAN%3E%3CSPAN%3E%26nbsp%3Bbin_at(TimeGenerated%2C%26nbsp%3B%3C%2FSPAN%3E%3CSPAN%3E1%3C%2FSPAN%3E%3CSPAN%3Eh%2C%26nbsp%3Bstart_month)%2C%26nbsp%3BComputer%3C%2FSPAN%3E%3C%2FDIV%3E%3CDIV%3E%3CSPAN%3E%7C%26nbsp%3B%3C%2FSPAN%3E%3CSPAN%3Eextend%3C%2FSPAN%3E%3CSPAN%3E%26nbsp%3Bavailable_per_hour%3Diff(heartbeat_per_hour%26gt%3B%3C%2FSPAN%3E%3CSPAN%3E0%3C%2FSPAN%3E%3CSPAN%3E%2C%26nbsp%3B%3C%2FSPAN%3E%3CSPAN%3Etrue%3C%2FSPAN%3E%3CSPAN%3E%2C%26nbsp%3B%3C%2FSPAN%3E%3CSPAN%3Efalse%3C%2FSPAN%3E%3CSPAN%3E)%3C%2FSPAN%3E%3C%2FDIV%3E%3CDIV%3E%3CSPAN%3E%7C%26nbsp%3B%3C%2FSPAN%3E%3CSPAN%3Esummarize%3C%2FSPAN%3E%3CSPAN%3E%26nbsp%3Btotal_available_hours%3Dcountif(available_per_hour%3D%3D%3C%2FSPAN%3E%3CSPAN%3Etrue%3C%2FSPAN%3E%3CSPAN%3E)%26nbsp%3B%3C%2FSPAN%3E%3CSPAN%3Eby%3C%2FSPAN%3E%3CSPAN%3E%26nbsp%3BComputer%2C%26nbsp%3Bbin(TimeGenerated%2C%26nbsp%3B%3C%2FSPAN%3E%3CSPAN%3E1%3C%2FSPAN%3E%3CSPAN%3Ed)%3C%2FSPAN%3E%3C%2FDIV%3E%3CDIV%3E%3CSPAN%3E%7C%26nbsp%3B%3C%2FSPAN%3E%3CSPAN%3Eextend%3C%2FSPAN%3E%3CSPAN%3E%26nbsp%3Btotal_number_of_buckets%3Dround(((end_month-sunday)%2B(saturday-start_month)%2B%3C%2FSPAN%3E%3CSPAN%3E1%3C%2FSPAN%3E%3CSPAN%3Eh)%2F%3C%2FSPAN%3E%3CSPAN%3E1%3C%2FSPAN%3E%3CSPAN%3Eh)%3C%2FSPAN%3E%3C%2FDIV%3E%3CDIV%3E%3CSPAN%3E%7C%26nbsp%3B%3C%2FSPAN%3E%3CSPAN%3Eextend%3C%2FSPAN%3E%3CSPAN%3E%26nbsp%3Bavailability_rate%3D(total_available_hours-48)*%3C%2FSPAN%3E%3CSPAN%3E100%3C%2FSPAN%3E%3CSPAN%3E%2Ftotal_number_of_buckets%3C%2FSPAN%3E%3C%2FDIV%3E%3CDIV%3E%3CSPAN%3E%7C%26nbsp%3B%3C%2FSPAN%3E%3CSPAN%3Eorder%3C%2FSPAN%3E%3CSPAN%3E%26nbsp%3B%3C%2FSPAN%3E%3CSPAN%3Eby%3C%2FSPAN%3E%3CSPAN%3E%26nbsp%3Bavailability_rate%26nbsp%3Bdesc%3C%2FSPAN%3E%3C%2FDIV%3E%3CDIV%3E%3CSPAN%3E%7C%26nbsp%3B%3C%2FSPAN%3E%3CSPAN%3Erender%3C%2FSPAN%3E%3CSPAN%3E%26nbsp%3Btimechart%26nbsp%3B%3C%2FSPAN%3E%3C%2FDIV%3E%3C%2FDIV%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2049183%22%20slang%3D%22en-US%22%3ERe%3A%20Machines%20availability%20on%20specific%20period%20of%20time%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2049183%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F878574%22%20target%3D%22_blank%22%3E%40Oleg__D%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EMaybe%20this%3F%3C%2FP%3E%0A%3CPRE%20class%3D%22lia-code-sample%20language-cpp%22%3E%3CCODE%3EHeartbeat%0A%2F%2F%20last%20month%20%0A%7C%20where%20TimeGenerated%20between%20(%20startofmonth(now()%2C-1)..%20endofmonth(now()%2C-1)%20)%0A%7C%20where%20Computer%20contains%20%22JBOX00%22%0A%2F%2F%20find%204th%20week%20which%20is%20week%20%223%22%0A%7C%20extend%20maintSaturday_%20%3D%20endofweek(startofmonth(now()%2C-1)%2C3)%20-1d%2C%20maintSunday_%20%3D%20endofweek(startofmonth(now()%2C-1)%2C3)%20%2B%201d%0A%2F%2F%20exclude%204th%20week%20from%20data%20set%0A%7C%20where%20%20TimeGenerated%20%20!between%20(%20maintSaturday_%20..%20maintSunday_%20)%0A%7C%20summarize%20heartbeat_per_hour%3Dcount()%20by%20bin(TimeGenerated%2C%201h)%2C%20Computer%0A%7C%20extend%20available_per_hour%3Diff(heartbeat_per_hour%26gt%3B0%2C%20true%2C%20false)%0A%7C%20serialize%20%0A%7C%20summarize%20total_available_hours%3Dcountif(available_per_hour%3D%3Dtrue)%2C%20total_number_of_buckets%20%3D%20max(row_number())%20by%20Computer%2C%20bin(TimeGenerated%2C%201d)%0A%7C%20extend%20availability_rate%3D(total_available_hours-48)*100%2Ftotal_number_of_buckets%0A%7C%20project%20TimeGenerated%2C%20availability_rate%0A%7C%20order%20by%20availability_rate%20desc%0A%7C%20render%20timechart%20%3C%2FCODE%3E%3C%2FPRE%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2052280%22%20slang%3D%22en-US%22%3ERe%3A%20Machines%20availability%20on%20specific%20period%20of%20time%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2052280%22%20slang%3D%22en-US%22%3E%3CP%3EHi%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%26nbsp%3B%2C%3C%2FP%3E%3CP%3EAmazing%2C%20I%20just%20adapted%20to%20my%20case%20and%20it%20looks%20perfectly!%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CPRE%20class%3D%22lia-code-sample%20language-powerquery%22%3E%3CCODE%3Elet%20end_time%3D(startofmonth(now())%20-%201ms)%3B%0Alet%20start_time%3Dstartofmonth(end_time)%3B%0AHeartbeat%0A%2F%2F%7C%20where%20TimeGenerated%20%26gt%3B%20start_time%20and%20TimeGenerated%20%26lt%3B%20end_time%0A%7C%20where%20TimeGenerated%20between%20(%20startofmonth(now()%2C-1)..%20endofmonth(now()%2C-1)%20)%0A%7C%20where%20Computer%20contains%20%22TEST%22%0A%2F%2F%20find%204th%20week%20which%20is%20week%20%223%22%0A%7C%20extend%20maintSaturday_%20%3D%20endofweek(startofmonth(now()%2C-1)%2C3)%20-1d%2C%20maintSunday_%20%3D%20endofweek(startofmonth(now()%2C-1)%2C3)%20%2B%201d%0A%2F%2F%20exclude%204th%20week%20from%20data%20set%0A%7C%20where%20%20TimeGenerated%20%20!between%20(%20maintSaturday_%20..%20maintSunday_%20)%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%20tostring(split(Computer%2C%20%22.%22)%5B0%5D)%2C%20bin(TimeGenerated%2C%201d)%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%0A%7C%20order%20by%20availability_rate%20desc%0A%7C%20render%20timechart%20%3C%2FCODE%3E%3C%2FPRE%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EHowever%2C%20could%20you%20please%20just%20help%20with%20the%20last%20thing.%20Why%20the%20chart%2Ftable%20starts%20with%20December%2013%20in%20this%20case%20but%20not%20December%201st%3F%26nbsp%3B%3C%2FP%3E%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20image-alt%3D%22Oleg__D_0-1610446266749.png%22%20style%3D%22width%3A%20400px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F245620i846CF07794F02234%2Fimage-size%2Fmedium%3Fv%3D1.0%26amp%3Bpx%3D400%22%20role%3D%22button%22%20title%3D%22Oleg__D_0-1610446266749.png%22%20alt%3D%22Oleg__D_0-1610446266749.png%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%3CP%3EThank%20you!%26nbsp%3B%3CIMG%20class%3D%22lia-deferred-image%20lia-image-emoji%22%20src%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Fhtml%2Fimages%2Femoticons%2Fsmile_40x40.gif%22%20alt%3D%22%3Asmile%3A%22%20title%3D%22%3Asmile%3A%22%20%2F%3E%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2052788%22%20slang%3D%22en-US%22%3ERe%3A%20Machines%20availability%20on%20specific%20period%20of%20time%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2052788%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F878574%22%20target%3D%22_blank%22%3E%40Oleg__D%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EGood%20to%20know.%26nbsp%3B%20The%20TimeChart%20will%20correctly%20start%20at%20Dec%201st%2C%20but%20if%20you%20want%20it%20in%20date%20order%20for%20the%20results%20view%2C%20change%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CPRE%20class%3D%22lia-code-sample%20language-cpp%22%3E%3CCODE%3E%7C%20order%20by%20TimeGenerated%20asc%0A%2F%2F%7C%20order%20by%20availability_rate%20desc%0A%3C%2FCODE%3E%3C%2FPRE%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2057009%22%20slang%3D%22en-US%22%3ERe%3A%20Machines%20availability%20on%20specific%20period%20of%20time%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2057009%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%3BIt%20is%20ok%20now.%20the%20problem%20was%20related%20to%20Data%20Retention%20which%20was%20set%20to%2030%20days.%20Therefore%20I%20didn't%20see%20more%20than%2030%20days%20ago%20and%20it%20was%20showing%20the%20value%20of%20(today-30days)%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI%20think%20I%20just%20need%20to%20wait%20for%20next%20month%20to%20collect%20the%20full%20data%20%3A)%3C%2Fimg%3E%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThank%20you%20again.%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2057018%22%20slang%3D%22en-US%22%3ERe%3A%20Machines%20availability%20on%20specific%20period%20of%20time%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2057018%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F878574%22%20target%3D%22_blank%22%3E%40Oleg__D%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EThanks%20for%20the%20update%2C%20good%20luck%20next%20month%20-%20maybe%20try%20it%20in%20our%20demo%20data%20set%3F%20Access%20for%20free%20%3CA%20href%3D%22https%3A%2F%2Fms.portal.azure.com%23%4072f988bf-86f1-41af-91ab-2d7cd011db47%2Fblade%2FMicrosoft_Azure_Monitoring_Logs%2FDemoLogsBlade%2FresourceId%2F%252FDemo%2Fsource%2FLogsBlade.AnalyticsShareLinkToQuery%2Fq%2FH4sIAAAAAAAAA5WTQY%25252FTMBCF75X6H4acHEjbRMttyR7gAOLCAQ5ICEVOPCHWJnZlj%25252BkW8eMZt9smbcphj4nfvPfNc9IjARpVkR6wFJ6kI9sO1lAnjN2JNIUVFINP75eLnqUHwVF8oT1ZRN0n5IMaJS0Xm81f2HXoEL7x4Uc06CShgoeJEUijro7fnZGWi9sGNdIO0YCAOXK2KtL1OlrMXkM6Gn6wwzYQOmhYI7XxkHx%25252B%25252F%25252BV7nieRG1rNWG%25252BpA8555BHddKD98Sm5S6IPPhGHwMDD9FVScEruKyiPyVF4o89Ikd1xq4XKnieDecHcGyjUgQ%25252Bfmj4oHBFbZwdQkiR4pHHNq%25252BLg1VjdFTh3dgF06MqHYZBO%25252F0HoTtdabdFVnQ2ubGwwJFKo91BrU0kSF2EZFF02uek0O5c%25252BqU%25252F%25252BlrqXdY%25252Bjr25bMY97yDMgFzCDVvYer%25252FDIkuyr0SxO%25252BCOhbsWNkDJ6HdjJenLa%25252FBJ%25252B22sSJ8YMknWS%25252Fsh%25252FMjevN9tNpTBZ45hvwlBzgG2rOjSPSL50TKDE%25252BQdZTerYFF06L0Izw76KIeXNnV4Xeb75T1p0s07xR81rzfxAoW%25252BixHEcayJE0zHOPwfjBsoFBAAA%2Ftimespan%2F2020-12-01T15%253A30%253A57.000Z%252F2021-01-13T15%253A30%253A57.592Z%22%20target%3D%22_self%22%20rel%3D%22nofollow%20noopener%20noreferrer%22%3Ehere%20%3C%2FA%3E%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2078633%22%20slang%3D%22en-US%22%3ERe%3A%20Machines%20availability%20on%20specific%20period%20of%20time%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2078633%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%3EIt%20looks%20perfect%20in%20demo%20data%20set%20%3A)%3C%2Fimg%3E%3CBR%20%2F%3EHowever%20when%20I%20pin%20it%20do%20dashboard%20I%20still%20see%20results%20for%20the%20last%2030%20days%2C%20despite%20the%20custom%20date%20set.%20Is%20there%20a%20way%20to%20bypass%20this%20override%20option%3F%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%20image-alt%3D%22Oleg__D_0-1611148175130.jpeg%22%20style%3D%22width%3A%20400px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F247729i4B4EFB3787C37953%2Fimage-size%2Fmedium%3Fv%3D1.0%26amp%3Bpx%3D400%22%20role%3D%22button%22%20title%3D%22Oleg__D_0-1611148175130.jpeg%22%20alt%3D%22Oleg__D_0-1611148175130.jpeg%22%20%2F%3E%3C%2FSPAN%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20image-alt%3D%22Oleg__D_1-1611148279801.png%22%20style%3D%22width%3A%20400px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F247730i58DC42371375522C%2Fimage-size%2Fmedium%3Fv%3D1.0%26amp%3Bpx%3D400%22%20role%3D%22button%22%20title%3D%22Oleg__D_1-1611148279801.png%22%20alt%3D%22Oleg__D_1-1611148279801.png%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E
Occasional Contributor

Hello,

I am working on query where it should show the monthly availability according to buckets. But it should skip the maintenance days (which is the 4th weekend of the month) and calculate the rest.

 

I have written the following attempt, but I am struggling with TimeGenerate where it basically should set the value as:

start_month until saturday

and (maintenance)

sunday until end_month.

 

this maintenance should not be included, so I can see the rest 696 hours of availability. 

Thank you.

 

let end_month = (startofmonth(now()) - 1h);
let start_month = startofmonth(end_month);
let saturday = endofweek(start_month, 3)-1d;
let sunday = startofweek(end_month, 0)+1d;
Heartbeat
where TimeGenerated > start_month and TimeGenerated < end_month
where Computer contains "TEST"
summarize heartbeat_per_hour=count() by bin_at(TimeGenerated, 1h, start_month), Computer
extend available_per_hour=iff(heartbeat_per_hour>0truefalse)
summarize total_available_hours=countif(available_per_hour==trueby Computer, bin(TimeGenerated, 1d)
extend total_number_of_buckets=round(((end_month-sunday)+(saturday-start_month)+1h)/1h)
extend availability_rate=(total_available_hours-48)*100/total_number_of_buckets
order by availability_rate desc
render timechart 
12 Replies
best response confirmed by Oleg__D (Occasional Contributor)
Solution

@Oleg__D 

 

Maybe this?

Heartbeat
// last month 
| where TimeGenerated between ( startofmonth(now(),-1).. endofmonth(now(),-1) )
| where Computer contains "JBOX00"
// find 4th week which is week "3"
| extend maintSaturday_ = endofweek(startofmonth(now(),-1),3) -1d, maintSunday_ = endofweek(startofmonth(now(),-1),3) + 1d
// exclude 4th week from data set
| where  TimeGenerated  !between ( maintSaturday_ .. maintSunday_ )
| summarize heartbeat_per_hour=count() by bin(TimeGenerated, 1h), Computer
| extend available_per_hour=iff(heartbeat_per_hour>0, true, false)
| serialize 
| summarize total_available_hours=countif(available_per_hour==true), total_number_of_buckets = max(row_number()) by Computer, bin(TimeGenerated, 1d)
| extend availability_rate=(total_available_hours-48)*100/total_number_of_buckets
| project TimeGenerated, availability_rate
| order by availability_rate desc
| render timechart 

Hello @Clive Watson,

Amazing, Thank you :smile:

I adapted to my case and it looks great!

 

let end_time=(startofmonth(now()) - 1ms);
let start_time=startofmonth(end_time);
Heartbeat
//| where TimeGenerated > start_time and TimeGenerated < end_time
| where TimeGenerated between ( startofmonth(now(),-1).. endofmonth(now(),-1) )
| where Computer contains "TEST"
// find 4th week which is week "3"
| extend maintSaturday_ = endofweek(startofmonth(now(),-1),3) -1d, maintSunday_ = endofweek(startofmonth(now(),-1),3) + 1d
// exclude 4th week from data set
| where  TimeGenerated  !between ( maintSaturday_ .. maintSunday_ )
| 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, bin(TimeGenerated, 1d) 
| extend total_number_of_buckets=round((end_time-start_time)/1h)
| extend availability_rate=total_available_hours*100/total_number_of_buckets
| order by availability_rate desc
| render timechart let end_time=(startofmonth(now()) - 1ms);
let start_time=startofmonth(end_time);
Heartbeat
//| where TimeGenerated > start_time and TimeGenerated < end_time
| where TimeGenerated between ( startofmonth(now(),-1).. endofmonth(now(),-1) )
| where Computer contains "TEST"
// find 4th week which is week "3"
| extend maintSaturday_ = endofweek(startofmonth(now(),-1),3) -1d, maintSunday_ = endofweek(startofmonth(now(),-1),3) + 1d
// exclude 4th week from data set
| where  TimeGenerated  !between ( maintSaturday_ .. maintSunday_ )
| 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, bin(TimeGenerated, 1d) 
| extend total_number_of_buckets=round((end_time-start_time)/1h)
| extend availability_rate=total_available_hours*100/total_number_of_buckets
| order by availability_rate desc
| render timechart 

 

 

However, could you maybe just help with one thing, why the start date is not the December 1st in this case, but December 13? And is there a way to sum all days instead of having availability rate/row for each day?

 

Oleg__D_0-1610445559078.png

Thanks again! :smile:

Hi @Clive Watson ,

Amazing, I just adapted to my case and it looks perfectly!

 

let end_time=(startofmonth(now()) - 1ms);
let start_time=startofmonth(end_time);
Heartbeat
//| where TimeGenerated > start_time and TimeGenerated < end_time
| where TimeGenerated between ( startofmonth(now(),-1).. endofmonth(now(),-1) )
| where Computer contains "TEST"
// find 4th week which is week "3"
| extend maintSaturday_ = endofweek(startofmonth(now(),-1),3) -1d, maintSunday_ = endofweek(startofmonth(now(),-1),3) + 1d
// exclude 4th week from data set
| where  TimeGenerated  !between ( maintSaturday_ .. maintSunday_ )
| 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 tostring(split(Computer, ".")[0]), bin(TimeGenerated, 1d) 
| extend total_number_of_buckets=round((end_time-start_time)/1h)
| extend availability_rate=total_available_hours*100/total_number_of_buckets
| order by availability_rate desc
| render timechart 

 

However, could you please just help with the last thing. Why the chart/table starts with December 13 in this case but not December 1st? 

Oleg__D_0-1610446266749.png

Thank you! :smile:

@Oleg__D 

 

Good to know.  The TimeChart will correctly start at Dec 1st, but if you want it in date order for the results view, change

 

| order by TimeGenerated asc
//| order by availability_rate desc

 

@Clive Watson It is ok now. the problem was related to Data Retention which was set to 30 days. Therefore I didn't see more than 30 days ago and it was showing the value of (today-30days)

 

I think I just need to wait for next month to collect the full data :)

 

Thank you again.

@Oleg__D 

Thanks for the update, good luck next month - maybe try it in our demo data set? Access for free here

 

@Clive Watson 

It looks perfect in demo data set :)
However when I pin it do dashboard I still see results for the last 30 days, despite the custom date set. Is there a way to bypass this override option?

 

Oleg__D_0-1611148175130.jpegOleg__D_1-1611148279801.png

 

 

@Oleg__D 

30days is the max (as far as I know), a custom range is for values between 0-30days. 
Azure Monitor Workbooks don't have this restriction, that may be an alterative for you? 

@Clive Watson To be honest, that looks much better :) 

Thank you!

Hello @Clive Watson,

 

Thanks a lot for help again! May I ask you please to help with 2 more questions.

1. The availability query result doesn't show for some reason machines older than Windows Server 2012. Do you know what could be a reason? Is it related to Logs configuration/settings?

2. We have a redundant cluster server like TESTVM01A and TESTVM01B. Is there a proper way to count these 2 machines in availability query as one?

 

Thank you.

@Oleg__D 

 

The Heartbeat table should get Agent data from Windows Server 2008R2 as well
Overview of the Azure monitoring agents - Azure Monitor | Microsoft Docs

 

For a cluster I don't think we have an identifier, so you would probably have to use a list in the query to count these, together
i.e 

let includeClusterNodes = dynamic(["nodeA","nodeB","nodeC"]);
 

Hello @Clive Watson, do you know what could be a reason why 2008 machines do not appear in table?