<h3>Device Inventory</h3>
[<A HREF="#age">By Age</a>]
[<A HREF="#model">By Model</a>]
[<A HREF="#os">By OS</a>]
[<A HREF="#location">By Location</a>]
[<A HREF="#ssid">Wireless SSID</a>]
<h4><A NAME="age">By Age</A></h4>
% $m->comp('SELF:show_age') if $cmd eq 'age';
<TABLE BORDER=0>
<TR>
    <TD>Find Devices </TD>
    <TD>
    <FORM ACTION="<%$r->uri%>" METHOD=GET>
    <INPUT TYPE="hidden" NAME="cmd" VALUE="age">
    <SELECT NAME="age_type">
        <OPTION VALUE="first"<%$age_type eq 'first' ? ' SELECTED' : ''%>>First Discovered
        <OPTION VALUE="last"<%$age_type eq 'last' ? ' SELECTED' : ''%>>Last Updated
    </SELECT>
    <SELECT NAME="age_bool">
% for ('in the last','not in'){
        <OPTION VALUE="<%$_ | h %>"<%$age_bool eq $_ ? ' SELECTED' : ''%>><%$_%>
% }
    </SELECT>
    <SELECT NAME="age_val">
% for (1..12) {
        <OPTION VALUE="<%$_%>"<%$age_val eq $_ ? ' SELECTED' : ''%>><%$_%>
% }
    </SELECT>
    <SELECT NAME="age_mul">
% for (qw/days months years/) {
        <OPTION VALUE="<%$_%>"<%$age_mul eq $_ ? ' SELECTED' : ''%>><%$_%>
% }
    </SELECT>
    <INPUT TYPE="submit" VALUE="Search">
    </FORM>
    </TD>
</TR>
<TR>
    <TD>Find Devices </TD>
    <TD>
    <FORM ACTION="<%$r->uri%>" METHOD=GET>
    <INPUT TYPE="hidden" NAME="cmd" VALUE="age">
    <INPUT TYPE="hidden" NAME="age_type" VALUE="uptime">
    That have been up for at least
    <SELECT NAME="age_val">
% for (1..12) {
        <OPTION VALUE="<%$_%>"<%$age_val eq $_ ? ' SELECTED' : ''%>><%$_%>
% }
    </SELECT>
    <SELECT NAME="age_mul">
% for (qw/days months years/) {
        <OPTION VALUE="<%$_%>"<%$age_mul eq $_ ? ' SELECTED' : ''%>><%$_%>
% }
    </SELECT>
    <INPUT TYPE="submit" VALUE="Search">
    </FORM>
    </TD>
</TR>
</TABLE>
<P>
<h4><A NAME="model">By Model</A></h4>
<TABLE BORDER=0 CELLSPACING=0 CLASS="box" WIDTH="100%">
<TR>
    <TH>Vendor</TH>
    <TH>Model</TH>
    <TH>Count</TH>
</TR>
<%perl>
$count = 0; $odd = 1;
foreach my $row (sort 
    {
        # Sort by vendor then model
        return $a->{vendor} cmp $b->{vendor} if $a->{vendor} ne $b->{vendor};
        return lc($a->{model}) cmp lc($b->{model});
    } @$model_count
                ) {

        # Change class if we change vendor
        if ($old_vendor ne $row->{vendor}){
            $odd++;
            $old_vendor = $row->{vendor};
        }
        $count += $row->{count};
</%perl>
<TR CLASS="match-<%$odd%2%>">
    <TD><A HREF="device_search.html?vendors=<%$row->{vendor}|u%>&models=show&os=show&os_ver=show"><%$row->{vendor}%></A></TD>
    <TD><A HREF="device_search.html?vendors=<%$row->{vendor}|u%>&models=<%$row->{model}|u%>&boolean=and&os=show&os_ver=show"><%$row->{model}%></A></TD>
    <TD><%$row->{count}%></TD>
</TR>
%                  }
<TR CLASS="match-<%++$odd%2%>">
    <TD>&nbsp;</TD>
    <TD>Total:</TD>
    <TD><%$count%></TD>
</TR>
</TABLE>
<h4><A NAME="os">By OS</A></H4>
<TABLE BORDER=0 CELLSPACING=0 CLASS="box" WIDTH="100%">
<TR>
    <TH>OS</TH>
    <TH>Version</TH>
    <TH>Count</TH>
</TR>
<%perl>
$count = 0;
foreach my $row (sort 
    {
        # Sort by OS then Version
        return $a->{os} cmp $b->{os} if $a->{os} ne $b->{os};
        return $a->{os_ver} cmp $b->{os_ver};
    } @$os_count ) {


    if ($old_vendor ne $row->{os}) { 
        $odd++;
        $old_vendor = $row->{os};
    }
    $count += $row->{count};
</%perl>
<TR CLASS="match-<%$odd%2%>">
    <TD><A HREF="device_search.html?os=<%$row->{os}||'IS NULL'%>&models=show&vendors=show"><% $row->{os} || 'Unknown' %></A></TD>
    <TD><A HREF="device_search.html?os=<%$row->{os}||'IS NULL'%>&os_ver=<%$row->{os_ver}||'IS NULL'%>&boolean=and&models=show&vendors=show"><% $row->{os_ver} || 'Unknown' %></a></TD>
    <TD><% $row->{count} %></TD>
</TR>
% }
<TR CLASS="match-<%++$odd%2%>">
    <TD>&nbsp;</TD>
    <TD>Total:</TD>
    <TD><%$count%></TD>
