Machines availability on specific period of time

[Oleg__D]

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 

[#2 · Accepted 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 

[#3]

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! 

[#4]

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! 

[#5]

@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

[#6]

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

[#7]

@Oleg__D 

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

[#8]

@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?

[#9]

@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? 

[#10]

@CliveWatson To be honest, that looks much better 🙂 

Thank you!

[#11]

Hello @CliveWatson,

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.

[#12]

@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"]);
 

[#13]

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

[#14]

@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:

[#15]

@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