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