1package App::Netdisco::Web::Plugin::Device::Ports; 2 3use Dancer ':syntax'; 4use Dancer::Plugin::DBIC; 5use Dancer::Plugin::Auth::Extensible; 6 7use App::Netdisco::Util::Web (); # for sort_port 8use App::Netdisco::Web::Plugin; 9 10register_device_tab({ tag => 'ports', label => 'Ports', provides_csv => 1 }); 11 12# device ports with a description (er, name) matching 13get '/ajax/content/device/ports' => require_login sub { 14 my $q = param('q'); 15 my $prefer = param('prefer'); 16 $prefer = '' 17 unless defined $prefer and $prefer =~ m/^(?:port|name|vlan)$/; 18 19 my $device = schema('netdisco')->resultset('Device') 20 ->search_for_device($q) or send_error('Bad device', 400); 21 my $set = $device->ports->with_properties; 22 23 # refine by ports if requested 24 my $f = param('f'); 25 if ($f) { 26 if (($prefer eq 'vlan') or (not $prefer and $f =~ m/^\d+$/)) { 27 return unless $f =~ m/^\d+$/; 28 } 29 else { 30 if (param('partial')) { 31 # change wildcard chars to SQL 32 $f =~ s/\*/%/g; 33 $f =~ s/\?/_/g; 34 # set wildcards at param boundaries 35 if ($f !~ m/[%_]/) { 36 $f =~ s/^\%*/%/; 37 $f =~ s/\%*$/%/; 38 } 39 # enable ILIKE op 40 $f = { (param('invert') ? '-not_ilike' : '-ilike') => $f }; 41 } 42 elsif (param('invert')) { 43 $f = { '!=' => $f }; 44 } 45 46 if (($prefer eq 'port') or not $prefer and 47 $set->search({-or => ['me.port' => $f, 'me.descr' => $f]})->count) { 48 49 $set = $set->search({ 50 -or => [ 51 'me.port' => $f, 52 'me.descr' => $f, 53 'me.slave_of' => $f, 54 ], 55 }); 56 } 57 else { 58 $set = $set->search({'me.name' => $f}); 59 return unless $set->count; 60 } 61 } 62 } 63 64 # filter for port status if asked 65 my %port_state = map {$_ => 1} 66 (ref [] eq ref param('port_state') ? @{param('port_state')} 67 : param('port_state') ? param('port_state') : ()); 68 69 return unless scalar keys %port_state; 70 71 if (exists $port_state{free}) { 72 if (scalar keys %port_state == 1) { 73 $set = $set->only_free_ports({ 74 age_num => (param('age_num') || 3), 75 age_unit => (param('age_unit') || 'months') 76 }); 77 } 78 else { 79 $set = $set->with_is_free({ 80 age_num => (param('age_num') || 3), 81 age_unit => (param('age_unit') || 'months') 82 }); 83 } 84 delete $port_state{free}; 85 # showing free ports requires showing down ports 86 ++$port_state{down}; 87 } 88 89 if (scalar keys %port_state < 3) { 90 my @combi = (); 91 92 push @combi, {'me.up' => 'up'} 93 if exists $port_state{up}; 94 push @combi, {'me.up_admin' => 'up', 'me.up' => { '!=' => 'up'}} 95 if exists $port_state{down}; 96 push @combi, {'me.up_admin' => { '!=' => 'up'}} 97 if exists $port_state{shut}; 98 99 $set = $set->search({-or => \@combi}); 100 } 101 102 # so far only the basic device_port data 103 # now begin to join tables depending on the selected columns/options 104 105 # get vlans on the port 106 # leave this query dormant (lazy) unless c_vmember is set or vlan filtering 107 my $vlans = $set->search({}, { 108 select => [ 109 'port', 110 { array_agg => 'port_vlans.vlan', -as => 'vlan_set' }, 111 { count => 'port_vlans.vlan', -as => 'vlan_count' }, 112 ], 113 join => 'port_vlans', 114 group_by => 'me.port', 115 }); 116 117 if (param('c_vmember') or ($prefer eq 'vlan') or (not $prefer and $f =~ m/^\d+$/)) { 118 $vlans = { map {( 119 $_->port => { 120 # DBIC smart enough to work out this should be an arrayref :) 121 vlan_set => $_->get_column('vlan_set'), 122 vlan_count => $_->get_column('vlan_count'), 123 }, 124 )} $vlans->all }; 125 } 126 127 # get aggregate master status (self join) 128 $set = $set->search({}, { 129 'join' => 'agg_master', 130 '+select' => [qw/agg_master.up_admin agg_master.up/], 131 '+as' => [qw/agg_master_up_admin agg_master_up/], 132 }); 133 134 # make sure query asks for formatted timestamps when needed 135 $set = $set->with_times if param('c_lastchange'); 136 137 # what kind of nodes are we interested in? 138 my $nodes_name = (param('n_archived') ? 'nodes' : 'active_nodes'); 139 $nodes_name .= '_with_age' if param('n_age'); 140 141 my $ips_name = ((param('n_ip4') and param('n_ip6')) ? 'ips' 142 : param('n_ip4') ? 'ip4s' 143 : 'ip6s'); 144 145 if (param('c_nodes')) { 146 # retrieve active/all connected nodes, if asked for 147 $set = $set->search({}, { prefetch => [{$nodes_name => $ips_name}] }); 148 $set = $set->search({}, { order_by => ["${nodes_name}.vlan", "${nodes_name}.mac", "${ips_name}.ip"] }); 149 150 # retrieve wireless SSIDs, if asked for 151 $set = $set->search({}, { prefetch => [{$nodes_name => 'wireless'}] }) 152 if param('n_ssid'); 153 154 # retrieve NetBIOS, if asked for 155 $set = $set->search({}, { prefetch => [{$nodes_name => 'netbios'}] }) 156 if param('n_netbios'); 157 158 # retrieve vendor, if asked for 159 $set = $set->search({}, { prefetch => [{$nodes_name => 'oui'}] }) 160 if param('n_vendor'); 161 } 162 163 # retrieve SSID, if asked for 164 $set = $set->search({}, { prefetch => 'ssid' }) 165 if param('c_ssid'); 166 167 # retrieve neighbor devices, if asked for 168 #$set = $set->search({}, { prefetch => [{neighbor_alias => 'device'}] }) 169 # if param('c_neighbors'); 170 # retrieve neighbor devices, if asked for 171 $set = $set->search({}, { 172 join => 'neighbor_alias', 173 '+select' => ['neighbor_alias.ip', 'neighbor_alias.dns'], 174 '+as' => ['neighbor_ip', 'neighbor_dns'], 175 }) if param('c_neighbors'); 176 177 # also get remote LLDP inventory if asked for 178 $set = $set->with_remote_inventory if param('n_inventory'); 179 180 # run query 181 my @results = $set->all; 182 183 # filter for tagged vlan using existing agg query, 184 # which is better than join inflation 185 if (($prefer eq 'vlan') or (not $prefer and $f =~ m/^\d+$/)) { 186 if (param('invert')) { 187 @results = grep { 188 (!defined $_->vlan or $_->vlan ne $f) 189 and 190 (0 == scalar grep {defined and $_ ne $f} @{ $vlans->{$_->port}->{vlan_set} }) 191 } @results; 192 } 193 else { 194 @results = grep { 195 (defined $_->vlan and $_->vlan eq $f) 196 or 197 (scalar grep {defined and $_ eq $f} @{ $vlans->{$_->port}->{vlan_set} }) 198 } @results; 199 } 200 } 201 202 # sort ports 203 @results = sort { &App::Netdisco::Util::Web::sort_port($a->port, $b->port) } @results; 204 205 # empty set would be a 'no records' msg 206 return unless scalar @results; 207 208 if (request->is_ajax) { 209 template 'ajax/device/ports.tt', { 210 results => \@results, 211 nodes => $nodes_name, 212 ips => $ips_name, 213 device => $device, 214 vlans => $vlans, 215 }, { layout => undef }; 216 } 217 else { 218 header( 'Content-Type' => 'text/comma-separated-values' ); 219 template 'ajax/device/ports_csv.tt', { 220 results => \@results, 221 nodes => $nodes_name, 222 ips => $ips_name, 223 device => $device, 224 vlans => $vlans, 225 }, { layout => undef }; 226 } 227}; 228 229true; 230