#!/usr/bin/perl -w

# Script Name: ciscowatcher.pl
# Author:      Ray Burkholder
# Email:       ray@oneunified.net
# Provided under GPL 2 License.
# No warranties express or implied supplied.

# Template based upon script  
#   from http://www.sendmail.org/~ca/email/examples/popauther.pl

# Makes a Fifo called /var/ciscowatcher.fifo and reads from that Fifo 
# Place the following in syslogd.conf to log to the fifo from syslog. 
#    local7.*                        |/var/ciscowatcher.fifo
#

# Error handling based upon http://www.perl.com/pub/a/2002/11/14/exception.html

use strict;
use DBI;
use Error qw(:try);
use Net::SMTP;

my $fifo = "/var/ciscowatcher.fifo";
my $watcherlog = "/var/log/ciscowatcher.log";
my $ciscowatcherpidfile = "/var/run/cisco.watcher.pid";
my $line;
my $ok2emitline = 0;
my @devices;

my %mailinfo = (
  to => 'root@localhost',
  from => 'root@localhost',
  server => '127.0.0.1',
  helo => 'slbmin04mc03.ntlan.bm'
  );

my $sDbConn=q{dbi:Pg:dbname=oneunified};
my $sDbUser = q{oneunified};
my $sDbPass = q{oneunified};

my $hDB = DBI->connect( $sDbConn, $sDbUser, $sDbPass, { RaiseError => 1, AutoCommit => 1 } );

sub parsetime($) {
  my ($time) = @_;  # string like:  17:03:39.321 AST Mon Nov 27 2006
  my $r;
  $time =~ /(\d+:\d+:\d+\.\d+) +(\S+) +\S+ +(\S+) +(\d+) +(\d+)/;
  $r = $5 . '-' . $3 . '-' . $4 . ' ' . $1 . ' ' . $2;
  return $r;
}


sub handler {
    my $sig = @_;
    $hDB->disconnect;
    close(FIFO);
    close(LOG);
    exit(0);
}

sub sendmail {
  my ( $mail, $device, $datetime, $messagetype, $line ) = @_;
#      \hash

  # http://search.cpan.org/~gbarr/libnet-1.19/Net/SMTP.pm
  my $smtp = Net::SMTP->new( $$mail{server},
                          Hello => $$mail{helo},
                          Timeout => 10,
                          Debug => 0
                         );
  $smtp -> mail( $$mail{from} );
  $smtp -> to( $$mail{to} );
  $smtp -> data();
  $smtp -> datasend( "To: " . $$mail{to} . "\n" );
  $smtp -> datasend( "Subject: [slbmin04mc03] $device $messagetype\n" );
  $smtp -> datasend( "\n" );
  $smtp -> datasend( "Syslog timestamp: $datetime\n" );
  $smtp -> datasend( $line . "\n" );
  $smtp -> dataend();
  $smtp -> quit;
print "sent mail\n";
}


