cancel
Showing results for 
Show  only  | Search instead for 
Did you mean: 

Virtual Machine and its host

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. 

(1) 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

If my response answered your question satisfactorily, please click "ACCEPT AS SOLUTION" to heighten visibility for future customers!

View solution in original post

(7) Replies

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

If my response answered your question satisfactorily, please click "ACCEPT AS SOLUTION" to heighten visibility for future customers!

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

 

 

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

If my response answered your question satisfactorily, please click "ACCEPT AS SOLUTION" to heighten visibility for future customers!

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. 

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.
If my response answered your question satisfactorily, please click "ACCEPT AS SOLUTION" to heighten visibility for future customers!

Hi @jjensen ,

can we also add cluster & vcenter details to this query ?