#!/usr/bin/perl -w use strict; use CGI qw(:standard); use FileHandle ; use File::Basename ; use HTML::Table ('1.06') ; # requires autoGrow, in 1.06 not 1.05 use lib '/usr/local/myNMS/bin' ; use My_Utils ('0.2'); use My_DButils ; use My_Config ; my $VERSION = '0.72.4' ; =pod Copyright (c) 2000-2001 John Stumbles and the University of Reading. All rights reserved. This program is free software; you can redistribute it and/or modify it under the same terms as Perl itself. C H A N G E H I S T O R Y ---------------------------- 0.72.4 02Mar01 big: cli-mode Query=HOSTS and Query=Users not running: validate_user was just regenerating web index pages because it was in cli mode. fix: hacked validate_user to return true in cli mode (it still regenerates indices as well as generating reports, but can live with that) 0.72.3 28Feb01 in Query_Hosts don't change MY_HREF to .. 0.72.2 21Feb01 cosmetic: fixed call to HTMLtop{} instead of () in device connections 0.72.1 20Feb01 removed $CONFIG{DIR}{BASE} from myNMS subdir specs 0.72 17Feb01 name of ACCESS varaibles changed 0.71 16Feb01 minor bug: wasn't passing $GROUP to print_index in CGI no-params mode 0.7 16Feb01 script now works in cli mode with no parameters given to generate index pages according to parameters supplied in config file - Frames/no-frames SEEMS to be working - removed sub IPadd_by_name - superseded by IPadd_by_IPname in DB_utils - $Explain_Query dropped 0.65 16Feb01 'search' form now works for IP hostname 0.64.2 14Feb01 use new HTML::Table 1.06 with init-from-hash and default auto-grow 0.64.1 12Feb01 fixed colouring 'pastel_blue' in dot1d table 0.64 12Feb01 improved: linking of devices before interconnection display colouring of device ages 0.63 09Feb01 tree-like display of device interconnections 0.62 09Feb01 sub connected_device calls new sub device_interconnections to draw connections in each direction between given devices. 0.61 08Feb01 using modified version of Table module with autoGrow split off some connected devices code into sub, to try to move to recursive device-device-device-... etc 0.6 08Feb01 Using HTML::Table for generating tables of connected devices. IPsort in My_Utils 0.2 is now a function taking a list & returning sorted list of IPadds - calls changed from 'sort IPsort {list}' to 'IPsort {list}' 0.51 07Feb01 better data structures in connections report, better report 0.50 06Feb01 validate_user reads access group info from config took out all special cases based on IP address etc connections data improved: gets connections by MAC of IP of ipAddrTable as well as by PhysAddr of ifTable fixed error in html_table_row 0.41 02Feb01 better presentation of connections 0.4 01Feb01 rough SNMP_devices connections table implemented html_table_cell contents default to $EMPTY_CELL 0.3 30Jan01 use Modules =cut my $DB = 'NMS'; my $dbh = DB_init($DB) ; my $INFO_t0=time(); my $INFO_t1 = $INFO_t0 ; sub TINFO { my $T=time(); printf"[done in %5u, total %5u] %s\n", ($T-$INFO_t1), ($T-$INFO_t0), join ' ', @_; $INFO_t1=time();}; my ($BASENAME, $PROGPATH, $EXT) = fileparse($0, '\.pl') ; my $PROGNAME = $BASENAME; $EXT and $PROGNAME .= ".$EXT" ; my $CGI = $ENV{HOME} ? 0 : 1 ; # detect whether we're running as CLI rather than CGI sub WARNING { if ($CGI) { printf "

WARNING %s

\n", join ' ', @_ ; } else { printf STDERR "\nWARNING: %s\nin $PROGNAME\n", join ' ', @_ ;}} ; my $MY_HREF="Query.cgi"; # global: gets modified for Hosts_Query which writes files to Hosts/ subdir my $MAX_HTML_TABLE_ROWS = 300 ; # no more than 300 rows in a table, otherwise use plain text list my $MAX_USERS_DETAIL_REPORT = 10 ; # do detailed report for no more than these users at a time my $EMPTY_CELL = ' ' ; sub html_table_header { '<'. join (' ', ('table', @_)) . ">\n" } ; sub html_table_row { my $buff=''; foreach (@_){$buff.=''.($_ or $EMPTY_CELL)."\n";} $buff."\n\n";} sub html_table_header_row { return sprintf "%s\n", join ('', @_) ; } sub html_table_footer { return "\n\n" ; } sub html_table_cell { my $a=(shift or $EMPTY_CELL); '<' . join(' ',('td',@_)). ">$a" ; } my %HTML_COLOUR = ( white => '#FFFFFF', light_grey => '#DDDDDD', mid_grey => '#BBBBBB', dark_grey => '#888888', black => '#000000', red => '#FF0000', green => '#00FF00', blue => '#0000FF', pastel_blue => '#99CCFF', bright_blue => '#00CCFF', blue_grey => '#9999FF', mustard => '#FFCC00', # or shit colour red_grey => '#FF9999', yellow => '#FFFF80', ) ; my %AGE_COLOUR = ( hour => $HTML_COLOUR{white}, day => '#DDDDDD', week => '#CCCCCC', month => '#BBBBBB', quarter => '#AAAAAA', ancient => '#888888', ) ; my $t_now = NOW() ; my $t_yesterday = $t_now - DAY() ; my $t_week = $t_now - WEEK() ; my $t_cutoff = $t_now - QUARTER() ; # 1/4 year ago my %ACCESS_GROUPS ; # table of groups, used to determine access to data. Set by validate_user() ; my $GROUP = '' ; exit unless ($GROUP = validate_user()) ; # check who's calling and chuck them off if necessary my $OUTDIR ; # output directory for batch-mode report generation my %SUBNET ; # local copy of hash # my $FRAME_BASE=''; my $TARGET ; my $FRAME_LINK = qq(frames\n); if ($ENV{HTTP_REFERER} and $ENV{HTTP_REFERER} =~ /$CONFIG{PAGE}{FRAME_QUERY}$/) # in frames mode? { $TARGET = $CONFIG{PAGE}{FRAME_TARGET} ; # frame version $FRAME_LINK = qq(no frames\n); } if (param()) { $FRAME_LINK = '' ; # no frames/no-frames links if we're answering a query if (param('--OUT')) # output directory spec { unless ($CGI) # non-CGI mode only { $OUTDIR = param('--OUT') ; } } if (param('VENDOR')) # called with this param by Vendor query to update table { exit unless Vendor_Query() ; } if (param('Query')) # most Queries have param('Query') set to type of Query { if (param('Query') eq 'SNMP') { exit unless Query_SNMP() ; } if (param('Query') eq 'IPadd') { exit unless Query_IPadd(param('search')) ; } elsif (param('Query') =~ /^VENDOR$/i) # if Vendor codes { exit unless Vendor_Query() ; } elsif (param('Query') eq 'User') # if User search { exit unless Query_User(param('search')) ; } elsif (param('Query') eq 'HOSTS') # if Hosts report { exit unless Hosts_Query() ; } } elsif (param('search')) # query from 'search' box has no param('Query') but only this param { if (param('search') =~ /^\d+\.\d+\.\d+\.\d+/) # looks like IP address? { exit unless Query_IPadd(param('search')) ; } # may be IP hostname or user if (my $IPadd = IPadd_by_IPname(param('search'))) { exit unless Query_IPadd($IPadd) ; } # (else) try username exit unless Query_User(param('search')) ; } } # if param() # (else) print HTMLtop($GROUP) . print_index($GROUP) ; ############################################################################## sub HTMLtop { my $param = shift; my ($Title, $BGCOLOR, $target, $frame_link) =('', '#FFFFFF', $TARGET, $FRAME_LINK) ; if (ref ($param)) { $Title = ($param->{-title} or ''); $BGCOLOR = $param->{-BGCOLOR} if $param->{-BGCOLOR}; $target = $param->{-TARGET} if $param->{-TARGET} ; $frame_link = $param->{-FRAME_LINK} if $param->{-FRAME_LINK} ; } else { $Title = $param ; } my $BUFFER ; $CGI and $BUFFER = header ; $BUFFER .= start_html( -title => "myNMS: $Title", # -target => $target, -BGCOLOR => $BGCOLOR, ) ; # work around CGI module's insistence on putting an HREF in the BASE tag even if we only want TARGET # # myNMS: INDEX [public] (frames) # # # we want # myNMS: INDEX [public] (frames) # * our base tag in here * # if ($target) { $BUFFER =~ s||\n| ; # $BUFFER =~ s||\n| ; } $BUFFER .= "\n"; $BUFFER .= $frame_link ; $BUFFER .= '

'; $BUFFER .= "$PROGNAME ver $VERSION at " . nameDate() ; $BUFFER .= " user $GROUP" if $GROUP ; $BUFFER .= "

$Title

\n" ; return $BUFFER ; } ############################################################################## sub BGCOLOR # as time_colour but wraps HTML colour spec in 'BGCOLOR="#xxxxxx"' { my ($time, $now_colour) = @_ ; my $colour = time_colour ($time, $now_colour) ; return qq(BGCOLOR="$colour") ; } ############################################################################## sub time_colour # given time, return html colour from %AGE_COLOUR # given optional parameter COLOR make this the default colour for current time # given optional list of colours, make these the colours for DAY, WEEK, QUARTER, ANCIENT times { my ($time, $now_colour, $day_colour, $week_colour, $month_colour, $quarter_colour, $ancient_colour) = @_ ; return '' unless defined $time ; my %age_colour = ( hour => ($now_colour or $HTML_COLOUR{white}), day => ($day_colour or '#DDDDDD'), week => ($week_colour or '#CCCCCC'), month => ($month_colour or '#BBBBBB'), quarter => ($quarter_colour or '#AAAAAA'), ancient => ($ancient_colour or '#888888'), ) ; return $age_colour{time_age($time)} ; } ############################################################################## sub time_age # given time, return age suitable for lookup in %AGE_COLOUR { my $time = shift or return 'ancient' ; my $tdiff = $t_now - $time ; # difference $tdiff < $HOUR and return 'hour' ; # less than 1 hour $tdiff < $DAY and return 'day' ; # less than 1 day $tdiff < $WEEK and return 'week' ; # less than 1 week $tdiff < $MONTH and return 'month' ; # less than 1 month $tdiff < $QUARTER and return 'quarter' ; # less than 1 quarter return 'ancient' ; # older than that } ############################################################################## sub Query_User { return 1 unless scalar @_ ; my @user = @_ ; # print HTMLtop(sprintf "Query: User %s", join ', ',@_); print HTMLtop('Query: User ' . join (', ',@_)); print "\n
"; print "\n

Query: User

" ; printf "

Updated at %s

\n", nameDate() ; my %passwd; if (scalar @user) { my $user ; if ((scalar @user) ==1 ) # just one item { $user = $user[0] ; %passwd = qpasswd_by_WHERE("username LIKE '$user%'") ; # try match for username unless (scalar (keys %passwd)) # not found? { %passwd = qpasswd_by_WHERE("full_name LIKE '%$user%' OR email_address LIKE '%$user%'") ; # try match for name/email } } else # given list of users to match { my @QUERY = () ; foreach $user (@user) { push @QUERY, "username LIKE '$user%' OR full_name LIKE '%$user%' OR email_address LIKE '%$user%'" ; } %passwd = qpasswd_by_WHERE(@QUERY) ; } } else # empty query string - query all DB { my %passwd = qpasswd_by_WHERE() ; } my ($user, $active_users, $blocked_users, $grace_users, $delete_users, $overquota_users) = ('',0,0,0,0,0) ; foreach $user (keys %passwd) # sort by username { $passwd{$user}{quota} = quota_info($passwd{$user}); if ($passwd{$user}{pwd} =~ m|BLOCKED|i) # in practice 'blocked' entries in pwd file are a bit inconsistent { $blocked_users++; $passwd{$user}{status}='BLOCKED'; } elsif ($passwd{$user}{pwd} =~ /^InGracePeriod$/) { $grace_users++; $passwd{$user}{status}='InGracePeriod'; } elsif ($passwd{$user}{pwd} =~ /^AboutToDelete$/) { $delete_users++; $passwd{$user}{status}='AboutToDelete'; } else { $passwd{$user}{status}='active'; $active_users++; } if ($passwd{$user}{quota}{status} > 2) { $passwd{$user}{status}='OverQuota'; $overquota_users++; } } if ( scalar(keys (%passwd)) > $MAX_USERS_DETAIL_REPORT) # too many users for detailed report { my %STATUS_COLOUR = ( active => $HTML_COLOUR{white}, InGracePeriod => $HTML_COLOUR{light_grey}, AboutToDelete => $HTML_COLOUR{mustard}, BLOCKED => $HTML_COLOUR{red_grey}, OverQuota => $HTML_COLOUR{yellow}, ) ; print html_table_header(); print html_table_row (b('active'), $active_users); print html_table_row (b('in grace period'), $grace_users); print html_table_row (b('about to delete'), $delete_users); print html_table_row (b('blocked'), $blocked_users); print html_table_row (b('over quota'), $overquota_users); print html_table_row (b('total'), scalar(keys (%passwd))) ; print html_table_footer() ; print html_table_header() ; my $table_row_format = qq(
%s%s %-30.30s %-14.14s %-5.5s %-5.5s %-28s %-4s %-8s %s %s\n) ;  #   *** bodge! *** should end with 
but this adds a blank line to each table row printf $table_row_format, $HTML_COLOUR{black}, qq(), 'username', 'name', 'passwd', 'uid','gid','home','qota', 'shell','email', '(alias)'; foreach $user (sort keys %passwd) # sort by username { unless ($passwd{$user}{'uid'}) # found user? { printf $table_row_format , $HTML_COLOUR{red}, '', $user, i(b('*** not found ***')), '','','','','','','' ; next ; } # (else) printf $table_row_format , $STATUS_COLOUR{$passwd{$user}{status}} , '', # no HREF_Username_Query($user), (procrustean (($passwd{$user}{full_name} or ''), '-30', ' ', '*')) , $passwd{$user}{pwd}, $passwd{$user}{uid}, $passwd{$user}{gid}, procrustean($passwd{$user}{home}, -28, ' ', '*') , $passwd{$user}{quota}{compact}, procrustean($passwd{$user}{shell}, -8, ' ', '*') , HREF_mailto($passwd{$user}{email_address}), ($passwd{$user}{email_aliases} ? '('.$passwd{$user}{email_aliases}.')' : '') ; } print html_table_footer() , '
', end_html; return 0; } # (else) detailed report for few users foreach $user (sort keys %passwd) { print html_table_header('border') ; unless ($passwd{$user}{uid}) # found user? { print html_table_row b($user), i(b('*** not found in passwd table (tried in username and full name fields) ***')); next ; } print html_table_row b('username'), HREF_Username_Query($user) ; print html_table_row b('name'), ($passwd{$user}{full_name} or $EMPTY_CELL) ; print html_table_row b('password'), $passwd{$user}{pwd} ; print html_table_row b('pwd changed'), ($passwd{$user}{pwd_ctime} ? sprintf("%15.15s",nameDate($passwd{$user}{pwd_ctime})) : $EMPTY_CELL); print html_table_row b('uid'), $passwd{$user}{uid} ; print html_table_row b('gid'), $passwd{$user}{gid} ; print html_table_row b('home'), $passwd{$user}{home} ; print html_table_row b('quota'), $passwd{$user}{quota}{verbose} ; print html_table_row b('shell'), $passwd{$user}{shell} ; print html_table_row b('email address'), (HREF_mailto($passwd{$user}{email_address}) or $EMPTY_CELL) ; print html_table_row b('email aliases'), ($passwd{$user}{email_aliases} or $EMPTY_CELL) ; print html_table_row b('netgroups'), (join (' + ', netgroups_by_user($user)) or $EMPTY_CELL); { my %Remote_User = Remote_User($user) ; print html_table_row b('remote'), (join (' + ', keys (%Remote_User)) or '(*no remote access*)'); } print html_table_footer(); # finish off this table my $user_hosts_table = user_hosts_table($user, \%passwd) ; print $user_hosts_table ? "

