Apologies if this was sent twice, but I have yet to see it hit the mailing
list.
--Nathan
____________________________________________________________________
Nathan Clemons, Systems Engineer
WinStar Internet and Hosting Services
800 South Main St. Mansfield, MA 02048
____________________________________________________________________
nclemons@winstar.com www.winstar.com (v) 800-234-0002 ext.1109
nathan@ici.net (f) 508-261-0430
---------- Forwarded message ----------
Date: Wed, 19 Jan 2000 13:40:30 -0500 (EST)
From: "Nathan Clemons [Staff]" <nathan@ici.net>
To: nocol-users@navya.com
Subject: Re: SQLMon
I also have added some code to support SIGHUP handling for rereading the
config file and saving state data. Let me know how this works, please.
Also several changes, as you all saw, from Rick are included.
--Nathan
On Wed, 19 Jan 2000, Nathan Clemons [Staff] wrote:
> Date: Wed, 19 Jan 2000 10:59:38 -0500 (EST)
> From: "Nathan Clemons [Staff]" <nathan@ici.net>
> To: nocol-users@navya.com
> Subject: SQLMon
>
> I made some changes to the SQL monitor provided by Rick. Let me know how
> this works for you all, seems to work beautifully for me.
>
> --Nathan
>
>
____________________________________________________________________
Nathan Clemons, Systems Engineer
WinStar Internet and Hosting Services
800 South Main St. Mansfield, MA 02048
____________________________________________________________________
nclemons@winstar.com www.winstar.com (v) 800-234-0002 ext.1109
nathan@ici.net (f) 508-261-0430
#!/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
#
## Modified by Nathan Clemons (nclemons@winstar.com)
# v.1.2 -- Made it so it did not use "die" if an attempted connection failed
# or the statement did not execute.
#
# v.1.3 -- Added SIGHUP handling to reread the config file and still preserve
# state information. Please send feedback to me, this may not be all
# finished yet. Also included several changes from Richard.
#
##
#
#
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
#$debug = 1; # 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
# Read the config file. Use '\t' as a separator for 'item'
sub rereadconf {
local ($var);
local ($host);
print "Received SIGHUP, will reread config file...\n";
@items = (); # we're going to rebuild this, so we need to clean
# out all the previous items first.
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
$xseverity{$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;
if (!$varvalue{$item}) { $varvalue{$item} = 1; }
if (!$varthres{$item}) { $varthres{$item} = 0; }
if (!$varunits{$item}) { $varunits{$item} = 'Up'; }
if (!$severity{$item}) { $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;
$SIG{'HUP'} = 'rereadconf';
local ($stime, $deltatime);
## Check state of each router
#
while (1) {
local ($host, $ipaddr) = @_ ;
$stime = time;
foreach $item (@items ) {
($host,$ipaddr) = split(/\t/,$item);
$rc = 0;
$failconnect = 0;
print "(debug) dotest: connecting to database $data_source{$item} ($username{$item}, $password{$item})\n" if $debug;
if ($dbh = DBI->connect($data_source{$item}, $username{$item}, $password{$item})) {
print "(debug) dotest: running command $query_string{$item}\n" if $debug;
if (!($rc = $dbh->do($query_string{$item}))) {
print "(debug) $host : " . $dbh->errstr . "\n" if $debug;
$rc = $failconnect = -1;
}
} else {
print "(debug) $host : $DBI::errstr" if $debug;
$rc = $failconnect = -2;
}
# $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));
}
}
if ($failconnect > -2) { $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 ;
|