"Nathan Clemons [Staff]" wrote:
>
> I'd love to write a good Perl DBI monitor, where you could specify
> username, password, port, and DBD type in the config file and have a SQL
> statement to use to test it.
>
> If anyone feels up to writing a FAQ on how to write a Perl based monitor,
> I'll be happy to contribute it when complete. Otherwise, I'm too busy
> working on the HUP situation to read the current Perl monitors and
> determine what I need to do.
>
Here's one I'd hacked out of ciscomon. It works pretty well. I didn't spend
a lot of time making the config file easy to use--the lines are very long
but you can't use a continuation character. I don't change if very often
anyway... Config file looks like this:
# Config file for SQLmon nocol monitor
#
# Format:
# POLLINTERVAL 600 (only the last one takes effect)
# <name> <addr> <maxseverity> <DBI driver> <username> <password> <SQL
query-string>
#
##
POLLINTERVAL 120
#
sql1 130.133.99.72 Critical dbi:mysql:mysql:localhost nocol xxyy select *
from user
sql2 130.133.99.73 Error dbi:mysql:mysql:sql2 nocol xxyy select *
from user
The first two fields are the standard sitename and siteaddr that nocol
wants. The third is the maximum severity that the device will rise to. The
third is the dbi driver string. The above works for mysql; others may be
different. Note that you have to include the hostname here--the script
doesn't try to pull it from field 1 or 2. Next two are the username and
password to access the database. Everything from there on is the sql query
to execute. If the query is successful it uses the return value in the
"value" field. If not it sets that to 0 and bumps the severity. The above
query--in case you're not dbi proficient--is selecting everything in the
user table of the mysql database.
The script will die if either the username/password doesn't work or if your
SQL is invalid.
The script itself is attached.
--
_______________________________________________________________________
Rick Beebe (203) 785-6416
Manager, Systems & Network Engineering FAX: (203) 785-3978
ITS-Med Client & Technology Services Richard.Beebe@yale.edu
Yale University School of Medicine
P.O. Box 208078, New Haven, CT 06520-8078
_______________________________________________________________________
#!/usr/local/bin/perl
#
# SQLmon - monitor for DBI accessible SQL servers
#
# Part of the NOCOL monitoring package.
#
## Contributed by Richard Beebe II (richard.beebe@yale.edu)
# Derived from ciscomon
#
##
#
#
use DBI;
require "nocollib.pl" ;
local ($vpad) = "";
############################
## Variables customization # overrides values in the nocollib.pl library
############################
$sleepint=60*10; # Seconds to sleep between tries.
############################
$debug = 0; # set to 1 for debugging output
$libdebug = 0; # set to 1 for debugging output
$maxseverity = $E_CRITICAL ;
$sev{'Critical'} = $E_CRITICAL;
$sev{'Error'} = $E_ERROR;
$sev{'Warning'} = $E_WARNING;
$sev{'Info'} = $E_INFO;
$prognm = $0; # save program name
# Read the config file. Use '\t' as a separator for 'item'
sub readconf {
local ($var);
local ($host);
open(CONFIG,"<$cfile")||die("Couldn't find $cfile, exiting");
while(<CONFIG>) {
chop;
if(/^\s*#/) {next;} # skip comments
if(/^\s*$/) {next;} # skip blank lines
if (/^(pollinterval)\s*(\S*)$/i) {
$sleepint = $2 if $2;
next;
}
if (/^(\S+)\s+(\S+)\s+(\S+)\s+(\S+)\s+(\S+)\s+(\S+)\s+(.+)$/)
{
$item="$1\t$2" ; # the name and address
$maxseverity{$item} = $sev{$3} ? $sev{$3} : $maxseverity;
$data_source{$item} = $4 if $4;
$username{$item} = $5;
$password{$item} = $6;
if (!$7) {
die "$item line doesn't have enough fields!";
}
$query_string{$item} = $7;
$sender{$item} = 'sqlmon';
$varname{$item} = 'SQLserver';
$sitename{$item} = $1;
$siteaddr{$item} = $2;
$varvalue{$item} = 1;
$varthres{$item} = 0;
$varunits{$item} = 'Up';
$severity{$item} = $E_INFO;
push (@items, $item);
next;
} else {
print "Ignoring illegal line: $_\n";
}
} # end while(CONFIG)
close(CONFIG);
if(0>$#items){die("Nothing to monitor in $cfile, exiting")};
if ($debug) {
print "Items are:\n"; foreach (@items) { print "\t$_\n" } ;
}
} # end: readconf
&nocol_startup;
&readconf;
local ($stime, $deltatime);
## Check state of each router
#
while (1) {
local ($host, $ipaddr) = @_ ;
$stime = time;
foreach $item (@items ) {
# ($host,$ipaddr) = split(/\t/,$item);
print "(debug) dotest: connecting to database $data_source{$item} ($username{$item}, $password{$item})\n" if $debug;
$dbh = DBI->connect($data_source{$item}, $username{$item}, $password{$item}) or die $DBI::errstr;
print "(debug) dotest: running command $query_string{$item}\n" if $debug;
$rc = $dbh->do($query_string{$item}) or die $dbh->errstr;
# $item = "$host\t$ipaddr";
$varvalue{$item} = $rc;
print "(debug) dotest: SQL query returned: ".$rc."\n" if $debug;
if ($rc > 0) { #status is UP
($mon{$item},$day{$item},$hour{$item},$min{$item}) = &gettime ;
if (!($nocop{$item} & $n_UP)) { # recent state change
$nocop{$item} = $nocop{$item} & ~($n_UP | $n_DOWN | $n_UNKNOWN) | $n_UP;
$loglevel{$item} = $severity{$item} ; # loglevel set to old level
$severity{$item} = $E_INFO;
&eventlog(&packevent($item)) ; # log to noclogd daemon
}
} else { # Status is DOWN
$varvalue{$item} = 0;
local($oseverity) = $severity{$item} ;
$severity{$item} = ($severity{$item} > $maxseverity{$item}) ? ($severity{$item} - 1) : $maxseverity{$item};
$nocop{$item}= $nocop{$item} & ~($n_UP | $n_DOWN | $n_UNKNOWN) | $n_DOWN;
if ($oseverity != $severity{$item}) { # severity change
($mon{$item},$day{$item},$hour{$item},$min{$item}) = &gettime ;
# smaller severity is more severe... set that as loglevel
$loglevel{$item}= $severity{$item} < $oseverity ? $severity{$item}:$oseverity;
&eventlog(&packevent($item));
}
}
$dbh->disconnect;
} # end: for $h ($host)
open(OEVENTS,">$datafile");
foreach $item (@items) {
if(!$forget{$item}) {
&writeevent(OEVENTS, $item);
}
}
close(OEVENTS);
$deltatime = time - $stime; # time to do tests
if ($sleepint > $deltatime) { sleep($sleepint - $deltatime); }
} # end: while(1)
###
### main
###
#&nocol_main ;
|