snmptosql

snmp的源代码,已经在我的ubuntu下编译通过

	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;
}