Setting up Nagios Monitoring for MSSQL Server

Nagios provides complete monitoring of MSSQL – including availability, database and table sizes, cache ratios, and other key metrics.sql-server

Implementing effective MSSQL monitoring with Nagios offers the following benefits:

  • Increased application availability
  • Increased database performance
  • Fast detection of database outages, failures, and table corruption
  • Predictive analysis of storage requirements and index performance

Downloading & installing NSclient++

In order to get monitoring stats into Nagios, we’ve installed the Nsclient++application on all our Windows machines. This is a very lightweight, handy client that enables all sorts of monitoring data. For our purposes, we’ve got it configured to pass Nagios checks to Windows Performance Monitor counters via the check_nt protocol.

We can download the latest version of NSclient++ from following link on the windows machine https://sourceforge.net/projects/nscplus/

You will be asked to enter the IP of nagios server (refer to screenshot below) :

pic1

Select Logon option and select the account from which NSClient++ need to interact.

pic2

Nsclient++ utilizes a simple ini file to set the basic configuration. Our Nagios server was added to the Allowed Hosts section, and “NSClientserver” was set =1 to allow the check_nt command to flow through.

Nagios Server Configuration

Make the following changes in nagios.cfg file

$ vi /usr/local/nagios/etc/nagios.cfg

uncomment the following line

# Definitions for monitoring a Windows machine
cfg_file=/usr/local/nagios/etc/objects/windows.cfg

Edit the windows.cfg file as below :

 $ vi /usr/local/nagios/etc/objects/windows.cfg
###############################################################################
# WINDOWS.CFG - SAMPLE CONFIG FILE FOR MONITORING A WINDOWS MACHINE
#
#
# NOTES: This config file assumes that you are using the sample configuration
#    files that get installed with the Nagios quickstart guide.
#
###############################################################################
###############################################################################
#
# HOST DEFINITIONS
#
###############################################################################
# Define a host for the Windows machine we'll be monitoring
# Change the host_name, alias, and address to fit your situation
define host {
use                     windows-server          ; Inherit default values from a template
host_name               winworkstation01               ; The name we're giving to this host
alias                   winworkstation01       ; A longer name associated with the host
address                 192.168.112.173             ; IP address of the host
contact_groups          dba-admins
check_period            24x7
check_command           check-host-alive
notification_period      24x7
}
###############################################################################
#
# HOST GROUP DEFINITIONS
#
###############################################################################
# Define a hostgroup for Windows machines
# All hosts that use the windows-server template will automatically be a member of this group
define hostgroup {
hostgroup_name          windows-servers         ; The name of the hostgroup
alias                   Windows Servers         ; Long name of the group
}
###############################################################################
#
# SERVICE DEFINITIONS
#
###############################################################################

# Create a service for monitoring the version of NSCLient++ that is installed
# Change the host_name to match the name of the host you defined above
define service {
use                     generic-service
host_name               winworkstation01
service_description     NSClient++ Version
check_command           check_nt!CLIENTVERSION
}

# Create a service for monitoring the uptime of the server
# Change the host_name to match the name of the host you defined above
define service {
use                     generic-service
host_name               winworkstation01
service_description     Uptime
check_command           check_nt!UPTIME
}

# Create a service for monitoring CPU load
# Change the host_name to match the name of the host you defined above
define service {
use                     generic-service
host_name               winworkstation01
service_description     CPU Load
check_command           check_nt!CPULOAD!-l 5,80,90
}

# Create a service for monitoring memory usage
# Change the host_name to match the name of the host you defined above

define service {
use                     generic-service
host_name               winworkstation01
service_description     Memory Usage
check_command           check_nt!MEMUSE!-w 80 -c 90
}
# Create a service for monitoring C:\ disk usage
# Change the host_name to match the name of the host you defined above
define service {
use                     generic-service
host_name               winworkstation01
service_description     C:\ Drive Space
check_command           check_nt!USEDDISKSPACE!-l c -w 80 -c 90
}

# Create a service for monitoring the W3SVC service
# Change the host_name to match the name of the host you defined above
define service {
use                     generic-service
host_name               winworkstation01
service_description     W3SVC
check_command           check_nt!SERVICESTATE!-d SHOWALL -l W3SVC
}

