perl/manager/snmptosql

#!/usr/bin/perl

use NetSNMP::manager::getValues qw(getValues);
use SNMP;
use DBI;
use Net::SMTP;

#===========================================================================
#  Global defines
#===========================================================================

$hostname = 'localhost';          # Host that serves the mSQL Database
$dbname = 'snmp';                 # mySQL Database name
$smtpserver = 'localhost';
$smtpfrom   = 'Net-SNMP Manager <wjhardaker@ucdavis.edu>';	# <===  CHANGE ME  ========
$doit = 1;
$somehosts = 0;

sub usage {
    print "$0 [-H host] [-u user] [-p password] [-l hostlist,...] [-v] [-h] [-n] [-d] [-m mib-to-load] <-m mibnode>\n";
    exit 0;
}

while ($#ARGV > -1) {
    $_ = shift @ARGV;
    usage if (/-h/);
    $hostname = shift if (/-H/);
    if (/-l/) {
	my $arg = shift;
	my @a = split(/,/,$arg);
	my $i;
	$somehosts = 1;
	foreach $i (@a) {
	    $dohost{$i} = 1;
	}
    }
    $user = shift if (/-u/);
    $pass = shift if (/-p/);
    $verbose = 1 if (/-v/);
    $delete = 1 if (/-d/);
    $doit = 0 if (/-n/);
    $tableexpr = shift if (/-t/);
    if (/-m/) {
	# load some mibs
	# SNMP::loadModules(shift);
	$ENV{'MIBS'} = shift;
    }
    if (/-M/) {
	# add a mib directory to look in
	$ENV{'MIBDIRS'} = shift;
	# SNMP::addMibDirs(shift);
    }
}

init_mib;

#===========================================================================
# Connect to the mSQL database with the appropriate driver
( $dbh = DBI->connect("DBI:mysql:database=$dbname;host=$hostname", $user, $pass))
    or die "\tConnect not ok: $DBI::errstr\n";

#
# delete history rows every so often.
#
my %count = getValues($dbh, 'setup', 'deletecount');

if (!defined($count{'max'})) {
    # default is to delete history rows once an hour.
    $dbh->do("insert into setup values('deletecount','max','6')");
    $count{'max'} = 6;
}

if (!defined($count{'current'})) {
    $dbh->do("insert into setup values('deletecount','current','0')");
} else {
    $count{'current'}++;
    if ($count{'max'} <= $count{'current'}) {
	$count{'current'} = 0;
	$deletehist = 1;
    }
    $dbh->do("update setup set valcol = $count{'current'} where lookup = 'deletecount' and varcol = 'current'");
}

#===========================================================================
# Get host records from database and process

