Nagios tsql query script - block checking

[Pablonhd]

Hi All,

Trying to write a script that will check a ms-sql 2000 server for blocks and tell me which stations are causing the issue. My sql query works well on the server itself but from the linux box I am getting no results or error that help me.

Here is the code:

#!/bin/bash
# This script is designed to be used by Nagios. It checks for blocks on MSSQL.
#
# Requirements:
#
# FreeTDS 6.0+ ([URL unfurl="true"]http://www.freetds.org/)[/URL]
#
# Original check_mssql.sh written by Tom De Blende (tom.deblende@village.uunet.be) in 2003.
#
# Version 1.0. First attempt
#
#
#
# You might want to change these values:

tsqlcmd=`which tsql`
catcmd=`which cat`
grepcmd=`which grep`
rmcmd=`which rm`
mktempcmd=`which mktemp`
wccmd=`which wc`
sedcmd=`which sed`
trcmd=`which tr`
uniqcmd=`which uniq`

###################################################################################################################

hostname=$1
usr=$2
pswd=$3
srv=$4

if [ ! "$#" == "4" ]; then
        echo -e "\nYou did not supply enough arguments. \nUsage: $0 <host> <username> <password> <version> \n \n$0 checks Microsoft SQL Server connectivity.$

elif [ $tsqlcmd == "" ]; then
        echo -e "tsql not found! Please verify you have a working version of tsql (included in the FreeTDS version 6.0+) and enter the full path in the scri$

fi

exit="3"


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

tmpfile=`$mktempcmd /tmp/$hostname.XXXXXX`

if [ $srv == "7" ]; then
        spid=7
elif [ $srv == "2000" ]; then
        spid=50
else
        echo -e "$srv is not a supported MS SQL Server version!" && exit "3"
fi

echo -e "declare @SPIDDetail table (
                  SPID        smallint,
                  sql_handle  binary(20),
                  SQL         varchar(4000))
            declare @Curr           binary(20),
                  @SQL        varchar(4000),
                  @CurrSPID   smallint
            insert into @SPIDDetail
            select distinct   t1.spid
                        , t1.sql_handle
                        , null
            from        sysprocesses t1 (nolock)
            inner join  sysprocesses t2 (nolock)
                  on    t1.spid = t2.blocked
                  and   t1.ecid = t2.ecid
                  and   t1.blocked = 0

            select      @CurrSPID = min(SPID) from @SPIDDetail
            while @CurrSPID is not null
            begin
                  select      @Curr = sql_handle from @SPIDDetail where spid = @CurrSPID

                  set @SQL = null
                  select      top 1 @SQL = convert(nvarchar(4000), [text])
                  from  ::fn_get_sql(@Curr)
                  update      @SPIDDetail
                  set   SQL = @SQL
                  where SPID = @CurrSPID

                  select      @CurrSPID = min(spid) from @SPIDDetail where spid > @CurrSPID
            end

            select
                         t1.hostname
            from        sysprocesses t1 (nolock)
            inner join  sysprocesses t2 (nolock)
                  on    t1.spid = t2.blocked
                  and   t1.ecid = t2.ecid
                  and   t1.blocked = 0
            inner join  @SPIDDetail s
                  on    t1.spid = s.spid
            order by t1.spid, t1.ecid\ngo"  > $tmpfile

# Running tsql to get the results back.

resultfile=`$mktempcmd /tmp/$hostname.XXXXXX`
errorfile=`$mktempcmd /tmp/$hostname.XXXXXX`
$tsqlcmd -S $hostname -U $usr -P $pswd < $tmpfile 2>$errorfile > $resultfile

$grepcmd -q "Login failed for user" $errorfile

if [ "$?" == "0" ]; then
        $rmcmd -f $tmpfile $resultfile $errorfile;
        echo CRITICAL - Could not make connection to SQL server. Login failed.;
        exit 2;
fi

$grepcmd -q "There was a problem connecting to the server" $errorfile

if [ "$?" == "0" ]; then
        $rmcmd -f $tmpfile $resultfile $errorfile;
        echo CRITICAL - Could not make connection to SQL server. Incorrect server name or SQL service not running.;
        exit 2;
fi

resultfileln=`$catcmd $resultfile | $wccmd -l | $sedcmd 's/  //g'`

if [ "$resultfileln" == "2" ]; then
        $rmcmd -f $tmpfile $resultfile $errorfile;
        echo CRITICAL - Could not make connection to SQL server. No data received from host.;
        exit 2;
else
        nmbr=`$catcmd $resultfile | $grepcmd -v locale | $grepcmd -v charset| $grepcmd -v 1\> | $sedcmd '/^$/d' | $sedcmd 's/ //g' | $wccmd -l | sed 's/ //g'`
        hosts=`$catcmd $resultfile | $grepcmd -v locale | $grepcmd -v charset| $grepcmd -v 1\> | $sedcmd '/^$/d' | $sedcmd 's/ //g' | $uniqcmd -c | $trcmd \\\n , | $sedcmd 's/,$/./g' | $sedcmd 's/,/, /g' | $sedcmd 's/  //g' | $trcmd \\\t " " | $sedcmd 's/ \./\./g' | $sedcmd 's/ ,/,/g'`
        $rmcmd -f $tmpfile $resultfile;
if [ "$nmbr" == "0" ]; then
        echo "OK - MS SQL Server $srv has $nmbr blocks: $hosts" | sed 's/: $/./g';
else
fi
exit 0;
fi

# Cleaning up.

$catcmd $tmpfile
$catcmd $resultfile
$catcmd $errorfile

$rmcmd -f $tmpfile $resultfile $errorfile
echo $stdio
exit $exit

I am able to induce blocks on the server and using query analyzer test the sql statemtent which will provdied a list of hosts blocking the server.

But on the linux box testing this command alway returns:

OK - MS SQL Server 2000 has 0 blocks.

I am trying to use the check_mssql.sh written by Tom De Blende to accomplish this task. I am not sure I if am passing the SQL query properly to the server or if I am incorrectly parsing the results or if the query is to complicated this particular script.

Any help would be appreciated.