Monitor Sql server blocking sessions in Nagios

Here is a script that uses Freetds to monitor sql server blocking it can be used by Nagios to trigger alarms.

#Script is designed to be used by Nagios. It checks for blocking sessions in MSSQL.

#

# Requirements:

#

# FreeTDS 6.0+ (http://www.freetds.org/)

#

# Original check_mssql.sh written by Tom De Blende (tom.deblende@village.uunet.be) in 2003.

# Changed script to check for blocking sessions in a database by Staffan.olofsson@b3it.se in 2013

#

# Script takes 4 parameters 1. Servername 2.username 3.password 4. 2000 (for SQL 2000 and up)

# Freedtds has to be configured with connection information

#

# Current threshold for blocking is 35000 ms. This can be changed in the SQL code below.

#

# You might want to change these values:

tsqlcmd=’/usr/bin/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 script."

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 "SELECT host_name,program_name,login_time,login_name,convert(nvarchar(4000), [text]),wait_duration_ms

FROM sys.dm_os_waiting_tasks w

INNER JOIN sys.dm_exec_sessions s

ON w.session_id = s.session_id

INNER JOIN sys.dm_exec_requests r

ON s.session_id = r.session_id

OUTER APPLY sys.dm_exec_sql_text (r.sql_handle) t

WHERE s.is_user_process = 1

and wait_duration_ms >35000\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

echo "$hosts $nmbr"

fi

exit 0;

fi

# Cleaning up.

$catcmd $tmpfile

$catcmd $resultfile

$catcmd $errorfile

$rmcmd -f $tmpfile $resultfile $errorfile

echo $stdio

exit $exit

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s