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.

Xymon Airco Temperature graph

Xymon Airco Temperature graph

In this case I will be using Xymon and a HWg-STE device to graph the temperature of 2 Airco units in a server room. To get this in Xymon you will need to create a custom Xymon test.

Note that in this case I am monitoring the airco output temperature (sensors in front of the airco), the overall room temperature is not measured here.

The output on the Xymon page will be something like this:

Airco_L : 167
Airco_R : 116
 
AverageT: 141.5

And the resulting graph something like this:

The HWg-STE is a SNMP enabled device so I will be using the perl NET:SNMP module to get the data from the device and import it in Xymon, which also means this should work with any snmp enabled device as long as you know the right SNMP OID’s.

First we start with the xymon ext script this is placed on the Xymon server in the ‘ext’ directory.
In this script you should change the values for the min/max temperature and ofcourse the device settings like the ip, community and oid value’s

xymon-temp01-sensors.pl:

#!/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 = 'temp01';
my $machine = 'TEMP01';
my $color = GREEN;
my $status = $bbtest . " OK";
 
## Main Program
#############################################################################
{
use Net::SNMP;
 
my $snmphost = '<IPOFTHEDEVICE>';
my $snmpcomm = '<SNMPCOMMUNITY>';
my $temphigha = 210;		# Average Temp High
my $tempwarna = 200;		# Average Temp Warning
 
my $temphigh1 = 200;		# Airco1 Temp High
my $templow1 = 100;		# Airco1 Temp Low
my $temphigh2 = 200;		# Airco2 Temp High
my $templow2 = 100;		# Airco2 Temp Low
my $session = '';
my $error = '';
 
# requires a hostname and a community string as its arguments
($session,$error) = Net::SNMP->session(Hostname => $snmphost,
                                       Community => $snmpcomm);
 
die "session error: $error" unless ($session);
 
my $result1 = $session->get_request(".1.3.6.1.4.1.21796.4.1.3.1.5.1");
die "request error: ".$session->error unless (defined $result1);
 
my $result2 = $session->get_request(".1.3.6.1.4.1.21796.4.1.3.1.5.2");
die "request error: ".$session->error unless (defined $result2);
 
$session->close;
 
my $temp1 = "".$result1->{".1.3.6.1.4.1.21796.4.1.3.1.5.1"}."";
my $temp2 = "".$result2->{".1.3.6.1.4.1.21796.4.1.3.1.5.2"}."";
 
my $tempa = (($temp1 + $temp2) / 2);
 
if ($temp1 > $temphigh1){ $color = "RED"; }
 elsif ($temp1 < $templow1) { $color = "YELOW"; }
 
if ($temp2 > $temphigh2){ $color = "RED"; }
 elsif ($temp2 < $templow2) { $color = "YELOW"; }
 
if ($tempa > $temphigha){ $color = "RED"; }
 elsif ($tempa > $tempwarna) { $color = "YELLOW"; }
 
 
## Send to Hobbit
#############################################################################
my $report_date = `/bin/date`;
chomp($report_date);
 
system("$ENV{BB} $ENV{BBDISP} 'status $machine.$bbtest $color $report_date \n\n
Airco_L : $temp1
Airco_R : $temp2
 
AverageT: $tempa
'\n");
 
}

Now we need to let the xymon server know to use this script so edit the hobbitlaunch.cfg file and add the following to the end of the file:

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

Ofcourse we want to graph the data so edit the hobbitgraph.cfg file and add the following to the end of the file:

[temk01]
                TITLE Airco Temperature
                YAXIS Temp (c) / 10
                DEF:AircoL=temk01.rrd:AircoL:AVERAGE
                DEF:AircoR=temk01.rrd:AircoR:AVERAGE
                DEF:AverageT=temk01.rrd:AverageT:AVERAGE
                AREA:AverageT#00FF0066:AverageT
                LINE1:AverageT#00BD27FF:
                LINE1:AircoL#F51D30:AircoL
                LINE1:AircoR#0000FF:AircoR
                COMMENT:\n
                GPRINT:AircoL:LAST:AircoL \: %5.0lf (cur)
                GPRINT:AircoL:MAX: \: %5.0lf (max)
                GPRINT:AircoL:MIN: \: %5.0lf (min)
                GPRINT:AircoL:AVERAGE: \: %5.0lf (avg)\n
                GPRINT:AircoR:LAST:AircoR \: %5.0lf (cur)
                GPRINT:AircoR:MAX: \: %5.0lf (max)
                GPRINT:AircoR:MIN: \: %5.0lf (min)
                GPRINT:AircoR:AVERAGE: \: %5.0lf (avg)\n
                GPRINT:AverageT:LAST:AverageT \: %5.0lf (cur)
                GPRINT:AverageT:MAX: \: %5.0lf (max)
                GPRINT:AverageT:MIN: \: %5.0lf (min)
                GPRINT:AverageT:AVERAGE: \: %5.0lf (avg)\n

To add the data into a RRD file we need to change the TEST2RRD line to include our new temp01 test, so edit the hobbitserver.cfg file and add temp01=ncv to the end of the TEST2RRD config line so it will look something like this:

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,temp01=ncv"

This means the data is processed by the NCV module, default the NCV module uses DERIVE but in this case we need GAUGE (look at the RRDTOOL website for the differences) so now just add the following line below the TEST2RRD config in the hobbitserver.cfg file:

NCV_temk01="AircoLinks:GAUGE,AircoRechts:GAUGE,Gemiddelde:GAUGE"

Edit the bb-hosts file to include the ‘temp01′ test (note that TEMP01 is also the hostname of the device)

xxx.xxx.xxx.xxx      TEMP01          # testip temp01

And your finished, just wait a while and the test should show up in Xymon as the ‘temp01′ column.

A few notes:

  • The column ‘temp01′ is only usable on 1 device in the bb-hosts, the xymon-temp01-sensors.pl script could be changed to work with the value’s from the bb-hosts file.
  • The perl script could be written a bit beter :)

