1
WITH packages_from_channels AS (
2
SELECT systempackageinstalled_1.mgm_id,
3
systempackageinstalled_1.system_id,
4
package.package_id,
5
systempackageinstalled_1.name,
6
systempackageinstalled_1.epoch,
7
systempackageinstalled_1.version,
8
systempackageinstalled_1.release,
9
systempackageinstalled_1.arch,
10
systempackageinstalled_1.type
11
FROM (((systempackageinstalled systempackageinstalled_1
12
JOIN systemchannel ON (((systempackageinstalled_1.mgm_id = systemchannel.mgm_id) AND (systempackageinstalled_1.system_id = systemchannel.system_id))))
13
JOIN channelpackage ON (((systemchannel.mgm_id = channelpackage.mgm_id) AND (channelpackage.channel_id = systemchannel.channel_id))))
14
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))))
15
)
16
SELECT system.mgm_id,
17
system.system_id,
18
system.hostname AS system_name,
19
system.organization,
20
systempackageinstalled.name AS package_name,
21
systempackageinstalled.epoch AS package_epoch,
22
systempackageinstalled.version AS package_version,
23
systempackageinstalled.release AS package_release,
24
systempackageinstalled.arch AS package_arch,
25
systempackageinstalled.synced_date
26
FROM ((system
27
JOIN systempackageinstalled ON (((system.mgm_id = systempackageinstalled.mgm_id) AND (system.system_id = systempackageinstalled.system_id))))
28
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))))
29
WHERE (packages_from_channels.package_id IS NULL)
30
ORDER BY system.mgm_id, system.organization, system.system_id, systempackageinstalled.name;