[Nagiosplug-devel] Yet another plugin to check MS SQL.

[Cal Evans]

This one does not require sqsh. This has not been tested with SQL 7. I
would appreciate any feedback.

Also, this has not been tested extensivly at all. I'm in the proces of
testing it as I can but would appreciate feedback from anyone who has
tested it.

=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
#
# Props to :
#   Tom DeBlende for the core concepts.
#   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.
#
# TODO:
# Paramertize the tmp directory
# Add a verbose mode that gives all output for testing.
#
###################################################################################################################

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"


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 [ ! "$#" == "4" ]; then
        		echo "You did not supply enough 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`

		tsql -S $HOSTNAME -U $USERLOGIN -P $PASSWORD < $TEMPFILE 2>/dev/null |
grep -v ">" | tail -n1 > $RESULTFILE

		if [ ! -s $RESULTFILE ]; then
			OUTPUT="CRITICAL - Could not connect to SQL server running on $HOSTNAME."
			EXITCODE='2'
		else
			OUTPUT="$(cat $RESULTFILE)"
        		EXITCD='0'
		fi

		# Cleaning up.

		rm -f $TEMPFILE $RESULTFILE
		echo $OUTPUT
		exit $EXITCD

                ;;
esac