Virtual Machine and its host

[flexeranoob]

We are trying to create a SQL script, which gets the Host of the virtual machine.  Further, we are trying to get the information (such as CPU/Cores etc) of this host.  Could someone help us with a SQL query?  Is hostID in ComplicanceComputer the right field?  Which field in the ComplianceComputer table does it map to?

Please help with SQL Query if possible. 

[jjensen · Accepted solution]

Hello @flexeranoob,

Does this help as a starting point?

SELECT
cc.ComputerName AS 'Host Computer Name',
cc.NumberOfProcessors AS 'Host Processor Count',
cc.NumberOfCores AS 'Host Core Count',
vm.FriendlyName AS 'VM Friendly Name',
vm.* 
FROM VirtualMachine vm
JOIN ComplianceComputer cc ON cc.ComplianceComputerID = vm.HostComplianceComputerID

HTH,

Joseph

[flexeranoob]

Thanks!  Let me try and see how this works out.

[jjensen]

No problem. I'd also add the following line at the bottom to clean up the output a bit:

ORDER BY [Host Computer Name] ASC

HTH,

Joseph

[flexeranoob]

Thanks.  One minor help.  How do I join it with the compliancecomputer table so that I can have a row for each VM and its corresponding host. 

[jjensen]

The query provided above should already do that, unless I have misunderstood your requirement. 1 row per VM, per host. In a test environment it seems to output correctly, with multiple rows for any host that has > 1 VM associated with it.

VirtualMachine.HostComplianceComputerID is a foreign key to ComplianceComputer, so that is being used for the JOIN.

[flexeranoob]

Thank you!