# Create a service for monitoring the Explorer.exe process
# Change the host_name to match the name of the host you defined above
define service {
use                     generic-service
    host_name               winworkstation01
    service_description     Explorer
    check_command           check_nt!PROCSTATE!-d SHOWALL -l Explorer.exe
}
define service  {
    use                      generic-service
    host_name                winworkstation01
    service_description      SMTP Service
    check_command            check_nt!SERVICESTATE!-d SHOWALL -l SMTPSVC
}
define service  {
    use                      generic-service
    host_name                winworkstation01
    service_description      IIS Admin Service
    check_command            check_nt!SERVICESTATE!-d SHOWALL -l IISADMIN
}
###SQL Server#####
define service  {
    use                      generic-service
    host_name                winworkstation01
    service_description      MSSQL Service
    check_command            check_nt!SERVICESTATE!-d SHOWALL -l MSSQLSERVER
}
define service  {
    use                      generic-service
    host_name                winworkstation01
    service_description      MSSQL Server Agent
    check_command            check_nt!SERVICESTATE!-d SHOWALL -l SQLSERVERAGENT
}
define service  {
    use                  generic-service
    host_name            winworkstation01
    service_description  MSSQL Data File Size
    check_command        check_nt!COUNTER! -l "\\SQLServer:Databases(_Total)\\Data File(s) Size (KB)","%.f KB"
    check_interval       5
}

Restart the nagios services

service nagios restart
service httpd restart

Login to Nagios web portal and verify that the metrics are getting monitored.

pic3

Check MSSQL_health Plugin

check_mssql_health is a plugin, which is used to monitor different parameters of a MS SQL server.

Preparation of the database :

In order for the plugin to operate correctly, a database user with specific privileges is required.

The most simple way is to assign the Nagios-user the role “serveradmin”. As an alternative you can use the sa-User for the database connection. Alas, this opens a serious security hole, as the (cleartext) administrator password can be found in the nagios configuration files

Reference : https://labs.consol.de/nagios/check_mssql_health/index.html

declare @dbname varchar(255)
declare @check_mssql_health_USER varchar(255)
declare @check_mssql_health_PASS varchar(255)
declare @check_mssql_health_ROLE varchar(255)
declare @source varchar(255)
declare @options varchar(255)
declare @backslash int
SET @check_mssql_health_USER = '"WIN-DP0TIN0GRK5\nagios"'
SET @check_mssql_health_ROLE = 'serveradmin'

/*********** NO NEED TO CHANGE ANYTHING BELOW THIS LINE *************/
SET @options = 'DEFAULT_DATABASE=MASTER, DEFAULT_LANGUAGE=English'
SET @backslash = (SELECT CHARINDEX('\', @check_mssql_health_USER))
IF @backslash > 0
  BEGIN
 SET @source = ' FROM WINDOWS'
 SET @options = ' WITH ' + @options
END
ELSE
  BEGIN
SET @source = ''
SET @options = ' WITH PASSWORD=''' + @check_mssql_health_PASS + ''',' + @options
END

PRINT 'create Nagios plugin user ' + @check_mssql_health_USER
EXEC ('CREATE LOGIN ' + @check_mssql_health_USER + @source + @options)
EXEC ('USE MASTER GRANT VIEW SERVER STATE TO ' + @check_mssql_health_USER)
EXEC ('USE MASTER GRANT ALTER trace TO ' + @check_mssql_health_USER)
EXEC ('USE MSDB GRANT SELECT ON sysjobhistory TO ' + @check_mssql_health_USER)
EXEC ('USE MSDB GRANT SELECT ON sysjobschedules TO ' + @check_mssql_health_USER)
EXEC ('USE MSDB GRANT SELECT ON sysjobs TO ' + @check_mssql_health_USER)
PRINT 'User ' + @check_mssql_health_USER + ' created.'
PRINT ''

declare dblist cursor for
select name from sysdatabases WHERE name NOT IN ('master', 'tempdb', 'msdb') open dblist
  fetch next from dblist into @dbname
while @@fetch_status = 0 begin
 EXEC ('USE [' + @dbname + '] print ''Grant permissions in the db '' + ''"'' + DB_NAME() + ''"''')
EXEC ('USE [' + @dbname + '] CREATE ROLE ' + @check_mssql_health_ROLE)
EXEC ('USE [' + @dbname + '] GRANT EXECUTE TO ' + @check_mssql_health_ROLE)
EXEC ('USE [' + @dbname + '] GRANT VIEW DATABASE STATE TO ' + @check_mssql_health_ROLE)
EXEC ('USE [' + @dbname + '] GRANT VIEW DEFINITION TO ' + @check_mssql_health_ROLE)
 EXEC ('USE [' + @dbname + '] CREATE USER ' + @check_mssql_health_USER + ' FOR LOGIN ' + @check_mssql_health_USER)
 EXEC ('USE [' + @dbname + '] EXEC sp_addrolemember ' + @check_mssql_health_ROLE + ' , ' + @check_mssql_health_USER)
EXEC ('USE [' + @dbname + '] print ''Permissions in the db '' + ''"'' + DB_NAME() + ''" granted.''')
fetch next from dblist into @dbname
end
close dblist
deallocate dblist

Verify Created Nagios SQL Server User

pic7

 

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 )

Google+ photo

You are commenting using your Google+ 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 )

Connecting to %s

Powered by WordPress.com.

Up ↑

%d bloggers like this: