flexeranoob
Frequent contributor

Virtual Machine and its host

Jump to solution

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
jjensen
Flexera Alumni

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
jjensen
Flexera Alumni

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

flexeranoob
Frequent contributor

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

 

 

0 Kudos

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!
0 Kudos
flexeranoob
Frequent contributor

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. 

0 Kudos
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!
0 Kudos
flexeranoob
Frequent contributor

Thank you!

0 Kudos
DevarajanR
Occasional contributor

Hi @jjensen ,

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

0 Kudos