1package App::Netdisco::DB::Result::Virtual::PortUtilization;
2
3use strict;
4use warnings;
5
6use base 'DBIx::Class::Core';
7
8__PACKAGE__->table_class('DBIx::Class::ResultSource::View');
9
10# NOTE this query is in `git grep 'THREE PLACES'`
11__PACKAGE__->table('port_utilization');
12__PACKAGE__->result_source_instance->is_virtual(1);
13__PACKAGE__->result_source_instance->view_definition(<<ENDSQL
14 SELECT d.dns AS dns, d.ip as ip,
15     sum(CASE WHEN (dp.type != 'propVirtual') THEN 1
16              ELSE 0 END) as port_count,
17     sum(CASE WHEN (dp.type != 'propVirtual' AND dp.up_admin = 'up' AND dp.up = 'up') THEN 1
18              ELSE 0 END) as ports_in_use,
19     sum(CASE WHEN (dp.type != 'propVirtual' AND dp.up_admin != 'up') THEN 1
20              ELSE 0 END) as ports_shutdown,
21     sum(CASE
22      WHEN ( dp.type != 'propVirtual' AND dp.up_admin = 'up' AND dp.up != 'up'
23             AND (age(now(), to_timestamp(extract(epoch from d.last_discover) - (d.uptime/100))) < ?::interval)
24             AND (last_node.time_last IS NULL OR (age(now(), last_node.time_last)) > ?::interval) )
25        THEN 1
26      WHEN ( dp.type != 'propVirtual' AND dp.up_admin = 'up' AND dp.up != 'up'
27             AND (age(now(), to_timestamp(extract(epoch from d.last_discover) - (d.uptime - dp.lastchange)/100)) > ?::interval) )
28        THEN 1
29      ELSE 0
30     END) as ports_free
31   FROM device d
32   LEFT JOIN device_port dp
33     ON d.ip = dp.ip
34   LEFT JOIN
35     ( SELECT DISTINCT ON (switch, port) * FROM node
36         ORDER BY switch, port, time_last desc ) AS last_node
37     ON dp.port = last_node.port AND dp.ip = last_node.switch
38   GROUP BY d.dns, d.ip
39   ORDER BY d.dns, d.ip
40ENDSQL
41);
42
43__PACKAGE__->add_columns(
44  'dns' => {
45    data_type => 'text',
46  },
47  'ip' => {
48    data_type => 'inet',
49  },
50  'port_count' => {
51    data_type => 'integer',
52  },
53  'ports_in_use' => {
54    data_type => 'integer',
55  },
56  'ports_shutdown' => {
57    data_type => 'integer',
58  },
59  'ports_free' => {
60    data_type => 'integer',
61  },
62);
63
641;
65