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