<& SELF:matches &>

Device Search

Hints

Device Reports

SSID: >Broadcast >Stealth >Either
<%args> @models => () @layers => () @vendors => () @os => () @os_ver => () $ip => '' $text => '' $dns => '' $name => '' $desc => '' $boolean => 'and' $exact => 0 $loc => '' $specific => undef $vlan => undef $ssid => undef $ssidbcast => undef <%shared> my $matches = undef; my $where = {}; my $awhere = {}; my $arg_exact; my $winsize = 5; # max size for select boxes. my $title = 'Search Results'; # Search Result Title <%init> my $time1= time; my $models = sql_column('device',['distinct(model)', 'true']); my $vendors = sql_column('device',['distinct(vendor)','true']); my $oses = sql_column('device',['distinct(os)', 'true']); my $os_vers = sql_column('device',['distinct(os_ver)','true']); $arg_exact = $exact; # Hack for VLANs - make it a specific search if (length $vlan and !defined $specific) { $specific = 'vlan_dev'; } # Clear non-specific terms if (defined $specific and length($specific)){ @models = @layers = @vendors = undef; $text = $name = $desc = $loc = undef; } if ($specific =~ /alias/i){ $title = 'Device Aliases (IPs) without DNS Entries'; # Show these cols $where->{location}++; $where->{alias}++; $where->{name}++; $where->{contact}++; $matches = sql_rows('device d left join device_ip i on d.ip = i.ip', ['d.ip','d.dns','d.location','i.alias','d.name','d.contact'], {'i.dns' => 'is null','i.alias' => 'is not null'}); } elsif ($specific =~ /DNS entries/){ $title = 'Devices without DNS entries'; $where->{vendor}++; $where->{model}++; $where->{location}++; $where->{name}++; $where->{contact}++; $matches = sql_rows('device', ['ip','vendor','model','location','name','contact'], {'dns' => 'is null'}); } elsif ($specific =~ /orphan/i){ $title = 'Devices Orphaned by missing topology info'; # Show these cols $where->{vendor}++; $where->{model}++; $where->{location}++; $where->{contact}++; my $G = make_graph(); my @S = $G->connected_components; # Find the strongly connected component that is biggest my $biggest = 0; for (my $sub = 0; $sub < @S; $sub++){ $biggest = scalar @{$S[$sub]} > scalar @{$S[$biggest]} ? $sub : $biggest; } my @orphans; for (my $j; $j < @S; $j++){ next if $j == $biggest; push (@orphans,@{$S[$j]}); } # Add in devices with no topology info at all. # make_graph() ignores these but this search shouldn't. my $qlist = [\\'not in (select distinct ip from device_port where remote_port is not null)']; # Also add in orphans if there were any. if (@orphans) { $qlist->[1] = \@orphans; } $matches = sql_rows('device',['ip','dns','vendor','model','location','contact'], {'ip'=>$qlist},1); } elsif ($specific =~ /multiple/i){ $title = 'Device ports with multiple nodes attached'; $where->{port}++; $where->{count}++; $matches = sql_rows('device_port d, node n, device i', ['d.ip','i.dns','d.port','count(distinct(n.mac))'], {'i.ip' => \'d.ip', 'd.ip' => \'n.switch', 'd.port' => \'n.port', 'd.remote_ip' => 'is null', 'n.active' => 1}, undef, "group by d.ip,d.port,i.dns having count(distinct(n.mac)) > 1"); } elsif ($specific =~ /disabled/i){ $title = 'Ports administratively disabled'; $where->{port}++; $where->{up_admin}++; $matches = sql_rows('device_port p, device d', ['d.ip','d.dns','p.port','p.up_admin'], {'d.ip' => \'p.ip', 'up_admin' => 'down'} ); } elsif ($specific =~ /vlan_dev/i){ $vlan =~ s/[^\d]//g; # untaint the vlan passed to us $title = "Devices Carrying VLAN $vlan"; $where->{vendor}++; $where->{model}++; $where->{os}++; $where->{description}++; $matches = sql_rows( 'device d left join device_vlan dv on d.ip = dv.ip right join device_port_vlan dpv on (d.ip = dpv.ip and dv.vlan = dpv.vlan)', ['distinct(d.ip)','d.dns','d.vendor','d.model','d.os','dv.description','dv.vlan'], {'dv.vlan' => \"$vlan"} ); } elsif ($specific =~ /vlan/i){ $vlan =~ s/[^\d]//g; # untaint the vlan passed to us $dns =~ s/[^-.A-Za-z0-9]//g; # untaint the dns passed to us $title = (($ip && $dns) ? "Ports on Device $dns" : "Device Ports") ." Carrying VLAN $vlan"; $where->{description}++; $where->{speed}++; $matches = sql_rows( 'device_port_vlan v, device_vlan dv, device d, device_port p', ['d.ip','d.dns','v.port','p.name','p.speed','dv.description'], {'d.ip' => \'v.ip', 'v.ip' => \'p.ip', (($ip && $dns) ? ('p.ip' => $ip) : ()), 'v.port' => \'p.port', 'v.vlan' => \"$vlan", 'dv.ip' => \'p.ip', 'dv.vlan' => \"$vlan"} ); } elsif ($specific =~ /blocking/i){ $title = 'Device ports that are blocking'; $where->{port}++; $where->{stp}++; $matches = sql_rows('device_port p, device d', ['d.ip','d.dns','p.port','p.up_admin','stp'], {'d.ip' => \'p.ip', 'stp' => 'broken','up' => 'up'} ); my $matches_blocking = sql_rows('device_port p, device d', ['d.ip','d.dns','p.port','p.up_admin','stp'], {'d.ip' => \'p.ip', 'stp' => [['blocking','broken']], 'up' => '!down'} ); push (@$matches,@$matches_blocking); } elsif ($specific =~ /broken/i){ $title = 'Broken topology links'; # select d.ip,d.dns,p.port,p.remote_ip,p.remote_port from device_port p , device d # where # d.ip = p.ip # and not exists # (select 1 from device_port q where q.ip = p.remote_ip and q.port = p.remote_port) # and not exists # (select 1 from device_ip a, device_port q where a.alias = p.remote_ip and q.ip = a.ip and q.port = p.remote_port) # and p.remote_ip is not null and p.remote_port is not null # order by p.ip; $matches = sql_rows('device_port p, device d', ['d.ip','d.dns','p.ip','p.port','p.remote_ip','p.remote_port','p.remote_type','p.remote_id'], {'d.ip' => \'p.ip', 'not exists' => \\'(select 1 from device_port q where q.ip = p.remote_ip and q.port = p.remote_port)', 'not exists ' => \\'(select 1 from device_ip a, device_port q where a.alias = p.remote_ip and q.ip = a.ip and q.port = p.remote_port)', 'p.remote_ip' => \\'is not null', 'p.remote_port' => \\'is not null', 'p.remote_type' => \\ ' NOT ILIKE \'%ip_phone%\' OR p.remote_type IS NULL', } ); $where->{port}++; $where->{remote_port}++; $where->{remote_ip}++; $where->{remote_id}++; $where->{remote_type}++; } elsif ($specific =~ /ip.phone/i){ $title = 'IP Phones discovered through SNMP'; $matches = sql_rows('device_port p, device d', ['d.ip','d.dns','p.ip','p.port','p.remote_ip','p.remote_port','p.remote_type','p.remote_id'], {'d.ip' => \'p.ip', 'not exists' => \\'(select 1 from device_port q where q.ip = p.remote_ip and q.port = p.remote_port)', 'not exists ' => \\'(select 1 from device_ip a, device_port q where a.alias = p.remote_ip and q.ip = a.ip and q.port = p.remote_port)', 'p.remote_ip' => \\'is not null', 'p.remote_port' => \\'is not null', 'p.remote_type' => \\ ' ILIKE \'%ip_phone%\'', } ); $where->{port}++; $where->{remote_port}++; $where->{remote_ip}++; $where->{remote_id}++; $where->{remote_type}++; } elsif ($specific =~ /SSID/) { $specific = "SSID"; $title = 'SSID Port Search - ' . $ssid; my %xtrawhere = (); if (!defined($ssidbcast) || ($ssidbcast cmp '') == 0) { $where->{broadcast}++; } else { $title .= " " . ($ssidbcast ? 'Broadcast' : 'Stealth'); $xtrawhere{'broadcast'} = $ssidbcast; } $matches = sql_rows('device_port_ssid p, device d', ['d.ip','d.dns','d.vendor','d.model','d.location','d.contact','p.port','p.broadcast','p.ssid'], {'d.ip' => \'p.ip', 'p.ssid' => sql_match($ssid,$exact), %xtrawhere } ); if ($where->{broadcast}) { foreach my $row (@$matches) { $row->{broadcast} = $row->{broadcast} ? 'Broadcast' : 'Stealth'; } } $where->{vendor}++; $where->{model}++; $where->{location}++; $where->{contact}++; $where->{ssid}++; } my @show_cols = (); # Search on all text fields if (length($text)) { my $match = sql_match($text,0); push(@{$where->{'d.ip/i.alias'}}, $match); push(@{$where->{'d.dns/i.dns'}}, $match); push(@{$where->{'d.name'}}, $match); push(@{$where->{'d.description'}}, $match); push(@{$where->{'d.location'}}, $match); push(@{$where->{'d.serial'}}, $match); push(@{$where->{'d.contact'}}, $match); $boolean = 'or'; } # Generalized Search Terms # Search on Model foreach my $model (@models){ next unless defined $model and length($model); if ($model eq 'show'){ push (@show_cols,'d.model'); next; } push (@{$where->{'d.model'}},$model); } # Search on OS foreach my $this_os (@os){ next unless defined $this_os and $this_os; if ($this_os eq 'show'){ push (@show_cols,'d.os'); next; } push (@{$where->{'d.os'}},$this_os); } # Search on OS Version foreach my $this_ver (@os_ver){ next unless defined $this_ver and $this_ver; if ($this_ver eq 'show'){ push (@show_cols,'d.os_ver'); next; } push (@{$where->{'d.os_ver'}},$this_ver); } # Search on Layers my @layer_search = ('_','_','_','_','_','_','_'); # @layer_search is computer indexed, left->right foreach my $layer (@layers){ next unless defined $layer and length($layer); next unless ($layer > 0 and $layer < 8); $layer_search[$layer-1] = 1; } # the database field is in order 87654321 my $layer_string = join('', reverse @layer_search); if ($layer_string =~ /1/){ $layer_string = '%'.$layer_string; push (@{$where->{'d.layers'}},$layer_string); } # Search on Vendor foreach my $vendor (@vendors){ next unless defined $vendor and length($vendor); if ($vendor eq 'show'){ push (@show_cols,'d.vendor'); next; } push (@{$where->{'d.vendor'}},$vendor); } # Search on Location if (length($loc)){ my $match = sql_match($loc,$exact); push(@{$where->{'d.location'}}, $match); } # Search on Description if (length($desc)){ my $match = sql_match($desc,$exact); push(@{$where->{'d.description'}}, $match); } # Search on DNS entry if (!$specific and length($dns)){ my $match = sql_match($dns,$exact); push(@{$where->{'d.dns/i.dns'}}, $match); } # Search on sysName if (length($name)){ my $match = sql_match($name,$exact); push(@{$where->{'d.name'}}, $match); } # Search on IP if (!$specific and length($ip)){ my $match = sql_match($ip,$exact); push(@{$where->{'d.ip/i.alias'}}, $match); push(@show_cols,'d.name'); } # Run Query if (scalar keys %$where and ! defined $specific and ! length($specific)){ #$matches = sql_rows('device',['*'],$where,$boolean eq 'or'); my @cols = (); foreach my $wcol (keys %$where){ foreach my $col (split('/',$wcol)) { # Skip the ones we're already selecting below next if ($col =~ /^i\./); next if ($col =~ /^d\.ip$/); next if ($col =~ /^d\.dns/); push(@cols,$col); } } # Add Columns to Grab info but not part of Search Terms foreach my $show (@show_cols){ next if (grep /^$show$/,@cols); push @cols,$show; } $matches = sql_rows('device d left join device_ip i on d.ip = i.ip', ['distinct(d.ip)','d.dns',@cols], $where, $boolean eq 'or'); # Change col1/col2 to just col1 for display (implicit priority to col1) foreach my $wcol (keys %$where){ if ($wcol =~ /^(.*)\/(.*)/){ delete $where->{$wcol}; push(@{$where->{$1}},undef); } } # Add Info Columns to columns to display foreach my $w (@show_cols){ next if (grep /^$w$/,keys(%$where)); push(@{$where->{$w}},undef); } } my $time2 = time; %# %# matches() - Display search results stored in @$matches. %# <%method matches > %return unless defined $matches;