References:

BigBrother SNMP users

Quick way of getting the amount of logged in users on a windows 2003 terminal server, from BigBrother. this is not a howto just a quick reference for myself, if someone try’s this and has problems just let me know, mayb I forgot something here.

Using larrd and rrdtool you can get the info into a nice graph.
* note that this does not work on windows 2003 SP1.


First create the snmpusers bigbro script to monitor snmp, this is a modified script of Gavin Goes

trouble@sun:$ mkdir $bbhome/ext/snmp
trouble@sun:$ vi snmpusers.sh
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
#!/bin/sh
 
# snmpusers v0.1
# this wil monitor the hrSystemNumUsers.0 from a win2k3 box.
#
#######################
# Original header
#######################
# BIG BROTHER - SCRIPT TO SUCK OUT CPU UTILIZATION FROM A
# WINDOWS 2000 Server running SNMP with MIB-II
# Gavin Goes - Cymple Solutions [gavin @ cymplesolutions.com]
#
# Please note that this is a very crude hack of the snmp v0.5 script
# by Sean Macguire. The script can be modified to check any OID on a
# host for comparison... (see OID check section)
#
# This program is under a BSD-style license, you may change it at
# will and redistribute, but giving credit is good manners...
 
# TO RUN THE SNMPCPU TEST ADD THE KEYWORD snmpusers TO THE "BBEXT"
# VARIABLE OF $BBHOME/ext/bbdef.sh.  THIS IS FOR SECURITY.
 
# THE EASIEST INSTALLATION OF THIS SCRIPT INVOLVES PLACING THE
# SCRIPT IN THE $BBHOME/ext directory, and adding the line:
#
#       : : snmpusers.sh
#
# TO THE $BBHOME/etc/bb-bbexttab FILE. TRY ADDING A HOST TO YOUR
# $BBHOME/etc/bb-hosts FILE FOLLOWING THIS EXAMPLE:
#
#  10.50.50.1   deadcat.domain.org # snmpusers
#
 
# BBPROG SHOULD JUST CONTAIN THE NAME OF THIS FILE
# USEFUL WHEN YOU GET ENVIRONMENT DUMPS TO LOCATE
# THE OFFENDING SCRIPT...
#
BBPROG=snmpusers; export BBPROG
 
TEST="snmpusers"
 
# BBHOME CAN BE SET MANUALLY WHEN TESTING.
# OTHERWISE IT SHOULD BE SET FROM THE BB ENVIRONMENT
#
BBHOME=/home/bigbro/bb/; export BBHOME  # FOR TESTING
 
if test "$BBHOME" = ""
then
        echo "BBHOME is not set... exiting"
        exit 1
fi
 