$cursor = getcursor("SELECT distinct host FROM hosttables");
nexthost: while (  $hostrow = $cursor->fetchrow_hashref ) {

    my $host = $hostrow->{'host'};

    next if ($somehosts && !defined($dohost{$host}));

    #set up the session
    print STDERR " starting $host\n" if ($verbose);
    my $x = $dbh->prepare("select groupname from hostgroups where host = '$host'");
    my $y = $x->execute();
    my $group = ${$x->fetchrow_hashref}{'groupname'};
    my @args = ('authgroup','default');
    print STDERR "$host...$y\n" if ($verbose);
    if (defined($y) && "$y" ne "0E0") {
        push @args,'authgroup',$group;
    }
    push @args,'authhost',$host;
    print STDERR "$host: $group\n" if ($verbose);

    print STDERR "authvals: ", join(", ", @args), "\n" if ($verbose);
    my %authvals = getValues($dbh, @args);
    if ($verbose) {
	print STDERR "parms for $host:";
	foreach my $i (keys(%authvals)) {
	    print STDERR "$i => $authvals{$i}, ";
	}
	print STDERR "\n";
    }

    my $sess = new SNMP::Session (  DestHost => $host, 
				    UseSprintValue => 1,
				    %authvals );
    print STDERR "Sess ($host): $sess, ref=" . ref($sess). "\n" if ($verbose);
    if (ref ($sess) ne "SNMP::Session") {
#	print STDERR "ack: \$sess not a SNMP::Session for $host ($!)\n";
	hosterror("$host");
	next nexthost;
    }

    # get various bits of system information.
    my $sysDescr = $sess->get('sysDescr.0');
    my $sysId = SNMP::translateObj($sess->get('sysObjectID.0'));
    my $versiontag = $sess->get('versionTag.0');
    my $sysuptime = $sess->get('sysUpTime.0');

    if ($sysDescr eq "" || $sysId eq "" || $versiontag eq "" || 
	$sysuptime eq "") {
	hosterror("$host","Problem collecting basic info");
	next;
    }

    $dbh->do("update hostgroups set sysObjectId = '$sysId', sysDescr = '$sysDescr', versionTag = '$versiontag', sysUpTime = '$sysuptime' where host = '$host'");

    # translate the sysUpTime to a real number for future use:
    {
	my ($d,$h,$m,$s,$fs) = ($sysuptime =~ /^(\d+):(\d+):(\d+):(\d+)\.(\d+)$/);
	$sysuptime = $fs + $s*100 + $m*100*60 + $h*100*60*60 + $d*100*60*60*24;
    }

    # get a list of tables we want to store
    $cmd = "SELECT * FROM hosttables where (host = '$host')";
    print STDERR " $cmd\n" if ($verbose);
    ( $tblh = $dbh->prepare( $cmd ) )
	or warn "\nnot ok: $DBI::errstr\n";
    ( $tblh->execute )
	or print( "\tnot ok: $DBI::errstr\n" );

    while (  $tablelist = $tblh->fetchrow_hashref ) {
	next if (defined($tableexpr) && $tablelist->{'tablename'} !~ /$tableexpr/);
	print STDERR "starting table $tablelist->{'tablename'}\n" if ($verbose);
	my $mib = $SNMP::MIB{SNMP::translateObj($tablelist->{'tablename'})};
	if (!$mib) {
	    warn "mib node $tablelist->{'tablename'} doesn't exist";
	    next;
	}
	my $children = get_children($mib);

	# create the table in our database if it doesn't exist.
	setuptable($dbh, $tablelist->{tablename}, $delete);
	if ($tablelist->{'keephistory'} > 0) {
	    setuptable($dbh, $tablelist->{tablename}, $delete, "hist");
	}
	
	$var = 
	    new SNMP::Varbind([SNMP::translateObj($tablelist->{'tablename'})]);
	my $void = SNMP::translateObj($tablelist->{'tablename'});
	my $val = $sess->getnext($var);
	print STDERR "init err: $sess->{'ErrorStr'}\n" if ($verbose);
	if ($sess->{'ErrorStr'} =~ /Timeout/) {
	    print STDERR "$host timed out\n" if ($verbose);
	    hosterror($host);
	    next nexthost;
	}
	$initlabel = "";
	print STDERR " starting $tablelist->{tablename}\n" if ($verbose);
	my %tbl_ids;
	while (1) {
	    my $varlabel = SNMP::Varbind::tag($var);
	    print STDERR "last $host " . SNMP::translateObj($varlabel) . ": $void\n" if ($verbose && SNMP::translateObj($varlabel) !~ /^$void/);

	    last if (SNMP::translateObj($varlabel) !~ /^$void/);
	    $varlabel = SNMP::translateObj(SNMP::Varbind::tag($var)) if ($varlabel =~ /^[\.0-9]+$/);
	    $initlabel = $varlabel if ($initlabel eq "");

	    my $val = $sess->getnext($var);
	    if ($sess->{'ErrorStr'} =~ /Timeout/) {
		print STDERR "$host timed out\n" if ($verbose);
		hosterror($host);
		next nexthost;
	    }
	    last if ($sess->{'ErrorStr'});
	    my $id = SNMP::Varbind::iid($var);
	    print STDERR "$initlabel = $varlabel\n" if ($verbose);
	    last if ($varlabel ne $initlabel);
	    my %vals;
	    $tbl_ids{$id} = 1;
	    foreach $c (@$children) {
		my $oid = $$c{'objectID'} . "." . $id;
		my $newvar = new SNMP::Varbind([$oid]);
		my $val = $sess->get($newvar);
		my $label = SNMP::translateObj($$c{'objectID'});
		$vals{$label} = $val;
	    }
	    my $cmd;

	    # check to see if the error previously existed and then
	    # delete the old entry.
	    my $olderr =
		checkrowforerrors($tablelist->{'tablename'}, $host, $id);
	    $dbh->do("delete from $tablelist->{tablename} where ( host = '$host'  and oidindex = '$id')");
	    $res = $dbh->do("select * from $tablelist->{'tablename'} where ( host = '$host' and oidindex = '$id')");
	    print STDERR "  result: $res\n" if ($verbose);
	    if ($res ne "0E0") {
		$cmd = "update $tablelist->{'tablename'} set ";
		foreach $h (keys(%vals)) {
		    $cmd .= "$h = '$vals{$h}', ";
		}
		$cmd .= " updated = NULL where (host = '$host' and oidindex = '$id')";
		
	    } else {
		$cmd = "insert into $tablelist->{'tablename'}(host, oidindex, " . join(", ",keys(%vals)) .
		    ") values('$host', '$id', '" .
			join("', '",values(%vals)). "')";
	    }

	    print STDERR "  $cmd\n" if ($verbose);
	    $dbh->do("$cmd")
		or warn "\nnot ok: $cmd => $DBI::errstr\n" if ($doit);

	    if ($tablelist->{'keephistory'} > 0) {
		$cmd = "insert into $tablelist->{'tablename'}hist (host, oidindex, sysUpTime, " 
		    . join(", ",keys(%vals))
		    . ") values('$host', '$id', $sysuptime, '"
		    . join("', '",values(%vals)). "')";
		print STDERR "  $cmd\n" if ($verbose);
		$dbh->do("$cmd")
		    or warn "\nnot ok: $cmd -> $DBI::errstr\n" if ($doit);
		
	    }

	    my $newerr = 
		checkrowforerrors($tablelist->{'tablename'}, $host, $id);
	    if ($newerr->{retval} != $olderr->{retval}) {
		 logerror($host, $newerr->{retval}, $newerr->{errfield}, 
			  $newerr->{errvalue});
	     }
	} # snmp loop

	# delete the data beyond the number of days requested.
	if ($deletehist && $tablelist->{'keephistory'} > 0) {
	    $dbh->do("delete from $tablelist->{'tablename'}hist where (unix_timestamp() - unix_timestamp(updated)) > $tablelist->{'keephistory'}*24*60*60 and host = '$host'") or warn "\nnot ok: $DBI::errstr\n" if ($doit);
	}

	my $curs = getcursor("select oidindex from $tablelist->{tablename} where host = '$host'");
	my $row;
	while ($row = $curs->fetchrow_hashref) {
	    print STDERR "  $row->{oidindex}\n" if ($verbose);
	    if (!defined($tbl_ids{$row->{oidindex}})) {
		$dbh->do("delete from $tablelist->{tablename} where oidindex = '$row->{oidindex}'");
		print STDERR "deleting: $host $tablelist->{tablename} $row->{oidindex}\n" if ($verbose);
	    }
	}
	print STDERR "  done with $tablelist->{tablename}\n" if ($verbose);
    } # table loop

    if (isbadhost($host)) {
	# let them out, they're no longer being bad.
	print STDERR "deleting: delete from hosterrors where host = '$host'\n" if ($verbose);
	$dbh->do("delete from hosterrors where host = '$host'");
	mailusers("$host responding again", "$host responding again",
		  getoncallforhost($host));
    }
    print STDERR "  done with $host\n" if ($verbose);
} # host loop

