Forum Discussion

Oleg__D's avatar
Oleg__D
Copper Contributor
Jan 08, 2021
Solved

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's avatar
    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
gaminhas's avatar
gaminhas
Copper Contributor
Aug 08, 2023

CliveWatson Could I please get some assistance as I have been trying to get the availability report for our Azure VMs uptime by selecting the data range as from 1st of July to 31st July and using the query below as per your advice but not getting the accurate available number of hours. Some of our VMs have been up 100% of time throughout the month but when running this query, the total available hours and availability rate is coming as incorrect :

let start_time=startofday(datetime("2023-07-01 00:00:00 AM"));
let end_time=endofday(datetime("2023-07-31 11:59:59 PM"));
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

 

We get the output as below, can I please get some advice if we need to change something to get the correct output:

Resources