seen on these hosts:

\n$user_hosts_table\n" : "

not seen on any hosts for which we have data

\n" ; print hr ; } # for users print html_table_footer(), '', end_html; return 0; } ############################################################################## sub user_hosts_table # given username, return html table of hosts on which user seen { my ($user, $passwd) = @_ ; $passwd or return '' ; my $buffer ; # have we got info on user seen on hosts? # get data from IP_MAC_user table: my $sth = $dbh->prepare("SELECT u.IPadd, u.MAC, v.Vendor, x.deviceID, s.sysName, p.Name, u.mtime FROM IP_MAC_user AS u LEFT JOIN Vendor_MAC AS v ON v.Code=left(u.MAC, 6) LEFT JOIN MAC_connections AS x ON u.MAC=x.MAC LEFT JOIN SNMP_system AS s ON x.deviceID=s.deviceID LEFT JOIN SNMP_ifTable AS p ON x.deviceID=p.deviceID AND x.ifIndex=p.ifIndex WHERE u.username='$user' AND u.mtime > $t_cutoff ORDER BY u.mtime DESC") ; $sth->execute ; if ($sth->rows()) { $buffer = sprintf "%s~ = home dir mountable on this host (Y/N)\n", ' 'x80 ; # new table $buffer .= html_table_header('border') ; $buffer .= html_table_header_row ('IP address', 'MAC address', HREF_Vendor(), 'Switch:Port', 'IP name', '~', 'netgroups', 'most recently', 'Ping') ; my ($IP, $MAC, $Vendor, $deviceID, $Switch, $Port, $mtime); $sth->bind_columns(undef, \($IP, $MAC, $Vendor, $deviceID, $Switch, $Port, $mtime)); while($sth->fetch) { my @IPnames = IPnames_by_IPadd($IP) ; # get IP name(s) for this address $buffer .= html_table_row ( # construct print string HREF_IPadd($IP), ($MAC or ''), HREF_Vendor_by_MAC($MAC, ($Vendor or '')), (HREF_SNMPdevice($deviceID, $Switch) . ":$Port"), (join ' ', @IPnames), # make name string out of individual names (filesys_exported($passwd->{$user}{home}, @IPnames) ? 'Y' : 'n'), (join ', ', netgroups_by_host(@IPnames)), # add netgroups relDate ($mtime, $t_now), ( ping($IP) ? ' Up' : 'Down' ), ); } $buffer .= html_table_footer() ; } return $buffer ; } ############################################################################## sub quota_info # return status, compact and verbose info for user # status: 0 = unknown, 1 = no quota, 2 = under quota, 3 = over space quota 4 = over files quota { my $user = shift or return (); unless (defined($user->{quota_bhard})) # quota info available ? { return { status => 0, verbose => 'quota info not available', compact => ' ? ', } ; } unless ($user->{quota_bhard} or $user->{quota_fhard}) # quota-d? { return { status => 1, verbose => 'no quota', compact => ' 0 ', } ; } my $percent_space = int (100 * $user->{quota_bcurr} / $user->{quota_bhard}) ; my $since_ctime = $user->{quota_ctime} ? sprintf "since %s", nameDate($user->{quota_ctime}) : '' ; if ($user->{quota_btime} or $user->{quota_ftime}) # over quota? { if ($user->{quota_btime}) # over blocks limit? { return { status => 3, compact => (sprintf "%3u%%", $percent_space) , verbose => (sprintf "over quota: filespace %u%% %u/%u KB %s (files OK: %u/%u)", $percent_space , $user->{quota_bcurr}, $user->{quota_bhard}, $since_ctime , $user->{quota_fcurr}, $user->{quota_fhard}) } ; } # over files limit return { status => 4, compact => 'FILE' , verbose => (sprintf "too many files: %u/%u (filespace OK %u%% %u/%u KB) %s", $user->{quota_fcurr}, $user->{quota_fhard} , $percent_space , $user->{quota_bcurr}, $user->{quota_bhard}, $since_ctime) } ; } if ($user->{quota_bhard} or $user->{quota_fhard}) # quota-d? { return { status => 2, compact => (sprintf "%3u%%", $percent_space) , verbose => (sprintf "%u%% - %u/%u KB; (%u/%u files) %s", $percent_space, $user->{quota_bcurr}, $user->{quota_bhard}, $user->{quota_fcurr}, $user->{quota_fhard}, $since_ctime) } ; } } ############################################################################## sub MACs_Query # print report on all MAC addresses { # hack: for MACs pages produced in ./Hosts subdir (URL) (see CHANGES) $MY_HREF="../Query.cgi"; # my $t_cutoff = $t_now - (60 * 60 * 24 * 30) ; # 30 days ago # my $t_yesterday = ($t_now - 60 * 60 * 24) ; ################################################################################### # print report ############# my $MACs_file = 'MACs.html' ; $OUTDIR = '' unless $OUTDIR ; my $MACs_fh = new FileHandle ">$OUTDIR/$MACs_file" or die "Could not open file to write: $OUTDIR/$MACs_file\n *** $! ***"; print $MACs_fh HTMLtop('MAC addreses'); my $Updated = sprintf "

Updated at %s