if test ! "$BBTMP"                      # GET DEFINITIONS IF NEEDED
then
         # echo "*** LOADING BBDEF ***"
        . $BBHOME/etc/bbdef.sh          # INCLUDE STANDARD DEFINITIONS
fi
 
#
# SET UP THE SNMP COMMANDS WE'LL NEED (AS WELL AS THE MIB DEFINITIONS
# WHICH USUALLY ARE IN /usr/local/share/snmp/mibs
# IF NOT, ACQUIRE THEM AND PLACE IN THERE
#
MIBS=ALL; export MIBS
SNMPGET="/usr/bin/snmpget"
SNMPWALK="/usr/bin/snmpwalk"
SED="/bin/sed"
 
if test ! -x "$SNMPGET"
then
        echo "Can't find snmpget! [$SNMPGET]... exiting"
        exit 1
fi
if test ! -x "$SNMPWALK"
then
        echo "Can't find snmpwalk! [$SNMPWALK]... exiting"
        exit 1
fi
 
#
# LOOK FOR THE snmpusers KEYWORD IN THE etc/bb-hosts FILE
# BUT MAKE SURE TO IGNORE COMMENTS
#
$GREP snmpusers $BBHOME/etc/bb-hosts | grep -v "^#" |
while read line
do
        set $line                       # GET ALL THE LINE ARGS
        HOST="$1"
        MACHINEMASK="$2"; export MACHINEMASK
 
        if test "$HOST" = "0.0.0.0"     # SUPPORT USING NAMES...
        then
                HOST="$MACHINEMASK"
        fi
 
        if test "$FQDN" != "TRUE"
        then
                OLDIFS="$IFS"
                IFS="."
                set $MACHINEMASK
                MACHINEMASK="$1"
                IFS="$OLDIFS"
        fi
 
        # echo "DOING HOST $MACHINEMASK"
        BOX=`echo $MACHINEMASK | $SED "s/./,/g"`
 
        # COMMUNITY WILL DEFAULT TO "public" UNLESS OTHERWISE
        # STATED IN $BBHOME/etc/bb-smtptab
 
        COMMUNITY="public"
 
        if test -f $BBHOME/etc/bb-snmptab       # IF NOT "public"
        then
                # SEARCH BY IP ADDRESS
                COMMUNITY=`grep "$HOST" $BBHOME/etc/bb-snmptab`
                if test "$COMMUNITY" != ""
                then
                        set $COMMUNITY
                        COMMUNITY="$2"
                else    # SEARCH BY NAME
                        COMMUNITY=`grep "$MACHINEMASK" $BBHOME/etc/bb-snmptab`
                        if test "$COMMUNITY" != ""
                        then
                                set $COMMUNITY
                                COMMUNITY="$2"
                        else
                                COMMUNITY="public"
                        fi
                fi
        fi
 
### OID Check section
### Modify the appropriate setting and OID to suit your needs for
### your specific device... Use a utility like GetIF to find out
### what OIDs your device can monitor
 
        # GET THE AMOUNT OF USERS FOR THIS DEVICE...
        SYSTEMUSERS=`$SNMPGET -v2c -c$COMMUNITY $HOST hrSystemNumUsers.0 2&gt;&amp;1`
 
                set $SYSTEMUSERS
                echo "1= $*" #just for debugging
                shift; shift; shift;
                echo "2 =$*" #just for debugging
                SYSTEMUSERS=`echo $* | $SED "s/\"//g"`
                REALUSERS="`/usr/bin/expr $SYSTEMUSERS - 1`"
 
        # Check if the value of the number of users is greater then
        # or equal to a specific value...
        if [ "$REALUSERS" -ge "50" ]
        then
                # SEND USERS MESSAGE AND EXIT
                $BB $BBDISP "status ${BOX}.${TEST} red `date` The number of users is $REALUSERS"
                continue;
        else
                COLOR="green"; TEST="snmpusers";
                $BB $BBDISP "status ${BOX}.${TEST} $COLOR `date` $LINE The number of users is $REALUSERS"
        fi
 
done

* don’t forget to chmod 755 the file.

Then add it to the bbconfig file:

trouble@sun:$ vi $bbhome/etc/bb-bbexttab
1
 : :snmp/snmpusers.sh

To get graphs create a file for larrd:

trouble@sun:$ cd /usr/local/larrd
trouble@sun:$ vi snmpusers-larrd.pl
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
#!/usr/bin/perl -w -w
#
# Set a path to the rrd tool lib
# Set as and $ENV and see what is up
use lib $ENV{'RRDPM'};
use RRDs;
 
