AW: tell me how many are you....

[Neil]

Here it is Andreas. I am very happy to share this perl script I wrote from 
scratch to the Nagios community :) 

There are dependencies though. 

1. You have to have freetds
2. An account on mssql server
3. the store procedure sp_blocker script found on microsoft site(i modified 
it to insert instead to blockcheck table rather than printing)
4. a job that runs this sp every 5-60 seconds(depends how much you like) 

The magic is really simple. I parse the blockcheck table and I order it by 
the time, I do a max on the waittime and that is it. If waittime is greater 
than 20secs, send an alert :) 

Have fun!!! ;) 

Neil 

#!/usr/bin/perl 


##
my ($y, $m, $d, $hh, $mm, $ss) = (localtime)[5,4,3,2,1,0]; $y += 1900; $m++;
# NOTE: 2 was added because, nagios is in WEST COAST and is monitoring a 
machine in CENTRAL
$hh = $hh + 2; 

my $iso_now = sprintf("%d-%02d-%02d %02d:%02d:%02d", $y, $m, $d, $hh, $mm, 
$ss); 

my $hour_result = 0;
my $min_result = 0;
my $day_result = 0;
my $linefromfile;
my $sqlresult;
my $blockwaittime; 

sub timeDiff (%) {
       my %args = @_; 

       my @offset_days = qw(0 31 59 90 120 151 181 212 243 273 304 334); 

       my $year1  = substr($args{'date1'}, 0, 4);
       my $month1 = substr($args{'date1'}, 5, 2);
       my $day1   = substr($args{'date1'}, 8, 2);
       my $hh1    = substr($args{'date1'},11, 2) || 0;
       my $mm1    = substr($args{'date1'},14, 2) || 0;
       my $ss1    = substr($args{'date1'},17, 2) if (length($args{'date1'}) 
> 16);
          $ss1  ||= 0; 

       my $year2  = substr($args{'date2'}, 0, 4);
       my $month2 = substr($args{'date2'}, 5, 2);
       my $day2   = substr($args{'date2'}, 8, 2);
       my $hh2    = substr($args{'date2'},11, 2) || 0;
       my $mm2    = substr($args{'date2'},14, 2) || 0;
       my $ss2    = substr($args{'date2'},17, 2) if (length($args{'date2'}) 
> 16);
          $ss2  ||= 0; 

       my $total_days1 = $offset_days[$month1 - 1] + $day1 + 365 * $year1;
       my $total_days2 = $offset_days[$month2 - 1] + $day2 + 365 * $year2;
       my $days_diff   = $total_days2 - $total_days1; 

       my $seconds1 = $total_days1 * 86400 + $hh1 * 3600 + $mm1 * 60 + $ss1;
       my $seconds2 = $total_days2 * 86400 + $hh2 * 3600 + $mm2 * 60 + $ss2; 

       my $ssDiff = $seconds2 - $seconds1; 

       my $dd     = int($ssDiff / 86400);
       my $hh     = int($ssDiff /  3600) - $dd *    24;
       my $mm     = int($ssDiff /    60) - $dd *  1440 - $hh *   60;
       my $ss     = int($ssDiff /     1) - $dd * 86400 - $hh * 3600 - $mm * 
60; 

       $hour_result = $hh;
       $min_result  = $mm;
       $day_result  = $dd;
} 

## 

my $tsqlcmd="/usr/local/bin/tsql";
my $catcmd="/bin/cat";
my $grepcmd="/bin/grep";
my $rmcmd="/bin/rm";
my $wccmd="/usr/bin/wc";
my $sedcmd="/bin/sed";
my $trcmd="/usr/bin/tr";
my $uniqcmd="/usr/bin/uniq"; 

my $RETVAL = 3;
my $RESULT = 0; 

my $hostname=$ARGV[0];
my $usr=$ARGV[1];
my $pswd=$ARGV[2];
my $srv=$ARGV[3]; 

