Is there a KQL Query to detect Session hosts that are in drain mode (not accepting new sessions)?

[garymansell]

Hi,

I have setup an Azure function to lookout for problem WVD session hosts (VMs running for more that 15 mins but not available in the host pool), which then shuts them down and sets Drain mode on.

I now want to create a Logic App that would check for Session hosts with Drain mode on and then email me if any are found (so that I can be warned of potentially problematic session hosts).

But, I don't seem to be able to find the Drain Mode / Allow New Sessions value in any of the WVD KQL tables - am I missing something here? How can I check for this Session host status in a logic app?

Thanks

Gary

[#2]

Check out the logic we going to use to update https://v-desktops.com/2021/02/17/a-simple-health-check-inventory-dashboard-for-wvd-arm/
https://twitter.com/DavidTaig/status/1401381330806444032
We will have an email alert for Errors and Warnings

[#3]

Thanks, but that does not really help me...

It seems (I may be wrong?) that Azure Logic Apps can't run Powershell to get the Drain mode state of session hosts in a Host Pool. So, it seems that Logic Apps are more easily created to run a KQL query and then email the results based on some logic.

Unfortunately, I don't think I can get the Drain Mode / Allow New Sessions value via a KQL query, so wondered if:

1) I am missing the value, and it is actually there, or can be obtained via a KQL Query
2) If not, is there some other way that I can get the info and use it in a Logic App.

[#4]

I don't believe there is a way to check if a Host is in Drain mode

For Allow New Sessions we are running PowerShell with Automation to get those details and more..

#AVD Host Status

[#5]

@David Taig can you help me with how you have setup this report in your screenshot.

is the script available somewhere ?

[#6]

Try this
Try this, this will list out the session hosts which are in Drained state and not allowingnewsessions. Set the status to Unavailable and it will list out the session hosts which are under Can't connect status
WVDAgentHealthStatus
| where SessionHostHealthCheckResult contains "available"
| join WVDAgentHealthStatus on AllowNewSessions
| where AllowNewSessions contains "False"