1package App::Netdisco::DB::Result::Virtual::SubnetUtilization; 2 3use strict; 4use warnings; 5 6use utf8; 7use base 'DBIx::Class::Core'; 8 9__PACKAGE__->table_class('DBIx::Class::ResultSource::View'); 10 11__PACKAGE__->table('cidr_ips'); 12__PACKAGE__->result_source_instance->is_virtual(1); 13__PACKAGE__->result_source_instance->view_definition(<<'ENDSQL'); 14 SELECT net as subnet, 15 power(2, (32 - masklen(net))) as subnet_size, 16 count(DISTINCT ip) as active, 17 round(100 * count(DISTINCT ip) / (power(2, (32 - masklen(net))))) as percent 18 FROM ( 19 SELECT DISTINCT net, ni.ip 20 FROM subnets s1, node_ip ni 21 WHERE s1.net <<= ?::cidr 22 AND ni.ip <<= s1.net 23 AND (( 24 ni.time_first IS null 25 AND ni.time_last IS null 26 ) OR ( 27 ni.time_last >= ? 28 AND ni.time_last <= ? 29 )) 30 AND s1.last_discover >= ? 31 UNION 32 SELECT DISTINCT net, di.alias as ip 33 FROM subnets s2, device_ip di JOIN device d USING (ip) 34 WHERE s2.net <<= ?::cidr 35 AND di.alias <<= s2.net 36 AND s2.last_discover >= ? 37 AND d.last_discover >= ? 38 ) as joined 39 GROUP BY net 40 ORDER BY percent ASC 41ENDSQL 42 43__PACKAGE__->add_columns( 44 "subnet", 45 { data_type => "cidr", is_nullable => 0 }, 46 "subnet_size", 47 { data_type => "integer", is_nullable => 0 }, 48 "active", 49 { data_type => "integer", is_nullable => 0 }, 50 "percent", 51 { data_type => "integer", is_nullable => 0 }, 52); 53 541; 55