Looking for the same (similar) alert. What I found was thought the Custom sql alert is there, you still have your hands tied a bit. The Custom SQL Alert limits what databases/tables you can query. Only there WHERE is available, Select and FROM are restricted. I want to alert on Operation Status != Connected. I found the db where that data is [SolarWindsOrion].[dbo].[VIM_Hosts]. This specific table cannot be queried from the Custom SQL Alert. The following select statement works in SQL managment studio. If you are not the VMWare admin talk to them. vCenter can be configure to send SNMP trap to Orion and you can alert off of those. I opted to just let vCenter send an email alert and by-pass Orion. (hint-hint nudge-nudge towards SolarWinds to work on ESX Host Alert templates ;-)
WHERE (OverallStatus != 'green' OR ConnectionState != 'connected')
You could try using JOIN in Custom SQL ALert.
JOIN VIM_Hosts ON VIM_Hosts.NodeID = Nodes.NodeID
WHERE (VIM_Hosts.OverallStatus != 'green' OR VIM_Hosts.ConnectionState != 'connected')
This worked awesome.
Injecting the Join in the From part is Genius!!