How do I enumerate all the VMs running on an ESXi host?

Hi all. I am attempting to create a new alert/alert action that sends a webhook notification to MS teams whenever an ESXi host goes down. The contents of the alert should list the VMs that are/were running on that host. The part I am struggling with is how to write the SWQL query to say "select VMs on XYZ host"

We do not have VMAN currently.

I appreciate any assistance.

Find more posts tagged with

Alert

solarwinds sam

Accepted answers

All comments

dombru

I figured it out:

SELECT 
    VM.DisplayName AS VM_Name
FROM 
    Orion.VIM.VirtualMachines AS VM
    JOIN Orion.VIM.Hosts AS H ON VM.HostID = H.HostID
WHERE 
    H.HostName = '${N=SwisEntity;M=Caption}'

adam.beedell

You dont need to join, the JOIN will make it slower, you can just do

where vm.host.hostname = '${N=SwisEntity;M=Caption}'

dombru

SELECT 
    VM.DisplayName AS VM_Name
FROM 
    Orion.VIM.VirtualMachines AS VM
WHERE 
    vm.host.hostName = '${N=SwisEntity;M=Caption}'

Thank you. Do you mean like this?

adam.beedell

Yeah, that or something quite similar

feeding in caption (as a node-level property) is probably not perfect, but it should work. Could also use vm.host.node.caption for example to match 1-4-1 in the event that there's a node per host (which is not guaranteed)

the dot properties can be used to navigate up or down already-joined stuff, and most stuff is already joined efficiently for you that way

in this query it wont make all that much difference, but it's easier and for more complex queries i've seen a 10x speed up removing unnecessary joins