1package App::Netdisco::DB::Result::Virtual::DuplexMismatch; 2 3use strict; 4use warnings; 5 6use base 'DBIx::Class::Core'; 7 8__PACKAGE__->table_class('DBIx::Class::ResultSource::View'); 9 10__PACKAGE__->table('duplex_mismatch'); 11__PACKAGE__->result_source_instance->is_virtual(1); 12__PACKAGE__->result_source_instance->view_definition(<<ENDSQL 13 SELECT dp.ip AS left_ip, d1.dns AS left_dns, dp.port AS left_port, dp.duplex AS left_duplex, 14 di.ip AS right_ip, d2.dns AS right_dns, dp.remote_port AS right_port, dp2.duplex AS right_duplex 15 FROM ( SELECT device_port.ip, device_port.remote_ip, device_port.port, device_port.duplex, device_port.remote_port 16 FROM device_port 17 WHERE 18 device_port.remote_port IS NOT NULL 19 AND device_port.up NOT ILIKE '%down%' 20 GROUP BY device_port.ip, device_port.remote_ip, device_port.port, device_port.duplex, device_port.remote_port 21 ORDER BY device_port.ip) dp 22 LEFT JOIN device_ip di ON dp.remote_ip = di.alias 23 LEFT JOIN device d1 ON dp.ip = d1.ip 24 LEFT JOIN device d2 ON di.ip = d2.ip 25 LEFT JOIN device_port dp2 ON (di.ip = dp2.ip AND dp.remote_port = dp2.port) 26 WHERE di.ip IS NOT NULL 27 AND dp.duplex <> dp2.duplex 28 AND dp.ip <= di.ip 29 AND dp2.up NOT ILIKE '%down%' 30 ORDER BY dp.ip 31ENDSQL 32); 33 34__PACKAGE__->add_columns( 35 'left_ip' => { 36 data_type => 'inet', 37 }, 38 'left_dns' => { 39 data_type => 'text', 40 }, 41 'left_port' => { 42 data_type => 'text', 43 }, 44 'left_duplex' => { 45 data_type => 'text', 46 }, 47 'right_ip' => { 48 data_type => 'inet', 49 }, 50 'right_dns' => { 51 data_type => 'text', 52 }, 53 'right_port' => { 54 data_type => 'text', 55 }, 56 'right_duplex' => { 57 data_type => 'text', 58 }, 59); 60 611; 62