# echo $hostname $usr $pswd $srv >> /tmp/monitor 

# Creating the command file that contains the sql statement that has to be 
run on the SQL server. 

my $tmpfile="/usr/local/nagios/var/omnitmp";
chomp($tmpfile); 


system "echo -e \"USE dbstats\ngo\nif exists (select * from dbo.sysobjects 
where id = object_id(N'[dbstats].[dbo].[parseblockcheck]') and 
OBJECTPROPERTY(id, N'IsUserTable') = 1) drop table 
[dbstats].[dbo].[parseblockcheck] \ngo\n CREATE TABLE 
[dbstats].[dbo].[parseblockcheck] ( [blocktimestamp] [varchar] (40) COLLATE 
SQL_Latin1_General_CP1_CI_AS NULL , [blockwaittime] [int] NULL ) ON 
[PRIMARY] \ngo\n insert into [dbstats].[dbo].[parseblockcheck] select 
'TIMESTAMP:'+ convert(char(16),Eventtime,120) as blocktimestamp, 
max(waittime) as blockwaittime from dbstats.dbo.blockcheck group by 
eventtime order by eventtime desc \ngo\n select  top 1 
'TIMESTAMP:'+convert(char(16),[dbstats].[dbo].blockcheck.Eventtime,120) + ', 
WAITTIME:'+convert(varchar(10),[dbstats].[dbo].blockcheck.waittime) + ' ms' 
+ '('+ convert(varchar(10),([dbstats].[dbo].blockcheck.waittime/1000)) +' 
secs)' + ', SPID Blocker:' + convert(varchar(5),SPID_Blocker) from 
[dbstats].[dbo].[parseblockcheck] inner join [dbstats].[dbo].blockcheck on 
dbstats.dbo.parseblockcheck.blocktimestamp='TIMESTAMP:'+ 
convert(char(16),Eventtime,120) and 
dbstats.dbo.parseblockcheck.blockwaittime=[dbstats].[dbo].blockcheck.waittim 
e order by blockcheck.eventtime desc\ngo\" > $tmpfile"; 


my $resultfile="/usr/local/nagios/var/omniresultfile";
my $errorfile="/usr/local/nagios/var/omnierrorfile"; 

chomp($resultfile);
chomp($errorfile); 

system "/usr/local/bin/tsql -S $hostname -U 'svcaccount' -P 'password' < 
$tmpfile 2>$errorfile > $resultfile"; 

system "$grepcmd -q \"Login failed for user\" $errorfile";
$RESULT = `echo $?`;
chomp($RESULT); 

if ( $RESULT == 0) {
       #system "$rmcmd -f $tmpfile $resultfile $errorfile";
       print "CRITICAL - Could not make connection to SQL server. Login 
failed.";
       exit 2;
} 

system "$grepcmd -q \"There was a problem connecting to the server\" 
$errorfile";
$RESULT = `echo $?`;
chomp($RESULT); 

if ( $RESULT == 0) {
       #system "$rmcmd -f $tmpfile $resultfile $errorfile";
       print "CRITICAL - Could not make connection to SQL server. Incorrect 
server name or SQL service not running.";
       exit 2;
} 

my $resultfileln=`$catcmd $resultfile | $wccmd -l | $sedcmd 's/  //g'`;
chomp($resultfileln); 

