[Nagiosplug-devel] check_mssql.sh v1.1

[Cal Evans]

This one has had more testing.  I fixed a problem with it not properly
reporting errors.

=C=
*
* Cal Evans
* http://www.christianperformer.com
* Stay plugged in to your audience!
*

#!/bin/sh
#
# Description :
# Checks the status of Microsoft SQL Server 2000. (Possibly other versions)
# Copyright (c) 2003 Cal Evans <cal at calevans.com>
#
# License : GPL
#
# Special Thanks to :
#   Tom DeBlende for the core concepts.
#   Dennis Deming for the nifty TSQL code
#   Jerome Tytgat for the verify_deps code
#   Scott Lambert for an excellent example of how to write these things
#   (check_adptraid.sh)
#
# Requirements :
# FreeTDS (http://www.freetds.org/)
#
# Version 1.0 : 07/18/2003
# Initial release.
#
# Version 1.1 : 07/19/2003
# Fixed the error checking so that a non-connect will be properly reported.
#
# Fixed the parameter check so that 3 parameters are now required but 4 won't
# cause a problem.
#
# Minor cleanups.
#
# TODO:
# Paramertize the tmp directory
# Add a verbose mode that gives all output for testing.
# Find a better parameter parsing routine.
#
################################################################################

PROGNAME=`basename $0`
PROGPATH=`echo $0 | sed -e 's,[\\/][^\\/][^\\/]*$,,'`
REVISION=`echo '$Revision: 1.3 $' | sed -e 's/[^0-9.]//g'`

. $PROGPATH/utils.sh

#
# Initalize a few variables
#
HOSTNAME=$1
USERLOGIN=$2
PASSWORD=$3
SERVER=$4
OUTPUT=''
EXITCODE="3"
ERRORMSG="There was a problem connecting to the server"

print_usage() {
        echo "Usage: $PROGNAME"
	echo "check_mssql.sh [host] [username] [password]"
	echo " "
	echo "Options:"
	echo "[host] "
	echo "The name or IP address of the server to check."
	echo " "

	echo "[username] "
	echo "The user login to use when connecting to the server."
	echo " "

	echo "[password]"
	echo "The password for the specified user."
	echo " "

        echo "Example:"
	echo "check_mssql dbserver foo bar"
}


print_help() {
        print_revision $PROGNAME $REVISION
        echo ""

        print_usage

	echo " "
	echo "check_mssql checks Microsoft SQL Server connectivity. It works with
versions 7 and 2000."
	echo "You need FreeTDS (http://www.freetds.org/) to connect to the SQL
server."
	echo " "
        echo " "
        support
        exit 0
}

verify_dep() {

        needed="bash tsql cat grep mktemp uniq tail"
        for i in `echo $needed`
        do
                type $i > /dev/null 2>&1 /dev/null
                if [ $? -eq 1 ]
                then
                        echo "I am missing an important component : $i"
                        echo "Cannot continue, sorry, try to find the
missing one..."
                        exit 3
                fi
        done
}


case "$1" in
        --help)
                print_help
                exit 0
                ;;
        -h)
                print_help
                exit 0
                ;;
        --version)
	        print_revision $PROGNAME $REVISION
                exit 0
                ;;
        -V)
                print_revision $PROGNAME $REVISION
                exit 0
                ;;
        *)

		verify_dep

		if [ "$#" -lt 3 ]; then
        		echo "This plugin requires 3 arguments."
			exit "3"
		fi

		TEMPFILE=`mktemp /tmp/$HOSTNAME.XXXXXX`

		echo "DECLARE @iUsers int," > $TEMPFILE
		echo "        @iAgeInMinutesOfOldestProcess int," >> $TEMPFILE
		echo "        @iMaxCPU int," >> $TEMPFILE
		echo "        @iMaxIO int," >> $TEMPFILE
		echo "        @iBlocks int" >> $TEMPFILE

		echo "SELECT @iUsers = COUNT(*) FROM sysprocesses WHERE spid > 50" >>
$TEMPFILE
		echo "SELECT @iAgeInMinutesOfOldestProcess = DATEDIFF( mi, MIN(
last_batch )," >> $TEMPFILE
		echo "        GETDATE() ) FROM sysprocesses WHERE spid > 50" >> $TEMPFILE
		echo "SELECT @iMaxCPU = MAX( cpu ) FROM sysprocesses WHERE spid > 50" >>
$TEMPFILE
		echo "SELECT @iMaxIO = MAX( physical_io ) FROM sysprocesses WHERE spid >
50" >> $TEMPFILE
		echo "SELECT @iBlocks = COUNT(*) FROM sysprocesses WHERE blocked > 0" >>
$TEMPFILE

		echo "SELECT  ('Users = '+convert(varchar, at iUsers)+" >> $TEMPFILE
		echo "         ' Age In Minutes Of Oldest User =
'+convert(varchar, at iAgeInMinutesOfOldestProcess) +" >> $TEMPFILE
		echo "         ' Max CPU User = '+convert(varchar, at iMaxCPU)+" >> $TEMPFILE
		echo "         ' Max IO User = '+convert(varchar, at iMaxIO)+" >> $TEMPFILE
		echo "         ' TotalBlocks = '+convert(varchar, at iBlocks)" >> $TEMPFILE
		echo "        ) as out" >> $TEMPFILE
		echo "go" >> $TEMPFILE

		RESULTFILE=`mktemp /tmp/$HOSTNAME.XXXXXX`
		ERRORFILE=`mktemp /tmp/$HOSTNAME.XXXXXX`

		tsql -S $HOSTNAME -U $USERLOGIN -P $PASSWORD < $TEMPFILE 2>$ERRORFILE
>$RESULTFILE

		# Check for the error message in the error file.
		SUCCESS="$(grep "$ERRORMSG" $ERRORFILE | wc -l)"

		if [ ! $SUCCESS == 0 ]; then
			OUTPUT="Error connecting to server running on $HOSTNAME."
			EXITCODE='2'
			# If we put in a verbose mode, dump the entire error file here.
		else
			OUTPUT="$(cat $RESULTFILE | grep -v ">" | tail -1)"
        	EXITCD='0'
		fi

		# Clean up.
		rm -f $TEMPFILE $RESULTFILE $ERRORFILE
		echo $OUTPUT
		exit $EXITCD

                ;;
esac