MSSQL Query from Linux- FreeTDS for Nagios Monitoring

FreeTDS for MSSQL queries from Linux

Using FreeTDS

Need to monitor a MSSQL server instance from Nagios? You can use FreeTDS to execute a MSSQL query from a Linux box.

Here’s how:

  1. Install FreeTDS:
    yum install freetds
  2. Configure FreeTDS (vi /usr/local/etc/freetds.conf):

    [servername]
    host = 192.168.0.10
    port = 1433
    tds version = 8.0
  3. Configure ODBC driver for FreeTDS (/etc/odbcinst.ini):

    # Define where to find the driver for the Free TDS connections.
    [freetds]
    Description = MS SQL database access with Free TDS
    Driver = /usr/lib/libtdsodbc.so
    Setup = /usr/lib/libtdsS.so
    UsageCount = 1
  4. Test connectivity:

    [root@techpain]# tsql -H servername -p 1433 -U dbuser -P dbpass
    locale is "en_US.UTF-8"
    locale charset is "UTF-8"
    using default charset "UTF-8"
    1> select field11 from column1 where field1 like '10%'
    2> GO
    field1
    101
    102
    103
    105
    106
    108
    109
    (7 rows affected)
    1> quit
    [root@techpain]#

Nagios MSSQL query

FreeTDS is cool because it allows you to use Nagios to query a MSSQL server using check_mssql command. Here’s how:

Download the check_mssql plugin to the plugins dir on your Nagios server. You can find it at the Nagios Exchange site.

Create command, host, and service definitions for it:

define command {
command_name check_mssql
command_line $USER1$/check_mssql -H hostname -U dbuser -P dbpass -q "select count(*) from column1 where field1 like '10%';" -r "7" -w 8 -c 10
}

define host {
host_name ServerName
alias MSSQL server
address 192.168.0.10
check_command check-host-alive
notification_interval 15
notification_options d,u,r
max_check_attempts 3
active_checks_enabled 1
passive_checks_enabled 0
notifications_enabled 1
check_period 24x7
notification_period 24x7
contact_groups admins
}

define service {
use generic-service
host_name ServerName
service_description MSSQLquery
check_command check_mssql
}

In the above example, Nagios executes the query expects a return of 7, warns at 8, and goes crit at 10.

Share

Leave a Reply

Your email address will not be published. Required fields are marked *