#!/usr/bin/perl

=head1 NAME

sqlmonitor - A database monitoring client written for Peep: The
Network Auralizer.

=head1 DESCRIPTION

Executes one or more queries and compares the results to allowed
maximum or minimum return values.

This is meant as a pedagogical example of a Peep client.  If you
modify or improve upon it, please contact the author.  I would enjoy
seeing your work!

=head1 USAGE

  ./sqlmonitor --help

  ./sqlmonitor --noautodiscovery --server=localhost --port=2001 --noda
+emon

  ./sqlmonitor --dsn=dbi:Oracle:MYCONSTR -dbuser=myuser --dbpass=mypas
+s

  ./sqlmonitor

If you have any problems, try turning on debugging output with
something like --debug=9.

The option --dsn is the data source (e.g., 'dbi:mysql:mydb:localhost:3
+307').
The option --dbuser is the database user name.
The option --dbpass is the database password.

For a description of all other available command-line options, see 
Net::Peep::Client.

=head1 CONFIGURATION

To use this client, include a section like the following in peep.conf:

  client sqlmonitor
    class home
    port 1999
    config
      # Event    Max     Min   Notification  SQL
      bad-users  0       0     warn          /home/bogus-user/sql/bad-
+users.sql
      bad-count  5       10    crit          /home/bogus-user/sql/bad-
+count.sql
    end config
    notification
      notification-hosts localhost
      notification-recipients bogus.user@bogusdomain.com
notification-level crit
    end notification
  end client sqlmonitor

and another section in the events block with something like

  events
  #Event Type      |          Path to Sound File           | # of soun
+ds to load
  ...
  bad-users          /usr/local/share/peep/sounds/misc/events/rooster.
+*        1
  bad-count          /usr/local/share/peep/sounds/misc/events/doorbell
+.*       1
  end events

The file C</home/bogus-user/sql/bad-users.sql> might look something li
+ke

  SELECT COUNT(*) FROM users WHERE username=passwd

In this case, you would be checking whether any users are dumb enough
to make their password the same as their user name.  (Hey, this is
just an example.)


The file C</home/bogus-user/sql/bad-count.sql> might look something li
+ke

  SELECT COUNT(*) FROM session

In this case, you would be checking whether the number of sessions
(e.g., users logged in) is within expectations.

=head1 AUTHOR

Collin Starkweather <collin.starkweather@colorado.edu> Copyright (C) 2
+001

=head1 SEE ALSO 

perl(1), peepd(1), Net::Peep, Net::Peep::Client, Net::Peep::BC,
Net::Peep::Notifier, Net::Peep::Notification, DBI, DBD::Oracle

http://peep.sourceforge.net

=cut

# Always use strict  [:-)] 
use strict;
use Net::Peep::BC;
use Net::Peep::Log;
use Net::Peep::Client;
use Net::Peep::Notifier;
use Net::Peep::Notification;
use DBI;
use vars qw{ %config $logger $client $dbh $conf %sth %sql };

my ($dsn,$dbuser,$dbpass) = ('dbi:Oracle:CONSTR','bogususer','boguspas
+s');

# The options here are in Getopt::Long style. (Read the PODs for more
# info.) The %options hash allows you to get your dsn, username, and
# password information from the command line
my %options = ( 'dsn=s' => \$dsn, 'dbuser=s' => \$dbuser, 'dbpass=s' =
+> \$dbpass);

$logger = new Net::Peep::Log;
$client = new Net::Peep::Client;

$client->name('sqlmonitor');

$client->initialize(%options) || $client->pods();

$dbh = DBI->connect($dsn,$dbuser,$dbpass) || die $DBI::errstr;

$client->parser( \&parse );
$conf = $client->configure();
$client->callback( \&loop );

$SIG{'INT'} = $SIG{'TERM'} = \&shutdown;

$client->MainLoop(60);

sub parse {
    for my $line (@_) {
    if ($line =~ /^\s*([\w\-]+)\s+(\d+)\s+(\d+)\s+(info|warn|crit)\s+(
+.*)$/) {
            $logger->log("Found event [$1], max [$2], min [$3], notifi
+cation [$4], and SQL file [$5].\n");
        $config{$1} = { max=>$2, min=>$3, notification=>$4, sql=>$5 };
    }
    }
} # end sub parse

sub loop {

    for my $key (sort keys %config) {
    $logger->log("Checking [$key] ...");
    # get the SQL statement if it hasn't been loaded already ...
    unless (exists $sql{$key}) {
        if (open(SQL,$config{$key}->{'sql'})) {
        $sql{$key} = join '', <SQL>;
                chomp($sql{$key});
        } else {
        warn "Cannot open [$config{'sql'}]:  $!";
        next;
        }
    }
    # we'll cache our statement handles for efficiency ...
    unless (exists $sth{$key}) {
            $logger->log("Preparing SQL $sql{$key}\n");
        $sth{$key} = $dbh->prepare($sql{$key}) ||
                $logger->log("Error preparing SQL:  $DBI::errstr");
    }
    if (exists $sth{$key} and defined $sth{$key}) {
        # execute the query
        my $rv = $sth{$key}->execute();
        # get the results
        my (@results) = $sth{$key}->fetchrow_array();
        $logger->log("The result of [$key] is [$results[0]].");
            my ($max,$min) = ($config{$key}->{'max'},$config{$key}->{'
+min'});
        if ($results[0] > $max || $results[0] < $min) {
        $logger->log("\tThe count for [$key] is out of bounds!  Sendin
+g notification ....");
        # The price is out of bounds!  We'll start peeping ...
        my $broadcast = Net::Peep::BC->new('sqlmonitor',$conf);
        $broadcast->send('sqlmonitor', type=>0, sound=>$key, location=
+>128, priority=>0, volume=>255);
        # In case we're away from our desk, we'll also send out an
        # e-mail notification.  Don't want to miss the action!
        my $notifier = new Net::Peep::Notifier;
        my $notification = new Net::Peep::Notification;
        $notification->client('sqlmonitor');
        $notification->status($config{$key}->{'notification'});
        $notification->datetime(time());
        $notification->message(<<"eop");
The result of the query
--
$sql{$key}
--
is [$results[0]].  It should be less than $max and greater than $min.
eop
                ;
        $notifier->notify($notification);
        }
    }
    }

} # end sub loop

sub shutdown {

    # we should clean up after ourselves ...
    print STDERR "Shutting down ...\n";
    if (defined($dbh)) {
    print STDERR "\tDisconnecting ...\n";
    for my $key (%sth) {
            if (defined($sth{$key})) {
            print STDERR "\t\tFinishing statement handle [$sth{$key}] 
+...\n";
                $sth{$key}->finish();
            }
        }
    $dbh->disconnect();
        print STDERR "\tDone.\n";
    }
    print STDERR "Done.\n";
    exit(0);

} # end sub shutdown

__END__
[download]