DBIx::Class generated query using new multiple prefetch collapsing logic, for Netdisco Device Port view with all view options enabled, including archive data.

gistfile1.sql

SELECT me.ip,
       me.port,
       me.creation,
       me.descr,
       me.up,
       me.up_admin,
       me.type,
       me.duplex,
       me.duplex_admin,
       me.speed,
       me.name,
       me.mac,
       me.mtu,
       me.stp,
       me.remote_ip,
       me.remote_port,
       me.remote_type,
       me.remote_id,
       me.manual_topo,
       me.is_uplink,
       me.vlan,
       me.pvid,
       me.lastchange,
       up != 'up'
AND age(to_timestamp(extract(epoch
                             FROM device.last_discover) - (device.uptime - lastchange)/100)) > '3 months'::interval,
  (SELECT COUNT(*)
   FROM
     (SELECT *
      FROM device_port_vlan
      WHERE NOT native ) dpvt
   WHERE dpvt.ip = me.ip
     AND dpvt.port = me.port ), port_vlans_tagged.ip,
                                port_vlans_tagged.port,
                                port_vlans_tagged.vlan,
                                port_vlans_tagged.native,
                                port_vlans_tagged.creation,
                                port_vlans_tagged.last_discover,
                                port_vlans_tagged.vlantype,
                                vlan.ip,
                                vlan.vlan,
                                vlan.description,
                                vlan.creation,
                                vlan.last_discover,
                                nodes_with_age.mac,
                                nodes_with_age.switch,
                                nodes_with_age.port,
                                nodes_with_age.active,
                                nodes_with_age.oui,
                                nodes_with_age.time_first,
                                nodes_with_age.time_recent,
                                nodes_with_age.time_last,
                                nodes_with_age.vlan,
                                nodes_with_age.time_last_age,
                                ips.mac,
                                ips.ip,
                                ips.dns,
                                ips.active,
                                ips.time_first,
                                ips.time_last,
                                neighbor_alias.ip,
                                neighbor_alias.alias,
                                neighbor_alias.subnet,
                                neighbor_alias.port,
                                neighbor_alias.dns,
                                neighbor_alias.creation,
                                device_2.ip,
                                device_2.creation,
                                device_2.dns,
                                device_2.description,
                                device_2.uptime,
                                device_2.contact,
                                device_2.name,
                                device_2.location,
                                device_2.layers,
                                device_2.ports,
                                device_2.mac,
                                device_2.serial,
                                device_2.model,
                                device_2.ps1_type,
                                device_2.ps2_type,
                                device_2.ps1_status,
                                device_2.ps2_status,
                                device_2.fan,
                                device_2.slots,
                                device_2.vendor,
                                device_2.os,
                                device_2.os_ver,
                                device_2.log,
                                device_2.snmp_ver,
                                device_2.snmp_comm,
                                device_2.snmp_class,
                                device_2.vtp_domain,
                                device_2.last_discover,
                                device_2.last_macsuck,
                                device_2.last_arpnip
FROM device_port me
JOIN device device ON device.ip = me.ip
LEFT JOIN
  (SELECT *
   FROM device_port_vlan
   WHERE NOT native ) port_vlans_tagged ON port_vlans_tagged.ip = me.ip
AND port_vlans_tagged.port = me.port
LEFT JOIN device_vlan vlan ON vlan.ip = port_vlans_tagged.ip
AND vlan.vlan = port_vlans_tagged.vlan
LEFT JOIN
  (SELECT *,
          replace(age (date_trunc('minute', time_last + interval '30 second'))::text, 'mon', 'month')
            AS time_last_age
   FROM node ) nodes_with_age ON nodes_with_age.port = me.port
AND nodes_with_age.switch = me.ip
LEFT JOIN node_ip ips ON ips.active = nodes_with_age.active
AND ips.mac = nodes_with_age.mac
LEFT JOIN device_ip neighbor_alias ON neighbor_alias.alias = me.remote_ip
LEFT JOIN device device_2 ON device_2.ip = neighbor_alias.ip
WHERE me.ip = '192.0.2.1';