1package App::Netdisco::Web::Plugin::Report::IpInventory;
2
3use Dancer ':syntax';
4use Dancer::Plugin::DBIC;
5use Dancer::Plugin::Auth::Extensible;
6
7use App::Netdisco::Web::Plugin;
8use NetAddr::IP::Lite ':lower';
9use POSIX qw/strftime/;
10
11register_report(
12    {   category     => 'IP',
13        tag          => 'ipinventory',
14        label        => 'IP Inventory',
15        provides_csv => 1,
16        api_endpoint => 1,
17        api_parameters => [
18          subnet => {
19            description => 'IP Prefix to search',
20            required => 1,
21          },
22          daterange => {
23            description => 'Date range to search',
24            default => ('1970-01-01 to '. strftime('%Y-%m-%d', gmtime)),
25          },
26          age_invert => {
27            description => 'Results should NOT be within daterange',
28            type => 'boolean',
29            default => 'false',
30          },
31          limit => {
32            description => 'Maximum number of historical records',
33            enum => [qw/32 64 128 256 512 1024 2048 4096 8192/],
34            default => '256',
35          },
36          never => {
37            description => 'Include in the report IPs never seen',
38            type => 'boolean',
39            default => 'false',
40          },
41        ],
42    }
43);
44
45get '/ajax/content/report/ipinventory' => require_login sub {
46
47    # Default to something simple with no results to prevent
48    # "Search failed!" error
49    (my $subnet = (param('subnet') || '0.0.0.0/32')) =~ s/\s//g;
50    $subnet = NetAddr::IP::Lite->new($subnet);
51    $subnet = NetAddr::IP::Lite->new('0.0.0.0/32')
52      if (! $subnet) or ($subnet->addr eq '0.0.0.0');
53
54    my $agenot = param('age_invert') || '0';
55
56    my $daterange = param('daterange')
57      || ('1970-01-01 to '. strftime('%Y-%m-%d', gmtime));
58    my ( $start, $end ) = $daterange =~ /(\d+-\d+-\d+)/gmx;
59
60    my $limit = param('limit') || 256;
61    my $never = param('never') || '0';
62    my $order = [{-desc => 'age'}, {-asc => 'ip'}];
63
64    # We need a reasonable limit to prevent a potential DoS, especially if
65    # 'never' is true.  TODO: Need better input validation, both JS and
66    # server-side to provide user feedback
67    $limit = 8192 if $limit > 8192;
68
69    my $rs1 = schema('netdisco')->resultset('DeviceIp')->search(
70        undef,
71        {   join   => ['device', 'device_port'],
72            select => [
73                'alias AS ip',
74                'device_port.mac as mac',
75                'creation AS time_first',
76                'device.last_discover AS time_last',
77                'dns',
78                \'true AS active',
79                \'false AS node',
80                \qq/replace( date_trunc( 'minute', age( now(), device.last_discover ) ) ::text, 'mon', 'month') AS age/,
81                'device.vendor',
82
83            ],
84            as => [qw( ip mac time_first time_last dns active node age vendor)],
85        }
86    )->hri;
87
88    my $rs2 = schema('netdisco')->resultset('NodeIp')->search(
89        undef,
90        {   join   => ['oui'],
91            columns   => [qw( ip mac time_first time_last dns active)],
92            '+select' => [ \'true AS node',
93                           \qq/replace( date_trunc( 'minute', age( now(), time_last ) ) ::text, 'mon', 'month') AS age/,
94                           'oui.company'
95                         ],
96            '+as'     => [ 'node', 'age', 'vendor' ],
97        }
98    )->hri;
99
100    my $rs3 = schema('netdisco')->resultset('NodeNbt')->search(
101        undef,
102        {   join   => ['oui'],
103            columns   => [qw( ip mac time_first time_last )],
104            '+select' => [
105                'nbname AS dns', 'active',
106                \'true AS node',
107                \qq/replace( date_trunc( 'minute', age( now(), time_last ) ) ::text, 'mon', 'month') AS age/,
108                'oui.company'
109            ],
110            '+as' => [ 'dns', 'active', 'node', 'age', 'vendor' ],
111        }
112    )->hri;
113
114    my $rs_union = $rs1->union( [ $rs2, $rs3 ] );
115
116    if ( $never ) {
117        $subnet = NetAddr::IP::Lite->new('0.0.0.0/32') if ($subnet->bits ne 32);
118
119        my $rs4 = schema('netdisco')->resultset('Virtual::CidrIps')->search(
120            undef,
121            {   bind => [ $subnet->cidr ],
122                columns   => [qw( ip mac time_first time_last dns active)],
123                '+select' => [ \'false AS node',
124                               \qq/replace( date_trunc( 'minute', age( now(), time_last ) ) ::text, 'mon', 'month') AS age/,
125                               \'null as vendor'
126                             ],
127                '+as'     => [ 'node', 'age', 'vendor' ],
128            }
129        )->hri;
130
131        $rs_union = $rs_union->union( [$rs4] );
132    }
133
134    my $rs_sub = $rs_union->search(
135        { ip => { '<<' => $subnet->cidr } },
136        {   select   => [
137                \'DISTINCT ON (ip) ip',
138                'mac',
139                'dns',
140                \qq/date_trunc('second', time_last) AS time_last/,
141                \qq/date_trunc('second', time_first) AS time_first/,
142                'active',
143                'node',
144                'age',
145                'vendor'
146            ],
147            as => [
148                'ip',     'mac',  'dns',  'time_last', 'time_first',
149                'active', 'node', 'age', 'vendor'
150            ],
151            order_by => [{-asc => 'ip'}, {-desc => 'active'}, {-asc => 'node'}],
152        }
153    )->as_query;
154
155    my $rs;
156    if ( $start and $end ) {
157        $start = $start . ' 00:00:00';
158        $end   = $end . ' 23:59:59';
159
160        if ( $agenot ) {
161            $rs = $rs_union->search(
162                {   -or => [
163                        time_first => [ undef ],
164                        time_last => [ { '<', $start }, { '>', $end } ]
165                    ]
166                },
167                { from => { me => $rs_sub }, }
168            );
169        }
170        else {
171            $rs = $rs_union->search(
172                {   -or => [
173                      -and => [
174                          time_first => undef,
175                          time_last  => undef,
176                      ],
177                      -and => [
178                          time_last => { '>=', $start },
179                          time_last => { '<=', $end },
180                      ],
181                    ],
182                },
183                { from => { me => $rs_sub }, }
184            );
185        }
186    }
187    else {
188        $rs = $rs_union->search( undef, { from => { me => $rs_sub }, } );
189    }
190
191    my @results = $rs->order_by($order)->limit($limit)->all;
192    return unless scalar @results;
193
194    if ( request->is_ajax ) {
195        my $json = to_json( \@results );
196        template 'ajax/report/ipinventory.tt', { results => $json };
197    }
198    else {
199        header( 'Content-Type' => 'text/comma-separated-values' );
200        template 'ajax/report/ipinventory_csv.tt', { results => \@results, };
201    }
202};
203
2041;
205