VM/HOST/CLUSTER Query on VMM

/* *** */
/* MSSQL query on VMMs VirtualManagerDB */
/* *** */

/* VMs */
SELECT
        /* (rownum = 1) for showing only newest entry, tbl_WLC_VMInstance.computername can be NULL */
        ROW_NUMBER() OVER(PARTITION BY COALESCE(tbl_WLC_VMInstance.computername,tbl_WLC_VObject.name) ORDER BY tbl_WLC_VObject.creationtime DESC) AS RowNum,

        /* name and/or description */
        tbl_WLC_VMInstance.computername,
        tbl_WLC_VObject.name,
        tbl_WLC_HWProfile.description,

        /* type */
        CASE
            WHEN tbl_ADHC_Host.OperatingSystemName LIKE '%Microsoft%' THEN 'microsoft_virtual_machine'
            ELSE 'vmware_virtual_machine'
        END AS device_type,

        /* inventory date */
        tbl_WLC_VObject.creationtime AS inventory_date,

        /* operating system */
        tbl_IL_OS.name AS operating_system,

        /* state, 2 seems to be related to VMHosts, 102 seems to be deprecated/non-existant */
        CASE tbl_WLC_VObject.ObjectState
            WHEN   0 THEN 'running'
            WHEN   1 THEN 'stopped'
            WHEN 107 THEN 'update failed'
            WHEN 201 THEN 'migration failed'
            WHEN 220 THEN 'missing'
            WHEN 223 THEN 'incomplete configuration'
            WHEN 225 THEN 'unsupported cluster configuration'
            ELSE CONCAT('unknown:', COALESCE(tbl_WLC_VObject.ObjectState,'NULL'))
        END AS object_status,

        /* cpu/ram infos */
        tbl_WLC_HWProfile.ram AS ram,
        tbl_WLC_HWProfile.virtualnumanodespersocketmaximum AS cpu_sockets,
        tbl_WLC_HWProfile.ProcessorCount AS cpu_chips,
        NULL AS cpu_core_count,
        tbl_ADHC_Host.ProcessorSpeed AS cpu_speed,
        COALESCE(tbl_ADHC_Host.ProcessorManufacturer, '') + COALESCE(' ' + tbl_ADHC_Host.ProcessorModel, '') AS cpu

    FROM tbl_WLC_VObject /* primary object table */

    LEFT JOIN tbl_WLC_VMInstance
        ON tbl_WLC_VMInstance.objectid = tbl_WLC_VObject.objectid

    LEFT JOIN tbl_IL_OS
        ON tbl_IL_OS.osid = tbl_WLC_VObject.OSID

    LEFT JOIN tbl_ADHC_Host
        ON tbl_ADHC_Host.hostid = tbl_WLC_VObject.hostid

    LEFT JOIN tbl_WLC_HWProfile
        ON tbl_WLC_HWProfile.HWProfileId = tbl_WLC_VObject.hwprofileid

/* Object Type 1 -> "normal"; 3 -> "VM template"; 46 -> "Snapshot" */
    WHERE tbl_WLC_VObject.objecttype = 1

UNION ALL

/* Hosts */
SELECT
        /* only needed for UNION */
        '1' AS RowNum,

        /* name and/or fqdn and/or description */
        View_ADHC_HostData.computername,

        /* might contain fqdn */
        SUBSTRING(CONCAT(View_ADHC_HostData.computername,'.'), 1, CHARINDEX('.',CONCAT(View_ADHC_HostData.computername,'.'))-1) AS name,

        /* only needed for UNION */
        '' AS description,

        /* type */
        CASE
            WHEN View_ADHC_HostData.OperatingSystemName LIKE '%Microsoft%' THEN 'microsoft_hyperv_host'
            WHEN View_ADHC_HostData.OperatingSystemName LIKE '%VMware%' THEN 'vmware_esx_host'
            ELSE NULL
        END AS device_type,

        /* last update time stamp */
        View_ADHC_HostData.LastUpdatedDateTime AS inventory_date,

        /* operating system */
        View_ADHC_HostData.OperatingSystemName AS operating_system,

        /* state, 2 seems to be related to VMHosts, 102 seems to be deprecated/non-existant */
        CASE View_ADHC_HostData.State
            WHEN   0 THEN 'running'
            WHEN   1 THEN 'stopped'
            WHEN 107 THEN 'update failed'
            WHEN 201 THEN 'migration failed'
            WHEN 220 THEN 'missing'
            WHEN 223 THEN 'incomplete configuration'
            WHEN 225 THEN 'unsupported cluster configuration'
            ELSE CONCAT('unknown:', COALESCE(View_ADHC_HostData.State,'NULL'))
        END AS object_status,

        /* cpu/ram infos */
        FLOOR(CAST(View_ADHC_HostData.TotalMemory AS bigint)/1024) AS ram,
        View_ADHC_HostData.PhysicalProcessorCount AS cpu_socket_count,
        View_ADHC_HostData.PhysicalProcessorCount AS cpu_chip_count,
        View_ADHC_HostData.LogicalProcessorCount AS cpu_core_count,
        View_ADHC_HostData.ProcessorSpeed AS cpu_speed,
        COALESCE(ProcessorManufacturer, '') + COALESCE(' ' + ProcessorModel, '') AS cpu

    FROM View_ADHC_HostData /* primary table */

UNION ALL


/* Cluster */
SELECT DISTINCT
        /* only needed for UNION */
        '1' AS RowNum,

        /* cluster name */
        tbl_ADHC_HostCluster.name AS computername,
        /* might contain fqdn */
        SUBSTRING(CONCAT(tbl_ADHC_HostCluster.name,'.'), 1, CHARINDEX('.',CONCAT(tbl_ADHC_HostCluster.name,'.'))-1) AS name,

        /* only needed for UNION */
        '' AS description,

        /* type */
        CASE
            WHEN tbl_ADHC_Host.OperatingSystemName LIKE '%Microsoft%' THEN 'microsoft_hyperv_cluster'
            WHEN tbl_ADHC_Host.OperatingSystemName LIKE '%VMware%' THEN 'vmware_esx_cluster'
            ELSE NULL
        END AS device_type,

        /* rest not applicable */
        NULL AS inventory_date,
        NULL AS operating_system,
        NULL AS object_status,
        NULL AS ram,
        NULL AS cpu_socket_count,
        NULL AS cpu_chip_count,
        NULL AS cpu_core_count,
        NULL AS cpu_speed,
        NULL AS cpu

    FROM tbl_WLC_VObject
    LEFT JOIN tbl_ADHC_Host
        ON tbl_ADHC_Host.hostid = tbl_WLC_VObject.hostid
    LEFT JOIN tbl_ADHC_HostCluster
        ON tbl_ADHC_HostCluster.clusterid = tbl_ADHC_Host.HostClusterID
WHERE tbl_ADHC_HostCluster.name IS NOT NULL
    AND tbl_ADHC_HostCluster.name <> ''