inventoryreport

WITH entitlements AS (

         SELECT systementitlement.mgm_id,

            systementitlement.system_id,

            string_agg(((systementitlement.system_group_id || ' - '::text) || (systementitlement.name)::text), ';'::text) AS entitlements

           FROM systementitlement

          GROUP BY systementitlement.mgm_id, systementitlement.system_id

        ), groups AS (

         SELECT systemgroupmember.mgm_id,

            systemgroupmember.system_id,

            string_agg(((systemgroupmember.system_group_id || ' - '::text) || (systemgroupmember.group_name)::text), ';'::text) AS system_groups

           FROM systemgroupmember

          GROUP BY systemgroupmember.mgm_id, systemgroupmember.system_id

        ), configchannels AS (

         SELECT systemconfigchannel.mgm_id,

            systemconfigchannel.system_id,

            string_agg(((systemconfigchannel.config_channel_id || ' - '::text) || (systemconfigchannel.name)::text), ';'::text) AS configuration_channels

           FROM systemconfigchannel

          GROUP BY systemconfigchannel.mgm_id, systemconfigchannel.system_id

        ), channels AS (

         SELECT systemchannel.mgm_id,

            systemchannel.system_id,

            string_agg(((systemchannel.channel_id || ' - '::text) || (systemchannel.name)::text), ';'::text) AS software_channels

           FROM systemchannel

          GROUP BY systemchannel.mgm_id, systemchannel.system_id

        ), v6addresses AS (

         SELECT systemnetaddressv6.mgm_id,

            systemnetaddressv6.system_id,

            systemnetaddressv6.interface_id,

            string_agg(((((systemnetaddressv6.address)::text || ' ('::text) || (systemnetaddressv6.scope)::text) || ')'::text), ';'::text) AS ip6_addresses

           FROM systemnetaddressv6

          GROUP BY systemnetaddressv6.mgm_id, systemnetaddressv6.system_id, systemnetaddressv6.interface_id

        )

 SELECT system.mgm_id,

    system.system_id,

    system.profile_name,

    system.hostname,

    system.minion_id,

    system.machine_id,

    system.registered_by,

    system.registration_time,

    system.last_checkin_time,

    system.kernel_version,

    system.organization,

    system.architecture,

    system.hardware,

    systemnetinterface.name AS primary_interface,

    systemnetinterface.hardware_address,

    systemnetaddressv4.address AS ip_address,

    v6addresses.ip6_addresses,

    configchannels.configuration_channels,

    entitlements.entitlements,

    groups.system_groups,

    systemvirtualdata.host_system_id AS virtual_host,

    (systemvirtualdata.virtual_system_id IS NULL) AS is_virtualized,

    systemvirtualdata.instance_type_name AS virt_type,

    channels.software_channels,

    COALESCE(systemoutdated.packages_out_of_date, (0)::bigint) AS packages_out_of_date,

    COALESCE(systemoutdated.errata_out_of_date, (0)::bigint) AS errata_out_of_date,

    system.synced_date

   FROM (((((((((system

     LEFT JOIN systemvirtualdata ON (((system.mgm_id = systemvirtualdata.mgm_id) AND (system.system_id = systemvirtualdata.virtual_system_id))))

     LEFT JOIN systemoutdated ON (((system.mgm_id = systemoutdated.mgm_id) AND (system.system_id = systemoutdated.system_id))))

     LEFT JOIN systemnetinterface ON (((system.mgm_id = systemnetinterface.mgm_id) AND (system.system_id = systemnetinterface.system_id) AND systemnetinterface.primary_interface)))

     LEFT JOIN systemnetaddressv4 ON (((system.mgm_id = systemnetaddressv4.mgm_id) AND (system.system_id = systemnetaddressv4.system_id) AND (systemnetinterface.interface_id = systemnetaddressv4.interface_id))))

     LEFT JOIN v6addresses ON (((system.mgm_id = v6addresses.mgm_id) AND (system.system_id = v6addresses.system_id) AND (systemnetinterface.interface_id = v6addresses.interface_id))))

     LEFT JOIN entitlements ON (((system.mgm_id = entitlements.mgm_id) AND (system.system_id = entitlements.system_id))))

     LEFT JOIN groups ON (((system.mgm_id = groups.mgm_id) AND (system.system_id = groups.system_id))))

     LEFT JOIN configchannels ON (((system.mgm_id = configchannels.mgm_id) AND (system.system_id = configchannels.system_id))))

     LEFT JOIN channels ON (((system.mgm_id = channels.mgm_id) AND (system.system_id = channels.system_id))))

  ORDER BY system.mgm_id, system.system_id;