how to monitoring SQL?

[Sotiroff, Kristian]

This is brilliant Mattias. There's quite a few checks here that I'm not doing :)

(ps. I also like to top post..)


-----Original Message-----
From: Mattias Ryrlén [mailto:mattias.ryrlen at op5.com] 
Sent: 20 November 2008 07:45
To: nagios-user Mailinglist
Subject: Re: [Nagios-users] how to monitoring SQL?

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



CONFIDENTIALITY NOTICE

This e-mail (and any attached files) is confidential and protected by 
copyright (and other intellectual property rights). If you are not the 
intended recipient please e-mail the sender and then delete the email and 
any attached files immediately. Any further use or dissemination is 
prohibited.

While MTV Networks Europe has taken steps to ensure that this email and 
any attachments are virus free, it is your responsibility to ensure that 
this message and any attachments are virus free and do not affect your 
systems / data.

Communicating by email is not 100% secure and carries risks such as delay, 
data corruption, non-delivery, wrongful interception and unauthorised 
amendment. If you communicate with us by e-mail, you acknowledge and 
assume these risks, and you agree to take appropriate measures to minimise 
these risks when e-mailing us. 

MTV Networks International, MTV Networks UK & Ireland, Greenhouse, 
Nickelodeon Viacom Consumer Products, VBSi, Viacom Brand Solutions 
International and Comedy Central are all trading names of MTV Networks 
Europe.  MTV Networks Europe is a partnership between MTV Networks Europe 
Inc. and Viacom Networks Europe Inc.  Address for service in Great Britain 
is UK House, 180 Oxford Street, London W1D 1DS, UK.


-------------------------------------------------------------------------
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