Specific Searches
Hints
- Matches on DNS or IP may be matching aliases inside those devices.
Check inside the device for the matching entries.
- If you want to find blank entries, search on "is null" or conversely "is not null"
- You can select multiple for Model,Vendor,Layer, and OS by holding down CTRL as you click.
<%args>
@models => ()
@layers => ()
@vendors => ()
@os => ()
@os_ver => ()
$ip => ''
$text => ''
$dns => ''
$name => ''
$desc => ''
$boolean => 'and'
$exact => 0
$loc => ''
$specific => undef
$ssid => undef
$ssidbcast => undef
%args>
<%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
%shared>
<%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;
# Specific Searches
# Clear non-specific terms
if (defined $specific and length($specific)){
@models = @layers = @vendors = undef;
$ip = $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 =~ /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', 'up' => 'up'}
);
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 ($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'],
{'d.ip' => \'p.ip',
'p.ssid' => $ssid,
%xtrawhere
}
);
if ($where->{broadcast}) {
foreach my $row (@$matches) {
$row->{broadcast} = $row->{broadcast} ? 'Broadcast' : 'Stealth';
}
}
$where->{vendor}++;
$where->{model}++;
$where->{location}++;
$where->{contact}++;
}
my @show_cols = ();
# Search on all text fields
if (length($text)) {
my $match = sql_match($text,0);
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 (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 (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;
%init>
%#
%# matches() - Display search results stored in @$matches.
%#
<%method matches >
%return unless defined $matches;
<%$title%>
% unless (scalar @$matches) {
No Results Found.
% return;
% }
Device |
% foreach my $col (sort keys %$where){
% my $colview = $col;
% $colview =~ s/^[^.]+\.//;
% $colview = $colview eq 'count' ? 'Number of Active
Nodes Connected' : $colview;
<%$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};
$dns = defined $dns ? $dns : $ip;
$dns =~ s/\Q$netdisco::CONFIG{domain}\E//;
$dns .= " ($port)" if defined $port;
$count++;
%perl>
<% $dns %> |
<%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";
}
%perl>
<%$val%> |
% }
%}
<% $count %> matches.
%method>
%#
%# title()
%#
<%method title>
- Device Search\
%method>
%# $Id: device_search.html,v 1.20 2006/04/04 22:55:52 fenner Exp $
%# vim:syntax=mason