[nocol-users] SQLMon

["Nathan Clemons [Staff]" <nathan at ici net>]

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
#
##
#
#
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


&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);

                $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" if $debug; 
                      $failconnect = -1;
                   }
                } else {
                   print "(debug) $host : $DBI::errstr" if $debug; 
                   $rc = $failconnect = -1; 
                }

#		$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 > -1) { $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 ;