while(1) {
    unless( -p $fifo) {
	unlink $fifo;
	system("mkfifo -m 644 $fifo") && die "Can't mkfifo $fifo: $!";
    }
    open(FIFO, "< $fifo");
    open(LOG,">>$watcherlog") || die("Can't open $watcherlog");
    print LOG "\n\$date Starting log for cisco.watcher at pid $$\n";
    select(LOG);
    $| = 1;
    select(STDOUT);
    $| = 1;
    $SIG{'INT'} = 'handler';
    $SIG{'QUIT'} = 'handler';
    $SIG{'KILL'} = 'handler';
    open(PID,">$ciscowatcherpidfile ");
    print PID "$$\n";
    close(PID);
    LOGLINE: while($line = <FIFO>) {
      $ok2emitline = 1;
      my @sections = split( /: /, $line );
#        print join( '|', @sections );
	# leading / trailing space stripping:
	# http://www.wdvl.com/Authoring/Languages/Perl/PerlfortheWeb/pattern_matching.html
      my @deviceinfo = split( / +/, $sections[0] );
      my $device = $deviceinfo[3];
      if ( 2 > $#sections ) {
        print "** ignored $line";
        next LOGLINE;
      }
      my $syslogdatetime = $sections[1];
      try{
	my $messagetype = $sections[2];
#	my $type = $sections[ 
        
        if ( $messagetype =~ 'DHCPD' ) { 
          $ok2emitline = 0;
        };  # ignore DHCPD lines
     

	if ( $device =~ /^ng/ ) {
 	  if ( $messagetype eq '%CONTROLLER-5-UPDOWN' ) {
	    $ok2emitline = 1;
            my ( $rv, $rc, $row_hash, $sth, $rows );
            my @pieces = split( /,/, $sections[3] );
            $sth = $hDB->prepare( q{select count(*) from controllerstatus where device=? and interface=?} );
            $rv = $sth->execute( $device, $pieces[0] );
            my $count = 0;
            if ( $rv ) {
              $sth->bind_columns( \$count );
              $rc = $sth->fetch();
              $sth->finish;
            }            
            if ( 0 == $count ) {
              $hDB->do( q{insert into controllerstatus ( device, interface, status, timeupdated, count ) values (?,?,?,'now', 1) }, 
                undef, ( $device, ,$pieces[0], $pieces[1] ) );
            }
            else {
              $hDB->do( q{update controllerstatus set status=?, timeupdated='now', count=count+1 where device=? and interface=? },
                undef, ( $pieces[1], $device, $pieces[0] ) );
            }
 	    my $controllerstatus = 
              $sections[3] 
              . "\nMay affect telephone or internet service." 
              . "\nSee http://slbmin04mc03/activecalls.html for current status.\n";
 	    sendmail( \%mailinfo, $device, $syslogdatetime, $messagetype, $controllerstatus );
	  }
	  if ( $messagetype eq '%ISDN-6-CONNECT' ) {
            my $count;
            $ok2emitline = 0;
            my ( $rv, $rc, $row_hash, $sth, $rows );
            my @pieces = split( / +/, $sections[3] );
#            print ( "  connect:  $pieces[1] is $pieces[6]\n" );
            $sth = $hDB->prepare( q{select count(*) from activecalls where device=? and interface=?} );
            $rv = $sth->execute( $device, $pieces[1] );
            $count = 0;
            if ( $rv ) {
              $sth->bind_columns( \$count );
              $rc = $sth->fetch();
              $sth->finish;
            }            
            if ( 0 == $count ) {
              $hDB->do( q{insert into activecalls ( device, interface, number, timecreated ) values (?,?,?,'now') }, 
                undef, ( $device, $pieces[1], $pieces[6] ) );
            }
            else {
              $hDB->do( q{update activecalls set number=?, timecreated='now' where device=? and interface=? },
                undef, ( $pieces[6], $device, $pieces[1] ) );
            }
            my $countername = $device . ' call attempts';
            $sth = $hDB->prepare( q{select count(*) from counters where item=?} );
            $rv = $sth->execute( $countername );
            $count = 0;
            if ( $rv ) {
              $sth->bind_columns( \$count );
              $rc = $sth->fetch();
              $sth->finish;
            }
            if ( 0 == $count ) {
              $hDB->do( q{ insert into counters (item, value) values ( ?, 1 )}, undef, ( $countername ) );
            }
            else {
              $hDB->do( q{ update counters set value=value+1 where item=?}, undef, ( $countername ) );
            }
 	  }
	  if ( $messagetype eq '%ISDN-6-DISCONNECT' ) {
            $ok2emitline = 0;
            my ( $rv, $rc, $row_hash, $sth, $rows );
            my @pieces = split( / +/, $sections[3] );
#            print ( "  disconnect:  $pieces[1] is $pieces[4] for $pieces[8]\n" );            
            $hDB->do( q{ update activecalls set number='', timecreated='now' where device=? and interface=? },
              undef, ( $device, $pieces[1] ) );
          }
          if ( $messagetype eq '%VOIPAAA-5-VOIP_CALL_HISTORY' ) {
            $ok2emitline = 0;
            $sections[3] =~ s/DisconnectText normal, unspecified/DisconnectText normal unspecified/;
            my @attributes = split( /, */, $sections[3] );
            my ( $calllegtype, $connectionid, $setuptime, $peeraddress, $peersubaddress );
            my ( $disconnectcause, $connecttime, $disconnecttime, $callorigin, $chargedunits );
            my ( $infotype, $transmitpackets, $transmitbytes, $receivepackets, $receivebytes );
            $peersubaddress = '';
	    $peeraddress = '';
            if ( $attributes[ 0] =~ /CallLegType (\d+)/ )      { $calllegtype = $1; }
            if ( $attributes[ 1] =~ /ConnectionId (.+)/ )      { $connectionid = $1; }
            if ( $attributes[ 2] =~ /SetupTime (.+)/ )         { $setuptime = $1; }
            if ( $attributes[ 3] =~ /PeerAddress (.+)/ )       { $peeraddress = $1; }
            if ( $attributes[ 4] =~ /PeerSubAddress (.*)/ )    { $peersubaddress = $1; }
            if ( $attributes[ 5] =~ /DisconnectCause (.+)/ )   { $disconnectcause = $1; }
            if ( $attributes[ 7] =~ /ConnectTime (.+)/ )       { $connecttime = $1; }
            if ( $attributes[ 8] =~ /DisconnectTime (.+)/ )    { $disconnecttime = $1; }
            if ( $attributes[ 9] =~ /CallOrigin (.+)/ )        { $callorigin = $1; }
            if ( $attributes[10] =~ /ChargedUnits (.+)/ )     { $chargedunits = $1; }
            if ( $attributes[11] =~ /InfoType (.+)/ )         { $infotype = $1; }
            if ( $attributes[12] =~ /TransmitPackets (\d+)/ ) { $transmitpackets = $1; }
            if ( $attributes[13] =~ /TransmitBytes (\d+)/ )   { $transmitbytes = $1; }
            if ( $attributes[14] =~ /ReceivePackets (\d+)/ )  { $receivepackets = $1; }
            if ( $attributes[15] =~ /ReceiveBytes (\d+)/ )    { $receivebytes = $1; }
#            print (" voip $device $connectionid, $peeraddress, $setuptime, $connecttime, $disconnecttime \n" );
            $setuptime = parsetime( $setuptime );
            $connecttime = parsetime( $connecttime );
            $disconnecttime = parsetime( $disconnecttime );
            $hDB->do( q{ insert into calllog ( device, connectionid, calllegtype, setuptime, 
              peeraddress, peersubaddress, disconnectcause, connecttime, disconnecttime, 
              callorigin, chargedunits, infotype, 
              transmitpackets, transmitbytes, receivepackets, receivebytes ) 
              values ( ?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,? ) },
              undef, ( $device, $connectionid, $calllegtype, $setuptime, 
              $peeraddress, $peersubaddress, $disconnectcause, $connecttime, $disconnecttime,
              $callorigin, $chargedunits, $infotype, 
              $transmitpackets, $transmitbytes, $receivepackets, $receivebytes ) );
  	    
            my ( $rv, $rc, $row_hash, $sth, $rows );
            $sth = $hDB->prepare( q{select count(*) from cdr where connectionid=?} );
            $rv = $sth->execute( $connectionid );
            my $count = 0;
            if ( $rv ) {
              $sth->bind_columns( \$count );
              $rc = $sth->fetch();
              $sth->finish;
            }            
            if ( 0 == $count ) {
              if ( 1 == $calllegtype ) {
 		$hDB->do( q{insert into cdr (device, connectionid, calllegtype1, address1, setuptime, connecttime, disconnecttime ) values (?,?,?,?,?,?,?)},
		  undef, ( $device, $connectionid, $calllegtype, $peeraddress, $setuptime, $connecttime, $disconnecttime ) );
              }
              if ( 2 == $calllegtype ) {
 		$hDB->do( q{insert into cdr (device, connectionid, calllegtype2, address2, setuptime, connecttime, disconnecttime ) values (?,?,?,?,?,?,?)},
		  undef, ( $device, $connectionid, $calllegtype, $peeraddress, $setuptime, $connecttime, $disconnecttime ) );
              }
            }
            else {
              if ( 1 == $calllegtype ) {
                $hDB->do( q{update cdr set calllegtype1=?, address1=?, setuptime=?, connecttime=?, disconnecttime=? where connectionid=?}, 
                  undef, ( $calllegtype, $peeraddress, $setuptime, $connecttime, $disconnecttime, $connectionid ) );
              }
              if ( 2 == $calllegtype ) {
                $hDB->do( q{update cdr set calllegtype2=?, address2=?, setuptime=?, connecttime=?, disconnecttime=? where connectionid=?}, 
                  undef, ( $calllegtype, $peeraddress, $setuptime, $connecttime, $disconnecttime, $connectionid ) );
              }
            }
          }
        }

        if ( $messagetype eq '%SEC-6-IPACCESSLOGDP' 
          || $messagetype eq '%SEC-CLUSTER_MEMBER_1-6-IPACCESSLOGDP'
          ) {
          $ok2emitline = 0;
          # access list stuff
        }
        if ( $messagetype eq '%SEC-6-IPACCESSLOGP' 
          || $messagetype eq '%SEC-CLUSTER_MEMBER_1-6-IPACCESSLOGP' 
          ) {
          $ok2emitline = 0;
          # access list stuff
        }
        if ( $messagetype eq '%OSPF-5-ADJCHG' ) {
          $ok2emitline = 1;
          my ( $process, $neighbor, $interface, $status );
          $sections[3] =~ /Process (\d+), Nbr (\d+\.\d+\.\d+\.\d+) on (\S+) from (.+)/;
          $process = $1;
          $neighbor = $2;
          $interface = $3;
          $status = $4;
#          print( "** ospf:  $process, $interface, $neighbor = $status\n" );
            my ( $rv, $rc, $row_hash, $sth, $rows );
            $sth = $hDB->prepare( q{select count(*) from ospfstatus 
              where device=? and process=? and interface=? and neighbor=?} );
            $rv = $sth->execute( $device, $process, $interface, $neighbor );
            my $count = 0;
            if ( $rv ) {
              $sth->bind_columns( \$count );
              $rc = $sth->fetch();
              $sth->finish;
            }
            if ( 0 == $count ) {
              $hDB->do( q{insert into ospfstatus ( device, process, interface, neighbor, status, transitioned, count )
                values ( ?,?,?,?,?,'now',1 ) },
                undef, ( $device, $process, $interface, $neighbor, $status ) );
            }
            else {
              $hDB->do( q{update ospfstatus set status=?, count=count+1 where
                device=? and process=? and interface=? and neighbor=? },
                undef, ( $status, $device, $process, $interface, $neighbor ) );
            }
 	  my $ospfstatus = $sections[3] . "\nSee http://slbmin04mc03/ospfstatus.html for current status.\n";
 	  sendmail( \%mailinfo, $device, $syslogdatetime, $messagetype, $ospfstatus );
        }
  	if ( $messagetype eq '%SYS-5-CONFIG_I' ) {
          $ok2emitline = 0;
        }
	if ( $messagetype eq '%LINK-5-CHANGED' ) {
          $ok2emitline = 1;
        }
        if ( $messagetype eq '%LINEPROTO-5-UPDOWN' 
          || $messagetype eq '%LINK-3-UPDOWN'
          || $messagetype eq '%LINEPROTO-CLUSTER_MEMBER_1-5-UPDOWN'
          || $messagetype eq '%LINK-CLUSTER_MEMBER_1-3-UPDOWN'

          ) {
          $ok2emitline = 0;
          $sections[3] =~ /Interface (.+),/;
          my $interface = $1;
          $sections[3] =~ /state to (\S+)/;
          my $status = $1;
          my ( $rv, $rc, $row_hash, $sth, $rows );
          $sth = $hDB->prepare( q{select count(*) from interfacestatus where device=? and interface=?} );
          $rv = $sth->execute( $device, $interface );
          my $count = 0;
          if ( $rv ) {
            $sth->bind_columns( \$count );
            $rc = $sth->fetch();
            $sth->finish;
          }
          if ( 0 == $count ) {
            $hDB->do( q{insert into interfacestatus (device, interface, linkstatus, protocolstatus, transitioned, count )
              values ( ?,?,'unknown', 'unknown', 'now', 0 ) },
              undef, ( $device, $interface ) );  
          }
          if ( $messagetype =~ /^%LINK/ ) {
            $hDB->do( q{update interfacestatus set linkstatus=?, transitioned='now', count=count+1 
              where device=? and interface=?}, undef, ( $status, $device, $interface ) );
          }
          if ( $messagetype =~ /^%LINE/ ) {
            $hDB->do( q{update interfacestatus set protocolstatus=?, transitioned='now', count=count+1 
              where device=? and interface=?}, undef, ( $status, $device, $interface ) );
          }
        }
	if ( $messagetype eq '%CRYPTO-4-PKT_REPLAY_ERR' ) {
          $ok2emitline = 1;
	}
        if ( $messagetype eq '%SYS-6-CLOCKUPDATE' ) {
          $ok2emitline = 0;
        }
        if ( $messagetype eq '%DOT11-6-ASSOC' 
          || $messagetype eq '%DOT11-6-DISASSOC'
          ) {
          $ok2emitline = 0;
          $sections[3] =~ /Interface (.+),/;
          my $interface = $1;
          my ( $phone, $mac );
          $phone = '';
          $mac = '';
          if ( $sections[3] =~ /tation\s+(SEP\S+)\s+(\S+\.\S+\.\S+)/ ) {
            $phone = $1;
            $mac = $2;
          }
          else {
            if ( $sections[3] =~ /tation\s+(\S+\.\S+\.\S+)/ ) {
              $mac = $1;
            }
          }
          my $status = 'unknown';
          if ( $messagetype =~ /6-ASSOC/ ) {
            $status = 'associated';
          }
          if ( $messagetype =~ /6-DISASSOC/ ) {
            $status = 'disassociated';
          }
          my ( $rv, $rc, $row_hash, $sth, $rows );
          $sth = $hDB->prepare( q{select count(*) from wirelessassoc where device=? and interface=? and mac=?} );
          $rv = $sth->execute( $device, $interface, $mac );
          my $count = 0;
          if ( $rv ) {
            $sth->bind_columns( \$count );
            $rc = $sth->fetch();
            $sth->finish;
          }
          if ( 0 == $count ) {
            $hDB->do( q{insert into wirelessassoc (device, interface, mac, phone, transitioned, status, count )
              values ( ?,?,?,?,'now',?,1 ) },
              undef, ( $device, $interface, $mac, $phone, $status ) );
          }
          else {
            $hDB->do( q{update wirelessassoc set status=?, count=count+1, transitioned='now', phone=?
              where device=? and interface=? and mac=? }, undef, ( $status, $phone, $device, $interface, $mac ) );
          }

        }
        if ( $messagetype eq '' ) {
          $ok2emitline = 0;
        }
        if ( $messagetype eq '%DOT11-7-AUTH_FAILED' ) {
          $ok2emitline = 1;
        }
        if ( $messagetype eq '%DOT11-6-ROAMED' ) {
          $ok2emitline = 0;
        }

        if ( $ok2emitline ) {
          print "$line";
#	  print "$device $messagetype $sections[3]\n";
        }
      } 
      catch Error with {
        my ( $ex ) = shift;
        print( "******* Error $ex on line:\n" );
        print( "  $line\n" );
      }
    }
    close(LOG);
}
exit(1);