Xymon Microsoft SQL data query

Using FreeTDS it is possible to connect from A Linux server to Microsoft SQL Server (in this case I use SQL Server 2005), I use this to run a query on a Microsoft SQL Server from Xymon.

First we need to install the required packages, in this case I use a Debian system:

sudo apt-get install libdbd-odbc-perl libdbi-perl tdsodbc freetds-common

Now configure a datasouce in the /etc/odbci.ini:

[DB01-DATASOURCE]
Driver          = /usr/lib/odbc/libtdsodbc.so
Description     = DB Server
Trace           = No
Server          = 192.168.0.1
Database        = testdb
;Port            = 4444
TDS_Version     = 8.0

And add the server (DB01) to the freetds config /etc/freetds/freetds.conf:

[DB01]
        host = 192.168.0.1
        port = 1433
        tds version = 8.0

Xymon setup:

Now create a xymon /ext/xymon-DB01query.pl script to grab the data from the SQL Server.

Below is a basic version only usable on a single host in xymon and without any red/green/orange checking it wil create a test name ‘DB01query’ inside xymon.

Also I presume getting 2 valuables back from the SQL server that are put in @DATA.

#!/usr/bin/perl -w
#############################################################################
# $Id: $
#############################################################################
use strict;
 
## BB and related test constants
#############################################################################
 
use constant GREEN => 'green';
use constant YELLOW => 'yellow';
use constant RED => 'red';
 
# BB Global variables
#############################################################################
 
my $bbtest = 'DB01query';
my $color = GREEN;
my $status = $bbtest . " OK";
 
## Main Program
#############################################################################
{
my $dbuser = 'CHANGETHIS';
my $dbpass = 'CHANGETHIS';
my $dbds = 'DB01-DATASOURCE';
my $machine = 'DB01';
my $perf = "";
my @DATA = "";
 
use DBI;
 
my $dbh = DBI->connect("dbi:ODBC:$dbds", $dbuser, $dbpass,
{PrintError => 1, RaiseError => 1, LongTruncOk=>1});
 
die "Unable for connect to server $DBI::errstr"
    unless $dbh;
 
my $sql = "select * from database01";
 
my $sth = $dbh->prepare($sql)
 or die "Can't prepare statement: $DBI::errstr";
 
$sth->execute();
 
while ( my @row = $sth->fetchrow_array ) {
 $perf = "@row\n";
}
 
@DATA = split(/ /,$perf);
 
$dbh->disconnect;
 
## Send to Hobbit
#############################################################################
my $report_date = `/bin/date`;
chomp($report_date);
 
system("$ENV{BB} $ENV{BBDISP} 'status $machine.$bbtest $color $report_date \n\n
 
RESULT1 : $DATA[0]
RESULT2: $DATA[1]
'\n");
 
}

I also want graphs from this so add a new section to the hobbitgraph.cfg:

[DB01query]
                TITLE DB01query
                YAXIS #
                DEF:RESULT1=DB01query.rrd:RESULT1:AVERAGE
                DEF:RESULT2=DB01query.rrd:RESULT1:AVERAGE
                LINE1:RESULT1#0000CC:RESULT1
                LINE1:RESULT2#FFFF00:RESULT2
                COMMENT:\n
                GPRINT:RESULT1:LAST:RESULT1\: %5.0lf (cur)
                GPRINT:RESULT1:MAX: \: %5.0lf (max)
                GPRINT:RESULT1:MIN: \: %5.0lf (min)
                GPRINT:RESULT1:AVERAGE: \: %5.0lf (avg)\n
                GPRINT:RESULT2:LAST:RESULT2\: %5.0lf (cur)
                GPRINT:RESULT2:MAX: \: %5.0lf (max)
                GPRINT:RESULT2:MIN: \: %5.0lf (min)
                GPRINT:RESULT2:AVERAGE: \: %5.0lf (avg)\n

Edit the hobbitserver.cfg to include the DB01query in the TEST2RRD as ‘DB01query=ncv’ and change the default mode to GAUGE:

TEST2RRD="cpu=la,disk,inode,qtree,memory,$PINGCOLUMN=tcp,http=tcp,dns=tcp,dig=tcp,time=ntpstat,vmstat,iostat,netstat,temperature,apache,bind,sendmail,mailq,nmailq=mailq,socks,bea,iishealth,citrix,bbgen,bbtest,bbproxy,hobbitd,files,procs=processes,ports,clock,lines,ops,stats,cifs,JVM,JMS,HitCache,Session,JDBCConn,ExecQueue,JTA,TblSpace,RollBack,MemReq,InvObj,snapmirr,snaplist,snapshot,if_load=devmon,temp=devmon,DB01query=ncv"
 
NCV_DB01query="RESULT1:GAUGE,RESULT1:GAUGE"

Now add this new test to the hobbitlaunch.cfg in xymon:

>
[DB01query]
        ENVFILE /vol/01/xymon/server/etc/hobbitserver.cfg
        NEEDS hobbitd
        CMD $BBHOME/ext/xymon-DB01query.pl
        LOGFILE $BBSERVERLOGS/DB01query.log
        INTERVAL 5m

Just wait a while and the test should show up in xymon.