1package App::Netdisco::DB::Result::Virtual::PortVLANMismatch; 2 3use strict; 4use warnings; 5 6use base 'DBIx::Class::Core'; 7__PACKAGE__->table_class('DBIx::Class::ResultSource::View'); 8 9__PACKAGE__->table('port_vlan_mismatch'); 10__PACKAGE__->result_source_instance->is_virtual(1); 11__PACKAGE__->result_source_instance->view_definition(<<'ENDSQL'); 12 WITH all_vlans AS 13 (SELECT ip, port, 14 array_to_string(array_agg( CASE WHEN native THEN 'n:' || vlan::text 15 ELSE vlan::text END 16 ORDER BY vlan ASC ), ', ') AS vlist 17 FROM device_port_vlan GROUP BY ip, port) 18 19 SELECT CASE WHEN length(ld.dns) > 0 THEN ld.dns ELSE host(ld.ip) END AS left_device, 20 lp.port AS left_port, 21 (SELECT vlist FROM all_vlans WHERE ip=lp.ip AND port=lp.port) AS left_vlans, 22 CASE WHEN length(rd.dns) > 0 THEN rd.dns ELSE host(rd.ip) END AS right_device, 23 rp.port AS right_port, 24 (SELECT vlist FROM all_vlans WHERE ip=rp.ip AND port=rp.port) AS right_vlans 25 FROM device ld 26 JOIN device_port lp USING (ip) 27 JOIN device_port rp ON lp.remote_ip=rp.ip AND lp.remote_port=rp.port 28 JOIN device rd ON rp.ip=rd.ip 29 WHERE ld.ip < rd.ip AND 30 (SELECT vlist FROM all_vlans WHERE ip=lp.ip AND port=lp.port) 31 != 32 (SELECT vlist FROM all_vlans WHERE ip=rp.ip AND port=rp.port) 33 ORDER BY left_device, left_port 34ENDSQL 35 36__PACKAGE__->add_columns( 37 'left_device' => { data_type => 'text' }, 38 'left_port' => { data_type => 'text' }, 39 'left_vlans' => { data_type => 'text' }, 40 41 'right_device' => { data_type => 'text' }, 42 'right_port' => { data_type => 'text' }, 43 'right_vlans' => { data_type => 'text' }, 44); 45 461; 47