#!/usr/bin/perl -w

# snmp-ifOperStatus.pl
# pod at tail

use strict;
use SNMP;
use Time::localtime;
use Spreadsheet::WriteExcel;

my $comm    = 'public';
my $swalk   = '/usr/bin/snmpwalk';
my @interfs = (
   'ifAdminStatus',
   'ifOperStatus',
   'ifDescr',
   );
my @sys  = (
   'sysName.0',
   'sysLocation.0',
   'sysUpTime.0',
   );
my %file = (
   in   => 'ifOperStatus.in',
   tmp  => 'ifOperStatus.tmp',
   csv  => 'ifOperStatus.csv',
   xls  => 'ifOperStatus.xls',
   );
my @unlink = (
   $file{tmp},
   $file{csv},
   $file{xls},
   );


# preliminaries and opening message
# files writable only by this user
# human-readable sysUpTime
umask oct 133;
$SNMP::use_sprint_value = 1;
print "\nStarting $0\n";


# read list of target devices from file or command line
unless (@ARGV) {
   print ("  No targets given at command-line, looking for input file.\n");
   unless (-r $file{in} && -T _) {
      print ("Error reading input file \"$file{in}\": $!\n\n");
      &USAGE();
      }
   @ARGV = $file{in};
   print ("  Reading input file $file{in}\n");
   chomp (@ARGV = <>);
   }


# sanity-check input file
# "exit" instead of "die" so no error to console
print "  Validating target list...\n";
foreach my $nonsane (@ARGV) {
   chomp $nonsane;
   print ("   $nonsane\n");
   unless ($nonsane =~ (/^(\w|-|\.)+$/)) {
      print(
         "\n  Ack - \"$nonsane\" is improperly formatted.\n",
         "  Only alphanumeric, underscore, dash and dot allowed.\n\n",
         "  Edit $file{in} or re-enter targets to remove puctuation,",
         "blank lines and/or blank spaces.\n\n",
         );
      exit;
      }
   }
   print ("  Specified targets names all valid.\n");


# delete pre-existing out/tmp files
foreach my $file(@unlink) {
   &UNLINK($file, 'verbose');
   }


# Write header to output file
open (CSV, ">$file{csv}") or die "Error opening $file{csv}: $!";
print CSV "date,time,device,hostname,location,uptime,total,active,inactive,disabled\n"
   or die "Error printing to $file{csv}: $!";
close (CSV) or die "Cannot close $file{csv}: $!";


# SNMP query target devices
print ("  Target devices queried:\n");
foreach my $dest (@ARGV) {
   print ("   $dest   ");
   # zero-out tmpfile, plus reset counters
   &UNLINK($file{tmp}, 'quiet');
   my %flag     = (
      VLAN               => 0,
      sc0                => 0,
      sl0                => 0,
      me1                => 0,
      'vlan Router'      => 0,
      Null               => 0,
      Loopback           => 0,
      Controller         => 0,
      'Port-channel'     => 0,
      'without GBIC'     => 0,
      'long haul'        => 0,
      ifOperStatus_up    => 0,
      ifOperStatus_down  => 0,
      ifAdminStatus_up   => 0,
      ifAdminStatus_down => 0,
      );

   # query target devices for port info using system call to "snmpwalk"
   open (TMP, ">$file{tmp}") or die "Can't open $file{tmp} for WO: $!";
   foreach my $interf (@interfs) {
      my $mibwalkinterf  = `$swalk $dest $comm $interf`;
      print (TMP "$mibwalkinterf") or die "Error printing to $file{tmp}: $!";
      }
   print (TMP "\n") or die "Error printing to $file{tmp}: $!";
   close (TMP) or die "Error closing $file{tmp}: $!";
   open (TMP, "<$file{tmp}") or die "Error opening $file{tmp} for RO: $!";
   open (CSV, ">>$file{csv}") or die "Error opening $file{csv} for append: $!";

   # Parse tmp file and increment counters for each hit, then do the math
   # substitution first to make matching cleaner
   while (<TMP>) {
      s/\.\d+\s*=\s*/_/;
      $flag{$1}++ if /(VLAN|sc0|sl0|me1|vlan Router|Null|Loopback)/;
      $flag{$1}++ if /(Controller|Port-channel|without GBIC)/;
      $flag{$1}++ if /(longhaul|if(?:Oper|Admin)Status_(?:up|down))/;
      }
   my $nonport = (
      $flag{VLAN} + $flag{Loopback}       +
      $flag{sc0}  + $flag{Controller}     +
      $flag{sl0}  + $flag{'vlan Router'}  +
      $flag{me1}  + $flag{'Port-channel'} +
      $flag{Null}
      );
   # old, incorrect math:
   # my $live  = ( $flag{ifOperStatus_up} + $nonport );
   # my $total = ($flag{ifOperStatus_up}-$nonport+$flag{ifOperStatus_down});
   # new, correct math:
   my $live  = $flag{ifOperStatus_up} - $nonport;
   my $total = ($live + $flag{ifOperStatus_down});

   # query target devices for system info using SNMP.pm
   &PCTIME();
   print (CSV "$dest,");
   print ("   $dest\n");
   my $sess = new SNMP::Session(DestHost => "$dest", Community => "$comm");
   foreach my $sys (@sys) {
      if (my $val = $sess->get("$sys")) {
         print (CSV "$val") or die "Error printing to $file{csv}: $!";
         }
      print (CSV ',') or die "Error printing to $file{csv}: $!";
      }
   print CSV "$total,$live,$flag{ifOperStatus_down},$flag{ifAdminStatus_down}\n"
      or die "Error printing to $file{csv}: $!";
   close (TMP) or die "Error closing $file{tmp}: $!";
   close (CSV) or die "Error closing $file{csv}: $!";
   }


