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

Accepted Solutions
jjensen
Flexera Alumni

Re: Virtual Machine and its host

Jump to 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

6 Replies
jjensen
Flexera Alumni

Re: Virtual Machine and its host

Jump to 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

flexeranoob
Frequent contributor

Re: Virtual Machine and its host

Jump to solution

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

 

 

0 Kudos
jjensen
Flexera Alumni

Re: Virtual Machine and its host

Jump to solution

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

Re: Virtual Machine and its host

Jump to solution

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

Re: Virtual Machine and its host

Jump to solution
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

Re: Virtual Machine and its host

Jump to solution

Thank you!

0 Kudos