# disconnect
$cursor->finish();
$dbh->disconnect();

#
# Subroutines
#

# setup a table in the database based on a MIB table.
sub setuptable {

    my %conversions = qw(INTEGER integer INTEGER32 integer OCTETSTR varchar(254) COUNTER integer UINTEGER integer IPADDR varchar(254) OBJECTID varchar(254) GAGUE integer OPAQUE varchar(254) TICKS integer GAUGE integer);

    # set up mib info
    my ($dbh, $mibnode, $delete, $suffix) = @_;

    my $mib = $SNMP::MIB{SNMP::translateObj($mibnode)};
    my $children = get_children($mib);
    my ($cmd, $j);

    if ($delete) {
	$cmd = "drop table if exists $mib->{label}";
	print STDERR "cmd: $cmd\n" if ($verbose);
	$dbh->do($cmd)
	    or warn "\nnot ok: $cmd -> $DBI::errstr\n" if ($doit);
    } elsif (($ret = $dbh->do("show tables like '$mib->{label}$suffix'")) ne "0E0") {
	# the table already exists
	return;
    }

    print STDERR "show tables like $mib->{label}$suffix: $ret\n" if($verbose);
    print STDERR " creating table for $mibnode ($mib->{label}$suffix)\n" if ($verbose);
    
    $cmd = "create table $mib->{label}$suffix (id integer auto_increment primary key, host varchar(32) not null, oidindex varchar(32) not null";
    foreach $j (sort { $a->{'subID'} <=> $b->{'subID'} } @$children) {
	if (!defined($conversions{$j->{type}})) {
	    print STDERR "no conversion for $j->{label} = ". $j->{type} . "!\n";
	    return;
	}
	$cmd .= ", $j->{label} $conversions{$j->{type}}";
    }
    $cmd .= ", updated timestamp";
    $cmd .= ", sysUpTime integer" if (defined($suffix));
    $cmd .= ",key oidindex (oidindex), key host (host))";

    print STDERR "cmd: $cmd\n" if ($verbose);
    $dbh->do("$cmd")
	or warn "\nnot ok: $cmd -> $DBI::errstr\n" if ($doit);

}

