We saw in my last blog how to install the SQL Server plugin for Nagios.
In this new blog, I will explain:

  • how to create a wrapper to avoid the unset of the PERLLIB variable
  • how to create and configure a configuration file by monitoring instance
  • how to create an SQL Login for nagios
  • How to subscribe to an alert

As we saw in the first nagios blog, to be able to run the check mssql plugin we have to unset the PERLLIB variable and to export library.
We unset the PERLLIB libraries because there are set with the Oracle Client and there is conflicts. We force the plugin check_mssql_health to use the PERLLIB libraries of the OS instead of these of the Oracle client by unsetting the PERLLIB libraries of the check_oracle_health plugin.
We will do that in a wrapped file:

b2ap3_thumbnail_Nagios_SQLServer_blog_correction1.jpg

After we are able to test our wrapper with success:

b2ap3_thumbnail_Nagios_SQLServer_nagios_b2.jpg

We have decided to create a configuration file per SQL Server instance to monitor.
This file is as follow:

b2ap3_thumbnail_Nagios_SQLServer_nagios_b3.jpg

The important things to see here is:

In the host definition line:

  • mssql_sid with the name of the instance given in the freetds.conf file (see first nagios blog)
  • mssql_usr with the user name used for the SQL Server connection
  • address with the IP Address of the SQL Server host

In the command line:

  • the call to the check_mssql_health_wrapped file
  • the counter used in this call, here cpu-busy
  • the warning threshold fixed at 80% of cpu used
  • the alert threshold fixed to 90% of cpu used

In the service line:

  • normal_check_interval which defines the interval to check the service under normal conditions, here 10 minutes
  • retry_check_interval which determines the number of minutes to wait before scheduling a re-check when service has changed to non-OK state, here 2 minutes
  • max_check_attempts which checks if the service has been retried max_check_attempts time without a change in its status, it will revert to being scheduled at normal_check_interval rate and a mail is sent to the contact_group, here 3 times
  • contact_groups which will receive alerts
  • notification_interval which determines every how many minutes alerts will be send

It means that the cpu-busy counter will be check every 10 minutes by nagios, in case of non-OK state the next check will be scheduled after 2 minutes and after 3 checks without change, an alert will be send to the contact group and another check will be scheduled 10 minutes later.  If the status stays non-ok a next alert will be sent after 6 hours.

A lots of counters are available for this plugin, to have a list, please go here.

An SQL Login can be created to connect to the SQL Server instance and databases to avoid to use sa user for example.
First create a windows domain account:

b2ap3_thumbnail_Nagios_SQLServer_Account1.jpg

b2ap3_thumbnail_Nagios_SQLServer_Account2.jpg

b2ap3_thumbnail_Nagios_SQLServer_Account2.jpg
Execute the following script in SSMS in a query window to create the new logins and grand permissions:

USE [master]
GO
CREATE LOGIN [DBITESTnagios_mssql_health] FROM WINDOWS WITH DEFAULT_DATABASE=[master]
GO
GRANT VIEW SERVER STATE to [DBITESTnagios_mssql_health]
USE [model]
GO
CREATE ROLE db_nagios_mssql_health
GRANT EXECUTE TO db_nagios_mssql_health
GRANT VIEW DATABASE STATE TO db_nagios_mssql_health
GRANT VIEW DEFINITION TO db_nagios_mssql_health
CREATE USER [DBITESTnagios_mssql_health] FOR LOGIN [DBITESTnagios_mssql_health]
EXEC sp_addrolemember'db_nagios_mssql_health', [DBITESTnagios_mssql_health]

After execute the following script to grand permission on database level, take care if a new database is installed later this script has to be run for this new db.

execute sp_MSforeachdb 'use [?]
print ''?''
USE [?]
CREATE ROLE db_nagios_mssql_health
GRANT EXECUTE TO db_nagios_mssql_health
GRANT VIEW DATABASE STATE TO db_nagios_mssql_health
GRANT VIEW DEFINITION TO db_nagios_mssql_health
CREATE USER [DBITESTnagios_mssql_health] FOR LOGIN [DBITESTnagios_mssql_health]
EXEC sp_addrolemember ''db_nagios_mssql_health'', [DBITESTnagios_mssql_health]'

 

To subscribe to an alert, new groups and contact can be defined.
Those objects will be created in a contact configuration file which will be added to the config.cfg file of nagios with this line:

# dbi-services contacts and contactgroups
cfg_file=/usr/local/nagios/etc/objects/DBI_contacts.cfg

The contact configuration file have the following structure:

b2ap3_thumbnail_Nagios_SQLServer_nagios_b4.jpg

I hope those information will help you to configure the SQL Server plugin for nagios and will give you service status details as the picture below:

b2ap3_thumbnail_Nagios_SQLServer_nagios_b5.jpg

See You.