contrib/check_mssql.sh

   1 #!/bin/sh
   2 # This script is designed to be used by Nagios. It checks for the availability of both Microsoft SQL Server 7 and 2000.
   3 #
   4 # Requirements:
   5 #
   6 # FreeTDS 6.0+ (http://www.freetds.org/)
   7 #
   8 # It was written by Tom De Blende (tom.deblende@village.uunet.be) in 2003.
   9 #
  10 # Version 1.0.
  11 # Version 1.1: Rewritten the initial script so that it not only works from the CLI but also from within Nagios. Always helpful...
  12 # Version 1.2: Grouped output so things look a bit better.
  13 # Version 2.0: Rewritten the plugin to support version 6.0+ of FreeTDS.
  14 #              Removed sqsh requirement as version 6.0+ of FreeTDS now offers its own CLI client: tsql.
  15 #              Older versions of FreeTDS are no longer supported.
  16 #
  17 #
  18 # You might want to change these values:
  19
  20 tsqlcmd=`which tsql`
  21 catcmd=`which cat`
  22 grepcmd=`which grep`
  23 rmcmd=`which rm`
  24 mktempcmd=`which mktemp`
  25 wccmd=`which wc`
  26 sedcmd=`which sed`
  27 trcmd=`which tr`
  28 uniqcmd=`which uniq`
  29
  30 ###################################################################################################################
  31
  32 hostname=$1
  33 usr=$2
  34 pswd=$3
  35 srv=$4
  36
  37
  38 if [ ! "$#" == "4" ]; then
  39         echo -e "\nYou did not supply enough arguments. \nUsage: $0 <host> <username> <password> <version> \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"
  40
  41 elif [ $tsqlcmd == "" ]; then
  42         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"
  43
  44 fi
  45
  46 exit="3"
  47
  48
  49 # Creating the command file that contains the sql statement that has to be run on the SQL server.
  50
  51 tmpfile=`$mktempcmd /tmp/$hostname.XXXXXX`
  52
  53 if [ $srv == "7" ]; then
  54         spid=7
  55 elif [ $srv == "2000" ]; then
  56         spid=50
  57 else
  58         echo -e "$srv is not a supported MS SQL Server version!" && exit "3"
  59 fi
  60
  61 echo -e "select loginame from sysprocesses where spid > $spid order by loginame asc\ngo" > $tmpfile
  62
  63
  64 # Running tsql to get the results back.
  65
  66 resultfile=`$mktempcmd /tmp/$hostname.XXXXXX`
  67 errorfile=`$mktempcmd /tmp/$hostname.XXXXXX`
  68 $tsqlcmd -S $hostname -U $usr -P $pswd < $tmpfile 2>$errorfile > $resultfile
  69
  70 $grepcmd -q "Login failed for user" $errorfile
  71
  72 if [ "$?" == "0" ]; then
  73         $rmcmd -f $tmpfile $resultfile $errorfile;
  74         echo CRITICAL - Could not make connection to SQL server. Login failed.;
  75         exit 2;
  76 fi
  77
  78 $grepcmd -q "There was a problem connecting to the server" $errorfile
  79
  80 if [ "$?" == "0" ]; then
  81         $rmcmd -f $tmpfile $resultfile $errorfile;
  82         echo CRITICAL - Could not make connection to SQL server. Incorrect server name or SQL service not running.;
  83         exit 2;
  84 fi
  85
  86 resultfileln=`$catcmd $resultfile | $wccmd -l | $sedcmd 's/  //g'`
  87
  88 if [ "$resultfileln" == "2" ]; then
  89         $rmcmd -f $tmpfile $resultfile $errorfile;
  90         echo CRITICAL - Could not make connection to SQL server. No data received from host.;
  91         exit 2;
  92 else
  93         nmbr=`$catcmd $resultfile | $grepcmd -v locale | $grepcmd -v charset| $grepcmd -v 1\> | $sedcmd '/^$/d' | $sedcmd 's/ //g' | $wccmd -l | sed 's/ //g'`
  94         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'`
  95         $rmcmd -f $tmpfile $resultfile;
  96         echo "OK - MS SQL Server $srv has $nmbr user(s) connected: $users" | sed 's/: $/./g';
  97         exit 0;
  98 fi
  99
 100 # Cleaning up.
 101
 102 $rmcmd -f $tmpfile $resultfile $errorfile
 103 echo $stdio
 104 exit $exit