VM Inventory

Detailed Virtual Machine Information along with Virtual Host.

Click to expand the code block

    WITH virtual_hosts AS (
    SELECT 
        device_fk,
        device_name,
        total_cpus,
        ram_mb,
        os_name,
        all_ips,
        device_type,
        os_architecture,
        os_version,
        os_version_number,
        threads_per_core,
        core_per_cpu,
        cpu_speed,
        vm_manager_device_fk,
        last_discovered
    FROM view_dbb_compute_v2
    WHERE virtual_host = true
    ),

    virtual_guests AS (
    SELECT 
        device_fk,
        device_name,
        all_ips,
        os_name,
        in_service,
        os_architecture,
        os_version,
        os_version_number,
        threads_per_core,
        total_cpus,
        core_per_cpu,
        cpu_speed,
        ram_mb,
        local_disk_count,
        virtual_host_device_fk,
        last_discovered
    FROM view_dbb_compute_v2
    WHERE device_type = 'virtual'
    )

    SELECT
    h.device_fk AS host_device_id,
    h.device_name AS host_device_name,
    h.total_cpus AS host_cpu_count,
    h.ram_mb AS host_ram_gb,
    h.os_name AS host_os_name,
    h.all_ips AS host_all_ips,
    vmm.device_name AS vm_manager,
    h.device_type AS host_hardware_type,
    h.os_architecture AS host_os_arch,
    h.os_version AS host_os_version,
    h.os_version_number AS host_os_version_number,
    CASE WHEN h.threads_per_core > 1 THEN 'yes' ELSE 'no' END AS host_is_hyperthreaded,
    h.core_per_cpu AS host_cpu_cores,
    h.cpu_speed AS host_cpu_speed_ghz,
    h.threads_per_core AS host_core_threads,
    CASE 
        WHEN h.core_per_cpu IS NULL THEN h.total_cpus
        WHEN h.threads_per_core > 1 THEN h.total_cpus * h.core_per_cpu * h.threads_per_core
        ELSE h.total_cpus * h.core_per_cpu
    END AS host_compute_power,

    g.device_fk AS guest_device_id,
    g.device_name AS guest_name,
    g.all_ips AS guest_ip_addresses,
    g.os_name AS guest_os_name,
    g.in_service AS guest_is_in_service,
    g.os_architecture AS guest_os_arch,
    g.os_version AS guest_os_version,
    g.os_version_number AS guest_os_version_number,
    CASE WHEN g.threads_per_core > 1 THEN 'yes' ELSE 'no' END AS guest_is_hyperthreaded,
    g.total_cpus AS guest_cpu_count,
    g.core_per_cpu AS guest_cpu_cores,
    g.cpu_speed AS guest_cpu_speed_ghz,
    g.ram_mb AS guest_ram_gb,
    g.local_disk_count AS guest_disk_count,
    g.threads_per_core AS guest_core_threads,
    CASE 
        WHEN g.core_per_cpu IS NULL THEN g.total_cpus
        WHEN g.threads_per_core > 1 THEN g.total_cpus * g.core_per_cpu * g.threads_per_core
        ELSE g.total_cpus * g.core_per_cpu
    END AS guest_compute_power,
    
    h.last_discovered AS host_last_update,
    g.last_discovered AS guest_last_update

    FROM virtual_hosts h
    LEFT JOIN virtual_guests g
    ON g.virtual_host_device_fk = h.device_fk
    LEFT JOIN view_dbb_compute_v2 vmm
    ON vmm.device_fk = h.vm_manager_device_fk
    ORDER BY h.device_name ASC;

NOTES

Use as part of your resource management.