if ($resultfileln == 2 ) {
       #system "$rmcmd -f $tmpfile $resultfile $errorfile";
       print "CRITICAL - Could not make connection to SQL server. No data 
received from host.";
       exit 2;
}
else {
       open (RESULTFILE, $resultfile); 

       while ($linefromfile = <RESULTFILE>) {
          if ( $linefromfile =~ /TIMESTAMP/ ) {
               $sqlresult =  $linefromfile;
               chomp($sqlresult);
          }
       }
       close(RESULTFILE); 

       # my $sqlresult=`$catcmd $resultfile | $grepcmd -v locale | $grepcmd 
 -v charset| $grepcmd -v '1>'`;
       my $datetime_from_blockcheck = $sqlresult;
       $blockwaittime = $sqlresult; 

       # sample: TIMESTAMP:2004-06-23 13:59:43.527, WAITTIME:5125 ms(5 secs)
       # TIMESTAMP:2004-06-23 12:47, WAITTIME:1235 ms(1 secs), SPID 
Blocker:360
       $datetime_from_blockcheck =~ 
s/^TIMESTAMP:(\d+-\d+-\d+\s\d+:\d+).*/$1/;
       $blockwaittime =~ 
s/^TIMESTAMP:\d+-\d+-\d+\s\d+:\d+,\sWAITTIME:\d+\sms.*(\d+)\ssecs.*/$1/; 

       my $timeDiffStr = &timeDiff( date1 => $datetime_from_blockcheck, 
date2 => $iso_now ); 

       if ($day_result < 1 ) {
          if ($hour_result < 1) {
             if ($min_result <= 15) {
                if ($blockwaittime >= 20) {
                   $RETVAL=2;
                   $RETSTR=$sqlresult;
                   print "[CRITICAL] $RETSTR";
#                   system "$rmcmd -f $tmpfile $resultfile $errorfile";
                   exit $RETVAL;
                }
             }
          }
       }
       $RETVAL=0;
       $RETSTR="System is in OK STATE. Please verify from Omni DB 
administrator if SPID blocker has been cleared.";
       print $RETSTR;
       #system "$rmcmd -f $tmpfile $resultfile $errorfile";
       exit $RETVAL;
} 


exit $RETVAL; 


Andreas Ericsson writes: 

> Neil wrote:
>> Thanks guys. I now have a baseline on how to spec our Nagios machine. I 
>> only have one more battle left and that is the management decision 
>> between choosing Servers Alive(M$0ft Window based) and Nagios. They like 
>> the Nagios because it is so flexible and we can write our own custom 
>> checks. I recently wrote a check that will return a value of CRITICAL(2) 
>> to Nagios if there is a deadlock going on in an MSSQL server.
> 
> Can you send that to me or publish it to the list? I've been looking to do 
> the same for some time now. 
> 
>> The only problem they have with Nagios is that it is open source. But I 
>> have given them tons of good information/advantages about opensource. I 
>> hope, within this week, we'll find out who the winner is.
> 
> I wish you the best of luck. 
> 
>> Thanks again.
>> Neil 
>> 
> 
> -- 
> Sourcerer / Andreas Ericsson
> OP5 AB
> +46 (0)733 709032
> andreas.ericsson at op5.se 
> 
> 
> -------------------------------------------------------
> This SF.Net email sponsored by Black Hat Briefings & Training.
> Attend Black Hat Briefings & Training, Las Vegas July 24-29 - digital self 
> defense, top technical experts, no vendor pitches, unmatched networking 
> opportunities. Visit www.blackhat.com
> _______________________________________________
> Nagios-users mailing list
> Nagios-users at lists.sourceforge.net
> https://lists.sourceforge.net/lists/listinfo/nagios-users
> ::: Please include Nagios version, plugin version (-v) and OS when 
> reporting any issue. ::: Messages without supporting info will risk being 
> sent to /dev/null
 


-------------------------------------------------------
This SF.Net email sponsored by Black Hat Briefings & Training.
Attend Black Hat Briefings & Training, Las Vegas July 24-29 - 
digital self defense, top technical experts, no vendor pitches, 
unmatched networking opportunities. Visit www.blackhat.com
_______________________________________________
Nagios-users mailing list
Nagios-users at lists.sourceforge.net
https://lists.sourceforge.net/lists/listinfo/nagios-users
::: Please include Nagios version, plugin version (-v) and OS when reporting any issue. 
::: Messages without supporting info will risk being sent to /dev/null