1
WITH entitlements AS (
2
SELECT systementitlement.mgm_id,
3
systementitlement.system_id,
4
string_agg(((systementitlement.system_group_id || ' - '::text) || (systementitlement.name)::text), ';'::text) AS entitlements
5
FROM systementitlement
6
GROUP BY systementitlement.mgm_id, systementitlement.system_id
7
), groups AS (
8
SELECT systemgroupmember.mgm_id,
9
systemgroupmember.system_id,
10
string_agg(((systemgroupmember.system_group_id || ' - '::text) || (systemgroupmember.group_name)::text), ';'::text) AS system_groups
11
FROM systemgroupmember
12
GROUP BY systemgroupmember.mgm_id, systemgroupmember.system_id
13
), configchannels AS (
14
SELECT systemconfigchannel.mgm_id,
15
systemconfigchannel.system_id,
16
string_agg(((systemconfigchannel.config_channel_id || ' - '::text) || (systemconfigchannel.name)::text), ';'::text) AS configuration_channels
17
FROM systemconfigchannel
18
GROUP BY systemconfigchannel.mgm_id, systemconfigchannel.system_id
19
), channels AS (
20
SELECT systemchannel.mgm_id,
21
systemchannel.system_id,
22
string_agg(((systemchannel.channel_id || ' - '::text) || (systemchannel.name)::text), ';'::text) AS software_channels
23
FROM systemchannel
24
GROUP BY systemchannel.mgm_id, systemchannel.system_id
25
), v6addresses AS (
26
SELECT systemnetaddressv6.mgm_id,
27
systemnetaddressv6.system_id,
28
systemnetaddressv6.interface_id,
29
string_agg(((((systemnetaddressv6.address)::text || ' ('::text) || (systemnetaddressv6.scope)::text) || ')'::text), ';'::text) AS ip6_addresses
30
FROM systemnetaddressv6
31
GROUP BY systemnetaddressv6.mgm_id, systemnetaddressv6.system_id, systemnetaddressv6.interface_id
32
)
33
SELECT system.mgm_id,
34
system.system_id,
35
system.profile_name,
36
system.hostname,
37
system.minion_id,
38
system.machine_id,
39
system.registered_by,
40
system.registration_time,
41
system.last_checkin_time,
42
system.kernel_version,
43
system.organization,
44
system.architecture,
45
system.hardware,
46
systemnetinterface.name AS primary_interface,
47
systemnetinterface.hardware_address,
48
systemnetaddressv4.address AS ip_address,
49
v6addresses.ip6_addresses,
50
configchannels.configuration_channels,
51
entitlements.entitlements,
52
groups.system_groups,
53
systemvirtualdata.host_system_id AS virtual_host,
54
(systemvirtualdata.virtual_system_id IS NULL) AS is_virtualized,
55
systemvirtualdata.instance_type_name AS virt_type,
56
channels.software_channels,
57
COALESCE(systemoutdated.packages_out_of_date, (0)::bigint) AS packages_out_of_date,
58
COALESCE(systemoutdated.errata_out_of_date, (0)::bigint) AS errata_out_of_date,
59
system.synced_date
60
FROM (((((((((system
61
LEFT JOIN systemvirtualdata ON (((system.mgm_id = systemvirtualdata.mgm_id) AND (system.system_id = systemvirtualdata.virtual_system_id))))
62
LEFT JOIN systemoutdated ON (((system.mgm_id = systemoutdated.mgm_id) AND (system.system_id = systemoutdated.system_id))))
63
LEFT JOIN systemnetinterface ON (((system.mgm_id = systemnetinterface.mgm_id) AND (system.system_id = systemnetinterface.system_id) AND systemnetinterface.primary_interface)))
64
LEFT JOIN systemnetaddressv4 ON (((system.mgm_id = systemnetaddressv4.mgm_id) AND (system.system_id = systemnetaddressv4.system_id) AND (systemnetinterface.interface_id = systemnetaddressv4.interface_id))))
65
LEFT JOIN v6addresses ON (((system.mgm_id = v6addresses.mgm_id) AND (system.system_id = v6addresses.system_id) AND (systemnetinterface.interface_id = v6addresses.interface_id))))
66
LEFT JOIN entitlements ON (((system.mgm_id = entitlements.mgm_id) AND (system.system_id = entitlements.system_id))))
67
LEFT JOIN groups ON (((system.mgm_id = groups.mgm_id) AND (system.system_id = groups.system_id))))
68
LEFT JOIN configchannels ON (((system.mgm_id = configchannels.mgm_id) AND (system.system_id = configchannels.system_id))))
69
LEFT JOIN channels ON (((system.mgm_id = channels.mgm_id) AND (system.system_id = channels.system_id))))
70
ORDER BY system.mgm_id, system.system_id;