\n", nameDate() ; print $MACs_fh $Updated; print $MACs_fh html_table_header() ; # print $MACs_fh html_table_header_row('MAC', 'deviceID', 'ifIndex', 'ifDescr') ; my $table_row_format = qq(
%s%s %s %s %s %s %s  %s %s %s\n) ;  #   *** bodge! *** should end with 
but this adds a blank line to each table row my $header_row = sprintf $table_row_format, $HTML_COLOUR{black}, qq(), 'MAC address', HREF_Vendor(), ' device ID ', 'ifIndex ', 'ifDescription' ; print $MACs_fh $header_row ; TINFO "querying SNMP_ifTable ..." ; my $SELECT = "SELECT deviceID, ifIndex, Descr, Name, Type, Speed, PhysAddress, AdminStatus, OperStatus, LastChangeAt FROM SNMP_ifTable ORDER BY PhysAddress, ifIndex" ; my $sth = $dbh->prepare($SELECT) ; $sth->execute ; if ($sth->rows) { my ($deviceID, $ifIndex, $Descr, $Name, $Type, $Speed, $PhysAddress, $AdminStatus, $OperStatus, $LastChange) ; $sth->bind_columns(undef, \($deviceID, $ifIndex, $Descr, $Name, $Type, $Speed, $PhysAddress, $AdminStatus, $OperStatus, $LastChange) ); while ($sth->fetch) { next unless $PhysAddress ; next if ($PhysAddress eq '000000000000') ; next unless (length ($PhysAddress) eq 12) ; # $PhysAddress =~ s/(..)(..)(..)(..)(..)(..)/$1:$2:$3:$4:$5:$6/; printf $MACs_fh $table_row_format , $HTML_COLOUR{white}, '', $PhysAddress, HREF_Vendor_by_MAC($PhysAddress), HREF_SNMP($deviceID, "deviceID=$deviceID"), (sprintf "%8.8s", $ifIndex), ($Descr or ''); } } $sth->finish ; TINFO "closing MACs file ..." ; print $MACs_fh html_table_footer(), end_html ; close $MACs_fh ; return 0; } ############################################################################## sub Query_IPadd # print report on a specific IP address { # my $IPadd = param('IPadd') or return 1; my $IPadd = shift or return 1; print HTMLtop( "Query: IPadd $IPadd". (param('VERBOSE') ? ' (VERBOSE)' : '')); print "\n
"; my %IPdev ; $IPdev{names} = join ' ', IPnames_by_IPadd($IPadd) ; $IPdev{netgroups} = join ' ', netgroups_by_host(IPnames_by_IPadd($IPadd)) ; # should really keep groups associated with specific names my $SELECT = "SELECT a.MAC, v.Vendor, a.time1, a.time2, x.deviceID, x.ifIndex, xs.sysName, xi.Name, u.username, u.mtime FROM IP_MAC AS a LEFT JOIN Vendor_MAC AS v ON v.Code=left(a.MAC,6) LEFT JOIN MAC_connections AS x ON a.MAC=x.MAC LEFT JOIN SNMP_system as xs ON x.deviceID=xs.deviceID LEFT JOIN SNMP_ifTable AS xi ON x.deviceID=xi.deviceID AND x.ifIndex=xi.ifIndex LEFT JOIN IP_MAC_user AS u ON a.IPadd=u.IPadd AND a.MAC=u.MAC WHERE a.IPadd='$IPadd' ORDER BY a.time2, u.mtime" ; my $sth = $dbh->prepare($SELECT) ; $sth->execute ; unless ($sth->rows) { print "no information found\n", end_html ; return ; } { # restrict scope of following variables to avoid side-effects my ($MAC, $Vendor, $time1, $time2, $x_deviceID, $x_ifIndex, $x_devName, $x_ifName, $username, $u_mtime) ; $sth->bind_columns(undef, \($MAC, $Vendor, $time1, $time2, $x_deviceID, $x_ifIndex, $x_devName, $x_ifName, $username, $u_mtime) ); while ($sth->fetch) { $IPdev{Vendor}{$MAC} = $Vendor; $IPdev{Time}{$time1}{Address} = $MAC; $IPdev{Time}{$time1}{Time2} = $time2; $username and $IPdev{Time}{$time1}{User}{$username} = $u_mtime ; $IPdev{SwitchPort}{$MAC} = { deviceID => $x_deviceID, ifIndex => $x_ifIndex, sysName => $x_devName, ifName => $x_ifName } ; } $sth->finish ; } $SELECT = "SELECT DISTINCT a.IPadd, a.MAC, s.deviceID, s.sysName, s.sysLocation, s.sysContact, s.sysDescr, s.sysObjectID, s.sysServices, s.ifNumber, s.UpSinceTime, s.mtime FROM IP_MAC AS a, SNMP_ipAddrTable AS si, SNMP_ifTable AS sf, SNMP_system AS s WHERE a.IPadd=si.IPadd AND a.MAC=sf.PhysAddress AND si.deviceID=sf.deviceID AND si.ifIndex=sf.ifIndex AND si.deviceID=s.deviceID AND a.IPadd='$IPadd' ORDER BY a.time2" ; $sth = $dbh->prepare($SELECT) ; $sth->execute ; if ($sth->rows) { my ($MAC, $deviceID, $sysName, $sysLocation, $sysContact, $sysDescr, $sysObjectID, $sysServices, $ifNumber, $UpSinceTime, $mtime) ; $sth->bind_columns(undef, \($IPadd, $MAC, $deviceID, $sysName, $sysLocation, $sysContact, $sysDescr, $sysObjectID, $sysServices, $ifNumber, $UpSinceTime, $mtime) ); while ($sth->fetch) { $IPdev{SNMP}{$MAC} = { deviceID => $deviceID, sysName => $sysName, sysLocation => $sysLocation, sysContact => $sysContact, sysDescr => $sysDescr, sysObjectID => $sysObjectID, sysServices => $sysServices, ifNumber => $ifNumber, UpSinceTime => $UpSinceTime, mtime => $mtime, } ; } } $sth->finish ; foreach my $MAC (keys %{$IPdev{SNMP}}) # if device(s) do(es) SNMP: { # get snmp ip table from DB: my $sth = $dbh->prepare("SELECT IPadd, ifIndex, NetMask from SNMP_ipAddrTable WHERE deviceID=$IPdev{SNMP}{$MAC}{deviceID}") ; $sth->execute; if ($sth->rows) { my ($IPadd, $index, $Mask) ; $sth->bind_columns(undef,\($IPadd, $index, $Mask)) ; while ($sth->fetch) { $IPdev{SNMP_ipAddr}{$MAC}{$index} = { NetMask => $Mask, IPadd => $IPadd } ; } } # get device if/dot1d table: # $IPdev{SNMP_ifTable}{$MAC} = device_if_dot1d_table($IPdev{SNMP}{$MAC}{deviceID}) ; } my $buffer = html_table_header('border cellpadding=2') ; my ($row, $colspan, $endrow) = ('', 'colspan=7', "\n") ; $buffer .= $row . html_table_cell( b("IP address")) . html_table_cell($IPadd, $colspan) . $endrow ; $buffer .= $row . html_table_cell( b("name(s)") ) . html_table_cell(($IPdev{names} or $EMPTY_CELL), $colspan) . $endrow ; $buffer .= $row . html_table_cell( b("netgroup(s)") ) . html_table_cell(($IPdev{netgroups} or $EMPTY_CELL), $colspan) . $endrow ; $buffer .= html_table_row ( b('MAC'), b(HREF_Vendor()), b('from'), b('to'), b('Switch:Port'), b('SNMP Name'), b('user(s)'), b('seen at')) ; foreach my $t1 (reverse sort keys %{$IPdev{Time}}) { my $MAC = $IPdev{Time}{$t1}{Address} ; my $users = scalar keys %{$IPdev{Time}{$t1}{User}} ; my $rowspan = ($users > 1) ? "rowspan=$users" : '' ; # only set rowspan if > 1 rows $buffer .= $row . html_table_cell ($MAC, $rowspan) . html_table_cell (HREF_Vendor_by_MAC($MAC,$IPdev{Vendor}{$MAC}), $rowspan) . html_table_cell (relDate($t1), $rowspan) . html_table_cell (relDate($IPdev{Time}{$t1}{Time2}), $rowspan) . html_table_cell (($IPdev{SwitchPort}{$MAC}{deviceID} ? HREF_SNMPdevice($IPdev{SwitchPort}{$MAC}{deviceID}, $IPdev{SwitchPort}{$MAC}{sysName}).':'.($IPdev{SwitchPort}{$MAC}{ifName} or '') : $EMPTY_CELL), $rowspan) ; $buffer .= html_table_cell ( (($IPdev{SNMP}{$MAC} and $IPdev{SNMP}{$MAC}{mtime} <= $IPdev{Time}{$t1}{Time2}) ? ($IPdev{SNMP}{$MAC}{sysName} or '*') : $EMPTY_CELL), $rowspan) ; if ($users) { foreach my $u (keys %{$IPdev{Time}{$t1}{User}}) { $buffer .= html_table_cell (HREF_Username_Query($u)) . html_table_cell (relDate($IPdev{Time}{$t1}{User}{$u})) ; $buffer .= $endrow . $row ; } } else { $buffer .= html_table_cell ($EMPTY_CELL) . html_table_cell ($EMPTY_CELL) ; } $buffer .= $endrow ; } $buffer .= html_table_footer() ; foreach my $SNMP_MAC (keys %{$IPdev{SNMP}}) # there may be more than one different SNMP info for device (e.g. 2 SNMP devices with same IP address) { $buffer .= html_table_header('border cellpadding=2') ; $buffer .= $row . html_table_cell( b('
SNMP system info
'), 'colspan=8') . $endrow ; $buffer .= $row . html_table_cell( b('sysName') ). html_table_cell( ($IPdev{SNMP}{$SNMP_MAC}{sysName} or $EMPTY_CELL), $colspan) . $endrow ; $buffer .= $row . html_table_cell( b('sysLocation')). html_table_cell( ($IPdev{SNMP}{$SNMP_MAC}{sysLocation} or $EMPTY_CELL),$colspan) . $endrow ; $buffer .= $row . html_table_cell( b('sysContact') ). html_table_cell( ($IPdev{SNMP}{$SNMP_MAC}{sysContact} or $EMPTY_CELL),$colspan) . $endrow ; $buffer .= $row . html_table_cell( b('sysDescr') ). html_table_cell( $IPdev{SNMP}{$SNMP_MAC}{sysDescr}, $colspan) . $endrow ; $buffer .= $row . html_table_cell( b('sysObjectID')). html_table_cell( $IPdev{SNMP}{$SNMP_MAC}{sysObjectID}, $colspan) . $endrow ; $buffer .= $row . html_table_cell( b('sysServices')). html_table_cell( $IPdev{SNMP}{$SNMP_MAC}{sysServices}, $colspan) . $endrow ; $buffer .= $row . html_table_cell( b('Up Since') ). html_table_cell( relDate($IPdev{SNMP}{$SNMP_MAC}{UpSinceTime}), $colspan) . $endrow ; $buffer .= $row . html_table_cell( b('checked at') ). html_table_cell( relDate($IPdev{SNMP}{$SNMP_MAC}{mtime}), $colspan) . $endrow ; $buffer .= $row . html_table_cell( b('deviceID') ). html_table_cell( $IPdev{SNMP}{$SNMP_MAC}{deviceID}.' (discovered: '.nameDate($IPdev{SNMP}{$SNMP_MAC}{deviceID}).')', $colspan) . $endrow ; $buffer .= html_table_footer() ; $buffer .= html_table_header('border cellpadding=2') ; my $ipAddr_header = $row.html_table_cell(b('
SNMP ipAddrTable
'),'colspan=3').$endrow. $row.html_table_cell(b('ifIndex')).html_table_cell(b('IP add')).html_table_cell(b('NetMask')).$endrow ; foreach my $ifX (keys %{$IPdev{SNMP_ipAddr}{$SNMP_MAC}}) { if ($ipAddr_header) # print header only once, and only if we have anything to put under it i.e. if we enter this loop { $buffer .= $ipAddr_header ; $ipAddr_header = '' ; } $buffer .= $row . html_table_cell($ifX) . html_table_cell($IPdev{SNMP_ipAddr}{$SNMP_MAC}{$ifX}{IPadd}) . html_table_cell($IPdev{SNMP_ipAddr}{$SNMP_MAC}{$ifX}{NetMask}) . $endrow ; } $buffer .= html_table_footer() ; $buffer .= $row . html_table_cell(device_if_dot1d_html_table($IPdev{SNMP}{$SNMP_MAC}{deviceID}) , 'colspan=8').$endrow; } $buffer .= html_table_footer() ; print $buffer, end_html ; return 0; } ############################################################################## sub Hosts_Query # print report on all hosts { # hack: for Hosts pages produced in ./Hosts subdir (URL) (see CHANGES) # $MY_HREF="../Query.cgi"; my %hostlist ; # hash of {ipaddress} my %MACs ; # list of (unique) MACs TINFO "getting subnets list ..." ; my %nets = Subnets() ; foreach my $sub_num (keys %nets) { $hostlist{$sub_num}{'IPadd'} = $nets{$sub_num}{net} . '.0.0' ; # make record of .0 host on network $hostlist{$sub_num}{'IPadd'} =~ s/(\d+\.\d+\.\d+\.0)\.0/$1/; # trim excess .0s from string } TINFO "querying IP_MAC ..." ; my $SELECT = "SELECT IPadd, MAC, time1, time2 FROM IP_MAC ORDER BY IPadd, time2" ; # order by last time to get most recent record # my $SELECT = "SELECT IPadd, MAC, time1, time2 FROM IP_MAC WHERE deviceID = 0 ORDER BY IPadd, time2" ; # order by last time to get most recent record my $sth = $dbh->prepare($SELECT) ; $sth->execute ; if ($sth->rows) { my $prev_IPadd = '' ; my $prev_MAC = '' ; my ($IPnum, $db_IPadd, $db_MAC, $db_time1, $db_time2) ; $sth->bind_columns(undef, \($db_IPadd, $db_MAC, $db_time1, $db_time2) ); while ($sth->fetch) { if (! $db_IPadd =~ /\d+\.\d+\.\d+\.\d+/) # check against corrupt records in DB { WARNING sprintf "Corrupt IP address in IP_MAC table: %-15s %-12s %s %s\n", $db_IPadd, $db_MAC, $db_time1, $db_time2 ; next ; } $MACs{$db_MAC} = defined ; # record MAC ; if ($db_IPadd ne $prev_IPadd) # if we have a new IP address { $IPnum = IPnum_by_IPadd($db_IPadd) ; # get integer value of IPaddress, used as hash key $hostlist{$IPnum}{IPadd} = $db_IPadd ; $hostlist{$IPnum}{MAC} = $db_MAC ; $hostlist{$IPnum}{t1} = $db_time1 ; $hostlist{$IPnum}{t2} = $db_time2 ; $hostlist{$IPnum}{MAC_changes} = 0 ; # record MAC:time1:time2 (hashref) for this IP push @{$hostlist{$IPnum}{MACtimes}}, { MAC => $db_MAC, time1 => $db_time1, time2 => $db_time2 } ; } else # same IPadd as last record ... { # whether MAC is new or the same, update t2 $hostlist{$IPnum}{t2} = $db_time2 ; if ($db_MAC ne $prev_MAC) # if we have a new MAC address for same IPadd { $hostlist{$IPnum}{MAC} = $db_MAC ; # update record with latest MAC .. $hostlist{$IPnum}{t1} = $db_time1 ; # .. and first-seen time for this new MAC $hostlist{$IPnum}{MAC_changes}++ ; # track number of changes of MAC address # record new MAC:time1:time2 (hashref) for this IP push @{$hostlist{$IPnum}{MACtimes}}, { MAC => $db_MAC, time1 => $db_time1, time2 => $db_time2 } ; } else # same MAC { if ($db_time2 > $t_cutoff) # don't bother with ancient records { my $MACtime = pop @{$hostlist{$IPnum}{MACtimes}} ; # get back hashref we last added push @{$hostlist{$IPnum}{MACtimes}}, { # and put back modified version MAC => $db_MAC, time1 => $MACtime->{time1}, # keep old t1 time2 => $db_time2 # replace t2 with later value } ; } } } $prev_IPadd = $db_IPadd ; # remember IPadd and MAC for next record off db $prev_MAC = $db_MAC ; } } $sth->finish ; TINFO "querying IP_hosts ..." ; $SELECT = "SELECT IPadd, name FROM IP_hosts" ; $sth = $dbh->prepare($SELECT) ; $sth->execute ; if ($sth->rows) { my ($IPnum, $db_IPadd, $db_IPname) ; $sth->bind_columns(undef, \($db_IPadd, $db_IPname) ); while ($sth->fetch) { if (! $db_IPadd =~ /\d+\.\d+\.\d+\.\d+/) # check against corrupt records in DB { WARNING sprintf "Corrupt IP address in IP_hosts table: %-15s %s\n", $db_IPadd, $db_IPname ; next ; } $IPnum = IPnum_by_IPadd($db_IPadd) ; # get integer value of IPaddress for sorting, later $hostlist{$IPnum}{'IPadd'} = $db_IPadd ; push @ { $hostlist{$IPnum}{'IPname'} }, $db_IPname ; # keep list of names } } $sth->finish ; # we now have all IPaddress + MAC + times + names; get usernames TINFO "querying IP_MAC_user ..." ; $SELECT = "SELECT IPadd, MAC, username, mtime FROM IP_MAC_user WHERE mtime > $t_cutoff ORDER BY IPadd, username, mtime" ; # group by IPadds then usernames, then most recent $sth = $dbh->prepare($SELECT) ; $sth->execute ; if ($sth->rows) { my $IPnum ; my ($db_IPadd, $db_MAC, $db_username, $db_mtime) ; $sth->bind_columns(undef, \($db_IPadd, $db_MAC, $db_username, $db_mtime) ); while ($sth->fetch) { next unless $db_MAC ; # skip unless we have MAC address unless ($IPnum and $hostlist{$IPnum}{IPadd} and ($hostlist{$IPnum}{IPadd} eq $db_IPadd)) # if we have a new IP address { $IPnum = IPnum_by_IPadd($db_IPadd) ; # get integer value of IPaddress, used as hash key $hostlist{$IPnum}{users} = 0 ; # initialise count } else # same IPadd as last record ... { next unless $hostlist{$IPnum}{MAC} ; # skip unless we have MAC addresses # skip unless MAC address matches next unless ($hostlist{$IPnum}{MAC} eq $db_MAC) ; unless ($hostlist{$IPnum}{username} and ($hostlist{$IPnum}{username} eq $db_username)) # if we have a new username { # count unless time is old: $hostlist{$IPnum}{users}++ unless $db_mtime < $t_week ; $hostlist{$IPnum}{username} = $db_username ; # store new username } } } } ###################################################################### # we now have IPaddress + MAC + times + names + users: get SNMP info # lookup SNMP info TINFO "looking up SNMP info" ; my %SNMP; %SNMP = SNMPdevices_by_WHERE() ; my $deviceID ; foreach $deviceID (keys %SNMP) { my $IPadd; foreach $IPadd (IPadds_by_deviceID($deviceID)) { next unless $IPadd =~ /^\d+\.\d+\.\d+\.\d+/ ; # skip unless proper IP address next if $IPadd =~ /^(127\.0\.|192\.168\.101|161\.71\.15|0\.0\.0)/ ; # skip loopback and various odd SLIP addresses my $IPnum = IPnum_by_IPadd($IPadd) ; $hostlist{$IPnum}{SNMP} = sprintf qq( %-2.2s ), $MY_HREF, $deviceID, $SNMP{$deviceID}{'sysServices'} ; $hostlist{$IPnum}{IPadd} = $IPadd unless $hostlist{$IPnum}{IPadd} ; } } #sprintf qq(dot1d), $MY_HREF, $devID ###################################################################### # get switch:ports for all MACs seen TINFO ('getting switch:ports for all', (scalar(keys %MACs)), 'MACs seen') ; my $Switch_Port = Switch_Port_by_MACs(keys %MACs) ; ########################################################################################################### # process entire list: # weed out localhost, # if MACs defined lookup Vendors and nearest switch ports and format times, else fill in undefined fields # tag records for colouring in report and count records # TINFO 'looking up Vendors and nearest switch ports, formatting times and counting records' ; my $IPnum; my $records = 0; my $hosts_active = 0; my $hosts_unseen = 0; my $hosts_recent = 0 ; my $hosts_unregistered = 0 ; my @dupIPlist ; # list of hosts with duplicate IP addresses foreach $IPnum (keys %hostlist) { unless ($hostlist{$IPnum}{IPadd}) { delete $hostlist{$IPnum} ; # delete record next ; } if ($hostlist{$IPnum}{IPadd} =~ /^127.0.0/) { delete $hostlist{$IPnum} ; # delete localhost record next ; } $hostlist{$IPnum}{status} = 'never' ; # ensure this field is defined for records derived from other than IP_MAC table $hostlist{$IPnum}{MAC_changes} = 0 unless $hostlist{$IPnum}{MAC_changes} ; $hostlist{$IPnum}{users} = 0 unless $hostlist{$IPnum}{users} ; if ($hostlist{$IPnum}{users} > 1) # multiple users? { $hostlist{$IPnum}{username} = sprintf " %3u ", $hostlist{$IPnum}{users} ; } else { $hostlist{$IPnum}{username} = HREF_Username_Query($hostlist{$IPnum}{username}) ; } $hostlist{$IPnum}{SwitchPort} = ' : ' ; $hostlist{$IPnum}{N_dupIPs} = '' ; # blank unless ($hostlist{$IPnum}{MAC}) # if we have no MAC for this host: { $hostlist{$IPnum}{MAC} = '' ; $hostlist{$IPnum}{HREF_Vendor} = ' ' x 8 ; $hostlist{$IPnum}{t1} = ' ' x 12 ; $hostlist{$IPnum}{t2} = ' ' x 12 ; $hosts_unseen++ ; } else # we do have a MAC address: { $hosts_active++ ; # count as seen $hostlist{$IPnum}{status} = 'active' ; $hostlist{$IPnum}{HREF_Vendor} = HREF_Vendor_by_MAC($hostlist{$IPnum}{MAC}) ; # changes of MAC address? if ($hostlist{$IPnum}{MAC_changes} > 1) { $hostlist{$IPnum}{N_dupIPs} = $hostlist{$IPnum}{MAC_changes} ; push @dupIPlist, $IPnum ; } if ($hostlist{$IPnum}{t2} > $t_yesterday) # recent? { $hosts_recent++ ; # count as recent $hostlist{$IPnum}{status} = 'recent' ; if ( $Switch_Port->{$hostlist{$IPnum}{MAC}}->{deviceID} and $Switch_Port->{$hostlist{$IPnum}{MAC}}->{sysName} and $Switch_Port->{$hostlist{$IPnum}{MAC}}->{ifName} ) # if we found a switch { # $Switch_Port->{$hostlist{$IPnum}{MAC}}->{ifName} = substr( $Switch_Port->{$hostlist{$IPnum}{MAC}}->{ifName}, -4 ) ; # chop to last 3 chars # $hostlist{$IPnum}{SwitchPort} = HREF_SNMPdevice($Switch_Port->{$hostlist{$IPnum}{MAC}}->{deviceID}, sprintf "%15.15s:%4.4s", $Switch_Port->{$hostlist{$IPnum}{MAC}}->{sysName}, $Switch_Port->{$hostlist{$IPnum}{MAC}}->{ifName} ) ; $hostlist{$IPnum}{SwitchPort} = HREF_SNMPdevice($Switch_Port->{$hostlist{$IPnum}{MAC}}->{deviceID}, sprintf "%10.10s:%s", $Switch_Port->{$hostlist{$IPnum}{MAC}}->{sysName}, procrustean($Switch_Port->{$hostlist{$IPnum}{MAC}}->{ifName},-6) ) ; } } $hostlist{$IPnum}{t1} = relDate ($hostlist{$IPnum}{t1}, $t_now) ; $hostlist{$IPnum}{t2} = relDate ($hostlist{$IPnum}{t2}, $t_now) ; } $hostlist{$IPnum}{SNMP} = ' ' x 4 unless $hostlist{$IPnum}{SNMP} ; # name? if ($hostlist{$IPnum}{'IPname'}) { $hostlist{$IPnum}{names} = join (' ', @{$hostlist{$IPnum}{IPname}}) ; } else # no name - unregistered host { $hostlist{$IPnum}{IPname} = () ; $hostlist{$IPnum}{names} = '*** unregistered ***' ; $hosts_unregistered++ ; # if a recent sighting mark as unregistered (not too bothered about non-current hosts) ($hostlist{$IPnum}{status} eq 'recent') and $hostlist{$IPnum}{status} = 'unregistered' ; } $records++ ; } return 0 unless $records ; # quit if we have nothing my %Net_Mask = ( '24' => '255.255.255.0', '22' => '255.255.252.0', '16' => '255.255.0.0', ) ; ################################################################################### # print reports ############# my $NetIndex_file = 'Net_Index.html' ; my $Host_file = 'All_Hosts.html' ; my $DupIP_file = 'Dup_IPadds.html' ; my $Subnet_file_prefix = 'Net_' ; $OUTDIR = '' unless $OUTDIR ; my $NetIndex_fh = new FileHandle ">$OUTDIR/$NetIndex_file" or die "Could not open file to write: $OUTDIR/$NetIndex_file\n *** $! ***"; my $Host_fh = new FileHandle ">$OUTDIR/$Host_file" or die "Could not open file to write: $OUTDIR/$Host_file\n *** $! ***"; my $DupIP_fh = new FileHandle ">$OUTDIR/$DupIP_file" or die "Could not open file to write: $OUTDIR/$DupIP_file\n *** $! ***"; my $Subnet_fh ; print $NetIndex_fh HTMLtop('Networks/Subnets'); print $Host_fh HTMLtop('Hosts - all'); print $DupIP_fh HTMLtop('Duplicate IPaddresses'); my $Updated = sprintf "

Updated at %s


\n", nameDate() ; print $NetIndex_fh $Updated; print $Host_fh $Updated; print $DupIP_fh $Updated; printf $DupIP_fh qq(
\n
users seen in web cache logs on these addresses
records since
%s

\n), relDate($t_cutoff) ;


    my  $hosts_summary_table = hosts_summary_table($hosts_active, $hosts_recent, $hosts_unseen, $hosts_unregistered, $records) ;

    print $Host_fh      $hosts_summary_table ;
    print $NetIndex_fh  $hosts_summary_table ;
    print $NetIndex_fh  qq(
All Networks/Subnets and Hosts
Duplicate IP addresses
MAC addresses on SNMP devices\n); print $NetIndex_fh html_table_header('border=1 cellpadding=4'); print $NetIndex_fh html_table_header_row ('Subnet', '/', 'Net Mask', 'Description', 'active', 'recent', 'unseen', 'unreg', 'total'); TINFO "sorting ..." ; my @IP = (sort keys %hostlist); my $subnet = 0 ; my %colour = ( active => $HTML_COLOUR{light_grey}, recent => $HTML_COLOUR{white}, unregistered => $HTML_COLOUR{yellow}, never => $HTML_COLOUR{dark_grey}, ) ; TINFO "printing ..." ; my $header_note = sprintf "chg = number of MAC address changes; SNMP = sysServices: %s %s %s; username or No. of users in last week\n", HREF_SNMP('1=repeater','sysServices=1'), HREF_SNMP('2=switch','sysServices=2'), HREF_SNMP('6=router','sysServices=6'); print $Host_fh $header_note, html_table_header() ; # print header rows my $table_row_format = qq(
%s%s %s %s %s %s %s  %s %s %s %s\n) ;  #   'should' end with 
but this adds a blank line to each table row my $header_row = sprintf $table_row_format, $HTML_COLOUR{black}, qq(), 'IP address ', 'chg', ' MAC ', HREF_Vendor(), ' switch:port ', ' first seen ', ' latest seen', HREF_SNMP(), 'username', 'IP name(s)' ; my $IPadd; my %SUBNET = () ; # hash for current subnet info my $subnet_buffer = '' ; my $subnet_dotted = '' ; my $subnet_title = '' ; push (@IP, '') ; # add a null value to the list so that the loop gets executed once after the last real value has been processed IPnum: foreach $IPnum (@IP) { $IPadd = ($hostlist{$IPnum}{'IPadd'} or ''); if (! $IPnum or ! $subnet or ($IPnum > $SUBNET{TOP})) # first/new subnet, or end of list ? { # print listing for last subnet to all-hosts report: print $Host_fh $subnet_buffer ; my $net_HREF = $subnet_dotted ; $net_HREF =~ s|^134\.225\.(\d+)|134.225.$1|; # HTML-highlight 3rd octet if one of our 134.225 numbers # if we had any active hosts in last subnet generate a listing if ($hosts_active) { $Subnet_fh = new FileHandle ">$OUTDIR/$Subnet_file_prefix$subnet_dotted.html" or die "Could not open file to write: $OUTDIR/Net_$subnet_dotted.html\n *** $! ***"; print $Subnet_fh HTMLtop("Network: $subnet_dotted - " . ($SUBNET{name} or 'unknown')); print $Subnet_fh (hosts_summary_table($hosts_active, $hosts_recent, $hosts_unseen, $hosts_unregistered, $records)) ; # print list print $Subnet_fh $header_note, html_table_header(); print $Subnet_fh $header_row ; print $Subnet_fh $subnet_buffer ; print $Subnet_fh html_table_footer(), end_html ; close $Subnet_fh ; $net_HREF = qq($net_HREF) ; # make href to this report } if ($subnet) # if we have reached end of one subnet then print row to Network Index table: { my $INDEXCOLOUR ; if ($SUBNET{name} =~ /NOT IN USE/i) { $INDEXCOLOUR = $HTML_COLOUR{dark_grey} ; } elsif ($SUBNET{name} =~ /NOT ROUTED/i) { if ($hosts_active) { $INDEXCOLOUR = $HTML_COLOUR{pastel_blue} ; } else { $INDEXCOLOUR = $HTML_COLOUR{blue_grey} ; } } else { $INDEXCOLOUR = $HTML_COLOUR{white} ; } printf $NetIndex_fh qq(%s\n), join (qq(
), ( $net_HREF , # network address with link to hosts report page $SUBNET{mask} , # / mask ($Net_Mask { $SUBNET{mask} } ? $Net_Mask { $SUBNET{mask} } : ''), # mask as dotted number $SUBNET{name} , # name $hosts_active, $hosts_recent, $hosts_unseen, $hosts_unregistered, $records, ) ); } last IPnum unless $IPnum ; # end here if we are on dummy end-of-list value, otherwise do new subnet: $subnet_buffer = '' ; # clear buffer for next subnet $hosts_active = 0 ; $hosts_recent = 0 ; $hosts_unseen = 0 ; $hosts_unregistered = 0 ; $records = 0 ; $subnet_title = '*** unable to identify network/subnet***'; if (($subnet, $SUBNET{dotted}, $SUBNET{mask}, $SUBNET{name}, $SUBNET{TOP}) = Subnet($IPnum)) # get array { $subnet_dotted = $SUBNET{dotted} ; $subnet_title = sprintf qq(%-20s %s%s), $subnet_dotted , # '; # blank line in all hosts report print $Host_fh $header_row ; print $Host_fh qq(
$subnet_title\n) ;

        }

        #   skip placemarker .0 addresses introduced from SUBNET list (as long as they are only placemarkers i.e. nothing seen on them)
        next IPnum if ($IPadd =~ /\.0$/ and !$hostlist{$IPnum}{MAC}) ;
        $subnet_buffer .= sprintf $table_row_format,
                $colour{$hostlist{$IPnum}{status}}, 
                '',     #   no  or could be ''
                #substr("$IPadd               ",0,15),       #
                HREF_IPadd($IPadd),
                ( $hostlist{$IPnum}{N_dupIPs} ? sprintf (qq(%3.3s), $hostlist{$IPnum}{'N_dupIPs'}) : ' 'x3 ) ,
                ($hostlist{$IPnum}{MAC} or ' 'x12),
                $hostlist{$IPnum}{HREF_Vendor},
                $hostlist{$IPnum}{SwitchPort},
                $hostlist{$IPnum}{t1} ,
                $hostlist{$IPnum}{t2} ,
                $hostlist{$IPnum}{SNMP}   ,
                $hostlist{$IPnum}{username},
                $hostlist{$IPnum}{names}
                ;
        $records++ ;
        if      ($hostlist{$IPnum}{status} eq 'active')       {   $hosts_active++ ; }
        elsif   ($hostlist{$IPnum}{status} eq 'recent')       {   $hosts_active++ ; $hosts_recent++ ; }
        elsif   ($hostlist{$IPnum}{status} eq 'never')        {   $hosts_unseen++ ; }
        elsif   ($hostlist{$IPnum}{status} eq 'unregistred')  {   $hosts_active++ ; $hosts_unregistered++ ; }

        # print report for any Duplicates on this address
        if (my $dupIP = dupIP_report_by_IPnum($IPnum, \%hostlist))
        {
            print $DupIP_fh $dupIP ;
        }
    }

