Forum Discussion
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...
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
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 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!