How to Monitor MSSQL with Nagios using check_mssql

Being in the hosting business presents many unique challenges… One of the most important challenges is making sure that the equipment and services that your clients websites are being hosted on are operational at all times. If a critical function or service has an issue, you need to immediately be notified of it and remediate the issue.

I saw the challenge in being able to effectively monitor all of our equipment, services and production websites and I knew that Nagios would be the solution for us. I installed and configured our Nagios solution on a dedicated CentOS 5.6 VM. I am also using NagiosQL to make the configuration of new Hosts and Services easier.

Nagios has some excellent built-in functionality for monitoring things such as public services… http, pop3, smtp, imap, ping, etc. Also, in conjunction with NSClient++, we have made the monitoring of all of our Windows servers a breeze. I have found a very unique challenge when trying to monitor MSSQL however. The first challenge was to find the right MSSQL Plugin. We did not have the need immediately to pull specific data from SQL – we just needed to know that the SQL Server Service was up and running and that we could successfully login. Given our requirements, I decided to use the check_mssql plugin. “This plugin checks various aspect of an MSSQL server. It will also execute queries or stored procedures and return results based on query execution times and expected query results.” Now we had the challenge of getting it installed and working. Below are the steps I took…

This plugin requires that several dependencies be installed on the Nagios server – most specifically PHP-mssql. I am running a CentOS 5.6 LAMP server with PHP 5.3, so we needed to find PHP53-mssql and install it or you’ll run into an awesome “Processing Conflict: php53-common conflicts php-common” error.

• Download the latest epel-release rpm from…

http://download.fedora.redhat.com/pub/epel/5/i386/

• Install epel-release rpm…

# rpm -Uvh epel-release*rpm

• Install php53-mssql rpm package…

# yum install php53-mssql

• Restart Apache…

# service httpd restart

Now that our server now has the support it needs to execute queries to an MSSQL server, we need to download the plugin and configure Nagios to use it…

• Change to the Nagios library directory…

# cd /usr/local/nagios/libexec

• Download the plugin. The latest version is available on the plugin page, but I’ve made version 0.6.6 available here. Then we rename it with the .php extension and give it the appropriate permissions…

# wget http://www.ptihosting.com/downloads/check_mssql.txt

# mv check_mssql.txt check_mssql.php

# chmod 777 check_mssql.php

• Now we’ll give the script a test to make sure it is working…

# ./check_mssql.php -H hostname -U username -P password

OK: Connect time=0.078837 seconds.

• Now that we know the script works, we will add the Nagios command in NagiosQL (or in Nagios commands.cfg)…

define command{
name check_mssql
command_name check_mssql
command_line $USER1$/check_mssql.php -H $HOSTADDRESS$ $ARG1$
}

• Now we need to add a new Service in NagiosQL (or add a new Service Definition in Nagios cfg files). Here is how mine looks…

define service {
        host_name                       MSSQLServer
        service_description             MS SQL Server
        check_command                   check_mssql!-U username -P password
        initial_state                   o
        max_check_attempts              1
        check_interval                  5
        retry_interval                  5
        active_checks_enabled           1
        check_period                    24×7
        notification_interval           10
        first_notification_delay        0
        notification_period             24×7
        notification_options            w,u,r,c,f,s
        notifications_enabled           1
        contacts                        Brad
        contact_groups                  null
        register                        1
}

• Lastly, we need to check our config and restart Nagios…

# service nagios restart

Running configuration check…done.
Stopping nagios: done.
Starting nagios: done.

• We should now get an OK status on the service…