TINFO "closing IPhosts files ..." ;

    print $Host_fh html_table_footer(), end_html ;
    close $Host_fh ;

    print $DupIP_fh "
\n", end_html; close $DupIP_fh; print $NetIndex_fh '
', html_table_footer(), end_html; close $NetIndex_fh ; TINFO "doing device MACs ..." ; MACs_Query() ; TINFO "exiting ..." ; return 0; } ############################################################################## sub hosts_summary_table { my ($active, $recent, $unseen, $unreg, $total) = @_ ; my $buffer = '
' ; $buffer .= html_table_header(); $buffer .= html_table_row (b('active (seen ever)'), $active); $buffer .= html_table_row (b('recent (seen today)'), $recent); $buffer .= html_table_row (b('unseen'), $unseen); $buffer .= html_table_row (b('unregistered'), $unreg); $buffer .= html_table_row (b('total'), $total) ; $buffer .= html_table_footer() ; $buffer .= "
\n" ; } ############################################################################## sub dupIP_report_by_IPnum # used by Hosts_Query: # returns printable string of Duplicate IP address information for given IP number { my ($IPnum, $hostlist) = @_ ; # IP num and hashref to $hostlist my $hostlist_IPnum = $hostlist->{$IPnum} ; return '' unless ($hostlist_IPnum->{MAC_changes} > 1) ; my $RECENT = 0 ; # flag to mark if this is a recent record we want to print my $prtbuff = '' ; # buffer to print to my $IPadd = $hostlist_IPnum->{IPadd} ; # join lists from IP_MAC and IP_MAC users into a single list, # organised as a hash indexed on last-time-seen + MAC address # (in case we have more than one record of same time) # which we can then sort on the index to print a chronological list my ($MACtime, %MAC_user_time) ; # foreach $MACtime (@{$hostlist{$IPnum}{MACtimes}}) foreach $MACtime (@{$hostlist_IPnum->{MACtimes}}) { next unless ($MACtime->{time2} > $t_cutoff ) ; # skip very old records $RECENT = 1 ; # flag that we've got some recent sightings %{$MAC_user_time{ $MACtime->{time2} . $MACtime->{MAC} }} = ( # index on t2 and MAC MAC => $MACtime->{MAC}, time1 => $MACtime->{time1}, time2 => $MACtime->{time2}, user => '' ) ; } # next IP unless $RECENT ; # go no further with this IP unless we have some recent MAC sightings return unless $RECENT ; # go no further with this IP unless we have some recent MAC sightings my %MAC_users = IP_MAC_user_BY_IPadd($IPadd) ; # hash of {MAC}{username}=mtime my ($MAC, $user) ; foreach $MAC (keys %MAC_users) { foreach $user (keys %{$MAC_users{$MAC}}) { next unless $MAC_users{$MAC}{$user} > $t_cutoff ; # ignore old records %{$MAC_user_time{ $MAC_users{$MAC}{$user} . $MAC }} = ( MAC => $MAC , time1 => 0 , time2 => $MAC_users{$MAC}{$user} , user => $user ) ; } } my $prev_MAC = '' ; # print what we've got for this IP: $prtbuff .= sprintf qq (%s %s\n), $IPnum, HREF_IPadd($IPadd), $hostlist_IPnum->{names} ; my $t ; foreach $t (reverse sort keys %MAC_user_time) { my $MAC = ($MAC_user_time{$t}{MAC} or ' ?? ') ; $prtbuff .= sprintf "\t%s %s - %s %s\n", ( $MAC eq $prev_MAC ? # is this MAC same as previous record? ' .. .. .. .. .. ' : # print dittos $MAC . ' ' . HREF_Vendor_by_MAC($MAC) # otherwise print values ), relDate($MAC_user_time{$t}{time1}, $t_now), relDate($MAC_user_time{$t}{time2}, $t_now), HREF_Username_Query($MAC_user_time{$t}{user}) ; $prev_MAC = $MAC_user_time{$t}{MAC} if ($MAC_user_time{$t}{MAC}) ; # remember MAC from this record unless it was missing } $prtbuff .= "\n" ; } ############################################################################## sub procrustean # given a string, a length and optionally a fillchar and optionally a cutchar # return the string, either padded to the specified length (with spaces, # or fillchar if specified) or truncated to the given length (or one less than # the given length with the cutchar at the cut position, if specified) # Always fill at right, but if length is negative truncate from the left of the string { my ($string, $length, $fillchar, $cutchar) = @_ ; return unless $length ; my $diff = abs($length) - length ($string); return $string unless $diff ; # string is exactly the right length? $fillchar = ' ' unless $fillchar ; if ($diff > 0) # string too short { # return ($length > 0) ? # normal: # $string . ($fillchar x $diff) : # fill at right; else: # ($fillchar x $diff) . $string ; # fill at left return $string . ($fillchar x $diff) ; } # (else) truncate if ($cutchar) { $length > 0 ? $length-- : $length++ ; } else { $cutchar = ''; } return ($length > 0) ? # cut at right: substr ($string, $length) . $cutchar : # cut at right; else: $cutchar . substr($string, $length) ; # cut at left } ############################################################################## sub HREF_mailto # given email address, return it as a mailto: hyperlink { my $emailadd = shift or return ''; my $recipient = '' ; if ($emailadd =~ /^([^(\s]+)(\s*\(.*)/) # e.g. like 'j.bloggs (->recipient)' { $emailadd = $1; $recipient = $2 ; } sprintf qq(%s%s), $emailadd, $emailadd, $recipient ; } ############################################################################## sub IP_MAC_user_BY_IPadd { my $IPadd = shift or return (); my $SELECT = "SELECT IPadd, MAC, username, mtime from IP_MAC_user WHERE IPadd='$IPadd'" ; my $sth = $dbh->prepare ($SELECT) ; $sth->execute ; my %IP_MAC_user; if ($sth->rows) { my ($IPadd, $MAC, $user, $mtime); $sth->bind_columns(undef, \($IPadd, $MAC, $user, $mtime)); while ($sth->fetch) { $IP_MAC_user{$MAC}{$user}=$mtime; } } $sth->finish ; return %IP_MAC_user; } ############################################################################## sub print_host_report # given a hash of host attributes, # retrieve extra info re SNMP etc # and print a report to stdout { my %host = @_ ; return unless (exist ($host {'IP address'} )); print html_table_header('border') ; my $IPadd = $host{'IPadd'} ; print html_table_row ( b('IP address'), $IPadd) ; print html_table_row ( b(HREF_Vendor()), $host{'HREF_Vendor'}) ; print html_table_row ( b('MAC address'), $host{'MAC'} ); print html_table_row ( b('MAC changes'), $host{'MAC_changes'} ) ; print html_table_row ( b('first seen'), $host{'t1'} ) ; print html_table_row ( b('last seen'), $host{'t2'} ) ; print html_table_row ( b('IP name(s)'), $host{'names'} ) ; # get SNMP info my %SNMP = dbSNMP_by_IPadd ($IPadd); # * should now have this in %SNMP if ($SNMP{'sysObjectID'}) # if SNMP info found { print html_table_row ( b('SNMP'), b('system')) . ' group' ; print html_table_row ( b('sysName'), ($SNMP{'sysName'} or '')) ; print html_table_row ( b('sysContact'), ($SNMP{'sysContact'} or '')) ; print html_table_row ( b('sysLocation'), ($SNMP{'sysLocation'} or '')) ; print html_table_row ( b('sysObjectID'), $SNMP{'sysObjectID'}) ; $SNMP{'sysDescr'} =~ s/\n/
/g; # sysDescr may contain newlines: format properly for html print html_table_row ( b('sysDescr'), ($SNMP{'sysDescr'} or '')) ; print html_table_row ( b('sysServices'), HREF_sysServices( $SNMP{'deviceID'}, $SNMP{'sysServices'}) ) ; print html_table_row ( b('Up Since'), YYYY_MM_DD_hh_mm($SNMP{'UpSinceTime'})) ; print html_table_row ( b('checked at'), YYYY_MM_DD_hh_mm($SNMP{'mtime'})) ; } print html_table_footer() ; print end_html; return 0; } ############################################################################## sub Query_SNMP # front end for all SNMP queries # - does compact listing for all devices, possibly filtered by type (sysServices) # - calls another function for verbose listing or device-specific report { if (param('Connections')) { return Query_SNMP_Connections(@_); } if (param('VERBOSE') or param('deviceID')) { return Query_SNMP_VERBOSE(@_); } my $SELECT = "SELECT i.IPadd, s.deviceID, s.sysName, s.sysLocation, s.sysDescr, s.sysServices, s.mtime FROM SNMP_system AS s, SNMP_ipAddrTable AS i WHERE s.deviceID=i.deviceID" ; if (param('sysServices')) { $SELECT .= ' AND s.sysServices=' . param('sysServices') ; } $SELECT .= ' AND s.mtime>' . (NOW() - WEEK()) ; # only get devices seen recently $SELECT .= ' ORDER BY s.sysName, s.deviceID, s.mtime, i.IPadd' ; print HTMLtop('Query: SNMP') ; my $sth = $dbh->prepare($SELECT); $sth->execute ; my $numDevs = $sth->rows ; unless ($numDevs) { print "no records found", end_html; return 0; } print '
' ; printf qq(

