1
WITH v6addresses AS (
2
SELECT systemnetaddressv6.mgm_id,
3
systemnetaddressv6.system_id,
4
systemnetaddressv6.interface_id,
5
string_agg(((((systemnetaddressv6.address)::text || ' ('::text) || (systemnetaddressv6.scope)::text) || ')'::text), ';'::text) AS ip6_addresses
6
FROM systemnetaddressv6
7
GROUP BY systemnetaddressv6.mgm_id, systemnetaddressv6.system_id, systemnetaddressv6.interface_id
8
)
9
SELECT systemerrata.mgm_id,
10
systemerrata.errata_id,
11
systemerrata.advisory_name,
12
systemerrata.system_id,
13
system.profile_name,
14
system.hostname,
15
systemnetaddressv4.address AS ip_address,
16
v6addresses.ip6_addresses,
17
systemerrata.synced_date
18
FROM ((((systemerrata
19
JOIN system ON (((systemerrata.mgm_id = system.mgm_id) AND (systemerrata.system_id = system.system_id))))
20
LEFT JOIN systemnetinterface ON (((system.mgm_id = systemnetinterface.mgm_id) AND (system.system_id = systemnetinterface.system_id) AND systemnetinterface.primary_interface)))
21
LEFT JOIN systemnetaddressv4 ON (((system.mgm_id = systemnetaddressv4.mgm_id) AND (system.system_id = systemnetaddressv4.system_id) AND (systemnetinterface.interface_id = systemnetaddressv4.interface_id))))
22
LEFT JOIN v6addresses ON (((system.mgm_id = v6addresses.mgm_id) AND (system.system_id = v6addresses.system_id) AND (systemnetinterface.interface_id = v6addresses.interface_id))))
23
ORDER BY systemerrata.mgm_id, systemerrata.errata_id, systemerrata.system_id;