# Create Excel binary format outfile
print ('  Munging csv outfile to xls binary format - ');
open (CSVFILE, $file{csv}) or die "Error opening $file{csv}: $!";
my $workbook  = Spreadsheet::WriteExcel -> new($file{xls});
my $worksheet = $workbook -> addworksheet();
my $row      = 0;
while (<CSVFILE>) {
   chomp;
   my @field = split(',', $_);
   my $column = 0;
   foreach my $token (@field) {
      $worksheet -> write($row, $column, $token);
      $column++;
      }
   $row++;
   }
my $format1 = $workbook -> addformat();
   $format1 -> set_bold();
   $format1 -> set_align('center');
   $format1 -> set_bg_color('tan');
   $format1 -> set_border();
$worksheet -> set_row(0, undef, $format1);
$workbook  -> close() or die "Error closing $workbook: $!";
print ("done.\n");


# Wrap it all up
&UNLINK($file{tmp}, 'verbose');
print ("Completed run of $0.\nResults at $file{csv} and $file{xls}\n\n");
exit;


###########################################################################
# localtime of host running script (not of target device)
sub PCTIME {
   printf CSV "%d-%d-%d,%d:%d:%d,",
      localtime -> mon()+1,
      localtime -> mday(),
      localtime -> year()+1900,
      localtime -> hour(),
      localtime -> min(),
      localtime -> sec(),
   ;
   }
###########################################################################
# cleanup temp files when done with them
sub UNLINK {
   my $file = $_[0];
   my $echo = $_[1];
   if (-e $file && -w _) {
      print "  Unlinking $file..."        if ($echo eq 'verbose');
      unlink $file or die "Error unlinking $file: $!";
      print "  *poof*\n"               if ($echo eq 'verbose');
      }
   }
###########################################################################
# don't really need to es'plain this one
sub USAGE {
print <<EOF

Usage : $0 device1 device2...<enter>
   or : $0 <enter>
      where $file{in} is textfile listing device IP address or DNS name, one per line.
perldoc $0 for a few more details.

EOF
   ;
   exit;
   }
###########################################################################


=head1 Name

snmp-ifOperStatus.pl

=head1 Summary

Query SNMP-enabled devices for interfaces up/down status and for
couple system info items.  I used CPAN SNMP module because it provides
command-line SNMP tools and allows MIB variable access by name.  Hope
I didn't waste my time skipping Net::SNMP + SNMP::MIB::Compiler.

Comments and critique are very much welcomed.