Click here for verbose list

), Verbose_URI(1) ; print "\n$numDevs records (devices + addresses)\n\n" ; print html_table_header('cellspacing=0'); print '

' ;
    my  $tbl_format = '%s %s %2s %-20.20s %-30.30s %-30.30s %s' ;
    printf $tbl_format, '   ID    ', '  IP address   ', 'Sv', 'sysName', 'sysLocation', 'sysDescr', 'checked at' ;

    my  ($IPadd, $deviceID, $sysName, $sysLocation, $sysDescr, $sysServices, $mtime) ;
    my  %PREV = ( deviceID => 0, sysName => '', sysServices => 0, mtime => 0 ) ;   #   hash to hold previous record
    my  $new_device = 1 ;
    $sth->bind_columns(undef, \($IPadd, $deviceID, $sysName, $sysLocation, $sysDescr, $sysServices, $mtime));
    while ($sth->fetch)
    {
        $new_device = ($deviceID==$PREV{deviceID} and $mtime==$PREV{mtime} and $sysName eq $PREV{sysName} and $sysServices==$PREV{sysServices})    #   same device
            ? 0 : 1 ;
        my  $colour = BGCOLOR($mtime) ;
        print "\n
" ;
        printf $tbl_format, 
            ($new_device ? (HREF_SNMP($deviceID, "deviceID=$deviceID")) : ' 'x9 ) ,     #   deviceID
#            (qq($IPadd) . ' 'x(15-length($IPadd)) ) ,              #   href to IP address
            HREF_IPadd($IPadd) ,                                                     #   href to IP address
            ($new_device ?
                (
                $sysServices , #   2 chars: sys Services
                ($sysName or ''), #   sysName
                ($sysLocation or ''), #   sysLocation
                ($sysDescr or ''),      #   sysDescr trimmed to 20 chars
                relDate($mtime,$t_now)   #   12 chars: checked at
                )
                : ('', '', '', '', '') 
            ) ;
        %PREV = (
            deviceID    =>  $deviceID,
            sysName     =>  $sysName,
            sysServices =>  $sysServices,
            mtime       =>  $mtime,
        ) ;
    }
    
    print '
