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