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