RE: {bb} SQL query checks

["Kimberly McKinnis" <kmckinnis@macrovision.com>]

I have a script that may serve as a decent example for you. 

It runs 'show table status' to calculate free tablespace. Just replace
the query and tweak as needed. We hard coded a lot of variables out of
laziness (and this was before I'd been using BB as long as I have now.)
so those may need changed. 

You'll want to drop perl script this into the ext dir on the db machine
it will be checking. Then put the name of the script into
etc/bb-bbexttab on the machine it's run on. Then on your BBPAGER host,
in bbwarnsetup.cfg, assign a number to your column for svcerrlist. 


#!/usr/bin/perl

# (C) 2004 Jonathan Disher <jdisher@macrovision.com>

$ENV{BBHOME} || die "$0: BBHOME not set\n";

use DBIx::DWIW;
use Time::Local;

$|=1;

$warnfree = 204800000; # 200GB
$critfree = 153600000; # 150GB
$color = "purple"; #so no info means bad!

my $db = DBIx::DWIW->Connect(   DB      => 'production',
                                User    => 'root',
                                Pass    => 'passwordgoeshere',
                                Host    => 'localhost',
                                Port    => 3306) or die;

$ENV{'BBPROG'}="tblspc";
$BB = "/usr/local/bb/bbc1.9e-btf/bin/bb";
$BBDISP = "10.230.19.84";
$TEST = "tblspc";
$svc = "tblspc";
$MACHINE = `hostname -f`;
$DATE = `date`;
chomp($MACHINE);
chomp($DATE);

# $MACHINE =~ s/\./,/g;

@status = $db->Arrays('show table status');

$totidx = $totdata = $totrows = $cnt = 0;
$num = scalar @status;

$free = $status[$cnt][14];

$free =~ /InnoDB free: (\d+) kB/;
$idbfree = $1;

if ($idbfree < $critfree) { $color = "red"; $status = "critical";
$return = "Critical InnoDB Free Space: $idbfree (Limit = $critfree)"; }
elsif ($idbfree < $warnfree) { $color = "yellow"; $status = "warning";
$return = "Warning InnoDB Free Space: $idbfree (Limit = $warnfree}"; }
else { $color = "green"; $status = "ok"; $return = "INNODB ok - InnoDB
Free Space: $idbfree"; }

$line = "status $MACHINE.$TEST $color $DATE - $STATUS ".$return;

# Now do the work
# system("$BB $BBDISP \"$line\"");
#print "system(\"$BB $BBDISP \"$line\"\");\n";

if ( $ENV{'BB'} && $ENV{'BBDISP'} )
  {
    `$ENV{'BB'} $ENV{'BBDISP'} \"$line\"`;
  }
  else
  {
    print $line, "\n";
  }


-----Original Message-----
From: owner-bb@bb4.com [mailto:owner-bb@bb4.com] On Behalf Of Bruce
Lysik
Sent: Friday, November 05, 2004 2:45 PM
To: 'bb@bb4.com'
Subject: {bb} SQL query checks


Hi,

I've been given ownership of the bigbrother monitoring at my company,
and
need to create some custom checks which simply use perl DBI to connect
to a
remote database, run an SQL query, and then get the result back into BB.
I'm familiar with the perl DBI side of this, but I'm not sure how to
wrap
this into BB.

Should I write BB client extension for this (following the example in
ext/ext-prot) which runs on the database machine?  Any advice is
appreciated.  Thanks.

--
Bruce Z. Lysik  <blysik@shutterfly.com>
--
=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-==-=-=-=-=-=-=-=-=-=-=-=
To unsubscribe from this list, or to subscribe to the bb-digest list
send e-mail to mailto:majordomo@bb4.com with unsubscribe bb -and/or-
subscribe bb-digest in the BODY of the message.



--
=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-==-=-=-=-=-=-=-=-=-=-=-=
To unsubscribe from this list, or to subscribe to the bb-digest list
send e-mail to mailto:majordomo@bb4.com with unsubscribe bb -and/or-
subscribe bb-digest in the BODY of the message.