sub getoncall {
    my @groups = @_;
    my $cur;
    my $row;
    my ($emails, @days, @hours, @two, $i);
    my %dayscon = qw(Sun 0 Mon 1 Tue 2 Wed 3 Thu 4 Fri 5 Sat 6);
    my @now = localtime(time());
    my %people;
    my $group;

    foreach $group (@groups) {
	$cur = getcursor("select * from oncall where groupname = '$group'");
      row: while (  $row = $cur->fetchrow_hashref ) {
	  @days = split(/,/,$row->{'days'});
	  foreach $i (@days) {
	      @two = split(/-/,$i);
	      if ($row->{'days'} eq "*" ||
		  (defined($dayscon{$i}) && $dayscon{$i} == $now[6]) ||
		  (defined($dayscon{$two[0]}) && defined($dayscon{$two[1]}) &&
		   (($dayscon{$two[0]} <= $now[6] && 
		     $dayscon{$two[1]} >= $now[6]) ||
		    (($dayscon{$two[0]} > $dayscon{$two[1]}) &&
		     ($dayscon{$two[0]} <= $now[6] || 
		      $dayscon{$two[1]} >= $now[6]))))) {
		  # we hit a valid day range
		  print STDERR "    hit it $row->{'email'} $now[6]\t($i)\t$row->{'days'}\n"
		      if ($verbose);
		  $people{$row->{'email'}} = $row->{'email'};
	      } else {
		  print STDERR "not hit it $row->{'email'} $now[6]\t($i)\t$row->{'days'}\n"
		      if ($verbose);
	      }	      
	  }
      }
    }
    return keys(%people);
}

sub getoncallforhost {
    my $host = shift;
    return getoncall(getgroupsforhost($host));
}

sub getcursor {
    my $cmd = shift;
    my $cursor;
    print STDERR "cmd: $cmd\n" if ($verbose);
    ( $cursor = $dbh->prepare( $cmd ))
	or die "\nnot ok: $DBI::errstr\n";
    ( $cursor->execute )
	or print( "\tnot ok: $DBI::errstr\n" );
    return $cursor;
}

my %expressions;
sub getexpr {
    my $table = shift;
    print "ref: ",ref($expressions{$table}),"\n" if ($verbose);
    if (!defined($expressions{$table})) {
	my $exprs = getcursor("SELECT * FROM errorexpressions where (tablename = '$table')");
	while (  $expr = $exprs->fetchrow_hashref ) {
	    push @{$expressions{$table}{'expr'}},$expr->{expression};
	    push @{$expressions{$table}{'returnfield'}},$expr->{returnfield};
	}
    }
    if (ref($expressions{$table}) ne "HASH") {
	# no expressions for this table.
	$expressions{$table}{'expr'} = [];
	$expressions{$table}{'returnfield'} = [];
    }
    return $expressions{$table};
}

