Machines availability on specific period of time

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 res...

azure monitor

CliveWatson

Jan 11, 2021

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

CliveWatson

Microsoft

Jan 12, 2021

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

Oleg__D

Copper Contributor

Jan 13, 2021

CliveWatson 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.

CliveWatson
Microsoft
Jan 13, 2021
Oleg__D

Thanks for the update, good luck next month - maybe try it in our demo data set? Access for free here
- Oleg__D
  Copper Contributor
  Jan 20, 2021
  CliveWatson
  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?
  - CliveWatson
    Microsoft
    Jan 20, 2021
    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?

Forum Discussion

Machines availability on specific period of time

Share

Resources