', html_table_footer(), end_html; return 0; } ############################################################################## sub Query_SNMP_Connections { print HTMLtop{-title => "Query: SNMP Network device interconnections", -BGCOLOR => '#EEEEEE'} ; my $Oldest = NOW() - $WEEK ; # get the data wee want in two separate selects, as this is MUCH quicker than one big one # # First: SNMP devices and their ifTables, using PhysAddress (MAC of interface) # to find what this device is connected to # (and looking for a corresponding IP address for the MAC from the IP_MAC table) # my $SELECT = "SELECT s.deviceID, s.sysName, s.sysLocation, s.sysServices, s.mtime, ift.ifIndex, ift.PhysAddress, ift.Name, arp.IPadd, arp.time2, x.deviceID, x.ifIndex, x.nMACs FROM SNMP_system AS s, SNMP_ifTable AS ift LEFT JOIN IP_MAC AS arp ON ift.PhysAddress=arp.MAC AND arp.time2>$Oldest LEFT JOIN MAC_connections AS x ON ift.PhysAddress=x.MAC WHERE s.deviceID=ift.deviceID AND s.sysServices<7 AND s.mtime>$Oldest ORDER BY arp.time2" ; my $sth = $dbh->prepare($SELECT); $sth->execute ; my $numDevs = $sth->rows ; unless ($numDevs) { $sth->finish; print "no records found", end_html; return 0; } my ($deviceID, $sysName, $sysLocation, $sysServices, $mtime, $if_ifIndex, $MAC, $if_Name, $IPadd, $arp_time, $X_deviceID, $X_ifIndex, $X_n) ; my (%device, %device_type) ; $sth->bind_columns(undef, \($deviceID, $sysName, $sysLocation, $sysServices, $mtime, $if_ifIndex, $MAC, $if_Name, $IPadd, $arp_time, $X_deviceID, $X_ifIndex, $X_n)); while ($sth->fetch) { $device_type{$sysServices}{$deviceID} = 1 ; # keep track of devices of each type $device{$deviceID}{Name} = ($sysName or '') ; $device{$deviceID}{Location} = ($sysLocation or '') ; $device{$deviceID}{Services} = $sysServices ; $device{$deviceID}{mtime} = $mtime ; $device{$deviceID}{deviceID} = $deviceID ; # seems a bit redundant but used for little_device_table() # next unless $if_ifIndex ; # should not be necessary: all retrieved records must have ifIndices $device{$deviceID}{if}{$if_ifIndex} = $if_Name ; # record ifName (should have one) next unless $MAC ; # should have this: Physical address of interface next if $MAC =~ /^0+$/ ; # skip all 0s MAC addresses (why do we get this garbage?) next unless $MAC =~ /^[0-9a-f]{12}$/ ; # accept only well-formed MAC addresses $IPadd and $device{$deviceID}{MAC}{$MAC}{IPadd} = $IPadd ; # record IP addresses, if we have one for this interface's PhysAddress # $if_ifIndex and push @{$device{$deviceID}{MAC}{$MAC}{ifs}}, $if_ifIndex ; # track ifs associated with this MAC address $X_deviceID and $device{$deviceID}{Connx}{$X_deviceID}{$X_ifIndex}{MAC}{$MAC} = $X_n ; # record our MAC adds and nMACs seen on this other device+interface } $sth->finish; # Second: SNMP devices and their ipAddrTables, looking up IP address # in IP_MAC to find a corresponding MAC to find what this device is connected to # $SELECT = "SELECT s.deviceID, ip.IPadd, arp.MAC, arp.time2, x.deviceID, x.ifIndex, x.nMACs FROM SNMP_system AS s, SNMP_ipAddrTable AS ip, IP_MAC AS arp LEFT JOIN MAC_connections AS x ON arp.MAC=x.MAC WHERE s.deviceID=ip.deviceID AND ip.IPadd=arp.IPadd AND arp.time2>$Oldest AND s.sysServices<7 AND s.mtime>$Oldest ORDER BY s.deviceID, arp.time2" ; $sth = $dbh->prepare($SELECT); $sth->execute ; $numDevs = $sth->rows ; if ($numDevs) { $sth->bind_columns(undef, \($deviceID, $IPadd, $MAC, $arp_time, $X_deviceID, $X_ifIndex, $X_n)); while ($sth->fetch) { next unless $MAC ; # no point continuing unless we have a mAC address next if $MAC =~ /^0+$/ ; # skip all 0s MAC addresses (don't think we should get any garbage from IP_MAC?) next unless $MAC =~ /^[0-9a-f]{12}$/ ; # accept only well-formed MAC addresses # record info for device's interfaces: # $IPadd and # should have IPadd: this is from ipAddrTable $device{$deviceID}{MAC}{$MAC}{IPadd} = $IPadd ; # record IP addresses $X_deviceID and $device{$deviceID}{Connx}{$X_deviceID}{$X_ifIndex}{MAC}{$MAC} = $X_n ; # record our MAC adds and nMACs seen on this other device+interface } } $sth->finish; foreach my $dev (keys %device) { foreach my $Xdev (keys %{$device{$dev}{Connx}}) { next if defined $device{$Xdev}{Connx}{$dev} ; # skip if we have something recorded for connection from that device to this next unless $device{$Xdev}{Services} and $device{$Xdev}{Services} <= 6 ; # skip unless other device is router/switch/repeater $device{$Xdev}{Connx}{$dev}{'-'}= defined ; # skip if we have something recorded for connection from that device to this } } my $table = new HTML::Table(-border => 1, width => '100\%', -bgcolor => 'FFFFFF'); my ($row, $col) = (1,1) ; foreach my $dtype (reverse sort keys %device_type) { foreach my $devID (sort {$device{$a}{Name} cmp $device{$b}{Name}} keys %{$device_type{$dtype}}) { next if (($device{$devID}{Services} < 6) and $device{$devID}{DONE}) ; # don't start a tree with a switch which has been included in any other tree little_device_table($table, $row, $col, $device{$devID}) ; # print device info $device{$devID}{DONE} = 1 ; # mark done so we don't try to print this device again my $Xdevs_row = print_connected_devices($table, $row, $col, \%device, $devID) ; my $dev_rowspan = ($Xdevs_row - $row) ; $table->setCellRowSpan($row, $col, $dev_rowspan) if $dev_rowspan > 1 ; $row = $Xdevs_row ; } } print $table->getTable ; print '
', end_html; return 0; } ############################################################################## sub print_connected_devices { my $MAX_HOPS = 5 ; my ($table, $row, $col, $device, @devIDs) = @_ ; my $devID = pop @devIDs ; # @devIDs is list of devices in this chain: get last off list push @devIDs, $devID ; # but put it back again my $max_row = my $X_row = my $if_row = $row ; foreach my $X_devID (sort {($device->{$a}{Name} or "ZZZZZZZZ $a") cmp ($device->{$b}{Name} or "ZZZZZZZZ $b")} keys %{$device->{$devID}{Connx}}) { next unless $device->{$X_devID}{Services} and $device->{$X_devID}{Services} < 7 ; # skip unless connected device is repeater/switch/router next if ($X_devID eq $devIDs[$#devIDs] or $X_devID eq $devIDs[$#devIDs-1]) ; # skip last device already printed in this chain of connections next if scalar @devIDs > $MAX_HOPS ; little_device_table($table, $row, $col+4, $device->{$X_devID}) ; # print connected-device (X_dev) info $device->{$X_devID}{DONE} = 1 ; my $rows_left = device_interconnections($table, $if_row, $col, $device->{$X_devID}, $device->{$devID}, 'left') ; # print connections $if_row += $rows_left ; my $rows_right = device_interconnections($table, $if_row, $col, $device->{$devID}, $device->{$X_devID}, 'right') ; # between dev and X_dev $if_row += $rows_right ; if ($rows_left and $rows_right and ($device->{$X_devID}{Services} < 6 )) # if devices are connected both ways or if connected device is a router { $X_row = print_connected_devices($table, $X_row, $col+4, $device, @devIDs, $X_devID) ; # recurse } # $X_row now holds max+1 row of stuff to the right of X_dev $max_row = $X_row > $if_row ? $X_row : $if_row ; my $rowspan = $max_row - $row ; $table->setCellRowSpan($row, $col+4, $rowspan) if $rowspan > 1 ; $if_row = $X_row = $row = $max_row ; } return $max_row ; } ############################################################################## sub device_interconnections { my ($table, $row, $col, $dev, $Xdev, $direction) = @_ ; my ($c1,$c2,$c3,$lt,$gt) = $direction eq 'right' ? (1,2,3,'','>') : (3,2,1,'<','') ; my $Xif_row = $row ; my $Xif_rowspan = 0 ; foreach my $X_ifX (keys %{$dev->{Connx}{$Xdev->{deviceID}}}) # get connected device's ifIndex { next if $X_ifX eq '-' ; # '-' is marker for dummy connection $table->setCell($Xif_row, $col+$c3, tt($X_ifX . ':' . ($Xdev->{if}{$X_ifX} ? substr($Xdev->{if}{$X_ifX}, -12) : ''))) ; my $if_row = $Xif_row ; foreach my $MAC (keys %{$dev->{Connx}{$Xdev->{deviceID}}{$X_ifX}{MAC}}) { # build list of things to display about this connection my @if = ($MAC) ; # first its MAC address $dev->{MAC}{$MAC}{IPadd} and push @if, $dev->{MAC}{$MAC}{IPadd} ; # then IP address if it has one # then interfaces associated with this MAC: if ($dev->{MAC}{$MAC}{ifs}) { foreach my $ifX (sort {0+$a <=> 0+$b} (@{$dev->{MAC}{$MAC}{ifs}})) # each interface associated with this MAC { push @if, $ifX . ':'. ($dev->{if}{$ifX} ? substr($dev->{if}{$ifX}, -12) : '') ; } } # join it all together with no of MACs of which this was one: $table->setCell($if_row, $col+$c1, tt(join('
', @if))) ; $table->setCell($if_row, $col+$c2, tt($lt . ($dev->{Connx}{$Xdev->{deviceID}}{$X_ifX}{MAC}{$MAC} or '') . $gt)) ; $table->setCellAlign($if_row, $col+$c2,$direction) ; $table->setCellBGColor($if_row, $col+$c1, 'FFFF99') ; $table->setCellBGColor($if_row, $col+$c2, 'FFFF99') ; $table->setCellBGColor($if_row, $col+$c3, 'FFFF99') ; $if_row++ ; } $Xif_rowspan = ($if_row - $Xif_row) ; $table->setCellRowSpan($Xif_row, $col+$c3, $Xif_rowspan) if $Xif_rowspan > 1; $Xif_row = $if_row ; } # done with this connected-ifIndex return $Xif_rowspan ; } ############################################################################## sub little_device_table # called by Query_SNMP_Connections with # $device{$deviceID}{Name} = ($sysName or '') ; # $device{$deviceID}{Location} = ($sysLocation or '') ; # $device{$deviceID}{Services} = $sysServices ; # $device{$deviceID}{mtime} = $mtime ; # # returns table of this info { my ($table, $row, $col, $dev) = @_ ; my %SNMP_deviceType = ( 1 => 'repeater', 2 => 'switch', 6 => 'router', ) ; $table->setCell($row, $col, '
' . $SNMP_deviceType{$dev->{Services}} . '
' . HREF_SNMP($dev->{deviceID}, "deviceID=$dev->{deviceID}") . '
' . '' . substr($dev->{Name},0,10) . '' . '
' . $dev->{Location} . '
' . relDate($dev->{mtime}) . '
' ); $table->setCellBGColor($row, $col, time_colour($dev->{mtime}, '#FFFFFF', '#FFEEEE', '#FFDDDD', '#FFB8B8', '#FFAAAA', '#FF8888')) ; } ############################################################################## sub _little_device_table # called by Query_SNMP_Connections with # $device{$deviceID}{Name} = ($sysName or '') ; # $device{$deviceID}{Location} = ($sysLocation or '') ; # $device{$deviceID}{Services} = $sysServices ; # $device{$deviceID}{mtime} = $mtime ; # # returns table of this info { my $dev = shift or return '' ; my %SNMP_deviceType = ( 1 => 'repeater', 2 => 'switch', 6 => 'router', ) ; my $devtab = html_table_header('width=100%') ; $devtab .= '' ; $devtab .= html_table_cell( $SNMP_deviceType{$dev->{Services}} ) ; $devtab .= html_table_cell( HREF_SNMP($dev->{deviceID}, "deviceID=$dev->{deviceID}"), 'align=right' ) ; $devtab .= "\n" ; $devtab .= '' . html_table_cell( substr($dev->{Name},0,10), 'colspan=2 align=center' ) . "\n" ; $devtab .= '' . html_table_cell( $dev->{Location}, 'colspan=2 align=center' ) . "\n" ; $devtab .= '' . html_table_cell( relDate($dev->{mtime}), 'colspan=2 align=center' ) . "\n" ; $devtab .= html_table_footer() ; } ############################################################################## sub Query_SNMP_VERBOSE { my $ORDER = ''; my $WHERE = ''; if (param('deviceID')) { my $deviceID = param('deviceID') ; if ($deviceID =~ /\d+\.\d+\.\d+\.\d+$/) # IP address? { # get deviceID for this address: $WHERE = 'deviceID=' . deviceID_by_IPadd($deviceID); } else { $WHERE = 'deviceID=' . $deviceID ; } } else { if (param('sysServices')) { $WHERE .= ' sysServices=' . param('sysServices') ; } if (param('ORDER')) { $ORDER = param('ORDER') ; } else { $ORDER = 'sysName' ; } } my %SNMP = SNMPdevices_by_WHERE($WHERE) ; my $numDevs = scalar (keys %SNMP) ; if ($numDevs == 1) # just one device? do verbose report { my $deviceID = (keys %SNMP)[0] ; # first and only device $SNMP{$deviceID}{deviceID} = $deviceID ; return SNMP_device_report(%{$SNMP{$deviceID}}) ; } # (else) many devices: do table of all print HTMLtop('SNMP Query (Verbose):' . ($WHERE and " Where $WHERE") . ($ORDER and " Ordered by $ORDER")) ; unless ($numDevs) { print "no records found", end_html; return 0; } print '
' ; printf qq(

This is the verbose SNMP devices listing: click here for a normal list

), Verbose_URI() ; # get IP addresses for each device: my %IPadds ; foreach my $deviceID (keys %SNMP) { $SNMP{$deviceID}{IPadd} = [ IPadds_by_deviceID($deviceID) ] ; # anonymous array ref # keep list of all IP adds on all devices my $IPadd ; foreach $IPadd (@{$SNMP{$deviceID}{IPadd}}) { $IPadds{$IPadd}=$deviceID; } } print "\n$numDevs devices\n

\n" ; my @devices ; if ($ORDER eq 'IPadd') { my $IP; foreach $IP (IPsort keys %IPadds) { push @devices, $IPadds{$IP} ; } } elsif ($ORDER =~ /^(sysName|sysDescr|sysObjectID|sysObjIDtxt|sysServices|sysLocation|sysContact|UpSinceTime|ifNumber|deviceID)$/) { @devices = (sort {($SNMP{$a}{$ORDER} or '') cmp ($SNMP{$b}{$ORDER} or '')} keys %SNMP) ; } else { @devices = (keys %SNMP) ; } # print devices' SNMP sys info print "
                                                             Sv = sysServices
