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

Execute a MSSQL query from DOS prompt – Nagios Monitoring using ‘sqlcmd’

MSSQL - sqlcmd

Using ‘sqlcmd’

You can use sqlcmd to execute a MSSQL query right from the DOS prompt. Here’s an example:

C:Userstechpain>sqlcmd -S servername -d dbname -U dbuser-P dbpass -q "select field1 from column1 where field1 like '10%';"
field1
------
100
101
102
105
106
108
109

 (7 rows affected)
1> quit
C:Userstechpain>

sqlcmd sytax

-a packet_size
-A (dedicated administrator connection)
-b (terminate batch job if there is an error)
-c batch_terminator
-C (trust the server certificate)
-d db_name
-e (echo input)
-E (use trusted connection)
-f codepage | i:codepage[,o:codepage] | o:codepage[,i:codepage]
-h rows_per_header
-H workstation_name
-i input_file
-I (enable quoted identifiers)
-k[1 | 2] (remove or replace control characters)
-K application_intent
-l login_timeout
-L[c] (list servers, optional clean output)
-m error_level
-M multisubnet_failover
-N (encrypt connection)
-o output_file
-p[1] (print statistics, optional colon format)
-P password
-q "cmdline query"
-Q "cmdline query" (and exit)
-r[0 | 1] (msgs to stderr)
-R (use client regional settings)
-s col_separator
-S [protocol:]server[instance_name][,port]
-t query_timeout
-u (unicode output file)
-U login_id
-v var = "value"
-V error_severity_level
-w column_width
-W (remove trailing spaces)
-x (disable variable substitution)
-X[1] (disable commands, startup script, environment variables and optional exit)
-y variable_length_type_display_width
-Y fixed_length_type_display_width
-z new_password
-Z new_password (and exit)

-? (usage)

For more details on command line options, see http://technet.microsoft.com/

Nagios MSSQL query

The sqlcmd tool is awesome because it allows you to use Nagios and NSclient/nrpe to query a MSSQL server. Here’s how:

  1. Create a .bat on your MSSQL server that contains your query and sends the output to a .txt file
  2. Configure the Windows firewall and NSclient/nrpe check on the MSSQL server
  3. Configure a Windows NSclient/NRPE check in Nagios to check the .txt file
  4. Profit
Share

MySQL Databases – mysqldump and restore

Its obviously been way to long since I’ve played with MySQL, because i ran into trouble trying to restore a db from a .sql mysqldump I created. Here are all the steps:

Create a .sql file from a MySQL Database in your current directory:
mysqldump dbname > filename.sql

A database must exist for the .sql file to be restored to, if you don’t have one already then:
mysql
create database
newdbname;
grant all on
newdbname.* to dbusername@localhost identified by ‘dbpassword‘;
flush privileges;
exit

Restore the .sql to the newdb database:
mysql newdbname < filename.sql

Share

Share