sub checkrowforerrors {
    my ($table, $host, $id) = @_;
    my $error;

    my $lastres = 0, $lastfield = '';
    my $expressions = getexpr($table);
    my $i;
    for($i=0; $i <= $#{$expressions->{'expr'}}; $i++) {
	if (!defined($expressions->{'prepared'}[$i])) {
	    $expressions->{'prepared'}[$i] = $dbh->prepare("select * from $table where $expressions->{'expr'}[$i] and host = ? and oidindex = ?")
		or warn "\nnot ok: $DBI::errstr\n";
	    print STDERR "preparing select * from $table where $expressions->{'expr'}[$i] and host = ? and oidindex = ? ==> ",ref($expressions->{'prepared'}[$i]),"\n" if($verbose);
	}
	my $prepared = $expressions->{'prepared'}[$i];
	print STDERR "x: ",ref($prepared),"\n" if($verbose);
	$prepared->execute($host, $id) or warn "\nnot ok: $DBI::errstr\n";
	while (  $error = $prepared->fetchrow_hashref ) {
	    print STDERR "$host: $expressions->{returnfield}[$i] = $error->{$expressions->{returnfield}[$i]}\n" if ($verbose);
	    return {'retval', 1,
		    'errfield', $expressions->{returnfield}[$i],
		    'errvalue', $error->{$expressions->{returnfield}[$i]}};
	}
	$lastres = $error->{$expressions->{returnfield}[$i]};
	$lastfield = $expressions->{returnfield}[$i];
    }
    return {'retval', 0, 
	    'errfield', $lastfield,
	    'errvalue', $lastres};
}

sub logerror {
    my ($host, $err, $field, $result) = @_;
    my $groups = getcursor("SELECT distinct groupname FROM hosttables where host = '$host'");
    my ($group, $person);
    my $msg = (($err) ? "error" : "normal");
		    
    my @people = getoncallforhost($host);
    $msg = "$msg: $host";
    $msg .= " $field = $result" if ($field || $result);
    mailusers("SNMP: $msg: $host $field", "$msg\n", @people);
}

sub mailusers {
    my $subject = shift;
    my $msg = shift;
    my @people = @_;
    my $person;
    my $smtpsock = Net::SMTP->new($smtpserver);

    $smtpsock->mail($smtpfrom);
    my $error = $smtpsock->recipient(@people);
    if (!$error) {
	print STDERR "failed to send mail to ",join(",",@people),"\n";
    }
    $smtpsock->data();
    $subject =~ s/\n//;
    $smtpsock->datasend("To: " . join(", ",@people) . "\n");
    $smtpsock->datasend("From: $smtpfrom\n");
    $smtpsock->datasend("Subject: $subject\n");
    $smtpsock->datasend("\n");
    $smtpsock->datasend("$msg\n");
    $smtpsock->dataend();
    $smtpsock->quit;
    print STDERR "mailed ",join(",",@people)," with $msg, $subject ($!)\n" if ($verbose);
}

sub hosterror {
    my $host = shift;
    my $error = shift || "No response";
    my $groups = getcursor("SELECT distinct groupname FROM hosttables where host = '$host'");
    my ($group, $person);
    my %mailed;

    return if (isbadhost($host)); # only send out a message once.
		    
    $dbh->do("insert into hosterrors(host, errormsg) values('$host','$error');");
    my @people = getoncallforhost($host);
    mailusers("No Response from $host", "$host: $error", @people);
}

sub isbadhost {
    my $host = shift;
    my $hosterr = getcursor("SELECT distinct host FROM hosterrors where host = '$host'");
    if ($hosterr->fetchrow_hashref) {
	return 1;
    }
    return 0;
}

sub getgroupsforhost {
    my $host = shift;
    my @retgroups;
    my $groups = getcursor("SELECT distinct groupname FROM hosttables where host = '$host'");
    while( $group = $groups->fetchrow_hashref ) {
	push @retgroups,$group->{'groupname'};
    }
    @retgroups;
}

sub get_children {
    my $mib = shift;
    my $children = $$mib{'children'};
    if (ref($children) ne "ARRAY") {
	warn "$mib has no chlidren";
	return;
    }

    if ($#{$children} == 0 && $mib->{'label'} =~ /Table$/) {
	# is a table, use entry?
	$children = $children->[0]{'children'};
	if (ref($children) ne "ARRAY") {
	    warn "$mib has no chlidren";
	    return;
	}
    }
    return $children;
}