</TR>
</TABLE>
<h4><A NAME="location">By Location</A></h4>
% if ($show_loc) {
<& SELF:show_loc &>
% } else {
<FORM ACTION="<%$r->uri%>" METHOD="GET">
<INPUT TYPE="hidden" NAME="show_loc" VALUE=1>
<INPUT TYPE="SUBMIT" VALUE="Inventory by Location">
</FORM>
% }
<h4><A NAME="ssid">Wireless SSID Inventory</A></h4>
% if ($show_ssid) {
<& SELF:show_ssid &>
% } else {
<FORM ACTION="<%$r->uri%>" METHOD="GET">
<INPUT TYPE="hidden" NAME="show_ssid" VALUE=1>
<INPUT TYPE="SUBMIT" VALUE="Wireless SSID Inventory">
</FORM>
% }
<%args>
$cmd      => undef
$age_type => 'last'
$age_val  => 2
$age_mul  => 'months'
$age_bool => 'not in'
$show_loc => 0
$show_ssid => 0
</%args>
<%shared>
my $age = [];
my $domain = $netdisco::CONFIG{domain};
my $odd = 1;
my ($arg_cmd,$arg_age_type,$count,$arg_show_loc);
</%shared>
<%init>
$arg_cmd = $cmd; $arg_age_type = $age_type; $arg_show_loc = $show_loc;
my $old_vendor = '';
my $model_count = sql_rows('device',['vendor','model','count(*)'],
                           undef,undef,'GROUP by model,vendor');

my $os_count = sql_rows('device',['os','os_ver','count(*)'],
                        undef,undef,'GROUP by os,os_ver');

if ($cmd eq 'age'){
    # prevent stuffing
    $age_val = 3 unless $age_val =~ m/^\d+$/;
    $age_mul =~ s/[^a-z ]+//g;

    my $bool = '>=';
       $bool = '<=' if $age_bool =~ /last/;

    # Default - Last Discovered
    my $col = 'age(now(),last_discover)';
    my $val = "$bool interval '$age_val $age_mul'";
    
    # First Created
    if ($age_type eq 'first'){
        $col = 'age(now(),creation)';
        $val = "$bool interval '$age_val $age_mul'";
    } 
    # Uptime
    elsif ($age_type eq 'uptime') {
        $col = 'uptime';
        my $interval = 0;
        my $mult = 60*60*24;    # 1 day in seconds
        $mult    = $mult * 30  if ($age_mul eq 'months');
        $mult    = $mult * 365 if ($age_mul eq 'years'); 
        $mult    *= 100;    # uptime is in 100ths of seconds
        $interval = $age_val * $mult;
        $val = "$bool $interval"
    }

    $age = sql_rows('device',['ip','dns','name','vendor','model','uptime',
                              'age(now(),creation) as creation_age',
                              'age(now(),last_discover) as last_discover_age'
                             ],
                    {$col => \\$val}
                   );
}
</%init>
%#
%#  show_age() -  Process by age requests
%#
<%method show_age>
<H4>Results</H4>
% unless (scalar @$age) {
No Results Found.
% return; }
<TABLE BORDER=0 CLASS="box" CELLSPACING=0 WIDTH="100%">
<TR>
    <TH>Device</TH>
    <TH>IP</TH>
    <TH>Vendor - Model</TH>
    <TH><%$arg_age_type eq 'uptime' ? 'Uptime' : 'First<BR>Discovered'%></TH>
    <TH>Last<BR>Updated</TH>
</TR>
<%perl>
my @results;
if ($arg_age_type eq 'uptime') {
    @results = sort { $b->{uptime} <=> $a->{uptime} } @$age;
} else {
    @results = sort {
        return $a->{dns} cmp $b->{dns} unless (! defined($a->{dns}) or ! defined $b->{dns});
        return $a->{ip} cmp $b->{ip};
    } @$age;
}

foreach my $dev (@results) {
    my $dns = $dev->{dns} || 'No DNS';
    $dns =~ s/\Q$domain\E//;
    my $creation_age = $m->comp("SELF:.trim_age", age => $dev->{creation_age});
    my $last_discover_age = $m->comp("SELF:.trim_age", age => $dev->{last_discover_age});
    my $uptime = $m->comp('device.html:uptime',uptime => $dev->{uptime});
</%perl>
<TR CLASS="match-<%++$odd %2%>">
    <TD><A HREF="device.html?ip=<%$dev->{ip}|u%>"><%$dns%></A></TD>
    <TD><A HREF="device.html?ip=<%$dev->{ip}|u%>"><%$dev->{ip}%></A></TD>
    <TD><A HREF="device_search.html?vendors=<%$dev->{vendor}|u%>&models=<%$dev->{model}|u%>&boolean=and"><%$dev->{vendor}%> - <%$dev->{model}%></A></TD>
    <TD><%$arg_age_type eq 'uptime' ? $uptime : $creation_age%></TD>
    <TD><%$last_discover_age%></TD>
</TR>
% }
</TABLE>

