Proforming MS-SQL Querys

[Petrucci, Joseph]

I use freetds to log on to MS-SQL from a Linux machine. Here is a Ksh plugin I use to do something similiar. And yes I know it doesn't meat the standards of a plugin (help/usage) and the error checking is poor but it was a 5 minute job that I needed for a specific problem.

=========================BEGINNING OF SCRIPT==================================
#!/bin/bash
##set -x
##############################################################################
## check_mssql_blocks : This script (plugin) logs onto a MS-SQL server (Version 7 or higher) 
## and reads the number of blocked processes from the sysprocesses table. depending on the 
## parameters it is given the script will exit with a return code of 2,1,or 0 (critical, warning, OK)
##
## The SQL-server must be configures in the freetds config file /usr/local/etc/freetds.conf

rm -f /tmp/dbblocks.${SERVER}

SERVER=$1
HOST=$2
PORT=$3
WARN=$4
CRITICAL=$5

USER1=`/bin/cat /usr/local/etc/.pass | grep ${SERVER} | cut -f2 -d":"`
PASS1=`/bin/cat /usr/local/etc/.pass | grep ${SERVER} | cut -f3 -d":"`

cat <<EOF | /usr/local/bin/tsql -S ${SERVER} -H ${HOST} -p ${PORT} -U ${USER1} -P ${PASS1} -I /usr/locaxl/etc/freetds.conf 2>/dev/null | grep ^^ | awk '{print $2}' > /tmp/dbblocks.${SERVER} 
use master
go
select '^^',count(*) from sysprocesses where blocked != 0
go
exit
EOF

if [ `cat /tmp/dbblocks.${SERVER} | wc -l` -eq 0 ]
then
	echo SQL could not connect to server ${SERVER}
	exit 2
fi

BLOCKED=`cat /tmp/dbblocks.${SERVER}`

if [ ${BLOCKED} -ge ${CRITICAL} ]
then
	echo SQL CRIITICAL ${BLOCKED} processes blocks
	exit 2
fi
if [ ${BLOCKED} -ge ${WARN} ]
then
	echo SQL WARNING ${BLOCKED} processes blocks
	exit 1
fi
if [ ${BLOCKED} -eq 0 ]
then
	echo SQL  OK - no blocked processes
	exit 0
fi
=========================END OF SCRIPT===================================

-----Original Message-----
From: nagios-users-admin at lists.sourceforge.net
[mailto:nagios-users-admin at lists.sourceforge.net]On Behalf Of Andreas
Ericsson
Sent: Wednesday, April 27, 2005 11:04 AM
To: Nagios Users Group
Subject: Re: [Nagios-users] Proforming MS-SQL Querys


Thomas Earl wrote:
> Greetings to all, 
> 
> I'm trying to find the best way to write a module to do a couple of
> MS-SQL querys and then compare the results... i.e.
> 
> $a = SELECT from CALLERS where are_logged_in
> $b = SELECT from CALLERS where on_calls
> 
> If $a = $b
>   report to nagios 
> [else]
>   exit
> 
> Of course the syntax of the above example is no where near what it needs
> to be, but you get the idea... 
> 
> I was reading something about sqsh, but I'm not running sybase so I
> don't know that is the right way... So I default to the experts... 
> 

MS-SQL == Sybase (more or less, anyway). They both speak the Tabular 
Data Stream protocol and thus can use the same client.

The plugin check_mssql.pl in contrib should point you in the right 
direction. It requires DBD::Sybase (go figure). To compile it, you'll 
needl need freetds or the proprietary Sybase libraries.

http://www.freetds.org
http://search.cpan.org/dist/DBD-Sybase

If memory serves me right, freetds comes with a client, a library, some 
headerfiles and a couple of examples so you can pick whichever one of C, 
shell and perl to hack up the script in (or python, ruby, tcl, expect 
too, but that wouldn't feel quite genuine, if you get my meaning).

> Any ideas would be great!
> 

Cheers. Let me know if you cook something up and want to share it.

> Thanks, 
> 
> Tom
> --
> Thomas Earl
> Network Engineer & Designer
> Communication Access Center
> 

-- 
Andreas Ericsson                   andreas.ericsson at op5.se
OP5 AB                             www.op5.se
Lead Developer


-------------------------------------------------------
SF.Net email is sponsored by: Tell us your software development plans!
Take this survey and enter to win a one-year sub to SourceForge.net
Plus IDC's 2005 look-ahead and a copy of this survey
Click here to start!  http://www.idcswdc.com/cgi-bin/survey?id=105hix
_______________________________________________
Nagios-users mailing list
Nagios-users at lists.sourceforge.net
https://lists.sourceforge.net/lists/listinfo/nagios-users
::: Please include Nagios version, plugin version (-v) and OS when reporting any issue. 
::: Messages without supporting info will risk being sent to /dev/null




-------------------------------------------------------
SF.Net email is sponsored by: Tell us your software development plans!
Take this survey and enter to win a one-year sub to SourceForge.net
Plus IDC's 2005 look-ahead and a copy of this survey
Click here to start!  http://www.idcswdc.com/cgi-bin/survey?id5hix
_______________________________________________
Nagios-users mailing list
Nagios-users at lists.sourceforge.net
https://lists.sourceforge.net/lists/listinfo/nagios-users
::: Please include Nagios version, plugin version (-v) and OS when reporting any issue. 
::: Messages without supporting info will risk being sent to /dev/null