<%$title%>

% unless (scalar @$matches) { No Results Found. % return; % } % foreach my $col (sort keys %$where){ % my $colview = $col; % $colview =~ s/^[^.]+\.//; % $colview = $colview eq 'count' ? 'Number of Active
Nodes Connected' : $colview; % } <%perl> my $count=0; my @sort_matches = exists $where->{count} ? sort {$b->{count} <=> $a->{count} } @$matches : sort {$a->{dns} cmp $b->{dns}} @$matches; foreach my $result (@sort_matches){ my $ip = $result->{ip}; my $dns = $result->{dns}; my $port = $result->{port}; my $vlan = $result->{vlan}; $dns = defined $dns ? $dns : $ip; $dns =~ s/\Q$netdisco::CONFIG{domain}\E//; $dns .= " ($port)" if defined $port; % if (defined $vlan) { # set up by specific=vlan_dev % } % else { # general case % } <%perl> foreach my $col (sort keys %$where){ # Trim off table name for column $col =~ s/^[^.]+\.//; my $val = $result->{$col}; # Column Specfic stuff if ($col eq 'remote_ip') { $val = "$val"; } % } % $count++; }
Device<%ucfirst($colview)%>
<% $dns %><% $dns %> <%$val%>
<% $count %> matches. %# %# title() %# <%method title> - Device Search\ %# $Id: device_search.html,v 1.35 2009/09/08 21:19:51 olly_g Exp $ %# vim:syntax=mason