Adding custom services for Windows host to be monitored by Nagios (Monitoring MS SQL Database Table)

Nagios has many in-built services which can be easily configured for a host so that it can be monitored. Services for Windows host like check cpu load, drive space, running processes can be monitored by using various parameters in check_nt command.
While monitoring, situation comes when you have to monitor a specific service which may be a part of an application, size of particular file or a table in database and many others. In these cases, you have to make your own customized program/script to monitor them. Nagios should be configured to run this customized program and get its output and show the service status.

Connect MSSQL using ODBC in Linux (CentOS)

For connecting MSSQL using ODBC in Linux you can use unixODBC and freeTDS.  unixODBC is a tool for using ODBC in Non Windows platform. MSSQL uses Tabular Data Stream (TDS) as a communication protocol which is same like in Sybase. freeTDS is an implementation of TDS protocol.

Installing unixODBC

      1.      Install gcc

#yum install gcc

      2.      Download unixODBC and untar it

#mkdir /root/downloads

#cd /root/downloads

#wget ftp://ftp.unixodbc.org/pub/unixODBC/unixODBC-2.3.0.tar.gz

#tar –xzf unixODBC-2.3.0.tar.gz

#cd unixODBC-2.3.0

      3.      Install unixODBC

#./configure

#make

#make install

Installing freeTDS

     1.      Set environmental variable at /etc/profile. Add following lines at end

#nano /etc/profile

# TDS SYBASE=/usr/local  LD_LIBRARY_PATH=${LD_LIBRARY_PATH}:$SYBASE/lib  export SYBASE LD_LIBRARY_PATH

/etc/profile

     2.      Download freeTDS and untar it

#cd /root/downloads

#wget http://ibiblio.org/pub/Linux/ALPHA/freetds/stable/freetds-0.82.tar.gz

#tar –xzf freetds-0.82.tar.gz

#cd freetds-0.82

     3.      Install freeTDS

# ./configure --with-tdsver=8.0 --with-unixodbc=/usr/local

#make

#make install

     4.      Now all your library will be inside /usr/local/lib and configuration files inside /usr/local/etc

     5.      Make template to register ODBC driver with unixODBC

#nano /usr/local/etc/tds.driver.template

[FreeTDS] Description     = v0.63 with protocol v8.0 Driver          = /usr/local/lib/libtdsodbc.so

tds.driver.template

     6.      Now install the driver entry using above template

# odbcinst -i -d -f /usr/local/etc/tds.driver.template

     7.      Make template to create ODBC data source name

#nano /usr/local/etc/tds.datasource.template

[MSSQLTest] Driver  = FreeTDS Description     = Sample Database Trace   = No Server      = 192.168.0.1 Port      = 1433 Database        = TestDB

tds.datasource.template

     8.      Now create odbc data source using above template

# odbcinst -i -s -f /usr/local/etc/tds.datasource.template

     9.      Finish. You can test it by giving command

                       # isql -v MSSQLTest username password