Microsoft Entra Suite Tech Accelerator
Aug 14 2024, 07:00 AM - 09:30 AM (PDT)
Microsoft Tech Community

AVD Session host uptime and Automate

Copper Contributor

Hi,

I am new to Azure monitor and reporting. I have written a KQL query to sum-up a daily uptime for AVD session hosts. When this query runs

1: It is not rounding the availability column.

2: How can I set a start time and end time fixed for a day (12AM till 11:59 pm)

3: I need to automate this daily run and email this report

Could you please give me any guidance on this. I give the screen shot below

Arumai_0-1706700954403.png

 

Thanks 

Arumai

 

5 Replies
1. You can use round(Availability,2) to round a vale to 2 decimal places as an example
2. This is yesterday, the first seen record and the last

Heartbeat
| where TimeGenerated between( startofday(ago(1d)) .. endofday(ago(1d)) )
| summarize min(TimeGenerated), max(TimeGenerated)


3. You'll need to look at a Automation Rule and Playbook for that, there are many templates in the Content Hub
Thanks Clive, much appreciated.

@Clive_Watson 

I am trying to extract last month report but it is erroring.  Is the query structure for the last month is OK?  Could you please look at this. Thanks in advance.

//Heartbeat
| where TimeGenerated between (startofmonth(ago(1m))) .. endofmonth(ago(1m)))
| summarize min(TimeGenerated), max(TimeGenerated)
//| 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 Available_Hours=countif(available_per_hour == true) by Computer
| extend Total_Hours=round((max(TimeGenerated) - min(TimeGenerated) / 1h)
| extend Availability_Percentage=Available_Hours * 100 / Total_Hours
| project
Computer,
Total_Hours,
Available_Hours,
round(Availability_Percentage, 0)

Arumai_0-1706879317642.png

 

@Arumai 

 

The month part is solved with this:

 

Heartbeat
| where TimeGenerated between ( startofmonth(now(),-1).. endofmonth(now(),-1) ) 
| summarize heartbeat_per_hour=count() by bin(TimeGenerated, 1h), Computer
| extend available_per_hour=iff(heartbeat_per_hour > 0, true, false)

 

Please take a look at my article about time, for other examples: How to align your Analytics with time windows in Azure Sentinel using KQL (Kusto Query Language) - M...  

Thanks Clive, it worked. Thanks for your help.