<& SELF:report &>
<%args>
$cmd => '';
$timeunit => 'months'
$timecount => 3
$sort => 'Free ports'
%args>
<%doc>
%doc>
<%shared>
my $matches = undef;
my $argsort = undef;
my $arg_timecount = undef;
my $arg_timeunit = undef;
my $valid_time_units = ['days', 'weeks', 'months', 'years'];
%shared>
<%init>
$argsort = $sort;
$arg_timecount = $timecount;
$arg_timeunit = $timeunit;
# Search for ports
if ($cmd eq 'search'){
# Make sure that only valid time units are passed, reset to "months" if the check fails
unless (scalar grep(/^$arg_timeunit$/, @$valid_time_units)) {
$arg_timeunit = 'months';
$timeunit = 'months';
}
# Make sure that a positive integer is passed as count, reset to "3" if the check fails
unless ($arg_timecount =~ m/^\d+$/) {
$arg_timecount = 3;
$timecount = 3;
}
# Get ports from device_port table
my $port_age = 'age(to_timestamp(extract(epoch from last_discover)-(uptime-lastchange)/100))';
my $tables = 'device_port dp join device d on d.ip = dp.ip';
my $cols = [
'd.dns',
'd.name',
'd.ip',
'd.location',
'sum(case when up=\'up\' then 1 else 0 end) as active',
"sum(case when (up<>\'up\' and $port_age <= interval \'$arg_timecount $arg_timeunit\') then 1 else 0 end) as recent",
"sum(case when (up<>\'up\' and $port_age > interval \'$arg_timecount $arg_timeunit\') then 1 else 0 end) as free",
];
my $group = 'group by d.dns, d.name, d.ip, d.location';
my $where = {};
#$netdisco::SQLCARP=1;
$matches = sql_rows($tables,$cols,$where,0,$group) || [];
#$netdisco::SQLCARP=0;
}
%init>
<%method report>
<%perl>
return unless defined $matches;
# Sort by routines
if ($argsort eq 'Device') {
@$matches = sort {
# Sort by DNS (or by device name if DNS is null)
my $deva = $a->{dns} || $a->{name};
my $devb = $b->{dns} || $b->{name};
return $deva cmp $devb;
} @$matches;
} elsif ($argsort eq 'Active ports') {
@$matches = sort {
# Sort by active count (descending), then by DNS or name
my $match1 = ($b->{active} <=> $a->{active});
if ($match1) {
return $match1;
} else {
my $deva = $a->{dns} || $a->{name};
my $devb = $b->{dns} || $b->{name};
return $deva cmp $devb;
}
} @$matches;
} elsif ($argsort eq 'Recently used ports') {
@$matches = sort {
# Sort by recent count (descending), then by DNS or name
my $match1 = ($b->{recent} <=> $a->{recent});
if ($match1) {
return $match1;
} else {
my $deva = $a->{dns} || $a->{name};
my $devb = $b->{dns} || $b->{name};
return $deva cmp $devb;
}
} @$matches;
} elsif ($argsort eq 'Free ports') {
@$matches = sort {
# Sort by free count (descending), then by DNS or name
my $match1 = ($b->{free} <=> $a->{free});
if ($match1) {
return $match1;
} else {
my $deva = $a->{dns} || $a->{name};
my $devb = $b->{dns} || $b->{name};
return $deva cmp $devb;
}
} @$matches;
}
%perl>