how to monitoring SQL?

[Mattias Ryrlén]

Hi,

I top post since so many that like it :)

You could use check_sql from nagiosexchange:
http://www.nagiosexchange.org/cgi-bin/page.cgi?g=Detailed%
2F1435.html;d=1

Then you could use our nice how-to:
http://www.op5.com/support/technical-information/how-to/43-monitoring-microsoft-sql-server

It uses the following plugins:
check_nt
negate
check_sql

actual check_commands to be used (since they are not in the how-to but
in our product)

check_mssql_db_file_size:
$USER1$/check_nt -H $HOSTADDRESS$ -v COUNTER -l "\
\SQLServer:Databases($ARG1$)\Data File(s) Size (KB)","Db file for $ARG1$
is: %.2f Kb" -w $ARG2$ -c $ARG3$

check_mssql_log_file_size:
$USER1$/check_nt -H $HOSTADDRESS$ -v COUNTER -l "\
\SQLServer:Databases($ARG1$)\Log File(s) Size (KB)","Log file for $ARG1$
is: %.2f Kb" -w $ARG2$ -c $ARG3$

check_nt_memory_page_per_sec:
$USER1$/check_nt -H $HOSTADDRESS$ -v COUNTER -l "\\Memory
\Pages/sec","Pages/sec %.2f" -w $ARG1$ -c $ARG2$

check_nt_physical_disk_time:
$USER1$/check_nt -H $HOSTADDRESS$ -v COUNTER -l "\\PhysicalDisk(_Total)\
% Disk Time","Physical Disk Time %.2f percent" -w $ARG1$ -c $ARG2$

check_mssql_num_deadlocks:
$USER1$/check_nt -H $HOSTADDRESS$ -v COUNTER -l "\
\SQLServer:Locks(_Total)\Number of Deadlocks/sec","Num of Deadlocks/sec
%.2f" -c $ARG1$

check_mssql_num_user_connections:
$USER1$/check_nt -H $HOSTADDRESS$ -v COUNTER -l "\SQLServer:General
Statistics\User Connections","User Connections %.2f" -w $ARG1$ -c $ARG2$

check_mssql_cache_hit_ratio:
$USER1$/negate --ok=CRITICAL --critical=OK $USER1$/check_nt -H
$HOSTADDRESS$ -v COUNTER -l "\SQLServer:Cache Manager(_Total)\Cache Hit
Ratio","Total Cache Hit Ratio %.2f percent" -c $ARG1$

check_mssql_backup_job:
$USER1$/check_sql -H $HOSTADDRESS$ -U "$ARG1$" -P "$ARG2$" -d Sybase -D
msdb -q "SELECT TOP 1 sysjobhistory.message FROM sysjobhistory join
sysjobs on (sysjobhistory.job_id=sysjobs.job_id) WHERE sysjobs.name =
'$ARG3$' AND sysjobhistory.step_name = '(Job outcome)' AND DATEDIFF(dd,
CONVERT(CHAR(8), sysjobhistory.run_date), GETDATE()) < 1 ORDER BY
sysjobhistory.run_date,sysjobhistory.run_time" -s -r -e ".*The job
succeeded\..*"

check_mssql_query_string_regex:
$USER1$/custom/check_sql -H $HOSTADDRESS$ -U "$ARG1$" -P "$ARG2$" -d
Sybase -D $ARG3$ -q "$ARG4$" -s -r -e "$ARG5$"

check_mssql_query_count:
$USER1$/custom/check_sql -H $HOSTADDRESS$ -U "$ARG1$" -P "$ARG2$" -d
Sybase -D $ARG3$ -q "$ARG4$" -s -W $ARG5$ -C $ARG6$

check_mssql_query_reponstime:
$USER1$/custom/check_sql -H $HOSTADDRESS$ -U "$ARG1$" -P "$ARG2$" -d
Sybase -D $ARG3$ -q "$ARG4$" -s -w $ARG5$ -c $ARG6$ -s

-- 
Vänliga hälsningar / Best Regards
Mattias Ryrlén

__________________________
op5 AB
Första Långgatan 19
SE-413 27 Göteborg
Mobil: +46 735-17 70 99
Support: +46 31-774 09 24
www.op5.com

On Wed, 2008-11-19 at 10:34 -0600, Marc Powell wrote:
> On Nov 19, 2008, at 8:18 AM, Marc Powell wrote:
> 
> >
> > On Nov 19, 2008, at 7:25 AM, Fernando Rocha wrote:
> >
> >> Hi Esteban,
> >>
> >> Try to run the command with "sh -x" before the check_mssql.sh and
> >> then send the result to us:
> >>
> >> [root at localhost libexec]# sh -x check_mssql.sh server user pass 2000
> >
> > And as the nagios user, not root.
> 
> *caveat*, I don't use this plugin nor do I know much about the  
> internals of SQL2000.
> 
> I tested it out and experienced the same error. It appears that the  
> query is not qualified enough, at least for the username I was logging  
> in with.
> 
> Changing
> 
> echo -e "select loginame from sysprocesses where spid > $spid order by  
> loginame asc\ngo" > $tmpfile
> 
> to
> 
> echo -e "select loginame from sys.sysprocesses where spid > $spid  
> order by loginame asc\ngo" > $tmpfile
> 
> resulted in a successful check --
> 
> $ ./check_mssql.sh <redacted> <redacted> <redacted> 2000
> OK - MS SQL Server 2000 has 1 user(s) connected: 1 <redacted>.
> 
> --
> Marc
> 
> -------------------------------------------------------------------------
> This SF.Net email is sponsored by the Moblin Your Move Developer's challenge
> Build the coolest Linux based applications with Moblin SDK & win great prizes
> Grand prize is a trip for two to an Open Source event anywhere in the world
> http://moblin-contest.org/redirect.php?banner_id=100&url=/
> _______________________________________________
> 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



-------------------------------------------------------------------------
This SF.Net email is sponsored by the Moblin Your Move Developer's challenge
Build the coolest Linux based applications with Moblin SDK & win great prizes
Grand prize is a trip for two to an Open Source event anywhere in the world
http://moblin-contest.org/redirect.php?banner_id=100&url=/
_______________________________________________
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