AnsweredAssumed Answered

SQL Query to pull all ESX Host and their VMware version

Question asked by Daniel Blanco Champion on Oct 4, 2018
Latest reply on Oct 10, 2018 by Yu_Ishitani

So can anyone help with this query? I'm trying to pull all my ESX hosts out of our UIM DB which can be done with this query:

 

--This will pull all the cs_id's of all the ESX Hosts in UIM:

select cs_id from CM_COMPUTER_SYSTEM_ATTR where cs_attr_key ='PrimaryRole' and cs_attr_value = 'VirtualMachineHost'

 

and then take all those CS_ID's and then pull the following fields out of the CM_COMPUTER_SYSTEM_ATTR table and show the them as columns. Trying to pull all the ESX Hosts and their version of ESX they are running.

The CM_COMPUTER_SYSTEM_ATTR  has multiple rows with the same cs_id which then have the property and the value in that row. 

Need to know how do I transpose or pivot the rows into columns from this table.

 

 

select * from CM_COMPUTER_SYSTEM_ATTR

where cs_attr_key ='PrimaryRole' and cs_attr_value = 'VirtualMachineHost' or cs_attr_key = 'HypervisorVersion' or cs_attr_key = 'PrimaryOSVersion' or cs_attr_key = 'PrimaryDnsName' or cs_attr_key = 'VirtualizationEnvironment'

order by cs_id,cs_attr_key

 

TIA....

Outcomes