From 1c8510de8a0b1266c3d1bd2b59f809088ec304f1 Mon Sep 17 00:00:00 2001 From: Stanley Hopcroft Date: Tue, 5 Aug 2003 09:56:13 +0000 Subject: [PATCH] Version 2.0 of MS SQL server plugin (contrib/check_mssql.sh) from T De Blende. git-svn-id: https://nagiosplug.svn.sourceforge.net/svnroot/nagiosplug/nagiosplug/trunk@646 f882894a-f735-0410-b71e-b25c423dba1c --- contrib/check_mssql.sh | 59 ++++++++++++++++++++++++++++-------------- 1 file changed, 40 insertions(+), 19 deletions(-) diff --git a/contrib/check_mssql.sh b/contrib/check_mssql.sh index 7faa6be..77c4dcd 100755 --- a/contrib/check_mssql.sh +++ b/contrib/check_mssql.sh @@ -3,18 +3,21 @@ # # Requirements: # -# Sqsh (http://www.sqsh.org/) -# FreeTDS (http://www.freetds.org/) +# FreeTDS 6.0+ (http://www.freetds.org/) # # It was written by Tom De Blende (tom.deblende@village.uunet.be) in 2003. # # Version 1.0. -# Version 1.1: rewritten the initial script so that it not only works from the CLI but also from within Nagios. Always helpful... -# Version 1.2: grouped output so things look a bit better. +# Version 1.1: Rewritten the initial script so that it not only works from the CLI but also from within Nagios. Always helpful... +# Version 1.2: Grouped output so things look a bit better. +# Version 2.0: Rewritten the plugin to support version 6.0+ of FreeTDS. +# Removed sqsh requirement as version 6.0+ of FreeTDS now offers its own CLI client: tsql. +# Older versions of FreeTDS are no longer supported. +# # # You might want to change these values: -sqshcmd="/usr/local/bin/sqsh" +tsqlcmd=`which tsql` catcmd=`which cat` grepcmd=`which grep` rmcmd=`which rm` @@ -26,7 +29,6 @@ uniqcmd=`which uniq` ################################################################################################################### - hostname=$1 usr=$2 pswd=$3 @@ -34,17 +36,17 @@ srv=$4 if [ ! "$#" == "4" ]; then - echo -e "\nYou did not supply enough arguments. \nUsage: $0 \n \n$0 checks Microsoft SQL Server connectivity. It works with versions 7 and 2000.\n\nYou need a working version of Sqhs (http://www.sqsh.org/) and FreeTDS (http://www.freetds.org/) to connect to the SQL server. \nIt was written by Tom De Blende (tom.deblende@village.uunet.be) in 2003. \n\nExample:\n $0 dbserver sa f00bar 2000\n" && exit "3" + echo -e "\nYou did not supply enough arguments. \nUsage: $0 \n \n$0 checks Microsoft SQL Server connectivity. It works with versions 7 and 2000.\n\nYou need a working version of FreeTDS (http://www.freetds.org/) and tsql (included in FreeTDS 6.0+) to connect to the SQL server. \nIt was written by Tom De Blende (tom.deblende@village.uunet.be) in 2003. \n\nExample:\n $0 dbserver sa f00bar 2000\n" && exit "3" -elif [ $sqshcmd == "" ]; then - echo -e "Sqsh not found! Please verify you have a working version of Sqsh (http://www.sqsh.org/) and enter the full path in the script." && exit "3" +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 script." && exit "3" fi exit="3" -# Creating the command file that contains the sql statement that has to be run on the SQL server. Normally one would use the -C parameter of sqsh, but it seems that there is a bug that doesn't allow statements with more than one blanc. +# Creating the command file that contains the sql statement that has to be run on the SQL server. tmpfile=`$mktempcmd /tmp/$hostname.XXXXXX` @@ -59,18 +61,37 @@ fi echo -e "select loginame from sysprocesses where spid > $spid order by loginame asc\ngo" > $tmpfile -# Running sqsh to get the results back. +# Running tsql to get the results back. resultfile=`$mktempcmd /tmp/$hostname.XXXXXX` -$sqshcmd -S $hostname -U $usr -P $pswd -w 100000 -i $tmpfile -o $resultfile 2>/dev/null +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 [ ! -s $resultfile ]; then - $rmcmd -f $tmpfile $resultfile; - echo CRITICAL - Could not make connection to SQL server.; - exit 2; +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 "\-\-\-\-\-" | $grepcmd -v "loginame" | $grepcmd -v "affected" | $sedcmd '/^$/d' | $sedcmd 's/ //g' | $wccmd -l | sed 's/ //g'`; - users=`$catcmd $resultfile | $grepcmd -v "\-\-\-\-\-" | $grepcmd -v "loginame" | $grepcmd -v "affected" | $sedcmd '/^$/d' | $sedcmd 's/ //g' | $uniqcmd -c | $trcmd \\\n , | $sedcmd 's/,$/./g' | $sedcmd 's/,/, /g' | $sedcmd 's/ //g' | $trcmd \\\t " "`; + nmbr=`$catcmd $resultfile | $grepcmd -v locale | $grepcmd -v charset| $grepcmd -v 1\> | $sedcmd '/^$/d' | $sedcmd 's/ //g' | $wccmd -l | sed 's/ //g'` + users=`$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; echo "OK - MS SQL Server $srv has $nmbr user(s) connected: $users" | sed 's/: $/./g'; exit 0; @@ -78,6 +99,6 @@ fi # Cleaning up. -$rmcmd -f $tmpfile $resultfile +$rmcmd -f $tmpfile $resultfile $errorfile echo $stdio exit $exit -- 2.30.2