DX Infrastructure Management

Expand all | Collapse all

SQL Query to pull all ESX Host and their VMware version

Jump to Best Answer
  • 1.  SQL Query to pull all ESX Host and their VMware version

    Posted 10-04-2018 04:46 PM

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



  • 2.  Re: SQL Query to pull all ESX Host and their VMware version
    Best Answer

    Posted 10-05-2018 03:04 AM

    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

     



  • 3.  Re: SQL Query to pull all ESX Host and their VMware version

    Posted 10-05-2018 03:07 AM

    attached screenshot

     



  • 4.  Re: SQL Query to pull all ESX Host and their VMware version

    Posted 10-05-2018 10:53 AM

    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?

     

     



  • 5.  Re: SQL Query to pull all ESX Host and their VMware version

    Posted 10-10-2018 01:15 AM

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



  • 6.  Re: SQL Query to pull all ESX Host and their VMware version

    Posted 10-10-2018 01:19 AM

    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.