[Date Prev]   [Date Next] [Thread Prev]   [Thread Next] [Date Index]   [Thread Index]

 

     Re: [nocol-users] MySQL monitor?

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