portvlanmismatch.sql

SELECT  ips[1] AS left_ip,
        ports[1] AS left_port,

        (SELECT array_agg(a) FROM jsonb_array_elements_text(vlans->0) AS a) AS left_vlans,
        (SELECT array_agg(a)
            FROM jsonb_array_elements_text(vlans->0) AS a
            WHERE a NOT IN
                (SELECT b FROM jsonb_array_elements_text(vlans->1) AS b)) as only_left_vlans,

        ips[2] AS right_ip,
        ports[2] AS right_port,

        (SELECT array_agg(a) FROM jsonb_array_elements_text(vlans->1) AS a) AS right_vlans,
        (SELECT array_agg(a)
            FROM jsonb_array_elements_text(vlans->1) AS a
            WHERE a NOT IN
                (SELECT b FROM jsonb_array_elements_text(vlans->0) AS b)) as only_right_vlans,

        CASE WHEN (jsonb_array_length(vlans->0) = 1 AND jsonb_array_length(vlans->1) = 1
                   AND position('n:' in vlans->0->>0) = 1 AND position('n:' in vlans->1->>0) = 1)
             THEN true ELSE false END AS native_translated

FROM (
    SELECT array_agg(ip) AS ips,
           array_agg(port) AS ports,
           jsonb_agg(DISTINCT vlist) AS vlans

    FROM (
        SELECT alldpv.ip,
               alldpv.port,
               jsonb_agg( CASE WHEN native THEN 'n:' || vlan::text ELSE vlan::text END ORDER BY vlan ASC )
                   FILTER (WHERE vlan IS NOT NULL) AS vlist,
               -- create a key for each port allowing pairs of ports to be matched
               CASE WHEN alldpv.ip <= alldpv.remote_ip THEN host(alldpv.ip)::text || '!' || alldpv.port::text
                    ELSE host(alldpv.remote_ip)::text || '!' || alldpv.remote_port::text END AS lowport

        FROM (
            SELECT dpv.ip, dpv.port, dpv.native, dip.ip AS remote_ip, dp.remote_port, dpv.vlan
            FROM device_port_vlan dpv

            LEFT JOIN device_port dp
                ON dpv.ip = dp.ip AND dpv.port = dp.port

            LEFT JOIN device_ip dip
                ON dp.remote_ip = dip.alias

            UNION

            SELECT dp2.ip, dp2.port, false, dip2.ip AS remote_ip, dp2.remote_port, dpv2.vlan
            FROM device_port dp2

            LEFT JOIN device_port dp3
                ON dp2.ip = dp3.ip AND dp2.port = dp3.slave_of AND dp2.has_subinterfaces

            LEFT JOIN device_port_vlan dpv2
                ON dp3.ip = dpv2.ip AND dp3.port = dpv2.port

            LEFT JOIN device_ip dip2
                ON dp2.remote_ip = dip2.alias
        ) alldpv

        WHERE vlan NOT IN ( 1002, 1003, 1004, 1005 ) AND remote_ip IS NOT NULL
        GROUP BY ip, port, remote_ip, remote_port
    ) ports_with_vlans

    GROUP BY lowport
) pairs_of_ports

WHERE jsonb_array_length(vlans) > 1
ORDER BY left_ip, left_port