Trying to expose the value for "ManagedStatus" from dbo.VIM_Hosts

Question

Seems trivial... Until it isn't. We're trying to create a modern widget showing the count of hosts in either ManagedStatus = 3 (warning) or ManagedStatus = 14 (critical) in a specific cluster. I'm running this down because this metric seems to give a more accurate ESX host status. Before you ask, this is an elastic environment so hosts are regularly spinning up and down based on cluster load which makes added the hosts as monitored nodes not practical. Thoughts? In database manager, this query works as expected: SELECT count(hostname) FROM [dbo].[VIM_Hosts] where clusterid = '' and ManagedStatus = '<1/3/14>' Creating custom SWQL in the widget: SELECT COUNT (HostName) AS Value FROM Orion.VIM.Hosts WHERE ( ( ClusterID = '

adam.beedell · Answer

Ooooh lovely nugget of info there!

Seashore · Answer

@adam.beedell  is correct and below is the whole query you should run in SWQL.

SELECT 
    COUNT(H.HostName) AS Value 
FROM Orion.VIM.Hosts AS H
WHERE 
    H.ClusterID = 40 -- YOUR ID HERE
    AND H.Status IN (1,3,14)

By adding "WITH QUERYSTATS" to the end of the query, when running it in SWQL Studio, you can see the actual SQL query it runs. This way we can see stat "SWQL:Status = SQL:ManagedStatus" in this case.

adam.beedell · Answer

Not sure which managedstatus lines up with, but status is the VH object and hoststatus is the vcenter's view which is like "does it have alarms against it" + "is it disconnected/infigured weird"