erratasystemsreport

WITH 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 systemerrata.mgm_id,

    systemerrata.errata_id,

    systemerrata.advisory_name,

    systemerrata.system_id,

    system.profile_name,

    system.hostname,

    systemnetaddressv4.address AS ip_address,

    v6addresses.ip6_addresses,

    systemerrata.synced_date

   FROM ((((systemerrata

     JOIN system ON (((systemerrata.mgm_id = system.mgm_id) AND (systemerrata.system_id = system.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))))

  ORDER BY systemerrata.mgm_id, systemerrata.errata_id, systemerrata.system_id;