Copper Contributor

Jan 08, 2021

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 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>0, true, false)

| summarize total_available_hours=countif(available_per_hour==true) by 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

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 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
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:
- Clive_Watson
  Bronze Contributor
  Aug 09, 2023
  gaminhas
  
  Try using make-series with default=0 that way you'll see any missing entries - if I was doing this today I'd use this.
  
  Go to Log Analytics and run query
  
  Compared to Summarize which doesnt display the missing results
  Go to Log Analytics and run query

Oleg__D

Copper Contributor

Jan 12, 2021

Hi CliveWatson ,

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?

Thank you!

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 12, 2021

Hello CliveWatson,

Amazing, Thank you

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?

Thanks again!

Forum Discussion

Machines availability on specific period of time

Share

Resources