=head1 Usage

 snmp-ifOperStatus.pl routerA switch2 deviceIII
    will query these devices for port status.

 snmp-ifOperStatus.pl
    with no arguments will read $file{in} for list of devices to query
    $file{in} would be text file that looks like so:
       routerA
       switch2
       deviceIII
    but no leading/trailing spaces, and no blank lines.

=head1 Requirements (Debian)

 binutils
 gcc
 snmp           (UCD SNMP apps)
 libsnmp4.1     (UCD SNMP library)
 libsnmp4.1-dev (UCD SNMP developement files)
 SNMP           (CPAN module)

=head1 Optional (Debian)

 gcc-doc
 binutils-doc
 snmpd        (UCD SNMP agent)

=head1 Resources

 Perl Monks        www.perlmonks.org
 UCD SNMP          ucd-snmp.ucdavis.edu
 SNMP module       search.cpan.org/search?dist=SNMP
 Excel module      search.cpan.org/search?dist=Spreadsheet-WriteExcel
 Debian GNU/Linux  www.debian.org

 UCD-SNMP command-line syntax
    snmpwalk device community mib_var

 SNMP.pm syntax to query one MIB variable at one host
    my $sess = new SNMP::Session(DestHost=>'localhost', Community=>'public');
    my $val  = $sess->get('sysDescr.0');
    print "$val\n";

=head1 Tested

 with:
    Perl 5.00503 on Debian 2.2 "Espy"
 against:
    Cisco 2916/24XL  - IOS 11.2
          3524/48    - IOS 12.0
          2948G      - CatOS 4.5
          4000       - CatOS 5.5
          5000, 6000 - CatOS 4.5, 5.3

=head1 Updated
 2001-07-10  13:30
   Correct calculation errors for (live|total) ports. 
 2001-05-07  10:00
   Reformat code for 80 character/line.
     (with a couple aggrevating exceptions)
 2001-04-17  11:15
   Insignificant tweaks.
 2001-04-16
   Add Excel format in addition to csv outfile.
 2001-04-10
   Add sanity-check of target names.
   Move prior-file-unlink code to *after* infile/targets checks.
 2001-04-09
   Assign keys and initial values when %flag first declared.
   Remove sysDescr.0 queries - do with separate Net::Telnet::Cisco script.
   Simplify @tmpfiles to $file{tmp} and <TMP>, since no sysDescr tmpfile.
   Call as "$flag{key}" instead of assign individual scalars for each key.
   Change global vars $sys and $var to lexical.
   Replace multiple scalars for files with %file hash.
 2001-04-04
   Fix uninitialized value errors by re-fitting clearflags section.
   Parse sysDescr tmpfile for each target instead of all at end.
   Un-subroutine clearing+setting of $flag keys
      to eliminate bunch more global vars.
   Replace individual $file{tmp}s with @tmpfiles.
 2001-04-03
   Add parsing of sysDescr for wanted text.
   Add &UNLINK() to reduce reduntant code.
   Add sysDescr.0 query with separate outfile.
   Un-subroutine to reduce unecessary global vars.
   Consistant indenting.
 2001-03-29
   Remove unecessary quotes.
   Change double to single quotes for strings.
 2001-03-20
   Add umask.
 2000-11-09
   $nonport to fixed innacurate results.
   MIB query for hostname, sysLocation.
   withoutGBIC, longhaul.
   timestamp for each target.
   human-readable sysUpTime.
   $flag{$1}++ to count matches.
 2000-10-13
   Initial working code.

=head1 ToDos

 "-i infile -o outfile -s snmpROstring" with Getopt::Long.
 Use File::Temp instead of $file{tmp}.
 Lock output file.
 Capture errors to snmp-ifOperStatus.log (make STDOUT "hot"?).
 Use CPAN SNMP.pm or Net::Snmp  instead of system call to "snmpwalk" to query for port info.
     then can combine elements of @sys and @interfs into one array of @mibvars.
     why does SNMP.pm only do ifOperStatus for individual interfaces, and not .0 for all?
 Figure out UCD SNMP Perl/Tk MIB browser - looks useful.

=head1 Author

 ybiC

=head1 Credits

 Thanks to swiftone, geektron, nedv and arturo for suggestions and critiques,
 and BigGuy for review of Spreadsheet::WriteExcel,
 and jmcnamara for *writing* S:WE,
 and vroom, of course for PM.

=cut