Found <%scalar @$age%> devices.
</%method>
%#
%# .trim_age(age=>string) - Trim postgres ugly age string
%#
<%method .trim_age>
    <%args>
        $age => ''
    </%args>
    <%perl>
        $age =~ s/mon/month/;
        if ($age =~ /(day|month|year)/){
            $age =~ s/(-)?\d{2}:\d{2}.*$//;
        } else {
            $age =~ s/(\d{2}:\d{2}):\d{2}(\.\d*)$/$1/;
        }
        return $age;
    </%perl>
</%method>
%#
%# show_ssid() - Inventory based on SSID
%#
<%method show_ssid>
<TABLE BORDER=0 CELLSPACING=0 CLASS="box" WIDTH="100%">
<TR>
    <TH>SSID</TH>
    <TH>Broadcast?</TH>
    <TH>Count</TH>
</TR>
<%perl>
my $ssid_count = sql_rows('device_port_ssid',['ssid','broadcast','count(ssid)'],
                        undef,undef,'group by ssid,broadcast');
$count = 0; $odd = 1; my $old_loc = '';
foreach my $row (sort { $b->{count} <=> $a->{count} } @$ssid_count) {
</%perl>
<TR CLASS="match-<%$odd%2%>">
    <TD><A HREF="device_search.html?ssid=<%$row->{ssid}|u%>&amp;specific=SSID" TITLE="All devices with this SSID"><%$row->{ssid}|h%></A></TD>
    <TD><%$row->{broadcast} ? "Broadcast" : "Stealth"%></TD>
    <TD><%$row->{count}%></TD>
</TR>
<%perl>
}
</%perl>
</TABLE>
[<A HREF="<%$r->uri%>">Hide</A>]
</%method>
%#
%# show_loc() - Sorts and shows by location
%#
<%method show_loc>
<TABLE BORDER=0 CELLSPACING=0 CLASS="box" WIDTH="100%">
<TR>
    <TH>Location</TH>
    <TH>IP Address</TH>
    <TH>sysName</TH>
    <TH>Vendor</TH>
    <TH>Model</TH>
</TR>
<%perl>
my $location_count = sql_rows('device',['location','dns','name','ip','vendor','model'],
                        undef,undef,undef);
$count = 0; $odd = 1; my $old_loc = '';
foreach my $row (sort 
    {
        # Sort by Location => Name => IP Address => Vendor => Model
        return $a->{location} cmp $b->{location} if $a->{location} ne $b->{location};
        return $a->{name}     cmp $b->{name}     if $a->{name}     ne $b->{name};
        return $a->{ip}       cmp $b->{ip}       if $a->{ip}       ne $b->{ip};
        return $a->{vendor}   cmp $b->{vendor}   if $a->{vendor}   ne $b->{vendor};
        return $a->{model}    cmp $b->{model}    if $a->{model}    ne $b->{model};
    } @$location_count
                ) {

        $count += $row->{count};
        $odd++ if $row->{location} ne $old_loc;
        $old_loc = $row->{location};
        my $dns = $row->{dns} || $row->{ip};
        $dns =~ s/\Q$domain\E//;
</%perl>
<TR CLASS="match-<%$odd%2%>">
    <TD><A><%$row->{location} || '[Not Set]'%></A></TD>
    <TD><A HREF=device.html?ip=<%$row->{ip}%> TITLE="Click to View Device"><%$dns|h%></A></TD>
    <TD><A HREF=device.html?ip=<%$row->{ip}%> TITLE="Click to View Device"><%$row->{name}|h%></A></TD>
    <TD><A HREF="device_search.html?vendors=<%$row->{vendor}|u%>" TITLE="All devices from this vendor"><%$row->{vendor}|h%></A></TD>
    <TD><A HREF="device_search.html?vendors=<%$row->{vendor}|u%>&models=<%$row->{model}|u%>&boolean=and" TITLE="All devices of this model"><%$row->{model}|h%></A></TD>
</TR>
%                  }
<TR CLASS="match-<%++$odd%2%>">
    <TD COLSPAN=3>&nbsp;</TD>
    <TD>Total:</TD>
    <TD><%scalar(@$location_count)%></TD>
</TR>
</TABLE>
[<A HREF="<%$r->uri%>">Hide</A>]
</%method>
%#
%# title()
%#
<%method title>
- Device Inventory \
</%method>
%# $Id: device_inv.html,v 1.16 2006/06/28 21:32:07 maxbaker Exp $
%# vim:syntax=mason