\n"; print html_table_header('border') ; print html_table_header_row('ID', 'IP address', 'name(s)', 'MAC address', HREF_Vendor(), 'Sv', 'sysName', 'sysLocation', 'sysContact', 'sysDescr', 'Up Since', 'checked at'); foreach my $deviceID (@devices) { # find IP addresses and names, and corresponding MAC addresses for this device my $IPadd ; my $numIPs = 0 ; my @IPtable ; # list of hashrefs of structures of formatted IPadd, names, MAC & vendor codes to print in table my $colour = BGCOLOR($SNMP{$deviceID}{mtime}) ; foreach $IPadd (IPsort @{$SNMP{$deviceID}{IPadd}}) { $numIPs++ ; # count rows my $MAC = MAC_by_IP ($IPadd) ; # get corresponding MAC address push @IPtable, { # compose anonymous hashref add => HREF_IPadd($IPadd) , name => (join (' ', IPnames_by_IPadd($IPadd)) or $EMPTY_CELL) , # names for this address MAC => $MAC , # MAC address vend => HREF_Vendor_by_MAC($MAC) # vendor with link to query } ; } my $IProw ; my $first_row = 1 ; foreach $IProw (@IPtable) { printf qq(\n); $first_row and print html_table_cell( HREF_SNMP($deviceID, "deviceID=$deviceID"), "rowspan=$numIPs $colour") ; print html_table_cell($IProw->{add}, $colour) ; print html_table_cell($IProw->{name}, $colour) ; print html_table_cell(($IProw->{MAC} or $EMPTY_CELL), $colour) ; print html_table_cell(($IProw->{vend} or $EMPTY_CELL), $colour) ; if ($first_row) { print html_table_cell( $SNMP{$deviceID}{sysServices}, "rowspan=$numIPs $colour") ; print html_table_cell( ($SNMP{$deviceID}{sysName} or $EMPTY_CELL), "rowspan=$numIPs $colour") ; print html_table_cell( ($SNMP{$deviceID}{sysLocation} or $EMPTY_CELL), "rowspan=$numIPs $colour") ; print html_table_cell( ($SNMP{$deviceID}{sysContact} or $EMPTY_CELL), "rowspan=$numIPs $colour") ; print html_table_cell( ($SNMP{$deviceID}{sysDescr} or $EMPTY_CELL), "rowspan=$numIPs $colour") ; print html_table_cell( relDate($SNMP{$deviceID}{UpSinceTime},$t_now), "rowspan=$numIPs $colour") ; print html_table_cell( relDate($SNMP{$deviceID}{mtime},$t_now), "rowspan=$numIPs $colour") ; $first_row = 0; } print "\n"; } } print html_table_footer(), end_html; return 0; } ############################################################################## sub SNMP_device_report # given %SNMP table for device, print report { my %SNMP = @_ ; return 1 unless scalar (keys %SNMP) ; print HTMLtop(($SNMP{sysName} or $SNMP{deviceID}) . (param('VERBOSE') ? ' (VERBOSE)' : '')); print '
'; # print sys info print SNMP_sysinfo_table(%SNMP) ; print SNMP_ipAddr_table(%SNMP) ; print device_if_dot1d_html_table($SNMP{deviceID}, param('VERBOSE')) ; # print dot1d table if it exists print "
"; print end_html; return 0; } ############################################################################## sub SNMP_sysinfo_table # given %SNMP table return as html table in verbose format { my %SNMP = @_ ; # general SNMP info for this device my $buffer ; # print sys info $buffer = html_table_header('border') ; $buffer .= html_table_row ( b("sysName"), ($SNMP{sysName} or $EMPTY_CELL) ) ; $buffer .= html_table_row ( b("sysLocation"), ($SNMP{sysLocation} or $EMPTY_CELL) ); $buffer .= html_table_row ( b("sysContact"), ($SNMP{sysContact} or $EMPTY_CELL) ); $buffer .= html_table_row ( b("sysDescr"), ($SNMP{sysDescr} or $EMPTY_CELL) ); my $Obj = $SNMP{sysObjectID}; $Obj .= '
' . $SNMP{sysObjIDtxt} if ($SNMP{sysObjIDtxt}) ; $buffer .= html_table_row ( b("sysObjectID"), $Obj ); $buffer .= html_table_row ( b("sysService"), $SNMP{sysServices} ); $buffer .= html_table_row ( b("Up Since"), nameDate($SNMP{UpSinceTime},$t_now) ); $buffer .= html_table_row ( b("Checked at"), nameDate($SNMP{mtime}, $t_now) ); $buffer .= html_table_row ( b("Device ID"), $SNMP{deviceID} . ' (discovered: ' . nameDate($SNMP{deviceID}) .')' ); $buffer .= html_table_footer() ; } ############################################################################## sub SNMP_ipAddr_table # given %SNMP table return IP addresses table in html format { my %SNMP = @_ ; # general SNMP info for this device return "
Error - no member {deviceID} in SNMP data structure passed to sub SNMP_ipAddr_table
\n" unless $SNMP{deviceID}; my $buffer ; unless ($SNMP{ipAddrTable}) # already have ipAddrTable in structure? { # get snmp ip table from DB: my $sth = $dbh->prepare("SELECT IPadd, ifIndex, NetMask from SNMP_ipAddrTable WHERE deviceID=$SNMP{deviceID}") ; $sth->execute; return ' ' unless ($sth->rows) ; my (%ipAddrTable, $IPadd, $index, $Mask) ; $sth->bind_columns(undef,\($IPadd, $index, $Mask)) ; while ($sth->fetch) { $SNMP{ipAddrTable}{$IPadd} = { NetMask => $Mask, ifIndex => $index } ; } } $buffer .= html_table_header ('border') ; $buffer .= html_table_header_row ('Ix', 'mask', 'IP address', 'name', 'MAC', HREF_Vendor() ) ; foreach my $IPadd (IPsort keys %{$SNMP{ipAddrTable}}) { my $MAC = MAC_by_IP ($IPadd) ; # get corresponding MAC address $buffer .= html_table_row ( $SNMP{ipAddrTable}{$IPadd}{ifIndex}, $SNMP{ipAddrTable}{$IPadd}{NetMask}, $IPadd, (join (' ', IPnames_by_IPadd($IPadd)) or $EMPTY_CELL) , # names for this address ($MAC or $EMPTY_CELL), (HREF_Vendor_by_MAC($MAC) or $EMPTY_CELL) # vendor with link to query ) ; } $buffer .= html_table_footer() ; return $buffer ; } ############################################################################## sub Verbose_URI # return current REQUEST_URI with VERBOSE parameter set to value given (or deleted, if value is undefined) { my $Verbose = shift ; my $URI = $ENV{REQUEST_URI} ; $URI =~ s/&VERBOSE=\d// ; return $URI unless (defined $Verbose) ; return "$URI&VERBOSE=$Verbose" ; } ############################################################################## sub device_if_dot1d_html_table { my $deviceID = shift or return '' ; my $Verbose = shift ; my $buffer ; my %ifTable = device_if_dot1d_table($deviceID, $Verbose) ; return "
no interfaces table found\n
" unless %ifTable ; $buffer .= $Verbose ? (sprintf qq(

The following is the verbose dot1d table: click here for normal list

), Verbose_URI()) : (sprintf qq(

The following is the normal dot1d table showing only interfaces which are up and only devices which seem to be directly connected to them: click here for verbose list

), Verbose_URI(1)) ; # print link to alternate ([non]verbose) page $buffer .= html_table_header('border') ; $buffer .= $Verbose ? html_table_header_row('iX', 'Name', 'PhysAddress', 'Spd', 'State', 'Since', 'MAC', HREF_Vendor(), 'IPadd', 'name(s)', 'seen at', 'users' ) : html_table_header_row('Name', 'Spd', 'State', 'Since', 'MAC', HREF_Vendor(), 'IPadd', 'name(s)', 'seen at', 'users' ) ; foreach my $ifIndex (sort {($a+0)<=>($b+0)} keys %ifTable) { my $state_colour = (defined $ifTable{$ifIndex}{OperStatus} and $ifTable{$ifIndex}{OperStatus} eq 'up') ? $HTML_COLOUR{white} : time_colour ($ifTable{$ifIndex}{LastChangeAt} or 0) ; $state_colour = qq(BGCOLOR="$state_colour") ; $buffer .= "\n"; my @MACs = sort keys %{$ifTable{$ifIndex}{dot1d}} ; my $numMACs = scalar @MACs ; $Verbose and $buffer .= html_table_cell ($ifIndex, "rowspan=$numMACs $state_colour") ; $buffer .= html_table_cell (($ifTable{$ifIndex}{Name} or $EMPTY_CELL), "rowspan=$numMACs $state_colour") ; $Verbose and $buffer .= html_table_cell (($ifTable{$ifIndex}{PhysAddress} or $EMPTY_CELL), "rowspan=$numMACs $state_colour") ; my $Speed = ($ifTable{$ifIndex}{Speed} or $EMPTY_CELL) ; $Speed =~ s/000000$/M/ ; # turn 10000000 into 10M etc $buffer .= html_table_cell ($Speed, "rowspan=$numMACs $state_colour") ; $buffer .= html_table_cell (($ifTable{$ifIndex}{OperStatus} or $EMPTY_CELL), "rowspan=$numMACs $state_colour") ; my $date = relDate($ifTable{$ifIndex}{LastChangedAt}) ; $date =~ s/^\s+$/$EMPTY_CELL/; $buffer .= html_table_cell ($date, "rowspan=$numMACs $state_colour") ; foreach my $MAC (@MACs) { if ($MAC) { my $colour = $ifTable{$ifIndex}{dot1d}{$MAC}{OnThisPort} ? 'white' : $HTML_COLOUR{'pastel_blue'} ; $colour = BGCOLOR ($ifTable{$ifIndex}{dot1d}{$MAC}{mtime}, $colour) ; $buffer .= html_table_cell ($MAC, $colour) ; $buffer .= html_table_cell (HREF_Vendor_by_MAC($MAC, ($ifTable{$ifIndex}{dot1d}{$MAC}{Vendor} or '??')), $colour) ; $buffer .= html_table_cell (HREF_IPadd($ifTable{$ifIndex}{dot1d}{$MAC}{IPadd}), $colour) ; $buffer .= html_table_cell (($ifTable{$ifIndex}{dot1d}{$MAC}{names} or $EMPTY_CELL), $colour) ; $buffer .= html_table_cell (relDate($ifTable{$ifIndex}{dot1d}{$MAC}{mtime}, $t_now), $colour) ; if ($ifTable{$ifIndex}{dot1d}{$MAC}{users}) { my @users = keys (%{$ifTable{$ifIndex}{dot1d}{$MAC}{users}}) ; # list f all users my $user = HREF_Username_Query($ifTable{$ifIndex}{dot1d}{$MAC}{last_user}) ; scalar @users > 1 and $user .= '+' . (scalar @users - 1); $buffer .= html_table_cell ($user, $colour) ; } else { $buffer .= html_table_cell ($EMPTY_CELL, $colour) ; } } else { $buffer .= ' '; } $buffer .= "\n"; } $buffer .= "\n"; } $buffer .= html_table_footer() ; } ############################################################################## sub device_if_dot1d_table # get the SNMP dot1d table { my $deviceID = shift or return '' ; my $Verbose = shift ; my $buffer ; my $SELECT = "SELECT s.ifIndex, s.Name, s.Speed, s.PhysAddress, s.AdminStatus, s.OperStatus, s.LastChangeAt, d.nMACs, d.MAC, d.mtime, v.Vendor, a.IPadd, n.names, u.username, u.mtime, x.deviceID, x.ifIndex FROM SNMP_ifTable AS s LEFT JOIN dot1d AS d ON s.deviceID=d.deviceID AND s.ifIndex=d.ifIndex LEFT JOIN MAC_connections AS x ON d.MAC = x.MAC LEFT JOIN Vendor_MAC AS v ON v.Code = left(d.MAC,6) LEFT JOIN IP_MAC AS a ON d.MAC = a.MAC LEFT JOIN IP_hostnames AS n ON a.IPadd=n.IPadd LEFT JOIN IP_MAC_user AS u ON a.IPadd = u.IPadd AND a.MAC=u.MAC AND u.mtime > a.time1 WHERE " ; $SELECT .= "d.deviceID=x.deviceID AND d.ifIndex=x.ifIndex AND " unless $Verbose ; $SELECT .= "s.deviceID=$deviceID ORDER BY s.ifIndex, d.mtime, a.time2, u.mtime" ; my $sth=$dbh->prepare($SELECT); $sth->execute; unless ($sth->rows) { return device_if_dot1d_table($deviceID, 1) unless $Verbose ; # try verbose # (else) - already tied versbose return () ; } my (%ifTable, $ifIndex, $Name, $Speed, $PhysAddress, $AdminStatus, $OperStatus, $LastChangedAt, $nMACs, $MAC, $MAC_mtime, $Vendor, $IPadd, $names, $username, $user_mtime, $X_deviceID, $X_ifIndex) ; $sth->bind_columns(undef,\($ifIndex, $Name, $Speed, $PhysAddress, $AdminStatus, $OperStatus, $LastChangedAt, $nMACs, $MAC, $MAC_mtime, $Vendor, $IPadd, $names, $username, $user_mtime, $X_deviceID, $X_ifIndex)) ; while ($sth->fetch) { $ifTable{$ifIndex}{Name} = $Name ; $ifTable{$ifIndex}{Speed} = $Speed ; $ifTable{$ifIndex}{PhysAddress} = $PhysAddress ; $ifTable{$ifIndex}{AdminStatus} = $AdminStatus ; $ifTable{$ifIndex}{OperStatus} = $OperStatus ; $ifTable{$ifIndex}{LastChangedAt} = $LastChangedAt ; if (defined $nMACs) # anything seen on this port in dot1d table? { # record for each MAC seen on port: latest time returned last so eventual record will be most recent $ifTable{$ifIndex}{dot1d}{$MAC}{mtime} = $MAC_mtime ; $ifTable{$ifIndex}{dot1d}{$MAC}{nMACs} = $nMACs ; $ifTable{$ifIndex}{dot1d}{$MAC}{Vendor} = $Vendor ; $ifTable{$ifIndex}{dot1d}{$MAC}{IPadd} = $IPadd ; $ifTable{$ifIndex}{dot1d}{$MAC}{names} = $names ; $ifTable{$ifIndex}{dot1d}{$MAC}{OnThisPort} = ((defined($X_deviceID) and defined($X_ifIndex) and ($X_deviceID == $deviceID) and ($X_ifIndex == $ifIndex)) ? 1 : 0) ; if ($username) { $ifTable{$ifIndex}{dot1d}{$MAC}{users}{$username} = $user_mtime ; # get a record for each username seen, with latest time seen $ifTable{$ifIndex}{dot1d}{$MAC}{last_user} = $username ; # get a record for each username seen, with latest time seen } } # (else) should only get this in verbose mode: interfaces with no MACs seen in dot1d table } return %ifTable ; } ############################################################################## sub device_interfaces_html_table # given device ID return SNMP interfaces table in HTML format { my $deviceID = shift or return '' ; my $buffer ; $buffer = html_table_header('border') ; $buffer .= html_table_header_row ('iX', 'Name', 'Type', 'Speed', 'PhysAddress', HREF_Vendor(), 'AdminStatus', 'OperStatus', 'LastChangeAt' ) ; my %ifTable = ifTable_by_deviceID($deviceID) ; foreach my $ifIndex (sort keys %ifTable) { # my $colour = $ifTable{$ifIndex}{OperStatus} eq 'up' ? $HTML_COLOUR{white} : time_colour ($ifTable{$ifIndex}{LastChangeAt}) ; my $colour = $ifTable{$ifIndex}{OperStatus} eq 'up' ? $HTML_COLOUR{white} : $HTML_COLOUR{mustard} ; $colour = qq(BGCOLOR="$colour") ; my $Speed = ($ifTable{$ifIndex}{'Speed'} or $EMPTY_CELL) ; $Speed =~ s/000000$/M/ ; $buffer .= html_table_cell ($ifIndex, $colour) ; # $buffer .= html_table_cell ($ifTable{$ifIndex}{Descr}, $colour) ; $buffer .= html_table_cell ($ifTable{$ifIndex}{Name}, $colour) ; $buffer .= html_table_cell ($ifTable{$ifIndex}{Type}, $colour) ; $buffer .= html_table_cell ($Speed, $colour) ; $buffer .= html_table_cell (($ifTable{$ifIndex}{'PhysAddress'} or $EMPTY_CELL), $colour) ; $buffer .= html_table_cell (HREF_Vendor_by_MAC($ifTable{$ifIndex}{PhysAddress}), $colour) ; $buffer .= html_table_cell ($ifTable{$ifIndex}{AdminStatus}, $colour) ; $buffer .= html_table_cell ($ifTable{$ifIndex}{OperStatus}, $colour) ; $buffer .= html_table_cell (relDate($ifTable{$ifIndex}{LastChangeAt}), $colour) ; $buffer .= "\n"; } $buffer .= html_table_footer() ; } ############################################################################## sub Vendor_Query { print HTMLtop('Vendor Query'); my %db_Maxlen = ('Vendor' => 8, 'Vendor_name' => 90, 'Comment' => 70 ) ; # print "\n

