3 use NetSNMP::manager::getValues qw(getValues);
8 #===========================================================================
10 #===========================================================================
12 $hostname = 'localhost'; # Host that serves the mSQL Database
13 $dbname = 'snmp'; # mySQL Database name
14 $smtpserver = 'localhost';
15 $smtpfrom = 'Net-SNMP Manager <wjhardaker@ucdavis.edu>'; # <=== CHANGE ME ========
20 print "$0 [-H host] [-u user] [-p password] [-l hostlist,...] [-v] [-h] [-n] [-d] [-m mib-to-load] <-m mibnode>\n";
27 $hostname = shift if (/-H/);
30 my @a = split(/,/,$arg);
37 $user = shift if (/-u/);
38 $pass = shift if (/-p/);
39 $verbose = 1 if (/-v/);
40 $delete = 1 if (/-d/);
42 $tableexpr = shift if (/-t/);
45 # SNMP::loadModules(shift);
49 # add a mib directory to look in
50 $ENV{'MIBDIRS'} = shift;
51 # SNMP::addMibDirs(shift);
57 #===========================================================================
58 # Connect to the mSQL database with the appropriate driver
59 ( $dbh = DBI->connect("DBI:mysql:database=$dbname;host=$hostname", $user, $pass))
60 or die "\tConnect not ok: $DBI::errstr\n";
63 # delete history rows every so often.
65 my %count = getValues($dbh, 'setup', 'deletecount');
67 if (!defined($count{'max'})) {
68 # default is to delete history rows once an hour.
69 $dbh->do("insert into setup values('deletecount','max','6')");
73 if (!defined($count{'current'})) {
74 $dbh->do("insert into setup values('deletecount','current','0')");
77 if ($count{'max'} <= $count{'current'}) {
78 $count{'current'} = 0;
81 $dbh->do("update setup set valcol = $count{'current'} where lookup = 'deletecount' and varcol = 'current'");
84 #===========================================================================
85 # Get host records from database and process
87 $cursor = getcursor("SELECT distinct host FROM hosttables");
88 nexthost: while ( $hostrow = $cursor->fetchrow_hashref ) {
90 my $host = $hostrow->{'host'};
92 next if ($somehosts && !defined($dohost{$host}));
95 print STDERR " starting $host\n" if ($verbose);
96 my $x = $dbh->prepare("select groupname from hostgroups where host = '$host'");
97 my $y = $x->execute();
98 my $group = ${$x->fetchrow_hashref}{'groupname'};
99 my @args = ('authgroup','default');
100 print STDERR "$host...$y\n" if ($verbose);
101 if (defined($y) && "$y" ne "0E0") {
102 push @args,'authgroup',$group;
104 push @args,'authhost',$host;
105 print STDERR "$host: $group\n" if ($verbose);
107 print STDERR "authvals: ", join(", ", @args), "\n" if ($verbose);
108 my %authvals = getValues($dbh, @args);
110 print STDERR "parms for $host:";
111 foreach my $i (keys(%authvals)) {
112 print STDERR "$i => $authvals{$i}, ";
117 my $sess = new SNMP::Session ( DestHost => $host,
120 print STDERR "Sess ($host): $sess, ref=" . ref($sess). "\n" if ($verbose);
121 if (ref ($sess) ne "SNMP::Session") {
122 # print STDERR "ack: \$sess not a SNMP::Session for $host ($!)\n";
127 # get various bits of system information.
128 my $sysDescr = $sess->get('sysDescr.0');
129 my $sysId = SNMP::translateObj($sess->get('sysObjectID.0'));
130 my $versiontag = $sess->get('versionTag.0');
131 my $sysuptime = $sess->get('sysUpTime.0');
133 if ($sysDescr eq "" || $sysId eq "" || $versiontag eq "" ||
135 hosterror("$host","Problem collecting basic info");
139 $dbh->do("update hostgroups set sysObjectId = '$sysId', sysDescr = '$sysDescr', versionTag = '$versiontag', sysUpTime = '$sysuptime' where host = '$host'");
141 # translate the sysUpTime to a real number for future use:
143 my ($d,$h,$m,$s,$fs) = ($sysuptime =~ /^(\d+):(\d+):(\d+):(\d+)\.(\d+)$/);
144 $sysuptime = $fs + $s*100 + $m*100*60 + $h*100*60*60 + $d*100*60*60*24;
147 # get a list of tables we want to store
148 $cmd = "SELECT * FROM hosttables where (host = '$host')";
149 print STDERR " $cmd\n" if ($verbose);
150 ( $tblh = $dbh->prepare( $cmd ) )
151 or warn "\nnot ok: $DBI::errstr\n";
153 or print( "\tnot ok: $DBI::errstr\n" );
155 while ( $tablelist = $tblh->fetchrow_hashref ) {
156 next if (defined($tableexpr) && $tablelist->{'tablename'} !~ /$tableexpr/);
157 print STDERR "starting table $tablelist->{'tablename'}\n" if ($verbose);
158 my $mib = $SNMP::MIB{SNMP::translateObj($tablelist->{'tablename'})};
160 warn "mib node $tablelist->{'tablename'} doesn't exist";
163 my $children = get_children($mib);
165 # create the table in our database if it doesn't exist.
166 setuptable($dbh, $tablelist->{tablename}, $delete);
167 if ($tablelist->{'keephistory'} > 0) {
168 setuptable($dbh, $tablelist->{tablename}, $delete, "hist");
172 new SNMP::Varbind([SNMP::translateObj($tablelist->{'tablename'})]);
173 my $void = SNMP::translateObj($tablelist->{'tablename'});
174 my $val = $sess->getnext($var);
175 print STDERR "init err: $sess->{'ErrorStr'}\n" if ($verbose);
176 if ($sess->{'ErrorStr'} =~ /Timeout/) {
177 print STDERR "$host timed out\n" if ($verbose);
182 print STDERR " starting $tablelist->{tablename}\n" if ($verbose);
185 my $varlabel = $var->[$SNMP::Varbind::tag_f];
186 print STDERR "last $host " . SNMP::translateObj($varlabel) . ": $void\n" if ($verbose && SNMP::translateObj($varlabel) !~ /^$void/);
188 last if (SNMP::translateObj($varlabel) !~ /^$void/);
189 $varlabel = SNMP::translateObj($var->[$SNMP::Varbind::tag_f]) if ($varlabel =~ /^[\.0-9]+$/);
190 $initlabel = $varlabel if ($initlabel eq "");
192 my $val = $sess->getnext($var);
193 if ($sess->{'ErrorStr'} =~ /Timeout/) {
194 print STDERR "$host timed out\n" if ($verbose);
198 last if ($sess->{'ErrorStr'});
199 my $id = $var->[$SNMP::Varbind::iid_f];
200 print STDERR "$initlabel = $varlabel\n" if ($verbose);
201 last if ($varlabel ne $initlabel);
204 foreach $c (@$children) {
205 my $oid = $$c{'objectID'} . "." . $id;
206 my $newvar = new SNMP::Varbind([$oid]);
207 my $val = $sess->get($newvar);
208 my $label = SNMP::translateObj($$c{'objectID'});
209 $vals{$label} = $val;
213 # check to see if the error previously existed and then
214 # delete the old entry.
216 checkrowforerrors($tablelist->{'tablename'}, $host, $id);
217 $dbh->do("delete from $tablelist->{tablename} where ( host = '$host' and oidindex = '$id')");
218 $res = $dbh->do("select * from $tablelist->{'tablename'} where ( host = '$host' and oidindex = '$id')");
219 print STDERR " result: $res\n" if ($verbose);
221 $cmd = "update $tablelist->{'tablename'} set ";
222 foreach $h (keys(%vals)) {
223 $cmd .= "$h = '$vals{$h}', ";
225 $cmd .= " updated = NULL where (host = '$host' and oidindex = '$id')";
228 $cmd = "insert into $tablelist->{'tablename'}(host, oidindex, " . join(", ",keys(%vals)) .
229 ") values('$host', '$id', '" .
230 join("', '",values(%vals)). "')";
233 print STDERR " $cmd\n" if ($verbose);
235 or warn "\nnot ok: $cmd => $DBI::errstr\n" if ($doit);
237 if ($tablelist->{'keephistory'} > 0) {
238 $cmd = "insert into $tablelist->{'tablename'}hist (host, oidindex, sysUpTime, "
239 . join(", ",keys(%vals))
240 . ") values('$host', '$id', $sysuptime, '"
241 . join("', '",values(%vals)). "')";
242 print STDERR " $cmd\n" if ($verbose);
244 or warn "\nnot ok: $cmd -> $DBI::errstr\n" if ($doit);
249 checkrowforerrors($tablelist->{'tablename'}, $host, $id);
250 if ($newerr->{retval} != $olderr->{retval}) {
251 logerror($host, $newerr->{retval}, $newerr->{errfield},
252 $newerr->{errvalue});
256 # delete the data beyond the number of days requested.
257 if ($deletehist && $tablelist->{'keephistory'} > 0) {
258 $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);
261 my $curs = getcursor("select oidindex from $tablelist->{tablename} where host = '$host'");
263 while ($row = $curs->fetchrow_hashref) {
264 print STDERR " $row->{oidindex}\n" if ($verbose);
265 if (!defined($tbl_ids{$row->{oidindex}})) {
266 $dbh->do("delete from $tablelist->{tablename} where oidindex = '$row->{oidindex}'");
267 print STDERR "deleting: $host $tablelist->{tablename} $row->{oidindex}\n" if ($verbose);
270 print STDERR " done with $tablelist->{tablename}\n" if ($verbose);
273 if (isbadhost($host)) {
274 # let them out, they're no longer being bad.
275 print STDERR "deleting: delete from hosterrors where host = '$host'\n" if ($verbose);
276 $dbh->do("delete from hosterrors where host = '$host'");
277 mailusers("$host responding again", "$host responding again",
278 getoncallforhost($host));
280 print STDERR " done with $host\n" if ($verbose);
291 # setup a table in the database based on a MIB table.
294 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);
297 my ($dbh, $mibnode, $delete, $suffix) = @_;
299 my $mib = $SNMP::MIB{SNMP::translateObj($mibnode)};
300 my $children = get_children($mib);
304 $cmd = "drop table if exists $mib->{label}";
305 print STDERR "cmd: $cmd\n" if ($verbose);
307 or warn "\nnot ok: $cmd -> $DBI::errstr\n" if ($doit);
308 } elsif (($ret = $dbh->do("show tables like '$mib->{label}$suffix'")) ne "0E0") {
309 # the table already exists
313 print STDERR "show tables like $mib->{label}$suffix: $ret\n" if($verbose);
314 print STDERR " creating table for $mibnode ($mib->{label}$suffix)\n" if ($verbose);
316 $cmd = "create table $mib->{label}$suffix (id integer auto_increment primary key, host varchar(32) not null, oidindex varchar(32) not null";
317 foreach $j (sort { $a->{'subID'} <=> $b->{'subID'} } @$children) {
318 if (!defined($conversions{$j->{type}})) {
319 print STDERR "no conversion for $j->{label} = ". $j->{type} . "!\n";
322 $cmd .= ", $j->{label} $conversions{$j->{type}}";
324 $cmd .= ", updated timestamp";
325 $cmd .= ", sysUpTime integer" if (defined($suffix));
326 $cmd .= ",key oidindex (oidindex), key host (host))";
328 print STDERR "cmd: $cmd\n" if ($verbose);
330 or warn "\nnot ok: $cmd -> $DBI::errstr\n" if ($doit);
338 my ($emails, @days, @hours, @two, $i);
339 my %dayscon = qw(Sun 0 Mon 1 Tue 2 Wed 3 Thu 4 Fri 5 Sat 6);
340 my @now = localtime(time());
344 foreach $group (@groups) {
345 $cur = getcursor("select * from oncall where groupname = '$group'");
346 row: while ( $row = $cur->fetchrow_hashref ) {
347 @days = split(/,/,$row->{'days'});
349 @two = split(/-/,$i);
350 if ($row->{'days'} eq "*" ||
351 (defined($dayscon{$i}) && $dayscon{$i} == $now[6]) ||
352 (defined($dayscon{$two[0]}) && defined($dayscon{$two[1]}) &&
353 (($dayscon{$two[0]} <= $now[6] &&
354 $dayscon{$two[1]} >= $now[6]) ||
355 (($dayscon{$two[0]} > $dayscon{$two[1]}) &&
356 ($dayscon{$two[0]} <= $now[6] ||
357 $dayscon{$two[1]} >= $now[6]))))) {
358 # we hit a valid day range
359 print STDERR " hit it $row->{'email'} $now[6]\t($i)\t$row->{'days'}\n"
361 $people{$row->{'email'}} = $row->{'email'};
363 print STDERR "not hit it $row->{'email'} $now[6]\t($i)\t$row->{'days'}\n"
369 return keys(%people);
372 sub getoncallforhost {
374 return getoncall(getgroupsforhost($host));
380 print STDERR "cmd: $cmd\n" if ($verbose);
381 ( $cursor = $dbh->prepare( $cmd ))
382 or die "\nnot ok: $DBI::errstr\n";
384 or print( "\tnot ok: $DBI::errstr\n" );
391 print "ref: ",ref($expressions{$table}),"\n" if ($verbose);
392 if (!defined($expressions{$table})) {
393 my $exprs = getcursor("SELECT * FROM errorexpressions where (tablename = '$table')");
394 while ( $expr = $exprs->fetchrow_hashref ) {
395 push @{$expressions{$table}{'expr'}},$expr->{expression};
396 push @{$expressions{$table}{'returnfield'}},$expr->{returnfield};
399 if (ref($expressions{$table}) ne "HASH") {
400 # no expressions for this table.
401 $expressions{$table}{'expr'} = [];
402 $expressions{$table}{'returnfield'} = [];
404 return $expressions{$table};
407 sub checkrowforerrors {
408 my ($table, $host, $id) = @_;
411 my $lastres = 0, $lastfield = '';
412 my $expressions = getexpr($table);
414 for($i=0; $i <= $#{$expressions->{'expr'}}; $i++) {
415 if (!defined($expressions->{'prepared'}[$i])) {
416 $expressions->{'prepared'}[$i] = $dbh->prepare("select * from $table where $expressions->{'expr'}[$i] and host = ? and oidindex = ?")
417 or warn "\nnot ok: $DBI::errstr\n";
418 print STDERR "preparing select * from $table where $expressions->{'expr'}[$i] and host = ? and oidindex = ? ==> ",ref($expressions->{'prepared'}[$i]),"\n" if($verbose);
420 my $prepared = $expressions->{'prepared'}[$i];
421 print STDERR "x: ",ref($prepared),"\n" if($verbose);
422 $prepared->execute($host, $id) or warn "\nnot ok: $DBI::errstr\n";
423 while ( $error = $prepared->fetchrow_hashref ) {
424 print STDERR "$host: $expressions->{returnfield}[$i] = $error->{$expressions->{returnfield}[$i]}\n" if ($verbose);
426 'errfield', $expressions->{returnfield}[$i],
427 'errvalue', $error->{$expressions->{returnfield}[$i]}};
429 $lastres = $error->{$expressions->{returnfield}[$i]};
430 $lastfield = $expressions->{returnfield}[$i];
433 'errfield', $lastfield,
434 'errvalue', $lastres};
438 my ($host, $err, $field, $result) = @_;
439 my $groups = getcursor("SELECT distinct groupname FROM hosttables where host = '$host'");
440 my ($group, $person);
441 my $msg = (($err) ? "error" : "normal");
443 my @people = getoncallforhost($host);
444 $msg = "$msg: $host";
445 $msg .= " $field = $result" if ($field || $result);
446 mailusers("SNMP: $msg: $host $field", "$msg\n", @people);
454 my $smtpsock = Net::SMTP->new($smtpserver);
456 $smtpsock->mail($smtpfrom);
457 my $error = $smtpsock->recipient(@people);
459 print STDERR "failed to send mail to ",join(",",@people),"\n";
463 $smtpsock->datasend("To: " . join(", ",@people) . "\n");
464 $smtpsock->datasend("From: $smtpfrom\n");
465 $smtpsock->datasend("Subject: $subject\n");
466 $smtpsock->datasend("\n");
467 $smtpsock->datasend("$msg\n");
468 $smtpsock->dataend();
470 print STDERR "mailed ",join(",",@people)," with $msg, $subject ($!)\n" if ($verbose);
475 my $error = shift || "No response";
476 my $groups = getcursor("SELECT distinct groupname FROM hosttables where host = '$host'");
477 my ($group, $person);
480 return if (isbadhost($host)); # only send out a message once.
482 $dbh->do("insert into hosterrors(host, errormsg) values('$host','$error');");
483 my @people = getoncallforhost($host);
484 mailusers("No Response from $host", "$host: $error", @people);
489 my $hosterr = getcursor("SELECT distinct host FROM hosterrors where host = '$host'");
490 if ($hosterr->fetchrow_hashref) {
496 sub getgroupsforhost {
499 my $groups = getcursor("SELECT distinct groupname FROM hosttables where host = '$host'");
500 while( $group = $groups->fetchrow_hashref ) {
501 push @retgroups,$group->{'groupname'};
508 my $children = $$mib{'children'};
509 if (ref($children) ne "ARRAY") {
510 warn "$mib has no chlidren";
514 if ($#{$children} == 0 && $mib->{'label'} =~ /Table$/) {
515 # is a table, use entry?
516 $children = $children->[0]{'children'};
517 if (ref($children) ne "ARRAY") {
518 warn "$mib has no chlidren";