$ds = "snmpusers";
 
@ds = ("DS:snmpusers:GAUGE:600:0:U",
       );
 
##################################
# Start of script
##################################
 
if( not $ENV{'BBHOME'} ) {
        print "$0: BBHOME is not setn";
        exit(1);
}
 
if( not -d $ENV{'BBHOME'} ) {
        print "$0: BBHOME is invalidn";
        exit(1);
}
 
if( not -d $ENV{'BBRRDS'} ) {
        print "$0: BBRRDS is not setn";
        exit(1);
}
 
##
# real work begins here
##
# go to the logs area for simplication of the list
#chdir "$ENV{'BBLOGS'}";
chdir "/home/bigbro/bbvar/logs";
 
# Glob the logs
LOG: while (  defined ($log = &lt;*.$ds&gt;)  ) {
        $host=$log;
        # create some handy vars
        $host =~ s/.$ds//;
        $host =~ s/,/./g;
        ($ctime) = (stat($log))[10];
 
        $colour_check="notok";
 
        # Grab lines
        open(LOG,"$log");
        while ( defined ($line = <log>) ) {
          chomp $line;
 
          # ensure colour is valid - ie don't graph purple results
          if($line =~ /green|yellow|red/) {
            $line =~ /users iss+(d+)/o;
            $users=$1;
            $colour_check="ok";
 
          } # end line colour check
 
          if ($colour_check ne "ok") {
                print "$0: WARN $host is not showing red, yellow, or green. $log skipped.n" if ($ENV{'WARN'});
                next LOG;
          }
 
        } # end while
        close(LOG);
 
        print "$0: DEBUG users: $usersn" if $ENV{'DEBUG'};
 
        # RRD for users
 
        $RRD="$ENV{'BBRRDS'}/$host.snmpusers.rrd";
 
        # if it doesn't exist, make it
        if ( not -f $RRD ) {
                        # RRAS defined in larrd.pl
                        @rras = split " ",$ENV{'RRAS'};
                        RRDs::create($RRD,@ds,@rras);
                        $ERR=RRDs::error;
                        if($ERR) {
                                print "$0: ERROR creating $RRD: $ERRn" if ($ENV{'ERROR'});
                                next;
                        }
                $ctime="N";
                print "$0: STATUS did not find $RRD, created.n" if ($ENV{'STATUS'});
        }
 
        RRDs::update("$RRD","$ctime:$users");
        $ERR=RRDs::error;
        if($ERR) {
               print "$0: WARN updating $RRD: $ERRn" if ($ENV{'WARN'});
        }
 
        # Sloppy
        ($ctime) = (stat($log))[10];
 
}
##############################################
# end of script
##############################################
</log>

Change the data list in larrd-config

trouble@sun:$ vi /usr/local/larrd/larrd-config.pl
1
@DATALIST=("la","disk","bbnet","vmstat","netstat","users","procs","memory","snmpusers");

Find your larrd-grapher.cgi and add the following section somewhere around line: 1470

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
 snmpusers =&gt; {
    hourly =&gt; {
        start_sec =&gt; "e-48h",
        title =&gt; "$host # of Users Last 48 Hours",
    },
    daily =&gt; {
        start_sec =&gt; "e-12d",
        title =&gt; "$host # of Users Last 12 Days",
    },
    weekly =&gt; {
        start_sec =&gt; "e-48d",
        title =&gt; "$host # of Users Last 48 Days",
    },
    monthly =&gt; {
        start_sec =&gt; "e-576D",
        title =&gt; "$host # of Users Last 576 Days",
    },
    yaxis =&gt; "#",
    lines =&gt; sub {
        my ($RRD,$service,$graph,$p,$color)=@_;
 
        return [
#            qq{DEF:p=$host.snmpusers.rrd:la:AVERAGE},
            qq{DEF:p=$host.snmpusers.rrd:snmpusers:AVERAGE},
            qq{AREA:p#00CC00:Users},
            qq{COMMENT:n},
            qq{GPRINT:p:LAST:Users   : %5.1lf%s (cur)},
            qq{GPRINT:p:MAX: : %5.1lf%s (max)},
            qq{GPRINT:p:MIN: : %5.1lf%s (min)},
            qq{GPRINT:p:AVERAGE: : %5.1lf%s (avg)n},
        ]
    },
  }

Restart bigbro and it should work.