SQL Query to pull all ESX Host and their VMware version

[Daniel Blanco]

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....

[Yu Ishitani]

Hope this helps.

 

select

       ccs.origin,

       ccs.name,

       ccs.ip,

       max(case when ccsa.cs_attr_key='HypervisorVersion' then "cs_attr_value" else null end) as Hypervisor_Details,

       max(case when ccsa.cs_attr_key='VirtualizationEnvironment' then "cs_attr_value" else null end) as HyperVisor_Vendor

 

from CM_COMPUTER_SYSTEM_ATTR ccsa

 

INNER JOIN CM_COMPUTER_SYSTEM ccs on ccs.cs_id = ccsa.cs_id

 

where ccs.dedicated = 'HostSystem'

 

group by ccs.origin,ccs.name,ccs.ip

order by ccs.origin,ccs.name,ccs.ip

 

[Yu Ishitani]

attached screenshot

 

[Daniel Blanco]

THANK YOU!!!!

 

Could you possibly explain the "max(case when" line....

 

How does that work?

 

select   ccs.origin, ccs.name, ccs.ip,

       max(case when ccsa.cs_attr_key='HypervisorVersion' then "cs_attr_value" else null end) as Hypervisor_Details,

       max(case when ccsa.cs_attr_key='VirtualizationEnvironment' then "cs_attr_value" else null end) as HyperVisor_Vendor

 

We are selecting those origin, name and ip columns from the CM_COMPUTER_SYSTEM table ccs, then what does

 

       max(case when ccsa.cs_attr_key='HypervisorVersion' then "cs_attr_value" else null end) as Hypervisor_Details,

 

is this saying use the value of the ccsa.cs_attr_value if the ccsa.cs_attr_key is equal to HyperVisorVersion and if it doesn't then skip/end?

 

 

[Yu Ishitani]

It was just found in google searching "table row pivot rotate"

[Yu Ishitani]

You are almost correct. If ccsa.cs_attr_key = HypervisorVersion then return value, otherwise return NULL.

In this example, "where clause" is important. Without it, it populates HypervisorVersion as NULL for most devices.