?
Solved

Nagios check_mssql

Posted on 2009-04-02
13
Medium Priority
?
3,388 Views
Last Modified: 2012-05-06
I am trying to monitor a mssql database with nagios.  I understand there is a plugin called check_mssql.sh under the contrib directory, but i'm not sure how to install it.  Do I just copy it into /usr/local/nagios/libexec directory?  When I do and then try to execute it from command line I get command not found as well as in Nagios service details?  I may also need guidance on how exectly to run a nagios plugin from command line.
0
Comment
Question by:bchadez
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 7
  • 6
13 Comments
 
LVL 9

Accepted Solution

by:
sda100 earned 2000 total points
ID: 24065464
Yes, you should put the plugin in the Nagios libexec directory (on FreeBSD it's /usr/local/libexec/nagios).

You also need a command definition for it in commands.cfg (on FreeBSD, /usr/local/etc/nagios/objects/commands.cfg), something similar to this example below perhaps?

This assumes you've also set up any relevant host/service definitions in Nagios too.

Steve :)



define command{
    command_name    check_mssql
    command_line    $USER1$/check_mssql -H $HOSTADDRESS$ $ARG1$ $ARG2$ $ARG3$
    }

Open in new window

0
 

Author Comment

by:bchadez
ID: 24069586
What should the service definition look like in my windows.cfg

# Service for monitoring MSSQL Database

define service{
      use                                windows-server
      host_name                  windows-server-1
      service_description            check_mssql
      check_command            ????????????????????????????????
      }
0
 
LVL 9

Expert Comment

by:sda100
ID: 24069812
Depends on the plugin you're using.  Have you tried running the plugin with the '-h' parameter?

If it's this one, for instance, and you've used my command example from my previous post, you might want something like this (untested):

        check_command        check_mssql!myusername!mypassword!version

The bits in bold you will need to change to whatever they need to be for your setup.

Instead of putting the password on the command line, you could use the resources.cfg file and define some user variables, like this:

        $USER9$=password

And then the check_command would look something like this:

        check_command        check_mssql!myusername!$USER9$!version

Let me know how you get on.

Steve :)
0
Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 

Author Comment

by:bchadez
ID: 24071584
How do you run a plugin with -h?  Is this from the command line?  Whenever I try to run any plugin from command line I get "command not found" even when I "cd" into the directory where the plugin resides.  I also tried using this command in Nagios and I get "Return code of 127 is out of bounds - plugin may be missing"
0
 
LVL 9

Expert Comment

by:sda100
ID: 24071716
You must make sure you have the plugin in the first place.  You should copy it to the directory with the rest of the plugins and make sure it' also executable (make the same as the other plugins).

You can run the plugin yourself, and see what happens - it doesn't need to be called by Nagios.  The -h parameter for help is only a convention not a requirement.

For the time being, forget Nagios and just get the plugin wo work by running it manually.

Steve
0
 

Author Comment

by:bchadez
ID: 24089054
How exactly do I run the plugin myself?  I've tried several from the command line but get "command not found".
plugins.jpg
0
 
LVL 9

Expert Comment

by:sda100
ID: 24090470
The reason it says "command not found" is because that path (/usr/local/nagios/libexec) is not in the executable path.

Try this (note the leading 2 chars):

./check_ping -h./check_mssql.sh -h
Steve :)
0
 

Author Comment

by:bchadez
ID: 24090854
Does this mean it dosen't support -h command?
check-mssql.jpg
0
 
LVL 9

Expert Comment

by:sda100
ID: 24092637
Can you include your check_mssql.sh script as an attachment here please?
0
 

Author Comment

by:bchadez
ID: 24093376
Couldn't attach that file type or tar.gz, hope this works for you.
#!/bin/sh
# This script is designed to be used by Nagios. It checks for the availability of both Microsoft SQL Server 7 and 2000.
#
# Requirements:
#
# 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 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:
 
tsqlcmd=`which 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. 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 [ $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.
 
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 loginame from sysprocesses where spid > $spid order by loginame asc\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'`
	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;
fi
 
# Cleaning up.
 
$rmcmd -f $tmpfile $resultfile $errorfile
echo $stdio
exit $exit

Open in new window

0
 

Author Comment

by:bchadez
ID: 24093384
Humm, just realized after posting this code that I don't believe I have FreeTDS.
0
 
LVL 9

Expert Comment

by:sda100
ID: 24095145
Aha! - let me know how you get on ;)
0
 

Author Comment

by:bchadez
ID: 24096649
Thanks!
0

Featured Post

 [eBook] Windows Nano Server

Download this FREE eBook and learn all you need to get started with Windows Nano Server, including deployment options, remote management
and troubleshooting tips and tricks

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

I've encountered valid database schemas that do not have a primary key.  For example, I use LogParser from Microsoft to push IIS logs into a SQL database table for processing and analysis.  However, occasionally due to user error or a scheduled task…
Introduction: When running hybrid database environments, you often need to query some data from a remote db of any type, while being connected to your MS SQL Server database. Problems start when you try to combine that with some "user input" pass…
In this brief tutorial Pawel from AdRem Software explains how you can quickly find out which services are running on your network, or what are the IP addresses of servers responsible for each service. Software used is freeware NetCrunch Tools (https…
This is my first video review of Microsoft Bookings, I will be doing a part two with a bit more information, but wanted to get this out to you folks.

762 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question