Virtual machines

Here is the report that we have working.  It tells us every virtual on each server.

SELECT     VirtualMachines.DisplayName, VirtualMachines.OperatingSystem, VirtualMachines.GuestState, Nodes.Caption
FROM         VirtualMachines INNER JOIN
                      Nodes ON VirtualMachines.HostNodeID = Nodes.NodeID

To just display virtual systems I think the following would work; however I think the approach that Wayne took is probably a bit more elegant.

SELECT *
FROM Nodes
WHERE EntityType = 'Orion.VirtualMachine'

Er, sorry, I had that wrong, I meant to type this...

SELECT Caption
FROM Nodes
WHERE EntityType = 'Orion.VirtualMachine'

@KCWayner - You might want to use PowerState not GuestState

If VMtools aren't running, GuestState show that the server is off, but PowerState is just that.

SELECT     VirtualMachines.DisplayName, VirtualMachines.OperatingSystem, VirtualMachines.PowerState, Nodes.Caption
FROM         VirtualMachines INNER JOIN
                      Nodes ON VirtualMachines.HostNodeID = Nodes.NodeID
Order by PowerState

Thanks for that.  I have it changed on mine now.

Thanks a lot for the help.