Vendor query

" ; my $New = 0; # create new record my $SELECT = "SELECT * FROM Vendor_MAC" ; my $Code = '' ; if (param('Code')) { $Code = param('Code') ; $SELECT .= sprintf " WHERE Code ='%s'", $Code ; } my $sth = $dbh->prepare($SELECT) ; $sth->execute; my $db_recs = $sth->rows ; my ($db_Code, $db_Vendor, $db_Vendor_name, $db_Comment) ; if ($db_recs) # record(s) exists? { $sth->bind_columns(undef, \($db_Code, $db_Vendor, $db_Vendor_name, $db_Comment)) ; } else # we may have been given a non-existing vendor code to match { $New = $Code ; # if Code specified but no records found then set flag to create new record ($db_Code, $db_Vendor, $db_Vendor_name, $db_Comment) = ($Code, '', '', '') ; } if ($GROUP ne 'noc') # non-noc users get a table of data { printf "
%u records

", $db_recs ; if ($db_recs) { print html_table_header('border') , html_table_header_row ( 'Code', 'Vendor', 'Vendor name', 'comment' ); while ($sth->fetch) { print html_table_row ($db_Code, $db_Vendor, $db_Vendor_name, $db_Comment) ; } print html_table_footer() ; } print end_html; return 0; } # (else: user == noc) noc users get table of editable fields my $COMMIT='Commit Changes' ; my (%Changes, @db_updates) ; if (param('VENDOR') and (param('VENDOR') eq $COMMIT)) { my $key; foreach $key (param()) { if ($key =~ /^(Vendor|Vendor_name|Comment):(\w+)$/) { $Changes{$2}{$1}=param($key); } } } printf "

%u records

", $sth->rows ; print start_form() , html_table_header('border') , html_table_header_row ( 'Code', 'Vendor', 'Vendor name', 'comment' ); while ($New or $sth->fetch) { # if we have edits to commit, check against current record my $update=0; if (defined ($Changes{$db_Code}{'Vendor'}) and ($Changes{$db_Code}{'Vendor'} ne $db_Vendor)) { $db_Vendor = $Changes{$db_Code}{'Vendor'} ; $update=1; } if (defined ($Changes{$db_Code}{'Vendor_name'}) and ($Changes{$db_Code}{'Vendor_name'} ne $db_Vendor_name)) { $db_Vendor_name = $Changes{$db_Code}{'Vendor_name'} ; $update=1; } if (defined ($Changes{$db_Code}{'Comment'}) and ($Changes{$db_Code}{'Comment'} ne $db_Comment)) { $db_Comment = $Changes{$db_Code}{'Comment'} ; $update=1; } if ($update) # any changes? { if ($New) { $update = sprintf "INSERT INTO Vendor_MAC VALUES ('%s',%s,%s,%s)", $db_Code , $dbh->quote($db_Vendor) , $dbh->quote($db_Vendor_name) , $dbh->quote($db_Comment) ; } else { $update = sprintf "UPDATE Vendor_MAC SET Vendor=%s,Vendor_name=%s,Comment=%s WHERE Code='%s'", $dbh->quote($db_Vendor) , $dbh->quote($db_Vendor_name) , $dbh->quote($db_Comment), $db_Code ; } push @db_updates, $update ; } print html_table_row ($db_Code, textfield ( -name => "Vendor:$db_Code", -default => $db_Vendor, -size => 8, -maxlength => $db_Maxlen{'Vendor'} , ), textfield ( -name => "Vendor_name:$db_Code", -default => $db_Vendor_name, -size => 60, -maxlength => $db_Maxlen{'Vendor_name'} , ), textfield ( -name => "Comment:$db_Code", -default => $db_Comment, -size => 40, -maxlength => $db_Maxlen{'Comment'} , ) ) ; $New = 0 ; # clear create-new-record flag in case it was set } print html_table_footer() ; if (param('Code')) {print hidden(-name => 'Code', -default => param('Code') ) ;} print html_table_header(), html_table_row ( submit ( -name => 'VENDOR', -value => $COMMIT ), reset() ) , html_table_footer(), end_form() ; # if we have db updates to make, do them now if (scalar @db_updates) { my $update; foreach $update (@db_updates) { $dbh->do ($update); } } print end_html; return 0; } ############################################################################## sub HREF_sysServices # return sysServices for given device, with href to any appropriate tables { my ($devID, $Services) = @_ ; return $EMPTY_CELL unless $Services ; return $Services . ' ' . HREF_SNMPdevice($devID) if ($Services == 2) ; return $Services ; } ############################################################################## sub HREF_SNMPdevice # given deviceID and optionally a text string (defaults to 'SNMP'), # return text string hyperlinked to Query for SNMP info (including dot1d table if it exists) for device of given ID { my $devID = shift or return ''; my $string = (shift or 'SNMP') ; # use 'SNMP' if no string supplied # sprintf qq(%s), $MY_HREF, $devID, $string ; return qq($string) ; } ############################################################################## sub HREF_IPadd # return IP address hyperlinked to Query for that address { my $IPadd = shift or return (' ' x 15) ; sprintf qq(%-15s), $MY_HREF, $IPadd, $IPadd ; } ############################################################################## sub HREF_SNMP # return string 'SNMP' hyperlinked to Query for SNMP info page { my $name = (shift or 'SNMP') ; my $var = (shift or ''); $var = "&$var" if $var; sprintf qq(%s) , $MY_HREF, $var, $name ; } ############################################################################## sub HREF_Vendor # return string 'Vendor' hyperlinked to Vendor codes page { sprintf qq( Vendor ) , $MY_HREF ; } ############################################################################## sub HREF_Vendor_by_MAC # given a MAC address and optionally also the corresponding Vendor code # (if not supplied look it up) # return Vendor code hyperlinked to Query for corresponding Vendor code; # if Vendor code not found then hyperlink to Query only if user is 'noc' # (noc may edit in a name for this code) { my ($MAC, $Vendor) = @_ ; $MAC or return (' ' x 8 ) ; $Vendor = Vendor_by_MAC($MAC) unless defined $Vendor; if ($Vendor) { return sprintf qq(%-8s), $MAC, $Vendor ; } elsif ($GROUP eq 'noc') { return sprintf qq(%s), $MAC, '   ??   ' ; } return '   ??   ' ; } ############################################################################## sub HREF_Username_Query # given a username return link to Query for user { # my $username = shift or return ' 'x8 ; # so this sub can be used simply to return a string of whitespace of correct width my $username = shift or return ' 'x8 ; # so this sub can be used simply to return a string of whitespace of correct width return sprintf qq(%-8s), $username, $username ; } ############################################################################## sub users_by_IP_MAC { my ($IP, $MAC) = @_ ; return () unless $IP; my $sth = $dbh->prepare("SELECT username, mtime FROM IP_MAC_user WHERE IPadd='$IP' and MAC='$MAC'") ; $sth->execute; return () unless ($sth->rows) ; my (@users, $user, $t); $sth->bind_columns (undef, \($user, $t)) ; while ($sth->fetch) { push @users, $user, $t ; } return @users; # effectively a hash of user => time } ############################################################################## sub filesys_exported # is filesystem specified exported to any of listed hostnames # (generally aliases for same machine) { my ($export, @names) = @_ ; return '' unless $export ; return '' unless (scalar @names) ; my $name ; foreach $name (@names) # for each host name { my $sth = $dbh->prepare("SELECT filesystem FROM export_hosts WHERE hostname='$name'") ; $sth->execute or die $sth->errstr; my $fs ; $sth->bind_columns(undef, \($fs) ); while ($sth->fetch) # for each filesystem exported to this hostname { # does the directory match the exported filesystem? return 1 if ($export =~ m|$fs|) } } return '' ; } ############################################################################## sub search_form { my $action = (shift or $MY_HREF) ; '

' . start_form(-method => 'GET', -action => $action) . 'Enter IP address, hostname, or username (or part of one) or real name:
' . textfield('search') . '
and press <RETURN> to query(s)' . end_form . '
' . hr . "\n" ; } ############################################################################## sub print_index { my $group = shift ; my $buffer = search_form() ; # template file from which to build groups' individual home.html pages: # file to build index from my $INDEX = $CONFIG{PAGE}{TEMPLATE} ; unless (open (INDEX, $INDEX)) { $buffer .= "

*** ERROR ***

Could not open $INDEX

$!\n" . end_html ; return 0; } my ($grps,$text) ; foreach () { next if (/^\s*$/) ; if (($grps,$text) = /^\{(\S+)\}\s*(.*)/) # something in {} at start of line { next unless ($grps =~ /\b$group\b/) ; # is our group mentioned ? } else { $text = $_; } if (/]+)>(.*)/) # 'match' directive { $text = $2; next unless (eval $1) ; } $buffer .= $text . "\n"; } close INDEX ; $buffer .= end_html ; } ############################################################################## sub validate_user # in CGI mode, either returns tag of user group, determined from directory in which script is invoked # or, if not in any user group directory, prints a page of links to group pages # In cli mode prints this gateway page plus index pages for each group. { # read structure of ACCESS_GROUPS table from config # (using home-brew My_Config module: a better module would give us this structure already cooked) my @dirs ; foreach my $tag (keys %{$CONFIG{ACCESS_GROUPS}}) { ($ACCESS_GROUPS{$tag}{dir}, $ACCESS_GROUPS{$tag}{name}, $ACCESS_GROUPS{$tag}{descr} ) = split /\t/, $CONFIG{ACCESS_GROUPS}{$tag} ; push @dirs, $ACCESS_GROUPS{$tag}{dir} ; } # look for any of our ACCESS_GROUPS users' dirs in path to ourself to determine in which user directory we were invoked # (and thus, relying on web server's .htaccess mechanism, which group of users we are authenticated as being a member of) if ($CGI) { my ($SubDir) = ($PROGPATH =~ m|.*/([^/]+)/$|) ; # get sub-dir in which this script found foreach my $tag (keys %ACCESS_GROUPS) { $ACCESS_GROUPS{$tag}{dir} eq $SubDir and return $tag ; } } # (else) either cli mode or CGI but not in group subdir my $fh = new FileHandle $CGI ? '>-' : ">$CONFIG{PAGE}{GATEWAY}" or die "Could not open $CONFIG{PAGE}{GATEWAY} - $!"; $CGI and print header ; print $fh start_html("myNMS gateway page"), "

myNMS

Please select the link to the appropriate pages

", html_table_header('border cellpadding=8') ; foreach my $group (keys %ACCESS_GROUPS) { my $dir = $ACCESS_GROUPS{$group}{dir} ; my $name = $ACCESS_GROUPS{$group}{name}; print $fh html_table_row ( qq(index page), qq(frames version), $ACCESS_GROUPS{$group}{descr} ) ; next if $CGI ; # cli mode: generate per-group index pages, with frames and non-frames versions my $page_head = HTMLtop{ -title => "INDEX [$ACCESS_GROUPS{$group}{name}]", -FRAME_LINK => qq(frames\n), }; my $page_index .= print_index($group) ; my $xfh = new FileHandle ">$CONFIG{DIR}{WWW}/$dir/$CONFIG{PAGE}{QUERY}" or die "Could not open $CONFIG{DIR}{WWW}/$dir/$CONFIG{PAGE}{QUERY} - $!"; print $xfh $page_head . $page_index ; close $xfh ; $xfh = new FileHandle ">$CONFIG{DIR}{WWW}/$dir/$CONFIG{PAGE}{FRAME_QUERY}" or die "Could not open $CONFIG{DIR}{WWW}/$dir/$CONFIG{PAGE}{FRAME_QUERY} - $!"; $page_head = HTMLtop{ -title => "INDEX [$ACCESS_GROUPS{$group}{name}] (frames)", -FRAME_LINK => qq(no frames\n), -TARGET => $CONFIG{PAGE}{FRAME_TARGET}, }; # convert to for frame version # $page_index =~ s///ig ; print $xfh $page_head . $page_index ; close $xfh ; } print $fh '

', html_table_footer(), end_html; return $CGI ? 0 : 1 ; # in cli mode we may be being called to do something else as well } ############################################################################## __END__