systemextrapackagesreport

WITH packages_from_channels AS (

         SELECT systempackageinstalled_1.mgm_id,

            systempackageinstalled_1.system_id,

            package.package_id,

            systempackageinstalled_1.name,

            systempackageinstalled_1.epoch,

            systempackageinstalled_1.version,

            systempackageinstalled_1.release,

            systempackageinstalled_1.arch,

            systempackageinstalled_1.type

           FROM (((systempackageinstalled systempackageinstalled_1

             JOIN systemchannel ON (((systempackageinstalled_1.mgm_id = systemchannel.mgm_id) AND (systempackageinstalled_1.system_id = systemchannel.system_id))))

             JOIN channelpackage ON (((systemchannel.mgm_id = channelpackage.mgm_id) AND (channelpackage.channel_id = systemchannel.channel_id))))

             JOIN package ON (((systempackageinstalled_1.mgm_id = package.mgm_id) AND (channelpackage.package_id = package.package_id) AND ((package.name)::text = (systempackageinstalled_1.name)::text) AND ((COALESCE(package.epoch, ''::character varying))::text = (COALESCE(systempackageinstalled_1.epoch, ''::character varying))::text) AND ((package.version)::text = (systempackageinstalled_1.version)::text) AND ((package.release)::text = (systempackageinstalled_1.release)::text) AND ((package.arch)::text = (systempackageinstalled_1.arch)::text))))

        )

 SELECT system.mgm_id,

    system.system_id,

    system.hostname AS system_name,

    system.organization,

    systempackageinstalled.name AS package_name,

    systempackageinstalled.epoch AS package_epoch,

    systempackageinstalled.version AS package_version,

    systempackageinstalled.release AS package_release,

    systempackageinstalled.arch AS package_arch,

    systempackageinstalled.synced_date

   FROM ((system

     JOIN systempackageinstalled ON (((system.mgm_id = systempackageinstalled.mgm_id) AND (system.system_id = systempackageinstalled.system_id))))

     LEFT JOIN packages_from_channels ON (((systempackageinstalled.mgm_id = packages_from_channels.mgm_id) AND (systempackageinstalled.system_id = packages_from_channels.system_id) AND ((systempackageinstalled.name)::text = (packages_from_channels.name)::text) AND ((COALESCE(systempackageinstalled.epoch, ''::character varying))::text = (COALESCE(packages_from_channels.epoch, ''::character varying))::text) AND ((systempackageinstalled.version)::text = (packages_from_channels.version)::text) AND ((systempackageinstalled.release)::text = (packages_from_channels.release)::text) AND ((systempackageinstalled.arch)::text = (packages_from_channels.arch)::text))))

  WHERE (packages_from_channels.package_id IS NULL)

  ORDER BY system.mgm_id, system.organization, system.system_id, systempackageinstalled.name;