check_mssql_health

Posted on July 15th, 2009 by lausser

declare @dbname varchar(255)
declare @check_mssql_health_USER varchar(255)
declare @check_mssql_health_PASS varchar(255)
declare @check_mssql_health_ROLE varchar(255)
declare @source varchar(255)
declare @options varchar(255)
declare @backslash int

/*******************************************************************/
SET @check_mssql_health_USER = '"[Servername|Domainname]\Username"'
SET @check_mssql_health_PASS = 'Password'
SET @check_mssql_health_ROLE = 'Rolename'
/*******************************************************************

PLEASE CHANGE THE ABOVE VALUES ACCORDING TO YOUR REQUIREMENTS

- Example for Windows authentication:
  SET @check_mssql_health_USER = '"[Servername|Domainname]\Username"'
  SET @check_mssql_health_ROLE = 'Rolename'

- Example for SQL Server authentication:
  SET @check_mssql_health_USER = 'Username'
  SET @check_mssql_health_PASS = 'Password'
  SET @check_mssql_health_ROLE = 'Rolename'

!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!
It is strongly recommended to use Windows authentication. Otherwise
you will get no reliable results for database usage.
!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!

*********** NO NEED TO CHANGE ANYTHING BELOW THIS LINE *************/

SET @options = 'DEFAULT_DATABASE=MASTER, DEFAULT_LANGUAGE=English'
SET @backslash = (SELECT CHARINDEX('\', @check_mssql_health_USER))
IF @backslash > 0
  BEGIN
    SET @source = ' FROM WINDOWS'
    SET @options = ' WITH ' + @options
  END
ELSE
  BEGIN
    SET @source = ''
    SET @options = ' WITH PASSWORD=''' + @check_mssql_health_PASS + ''',' + @options
  END

PRINT 'create Nagios plugin user ' + @check_mssql_health_USER
EXEC ('CREATE LOGIN ' + @check_mssql_health_USER + @source + @options)
EXEC ('USE MASTER GRANT VIEW SERVER STATE TO ' + @check_mssql_health_USER)
PRINT 'User ' + @check_mssql_health_USER + ' created.'
PRINT ''

declare dblist cursor for
  select name from sysdatabases WHERE name NOT IN ('master', 'tempdb', 'msdb') open dblist
    fetch next from dblist into @dbname
    while @@fetch_status = 0 begin
      EXEC ('USE [' + @dbname + '] print ''Grant permissions in the db '' + ''"'' + DB_NAME() + ''"''')
      EXEC ('USE [' + @dbname + '] CREATE ROLE ' + @check_mssql_health_ROLE)
      EXEC ('USE [' + @dbname + '] GRANT EXECUTE TO ' + @check_mssql_health_ROLE)
      EXEC ('USE [' + @dbname + '] GRANT VIEW DATABASE STATE TO ' + @check_mssql_health_ROLE)
      EXEC ('USE [' + @dbname + '] GRANT VIEW DEFINITION TO ' + @check_mssql_health_ROLE)
      EXEC ('USE [' + @dbname + '] CREATE USER ' + @check_mssql_health_USER + ' FOR LOGIN ' + @check_mssql_health_USER)
      EXEC ('USE [' + @dbname + '] EXEC sp_addrolemember ' + @check_mssql_health_ROLE + ' , ' + @check_mssql_health_USER)
      EXEC ('USE [' + @dbname + '] print ''Permissions in the db '' + ''"'' + DB_NAME() + ''" granted.''')
      fetch next from dblist into @dbname
    end
close dblist
deallocate dblist

declare @dbname varchar(255)
declare @check_mssql_health_USER varchar(255)
declare @check_mssql_health_ROLE varchar(255)

SET @check_mssql_health_USER = '"[Servername|Domainname]\Username"'
SET @check_mssql_health_ROLE = 'Rolename'

declare dblist cursor for
  select name from sysdatabases WHERE name NOT IN ('master', 'tempdb', 'msdb') open dblist
    fetch next from dblist into @dbname
    while @@fetch_status = 0 begin
      EXEC ('USE [' + @dbname + '] print ''Revoke permissions in the db '' + ''"'' + DB_NAME() + ''"''')
      EXEC ('USE [' + @dbname + '] EXEC sp_droprolemember ' + @check_mssql_health_ROLE + ' , ' + @check_mssql_health_USER)
      EXEC ('USE [' + @dbname + '] DROP USER ' + @check_mssql_health_USER)
      EXEC ('USE [' + @dbname + '] REVOKE VIEW DEFINITION TO ' + @check_mssql_health_ROLE)
      EXEC ('USE [' + @dbname + '] REVOKE VIEW DATABASE STATE TO ' + @check_mssql_health_ROLE)
      EXEC ('USE [' + @dbname + '] REVOKE EXECUTE TO ' + @check_mssql_health_ROLE)
      EXEC ('USE [' + @dbname + '] DROP ROLE ' + @check_mssql_health_ROLE)
      EXEC ('USE [' + @dbname + '] print ''Permissions in the db '' + ''"'' + DB_NAME() + ''" revoked.''')
      fetch next from dblist into @dbname
    end
close dblist
deallocate dblist

PRINT ''
PRINT 'drop Nagios plugin user ' + @check_mssql_health_USER
EXEC ('USE MASTER REVOKE VIEW SERVER STATE TO ' + @check_mssql_health_USER)
EXEC ('DROP LOGIN ' + @check_mssql_health_USER)
PRINT 'User ' + @check_mssql_health_USER + ' dropped.'

nagsrv$ check_mssql_health --mode mem-pool-data-buffer-hit-ratio
CRITICAL - buffer cache hit ratio is 71.21% | buffer_cache_hit_ratio=71.21%;90:;80:

nagsrv$ check_mssql_health --mode batch-requests
OK - 9.00 batch requests / sec | batch_requests_per_sec=9.00;100;200

nagsrv$ check_mssql_health --mode full-scans
OK - 6.14 full table scans / sec | full_scans_per_sec=6.14;100;500

nagsrv$ check_mssql_health --mode cpu-busy
OK - CPU busy 55.00% | cpu_busy=55.00;80;90

nagsrv$ check_mssql_health --mode database-free --name AdventureWorks
OK - database AdventureWorks has 21.59% free space left | 'db_adventureworks_free_pct'=21.59%;5:;2: 'db_adventureworks_free'=703MB;4768371582.03:;1907348632.81:;0;95367431640.62

nagsrv$ check_mssql_health --mode database-free --name AdventureWorks \
--warning 700: --critical 200: --units MB
WARNING - database AdventureWorks has 694.12MB free space left | 'db_adventureworks_free_pct'=21.31%;0.00:;0.00: 'db_adventureworks_free'=694.12MB;700.00:;200.00:;0;95367431640.62

nagsrv$ check_mssql_health --mode page-life-expectancy
OK - page life expectancy is 8950 seconds | page_life_expectancy=8950;300:;180:

nagsrv$ check_mssql_health --mode database-backup-age --name AHLE_WORSCHT \
--warning 72 --critical 120
WARNING - AHLE_WORSCHT backupped 102h ago | 'AHLE_WORSCHT_bck_age'=102;72;120 'AHLE_WORSCHT_bck_time'=12

[global]
# TDS protocol version
# tds version = 4.2
tds version = 8.0

[sourcecode language='xml']
[dbsrv1instance01]
        host = 192.168.1.19
        port = 1433
        instance = instance01

[dbsrv1instance02]
        host = 192.168.1.19
        port = 1433
        instance = instance02

Gerhard Laußer

Check_mssql_health is published under the GNU General Public License.

http://www.gnu.de/documents/gpl.de.html[GPL]

Author

168 Responses to “check_mssql_health”

1. axel Says:
   October 13th, 2009 at 9:54

   cool

2. david Says:
   October 25th, 2009 at 22:57

   thank you. Could you translate instructions ?

   

   lausser Reply:
   October 26th, 2009 at 22:11

   It’s on my list. (To be honest, it’s on my list since half a year). I’ll try to get some spare time to do the translation.

   

   ChandraShaker Reply:
   March 8th, 2011 at 22:43

   @david,

   1) please visit http://www.google.com/language_tools?hl=EN

   2) past the below url

   http://labs.consol.de/nagios/check_mssql_health/

   3) select your destination language, source language is german

   4) wait for few min, you will see the output in the language you prefered

   Regards Chandra

3. Ron Says:
   October 27th, 2009 at 20:14

   Nice tool, got it setup completely and it’s monitoring SQL servers, however I can’t seem to monitor instances, always got an error, any idea how to work around this?

   Cheers, Ron

   

   lausser Reply:
   October 29th, 2009 at 20:42

   You are surely using −−hostname … −−port. This is ok, as long as you only have the default instance. If you want to address particular instances, −−server is the way to go. You need to add a section to the /etc/freetds.conf file.

   [myinst1] host = 192.168.1.100 port = 1433 instance = myinstance1

   …

   Now you can call check_mssql_health −−server myinst1 (this is the title of the section describing the instance).

   Gerhard

4. Jason Says:
   November 4th, 2009 at 18:08

   Thanks for this tool

5. Ron Says:
   November 5th, 2009 at 10:48

   Thanks Gerhard! That did the trick :)

6. Diego Fernández Says:
   November 5th, 2009 at 18:36

   Hi, I’m testing your plugin and it works fine except lock options that return:

   unable to aquire lock info

   Sorry for my very bad english.

   Thanks. Diego

7. Birk Bohne Says:
   November 6th, 2009 at 17:13

   I have tested the needed permissions for a non sa user to run the checks. The external Windows user is needed because of an internal non sa user gets an empty return from “exec master.dbo.xp_fixeddrives” and that produces wrong results for the “database-free” check. So first you must create a Windows user (domain or local) without admin rights on the sql server and then you can create the needed permissions with the following script.

   CREATE LOGIN “SERVERNAME or DOMAIN\nagios_mssql_health” FROM WINDOWS WITH DEFAULT_DATABASE=MASTER, DEFAULT_LANGUAGE=English;

   USE MASTER GRANT VIEW SERVER STATE to “SERVERNAME or DOMAIN\nagios_mssql_health”

   USE MODEL CREATE ROLE db_nagios_mssql_health GRANT EXECUTE TO db_nagios_mssql_health GRANT VIEW DATABASE STATE TO db_nagios_mssql_health GRANT VIEW DEFINITION TO db_nagios_mssql_health CREATE USER “SERVERNAME or DOMAIN\nagios_mssql_health” FOR LOGIN “SERVERNAME or DOMAIN\nagios_mssql_health”; EXEC sp_addrolemember ‘db_nagios_mssql_health’, “SERVERNAME or DOMAIN\nagios_mssql_health”

   USE DB1 CREATE ROLE db_nagios_mssql_health GRANT EXECUTE TO db_nagios_mssql_health GRANT VIEW DATABASE STATE TO db_nagios_mssql_health GRANT VIEW DEFINITION TO db_nagios_mssql_health CREATE USER “SERVERNAME or DOMAIN\nagios_mssql_health” FOR LOGIN “SERVERNAME or DOMAIN\nagios_mssql_health”; EXEC sp_addrolemember ‘db_nagios_mssql_health’, “SERVERNAME or DOMAIN\nagios_mssql_health”

   USE DB2 CREATE ROLE db_nagios_mssql_health GRANT EXECUTE TO db_nagios_mssql_health GRANT VIEW DATABASE STATE TO db_nagios_mssql_health GRANT VIEW DEFINITION TO db_nagios_mssql_health CREATE USER “SERVERNAME or DOMAIN\nagios_mssql_health” FOR LOGIN “SERVERNAME or DOMAIN\nagios_mssql_health”; EXEC sp_addrolemember ‘db_nagios_mssql_health’, “SERVERNAME or DOMAIN\nagios_mssql_health”

   Then the nagios checks are like “check_mssql_health -server sql1 -username sql1\nagios_mssql_health -password PW -name DB1 -mode database-free”. Enumerating all non system databases and set the needed permission would be nice for the db script. I have tested it will all checks in version 1.5.3 on Windows 2003 R2 SP2 and SQL2005 SP2.

   greetings Birk

8. Andrey Says:
   November 19th, 2009 at 15:51

   Thanks for this detailed description in English!

9. Jens Says:
   November 30th, 2009 at 11:35

   Hi,

   Also nachdem ich alle Probleme mit DBD:Sybase irgendwie in den Griff bekommen habe, erhalte ich bei egal welcher Abfrage nur die Ausgabe Speicherzugriffsfehler. Gibt es eine Art von Debug-Modus, um den Fehler einzugrenzen?

10. BorisPlus Says:
    December 3rd, 2009 at 20:24

    Hello. I wrote message to your mail. I have some strange problem with this plugin. help me, please.

11. CTAC Says:
    December 4th, 2009 at 5:38

    version 1.5.3

    ./configure –with-mymodules-dyn-dir=/usr/lib/nagios/plugins –with-mymodules-dir=/usr/lib/nagios/plugins –with-statefiles-dir=/var/lib/nagios

    but

    make install

    makes:

    test -z “/usr/local/nagios/libexec” || /bin/mkdir -p “/usr/local/nagios/libexec” /usr/bin/install -c ‘check_mssql_health’ ‘/usr/local/nagios/libexec/check_mssql_health’

    please fix it and may be you find time to create an SPEC-file for rpmbuild, please!

12. Dietmar Says:
    December 8th, 2009 at 12:29

    Hallo Herr Laußer,

    ich teste gerade Ihr Plugin check_mssql_health in der Version 1.5.3.

    Leider ohne Erfolg :-(

    Ich erhalte für folgenden Befehl: root@dumon01:/usr/local/nagios/libexec# ./check_mssql_health –hostname duna1 –username sa –password PassworD –mode connected-users

    folgende Ausgabe: CRITICAL – cannot connect to duna1. DBI connect(‘;host=duna1;port=1433′,’sa’,…) failed: (no error string) at ./check_mssql_health line 1977

    Ein Test mit tsql und den Anmeldedaten funktioniert einwandfrei.

    Viele Grüsse aus Duisburg Dietmar

    

    lausser Reply:
    December 8th, 2009 at 14:59

    Versuchen sie es bitte mit folgendem Miniscript:

    use strict; use warnings; use DBI; my $dsn = "DBI:Sybase:"; my $hostname = 'duna1'; my $port = '1433'; my $server = undef; if ($hostname) { $dsn .= sprintf ";host=%s", $hostname; $dsn .= sprintf ";port=%s", $port; } else { $dsn .= sprintf ";server=%s", $server; } if (my $dbh = DBI->connect( $dsn, "sa", "PassworD", { RaiseError => 1, AutoCommit => 0, PrintError => 1 })) { printf 'connected\\n'; $dbh->disconnect(); } else { printf 'could not connect: %s\\n', DBI::errstr(); }

    Aus den doppelten Backslashes muss man jeweils einen einfachen Backslash machen. (Mein Editor spinnt…)

13. Dietmar Says:
    December 16th, 2009 at 17:06

    Hallo Herr Laußer, habe das Miniscript getestet. Leider ohner Erfolg :-( Hier die Ausgabe: root@dumon01:~# perl ./test.pl DBI connect(‘;host=duna1;port=1433′,’sa’,…) failed: (no error string) at ./test.pl line 14 root@dumon01:~#

    Ich habe keine Idee was es sein könnte.

    

    Dietmar Reply:
    December 16th, 2009 at 17:55

    Hallo Herr Laußer, das Script von TheCry (http://www.icinga-portal.org/wbb/index.php?page=Thread&threadID=14547) funktioniert bei mir ohne Probleme.Ich habe in der /etc/freetds/freetds.conf keinen Server konfiguriert, nur unter [global] die tds version = 8.0 gesetzt. Viele Grüsse, Dietmar

14. Ross Says:
    December 22nd, 2009 at 17:39

    In Nagios, the output of ‘list-databases’ mode shows just the first database name (ReportServer) instead of showing the entire list of DBs. In the command-line, however, check_mssql_health shows the complete list (a \n separated list). Perhaps the ‘\n’ character is interpreted by nagios as special char, so it shows just the first line and not the others. How can I modify the output so that the db list is done by a ‘;’ separated list (for example) ?

    

    lausser Reply:
    December 23rd, 2009 at 11:00

    Since version 3 Nagios can read multiple lines of plugin output. Maybe you still have a 2.x version?

    

    Ross Reply:
    December 23rd, 2009 at 11:17

    @lausser, no, I have the latest 3.2.0.

    This is the line in Nagios:

    SQL Server DB Lists OK 12-23-2009 10:01:13 0d 7h 9m 3s 1/3 ReportServer

    Instead, the execution via shell of the same command yields:

    ReportServer ReportServerTempDB master model msdb prova tempdb OK – have fun

    My system is CentOS 5.4, Apache 2.2.3, Nagios 3.2.0, check_mssql_health 1.5.3…

    

    lausser Reply:
    December 23rd, 2009 at 20:59

    Strange…maybe when run in the Nagios environment it does not find databases at all. Please create the file /tmp/check_mssql_health.trace and watch it with tail -f. You should see the command “SELECT name, database_id FROM master.sys.databases” and then a list of database names as the result. Maybe for an unknown reason this list is empty.

    

    Ross Reply:
    December 24th, 2009 at 12:29

    It’s not empty. At least, the first one is there! Ok, I look at that, better. In the “Services” page in Nagios (the page that lists all the defined services in the network), just the first DB is shown (ReportServer). Nevertheless, clicking on the link of that service, a detailed view of the service is presented, and here all databases are listed! So, it seems like a visualization problem in the Services page in Nagios, not in your plugin. So, if you want to see all databases, you have to click on the related link of the service, the one that shows a detailed view of the service… Yes, it’s a bit uncomfortable, but it works.

15. augustinus Says:
    January 10th, 2010 at 16:50

    Hallo Gerhard,

    ich habe eine Frage zu den Environment-Variablen. Kann ich diese auch in den eigenen Modulen (CheckMssqlHealthX.pm) abfragen und verwenden? %ENV habe ich schon ausprobiert, das hilft offenbar nix.

    Viele Grüße

    

    lausser Reply:
    January 10th, 2010 at 19:26

    Welche meinst du damit speziell? Es sollte kein Problem sein, z.B. $ENV{HOME} zu verwenden, da der Code eigener Module im gleichen Prozess ausgeführt wird wie das Plugin selbst.

    

    augustinus Reply:
    January 11th, 2010 at 12:40

    Hallo Gerhard,

    sorry aber ich habe ich habe gestern wohl versucht die Variable innerhalb des q{}-Blocks anzusprechen. Das kann natürlich nicht gehen.

    Ich habe jetzt das SQL-Statement in einen spearaten String ausgelagert, damit klappt das dann auch.

    Viele Grüße und vielen lieben Dank.

    Andi

16. Bernd Says:
    February 2nd, 2010 at 14:24

    Hallo,

    ich hab leider ein Problem bei der Erstellung des Benutzers mit dem obigen Script (SQL-Server 2008):

    Ich bekomme folgende Fehlermeldungen: Msg 102, Level 15, State 1, Line 35 Incorrect syntax near ‘;’. Msg 156, Level 15, State 1, Line 40 Incorrect syntax near the keyword ‘ELSE’.

    -> Im Script betrifft dies folgende Zeilen: Msg 102, Level 15, State 1, Line 35 Incorrect syntax near ‘;’. Msg 156, Level 15, State 1, Line 40 Incorrect syntax near the keyword ‘ELSE’.

    Kenne mich leider mit dem Scripting nicht so aus & hoffe auf eure Unterstützung.

    LG, Bernd

    

    Bernd Reply:
    February 2nd, 2010 at 14:26

    @Bernd, es sind folgende Zeilen betroffen: SET @backslash = (SELECT CHARINDEX(‘\’, @check_mssql_health_USER)) IF @backslash > 0 BEGIN SET @source = ‘ FROM WINDOWS’ SET @options = ‘ WITH ‘ + @options END ELSE

    

    lausser Reply:
    February 2nd, 2010 at 15:16

    Tut mir leid, da hat WordPress wieder mal im Sourcecode des Scripts rumgepfuscht. Kopiers dir in einen Editor und ersetze die Zeichenfolge " durch ein Anführungszeichen (doppeltes Hochkomma) sowie die Zeichenfolge > durch ein “grösser”-Zeichen (spitze Klammer rechts). Du findest die Scripts auch zum Download unter http://www.nagios-das-praxisbuch.de/?page_id=10

17. tio Says:
    February 18th, 2010 at 11:53

    hi lausser i got the problem like this : CRITICAL – cannot connect to 172.17.80.88. DBI connect(‘;host=172.17.80.88;port=1433′,’domain\\username’,…) failed: (no error string) at /usr/lib/nagios/plugins/check_mssql_health line 1977

    can you give me the solution please … thanks lausser

    

    lausser Reply:
    February 20th, 2010 at 0:06

    Are you 100% sure you can reach the database server? Try this first:

    tsql -H 172.17.80.88 -p 1433 -U <username> -P <password>

18. Rob Says:
    February 20th, 2010 at 1:56

    sql -H server -p port -U -P locale is “en_US.UTF-8″ locale charset is “UTF-8″ 1> quit but when I run ./check_mssql_health -d -hostname edc-syspulsedb -username edit -password edit –mode connection-time -database QDB -warning 5 -critical 10 CRITICAL – cannot connect to server. DBI connect(‘;host=edc-syspulsedb;port=1433′,’edit’,…) failed: (no error string) at ./check_mssql_health line 1907. Any help would be greatly appreciated.

    Rob

19. robbyck Says:
    February 20th, 2010 at 2:08

    The above post is with 1.5.2 where it states line 1907. With version 1.5.3 it is line 1977.

    Thanks, Rob

20. Phil Yardley Says:
    February 20th, 2010 at 12:00

    Hi, I’m getting the following error through Nagios when running the plug in… I’m not sure if it’s to do with the login or something else? any assistance would be appreciated..

    the error is: CRITICAL – cannot connect to 192.168.0.20. DBI connect(‘:host=192.168.0.20:port=1433′,’DRLNagiosmonitor’,…) failed: (no error string) at /usr/local/nagios/libexec/check_mssql_health line 1977

    No matter how I put the DOAMIN\username in to the command file, it doesn’t seem to output as I expect !

    my command line in nagios is:

    ‘check_mssql’ command definition

    define command{ command_name check_mssql command_line $USER1$/check_mssql_health -hostname $HOSTADDRESS$ -username DRL\\Nagiosmonitor -password xXxXxX -port 1433 -mode $ARG1$ -warning $ARG2$ -critical $ARG3$ }

    thanks.

    Phil

    

    lausser Reply:
    February 22nd, 2010 at 11:55

    Please download this mini-testscript. mssql_mini_test Maybe it’s the domain\\username you’re using.

    

    Phil Yardley Reply:
    February 23rd, 2010 at 0:42

    @lausser, Thanks Lausser, the output is as follows:

    connecting as user drl\nagiosmonitor DBI connect(‘;host=drlXXxxXXxx;port=1433′,’drl\nagiosmonitor’,…) failed: (no error string) at test.pl line 41

    Does this help?

    thanks

    

    Phil Yardley Reply:
    February 24th, 2010 at 1:12

    Hi Lausser,

    Getting somewhere :)

    By using the –server option and defining the servers in the freetds.conf – I can get one of the servers a little further (I think)

    I am now getting the error: ct_result(ct_dynamic(CS_PREPARE)) returned 15 at /usr/lib/perl5/DBD/Sybase.pm line 124. DBD::Sybase::db prepare failed: Server message number=8009 severity=16 state=1 line=1 server=DRLSQL3text=The incoming tabular data stream (TDS) remote procedure call (RPC) protocol stream is incorrect. Parameter 4 (“”): Data type 0×38 is unknown. at check_mssql_health line 2007. UNKNOWN – unable to aquire counter data

    Any pointers?

    thanks

    

    lausser Reply:
    February 24th, 2010 at 1:42

    So you’re using the plugin, not the mini_test? Please use the latter, as the problem must be somewhere in the DBD::Sybase module or the freetds lib. If you follow this thread http://www.icinga-portal.org/wbb/index.php?page=Thread&threadID=15173 you’ll see, some guy had the same error messages “Server message number=8009….” and found a solution. Maybe this works for you too.

21. robbyck Says:
    February 22nd, 2010 at 23:11

    Here is what I get when I run the test script. DBI connect(‘;host=;port=1433′,”,…) failed: (no error string) at mssql_mini_test line 41

    

    lausser Reply:
    February 23rd, 2010 at 15:53

    So there is no “connecting as user ….” in your output. Very strange. Maybe you should ask here http://search.cpan.org/~mewp/DBD-Sybase-1.09/Sybase.pm for help. If the mini-script does not work, there’s not much i can do.

22. angry_admin Says:
    February 24th, 2010 at 13:49

    http://ideas.nagios.org/a/dtd/22035-3955

23. robbyck Says:
    February 25th, 2010 at 0:30

    After recompiling DBD::Sybase I got it to work on Ubuntu. robbyck

    

    lausser Reply:
    February 25th, 2010 at 2:49

    Did you recompile DBD::Sybase out of the box or did you have to add these #undefs in dbdimp.c as described in the http://www.icinga-portal.de link above?

24. vincenzo Says:
    March 4th, 2010 at 16:01

    Do you have more detailed installation instructions for a linux newbie?

25. Michael Says:
    March 19th, 2010 at 15:34

    Guten Tag zusammen,

    ich habe ein Problem beim Einbinden des check_mssql_health. Starte ich den check auf der Kommandozeilenebene ./check_mssql_health -hostname xxx.xxx.xxx.xxx-username SDSINT\\nagios -password geheim -port 1433 -mode connected-users -warning 100 -critical 300

    klappt alles wunderbar :

    OK – 73 connected users | connected_users=73;100;300

    Versuch ich das ganze aus Nagios mit dem eintrag in der hosts.cfg

    define service{ host_name sds11-sv000mv2 service_description MS-SQL Connected User test check_command check_mysql!xxx.xxx.xxx.xxx!SDSINT\\nagios!geheim!1433!connected-users!100!300 contact_groups nagiosadmin use template-service } und dem entspechenden Eintrag in der checkcammads.cfg

    Überwachung MS-SQL

    define command { command_name check_mysql command_line $USER1$/check_mssql_health -hostname $HOSTADDRESS$ -username $ARG1$ -password $ARG2$ -port $ARG3$ -mode $ARG4$ -warning $ARG5$ -critical $ARG6$ # Argumente : # $ARG1$ = Datenbankuser # $ARG2$ = Userpassword # $ARG3$ = Datenbankport # $ARG4$ = Abfragemodi # $ARG5$ = Warnschwelle # $ARG6$ = Alarmschwelle } klappt nichts :

    CRITICAL – cannot connect to 10.55.112.14. DBI connect(‘:host=10.55.112.14:port=1433′,’SDSINTnagios’,…) failed: Server message number=18456 severity=14 state=1 line=1 server=SV000MV2SV000MV2_INST text=Login failed for user ‘SDSINTnagios’.OpenClient message: LAYER = (0) ORIGIN = (0) SEVERITY = (78) NUMBER = (34)

    Google hat mir da auch nicht wirklich weitergeholfen.

    HILFE

    

    lausser Reply:
    March 22nd, 2010 at 2:57

    http://www.mail-archive.com/nagios-users@lists.sourceforge.net/msg25694.html Ist wohl ein Problem mit den Backslashes in der Servicedefinition.

26. Michael Says:
    March 24th, 2010 at 16:21

    Danke für den Hinweis gelöst habe ich es über die ressource.cfg

    Da klappt es dann auch

27. Benoit P. Says:
    March 30th, 2010 at 16:19

    Hello,

    I have a little problem with the script, i’m unable to specify a TCP port, the script exist with no error and he doesn’t even try to connect to the server (no trace with tcpdump) :

    /usr/local/nagios/libexec/check_mssql_health –hostname=10.66.149.10 –port=1235 –mode=connected-users –username=nagios –password=ziplepingouin CRITICAL – cannot connect to 10.66.149.10. DBI connect(‘;host=10.66.149.10;port=1235′,’nagios’,…) failed: (no error string) at /usr/local/nagios/libexec/check_mssql_health line 1977

    Any idea ? Regards.

    

    lausser Reply:
    March 30th, 2010 at 17:13

    Please scroll up and you’ll find a small Perl-script for testing purposes (hostname duna1). Change $hostname and $port and try it with your own ip and port. (and watch out for the double backslashes in the printf statements. these must be single backslashes). Do you get a “connected” message?

28. Benoit P. Says:
    April 1st, 2010 at 9:30

    Hello,

    Same problem with the little test script.

    monitor:~# perl test.pl

    DBI connect(‘;host=10.66.149.10;port=1235′,’nagios’,…) failed: (no error string) at test.pl line 14

    But i think i have found the root problem My perl package of DBD-Sybase is version 1.00 witch do not implement connexion with host/port. (i’m under Debian).

29. Alex Says:
    April 2nd, 2010 at 23:26

    Hello Guys,

    Question about the SQL script section: /*******************************************************************/ SET @check_mssql_health_USER = ‘"[Servername|Domainname]\Username"’ SET @check_mssql_health_PASS = ‘Password’ SET @check_mssql_health_ROLE = ‘Rolename’ /******************************************************************* which is supposed to give “minimal, yet sufficient privileged monitoring-user.” What should I replace ‘Rolename’ with to get minimal/sufficient access?

    Thank you,

    AK

    

    lausser Reply:
    April 3rd, 2010 at 1:36

    You can replace ‘Rolename’ with whatever string you want (whatever fits your company’s naming conventions best) because this role will be created later in the script.

    

    Alex Reply:
    April 5th, 2010 at 20:12

    @lausser, Great. Could you please clarify a few more things for me? ‘"[Servername|Domainname]\Username"’ What is with this “&quot” business. MS SQL does not seem to like it. Also, in “IF @backslash > 0″ “>” is used instead of “>”. Why?

    

    Alex Reply:
    April 5th, 2010 at 20:13

    @lausser, Never mind. It looks like HTML formatting issue.

30. Birk Bohne Says:
    April 21st, 2010 at 18:27

    Today i had run into a Windows login problem on a sql Server. I have done the same configuration steps as on a other server that has no login problems.

    ./check_mssql_health -server sqlserver001 -username sqlserver001\\nagios_mssql_health -password PASS -warning 5 -critical 5 -mode connection-time CRITICAL – cannot connect to sqlserver001. DBI connect(‘;server=sqlserver001′,’sqlserver001\nagios_mssql_health’,…) failed: Server message number=18452 severity=14 state=1 line=1 server=sqlserver001 text=Fehler bei der Anmeldung für den Benutzer ”. Der Benutzer ist keiner vertrauenswürdigen SQL Server-Verbindung zugeordnet.OpenClient message: LAYER = (0) ORIGIN = (0) SEVERITY = (78) NUMBER = (46) Server sqlserver001, database Message String: Login incorrect.

    The problem is that the username string is longer the 32 chars (sqlserver001\\nagios_mssql_health). The machine without the login problems has the same username but a shorter hostname. So i have shortend the username and then the plugin works without problems.

    ./check_mssql_health -server sqlserver001 -username sqlserver001\\nagios_mssql -password PASS -warning 5 -critical 5 -mode connection-time OK – 0.04 seconds to connect as sqlserver001\nagios_mssql | connection_time=0.04;5;5

    You can see the cutoff logon string if you enable the debug mode within the freetds.conf.

    dump file = /tmp/freetds.log dump file append = yes debug level = 99

    greetings Birk

31. Stefan Senftleben Says:
    April 23rd, 2010 at 10:41

    Hallo! Funktioniert das Skript von Birk Bohne auch mit SQL2000-Servern? Grüße Stefan

    

    lausser Reply:
    April 26th, 2010 at 17:27

    Ich will meine Hand nicht dafür ins Feuer legen, aber ich würde sagen, es ist auch für SQL2000 geeignet.

    

    Birk Bohne Reply:
    April 27th, 2010 at 16:12

    @Stefan Senftleben,

    Ich habe es nur mit SQL2005 getestet. Demnächst werde ich es für SQL2008 brauchen, aber SQL2000 DB’s habe ich leider keine mehr.

    Gruß Birk

32. Rick Says:
    May 5th, 2010 at 18:00

    I have had this working for several months monitoring many sql servers but all of the sudden the status reports as mumms. What does that mean?

    

    lausser Reply:
    May 10th, 2010 at 0:11

    I have no idea. “It worked and now it doesn’t work” means, something was changed.

33. cosmefulanito Says:
    May 17th, 2010 at 22:25

    hi, i have this error

    CRITICAL – cannot connect to nt03. Can’t locate DBI.pm in @INC (@INC contains: . /usr/lib/perl5/site_perl/5.10.1

    any idea how to work around this?

    

    lausser Reply:
    May 18th, 2010 at 0:19

    You need the perl modules DBI (as the error message says) and DBD::Sybase as well as the freetds software.

34. DNESH Says:
    May 18th, 2010 at 4:54

    How do you pronounce NAGIOS, is it using a soft G or a hard one?

    Thanks

    

    lausser Reply:
    May 18th, 2010 at 13:01

    It’s a hard G: http://community.nagios.org/2007/02/20/nagios-pronunciation/

35. Tapan Says:
    May 20th, 2010 at 19:44

    Hi,

    I am trying to monitor MSSQL IO Busy and CPU busy check but i am getting below mentioned error under nagios however if i fire the same command in nagios server then output is just fine. Fyi i am able to monitor connected users and connection time with nagios perfectly.

    **ePN /usr/lib/nagios/plugins/libexec/check_mssql_health: “printf() on closed filehandle STATE at (eval 10) line 1647,”.

    Please suggest.

    Regards Tapan Thapa

36. Tapan Says:
    May 21st, 2010 at 17:37

    For those who are facing the same type of ssue, i am replying to my own post. I am able to monitor mssql stats, if i put command like this in nagios:

    /usr/bin/perl check_mssql_health -server xx.xx.xx.xx -username xxxxxx -password xxxxxx -mode transactions

    define command{ command_name check_iis command_line $USER2$ /usr/lib/nagios/plugins/check_iis $HOSTADDRESS$ }

    Where in $USER2$ point to /usr/bin/perl in resource.cfg

    Regards Tapan Thapa

    

    Shawn Reply:
    June 1st, 2010 at 10:43

    @Tapan, I’m having a similar issue on the io-busy check. Tapan, I don’t understand how your iis check fixed this problem. Could you please explain a bit more?

37. bigpeti Says:
    June 18th, 2010 at 9:30

    Hi, With check_mssql_health_1.5.3 I have this error too:

    root@nagiossrv:/usr/local/nagios/libexec# perl check_mssql_health –host xxx.xxx.xxx.xxx –port 1433 –username sa –password Password –mode connection-time

    CRITICAL – cannot connect to xxx.xxx.xxx.xxx. DBI connect(‘;host=xxx.xxx.xxx.xxx;port=1433′,’sa’,…) failed: (no error string) at check_mssql_health line 1977

    But I tried check_mssql_health_1.5.1. The error disappeared, but I have two other.:

    root@nagiossrv:/usr/local/nagios/libexec# perl check_mssql_health –host xxx.xxx.xxx.xxx –port 1433 –username sa –password Password –mode connection-time

    Use of uninitialized value in concatenation (.) or string at check_mssql_health line 968. Use of uninitialized value in sprintf at check_mssql_health line 1212. OK – 0.03 seconds to connect as | connection_time=0.03;1;5

    I copy this two lines here. Can you help me?

    965 if (lc $self->{servicename} ne ‘mssqlserver’) { 966 # braucht man fuer abfragen von dm_os_performance_counters 967 # object_name ist entweder “SQLServer:Buffer Node” oder z.b. “MSSQL$OASH: Buffer Node” 968 $self->{servicename} = ‘MSSQL$’.$self->{servicename}; 969 } else { 970 $self->{servicename} = ‘SQLServer’; 971 }

    1210 $self->merge_nagios($self->{memorypool}); 1211 } elsif ($params{mode} =~ /^server::connectiontime/) { 1212 $self->add_nagios( 1213 $self->check_thresholds($self->{connection_time}, 1, 5), 1214 sprintf “%.2f seconds to connect as %s”, 1215 $self->{connection_time}, $self->{dbuser}); 1216 $self->add_perfdata(sprintf “connection_time=%.2f;%d;%d”,

    thank you

    Peter

38. bigpeti Says:
    June 18th, 2010 at 9:37

    Probably readable: . . Hi, With check_mssql_health_1.5.3 I have this error too: . root@nagiossrv:/usr/local/nagios/libexec# perl check_mssql_health –host xxx.xxx.xxx.xxx –port 1433 –username sa –password Password –mode connection-time . CRITICAL – cannot connect to xxx.xxx.xxx.xxx. DBI connect(‘;host=xxx.xxx.xxx.xxx;port=1433′,’sa’,…) failed: (no error string) at check_mssql_health line 1977 . But I tried check_mssql_health_1.5.1. The error disappeared, but I have two other: . root@nagiossrv:/usr/local/nagios/libexec# perl check_mssql_health –host xxx.xxx.xxx.xxx –port 1433 –username sa –password Password –mode connection-time . Use of uninitialized value in concatenation (.) or string at check_mssql_health line 968. Use of uninitialized value in sprintf at check_mssql_health line 1212. OK – 0.03 seconds to connect as | connection_time=0.03;1;5 . I copy this two lines here. Can you help me? . 965 if (lc $self->{servicename} ne ‘mssqlserver’) { 966 # braucht man fuer abfragen von dm_os_performance_counters 967 # object_name ist entweder “SQLServer:Buffer Node” oder z.b. “MSSQL$OASH: Buffer Node” 968 $self->{servicename} = ‘MSSQL$’.$self->{servicename}; 969 } else { 970 $self->{servicename} = ‘SQLServer’; 971 } . . . 1210 $self->merge_nagios($self->{memorypool}); 1211 } elsif ($params{mode} =~ /^server::connectiontime/) { 1212 $self->add_nagios( 1213 $self->check_thresholds($self->{connection_time}, 1, 5), 1214 sprintf “%.2f seconds to connect as %s”, 1215 $self->{connection_time}, $self->{dbuser}); 1216 $self->add_perfdata(sprintf “connection_time=%.2f;%d;%d”, . . . thank you . Peter

    

    lausser Reply:
    June 22nd, 2010 at 18:44

    I interpret the 1.5.1-errors as: connection was established, but neither “SELECT SYSTEM_USER” nor “SELECT @@SERVICENAME” returned a result. Can you please login as “sa” with a db command line client and try the two statements? What do you get as results? I have no explanation, why it fails earlier with 1.5.3, because the db-establish-connection-code did not change between the two releases. What you could try is to create an entry in the /etc/freetds.conf

    [TEST] port = 1433 tds version = 8.0 host = xxx.xxx.xxx.xxx

    and then use server instead of hostname and port. check_mssql_health –server TEST –username sa –password ….. If that doesn’t help, go to line 1977 (of the 1.5.3 version) and add the following line just before the one with “DBI->connect”

    DBI->trace(2);

    

    Mark Thomas Reply:
    April 6th, 2011 at 19:04

    @lausser,

    This last instuction worked for me! Well this is the last one I read for now anyway Thank you. Mark

39. bigpeti Says:
    June 18th, 2010 at 9:38

    OMG What an editor!

    

    lausser Reply:
    June 22nd, 2010 at 18:46

    So true :-)

40. theman Says:
    July 14th, 2010 at 12:17

    All works for me but it seems the result is wrong. I have all the parameters right. The database-free seems to be working the wrong way

    

    lausser Reply:
    July 14th, 2010 at 16:35

    Any more details? If you use your own thresholds, did you add a ‘:’ to them?

    .... --warning 10: --critical 5:

    meaning “warn if less than 10″…
41. Hector Roman Says:
    July 26th, 2010 at 3:09

    I have problems using the ubuntu check_mssql_healt 10.4 command.cfg my file is:

    ‘Check_nmssql_health’ command definition

    define command ( command_name check_mssql_health command_line $ USER1 $ / check_mssql_health-server $ HOSTNAME $-username-password nagios nagiosadmin – mode-s $ ARG1 $ 1,234,567 )

    service_nagios2.cfg file

    define service ( use generic-service host_name salvades-virt-winxp SQL Connection service_description time check_command check_mssql_health! connection-time )

    define service ( use generic-service host_name salvades-virt-winxp IO service_description Busy check_command check_mssql_health! io-busy )

    Nagios Error says:

    SQL Connection time UNKNOWN 7/25/2010 21:03:53 0d 3h 50m 20s 4 / 4 ** EPN / usr / lib / nagios / plugins / check_mssql_health: “Option s is ambiguous (scream, server, shell).”

    Busy IO UNKNOWN 7/25/2010 21:04:00 0d 3h 20m 9s 4 / 4 ** EPN / usr / lib / nagios / plugins / check_mssql_health: “Option s is ambiguous (scream, server, shell).”

    I have properly configured my DTS, I can connect from the console without any problem, if someone could indicate some solution would be ideal, greetings from Santiago de Chile

    

    lausser Reply:
    July 28th, 2010 at 9:32

    Either you have blanks in your command line arguments or you need to switch off the embedded perl interpreter

42. Hector Roman Says:
    July 28th, 2010 at 18:34

    yea!!!!! succefull!!!!!! thanks men

43. Nate Says:
    August 6th, 2010 at 23:48

    We are setting this up and are getting logon errors. I have verified user/password

    Server message number=18456 severity=14 state=1 line=1 server=xxx text=Login failed for user ‘sa’. OpenClient message: LAYER = (0) ORIGIN = (0) SEVERITY = (78) NUMBER = (46) Server , database Message String: Login incorrect. at ./check_mssql_health line 1977

    

    lausser Reply:
    August 8th, 2010 at 12:05

    This error comes from the DBD::Sybase perl module which handles the communitaction between database and plugin. In one of the first postings of this board you can find a minimal script for testing the connection.

44. Mariano Says:
    August 10th, 2010 at 23:51

    Hi, I was trying the “database-backup-age” mode, and found it was not recognized. Looking at the code the option seems to be “backup-age”. Maybe you could update the docs ?

    Thanks for the plugin!

    

    lausser Reply:
    August 11th, 2010 at 0:18

    did you use a current release? backup-age is an alias of database-backup-base. both should work.

    

    Mariano Reply:
    August 11th, 2010 at 16:37

    @lausser, Yes, I’m using 1.5.3 as provided by download link above. With database-backup-age (as stated on docs) I get:

    UNKNOWN – mode database-backup-age

    

    lausser Reply:
    August 11th, 2010 at 17:11

    Ok, now i see. In the the newest (not yet released) version backup-age is an alias for database-backup-age. I’ll release it asap (tomorrow or friday).

45. Daniel Says:
    August 11th, 2010 at 11:33

    Hello,

    there are some differences with your description on this site and the help screen of the plugin. Especially the mode “backup-age” is called on your site “database-backup-age”. You should check and upate the help screen and the description on this site, because it’s most important for the first steps.

    Anyway, thanks very much for the good work!

    

    lausser Reply:
    August 18th, 2010 at 14:16

    There’s a new version where the parameters should be ok.

46. querwin Says:
    August 17th, 2010 at 11:29

    Hello,

    I can’t succeed in using this plugin. I have this error :

    [root@helios libexec]# ./check_mssql_health –hostname CPTA-PROD-SQL –mode connection-time –username=SA –password=CPTAPROD CRITICAL – cannot connect to CPTA-PROD-SQL. DBI connect(‘;host=CPTA-PROD-SQL;port=1433′,’SA’,…) failed: OpenClient message: LAYER = (0) ORIGIN = (0) SEVERITY = (78) NUMBER = (41) Server , database Message String: Server is unavailable or does not exist. at ./check_mssql_health line 2089

    Do you have an idea of the reason of this error ?

    

    lausser Reply:
    August 18th, 2010 at 14:13

    Configure /etc/freetds.conf and try it with –server. Also try tsql.

    

    querwin Reply:
    August 18th, 2010 at 16:06

    @lausser, I’ve try it but I still have a problem to connect to the distant database.

    /etc/freetds.conf = [CPTAPROD] host = PORT-PROD-MY port = 1433 instance = CPTAPROD tds version = 4.2

    and :

    TDSVER=4.2 tsql -S CPTAPROD -U SA locale is “fr_FR.UTF-8″ locale charset is “UTF-8″ Password: There was a problem connecting to the server

    How can I know if the problem comes from the distant server which can probably refused my connection or from my local server which can have a bad configuration.

    

    lausser Reply:
    August 18th, 2010 at 16:56

    Try “tds version = 8.0″

47. Jacques Says:
    August 30th, 2010 at 22:27

    Hi,

    I’m still trying to debug my setup…

    Ubuntu 10.04 ; Nagios 3.20

    Check_mysql_health is working perfectly and I wish to have the same kind of monitoring for Ms SQL.

    I can connect with tsql :

    tsql -S IP.AD.DR.ES -p 1433 -U “domain\user” -P password locale is “en_CA.UTF-8″ locale charset is “UTF-8″ 1> exit

    I can also run the minitest :

    my $hostname = IP.AD.DR.ES; my $port = 1433; my $server = IP.AD.DR.ES;

    my $dsn = “DBI:Sybase:”;

    #

    Login data

    # my $username = ‘domain\user’; my $password = ‘password’;

    #

    #

    #

    Connection data

    #

    You have two alternatives:

    #

    1) If you defined a connection in /etc/freetds.conf

    $server = ‘server-name’;

    In /etc/freetds/freetds.conf

    [global] # TDS protocol version tds version = 8.0

    A typical Microsoft server

    [SERVER-NAME] host = IP.AD.DR.ES port = 1433 tds version = 8.0

    ./minitest connecting as user domain\user

    and it exits instantly right after that.

    When I try /usr/lib/nagios/plugins/check_mysql_health –hostname IP.AD.DR.ES –port 1433 –username “domain\user” –password “password” –mode uptime

    the command just time out. It times out for any mode.

    Any idea ?

    Thanks for your help,

    Jacques

    

    lausser Reply:
    September 10th, 2010 at 10:49

    So you configured an entry in freetds.conf? Then try please: check_mssql_health –server SERVER-NAME –username ‘domain\user’ –password pw –mode uptime

    

    Jacuqes Reply:
    September 10th, 2010 at 17:57

    @lausser,

    Thanks for the tip. With that syntax, I can run the tests and receive the results.

    Jacques

48. Jacques Says:
    August 30th, 2010 at 22:46

    Sorry for my typo… I tested with check_mYsql_health.

    Whith check_mssql_health, there is no time out, but the same error message :

    /usr/lib/nagios/plugins/check_mssql_health –hostname IP.AD.DR.ES –port 1433 –username “domain\user” –password “password” –mode io-busy CRITICAL – cannot connect to IP.AD.DR.ES. DBI connect(‘;host=IP.AD.DR.ES;port=1433′,’domain\user’,…) failed: (no error string) at /usr/lib/nagios/plugins/check_mssql_health line 2089

    Thanks for your help

    Jacques

49. Michael Says:
    September 3rd, 2010 at 10:52

    How to use the -encode ? I tried just a simple example check_mssql_health -mode encode SELECT 0

    but get errors

    Can’t open SELECT: Datei oder Verzeichnis nicht gefunden at /usr/local/nagios/libexec/check_mssql_health line 3345. Can’t open 0: Datei oder Verzeichnis nicht gefunden at /usr/local/nagios/libexec/check_mssql_health line 3345. Use of uninitialized value in scalar chomp at /usr/local/nagios/libexec/check_mssql_health line 3346. Use of uninitialized value in substitution (s///) at /usr/local/nagios/libexec/check_mssql_health line 3347. Use of uninitialized value in printf at /usr/local/nagios/libexec/check_mssql_health line 3348.

    thanks

    

    lausser Reply:
    September 10th, 2010 at 10:51

    echo "your sql statement" | check_mssql_health --mode encode

50. Installer 3 plugins Nagios dans EON 1.2, coupé d’Internet, level 1 – Ma vie parmi les lapins nains et les canaux wifi saturés Says:
    September 3rd, 2010 at 15:04

    [...] check_mssql_health (sonde de vérifications diverses pour les bases de données Microsoft SQL Server 2000 ou mieux) [...]

51. Michael Says:
    September 3rd, 2010 at 15:42

    ok ifind it myself..

    i use

    echo ‘SELECT 0′ | ./check_mssql_health –mode encode

    SELECT%200

    and it works…

52. Installer 3 plugins Nagios dans EON 1.2, coupé d’Internet, level 3 – Ma vie parmi les lapins nains et les canaux wifi saturés Says:
    September 15th, 2010 at 10:47

    [...] le dernier plugin, check_mssql_health, j’ai vraiment du faire face à de gros ennuis. Ce script Perl, très bien fait une fois [...]

53. Zwindler Says:
    September 15th, 2010 at 11:01

    Sorry for the automatic comments from my blog (you might want to delete them), I was just posting some review about this plugin and it seems that it just dropped some unwanted comments here. Don’t know why, and i’m sorry.

    By the way, this plugin works wonderfully for me, even though I use it for very basic checks…

    

    lausser Reply:
    September 15th, 2010 at 11:05

    No problem, i have to thank you for the review (i just read it).

54. Jonas Says:
    September 16th, 2010 at 11:09

    Cheers Lausser!! Thx for great tool. I´ve use most of the features and its perfect for our needs. I have a small problem with the Mode “database-backup-age” It works find but includes tempdb witch report “never been backuped” of course. Is there any easy way to exclude some db´s in the script. Thanks in advance & Regards // Jonas

    

    lausser Reply:
    September 16th, 2010 at 12:46

    You can use the parameter –name to select a specific database. With the parameter –regexp the value of –name is interpreted as a regular expression. This can also be used to exclude some databases.

    --name=’^(?!(tempdb))’ --regexp

    means: match every database, except tempdb.

    If you want to exsclude more than one database:

    --name=’^(?!(tempdb|anotherdb|andanotherdb))’ --regexp

55. Jonas Says:
    September 17th, 2010 at 15:02

    Splended! Works fine. Thanks

56. Wel Says:
    October 1st, 2010 at 14:45

    Hi Lausser, i notice database-free is not working well, i think cause it gives me the wrong values. it always gives me a 100% free space left where in fact. How do i go about this?

    

    lausser Reply:
    October 1st, 2010 at 19:52

    create a file /tmp/check_mssql_health.trace and run the plugin again. Then send me the file.

57. Wel Says:
    October 2nd, 2010 at 1:32

    Hi Thanks, for you quick reply. This is the trace file you requested. I keep getting 100% free space.

    Sat Oct 2 07:16:17 2010: SQL: SELECT @@VERSION ARGS: $VAR1 = [];

    Sat Oct 2 07:16:17 2010: RESULT: $VAR1 = [ 'Microsoft SQL Server 2005 - 9.00.3068.00 (X64) Feb 26 2008 23:02:54 Copyright (c) 1988-2005 Microsoft Corporation Enterprise Edition (64-bit) on Windows NT 5.2 (Build 3790: Service Pack 2) ' ];

    Sat Oct 2 07:16:17 2010: SQL: SELECT SYSTEM_USER ARGS: $VAR1 = [];

    Sat Oct 2 07:16:17 2010: RESULT: $VAR1 = [ 'Admin' ];

    Sat Oct 2 07:16:17 2010: SQL: SELECT @@SERVICENAME ARGS: $VAR1 = [];

    Sat Oct 2 07:16:17 2010: RESULT: $VAR1 = [ 'MSSQLSERVER' ];

    Sat Oct 2 07:16:17 2010: SQL:

          SELECT name, database_id FROM master.sys.databases
    

    ARGS: $VAR1 = [];

    Sat Oct 2 07:16:17 2010: RESULT: $VAR1 = [ [ 'master', 1 ], [ 'tempdb', 2 ], [ 'model', 3 ], [ 'msdb', 4 ], [ 'xxx', 5 ], [ 'dbstats', 6 ] ];

    Sat Oct 2 07:16:17 2010: SQL:

      SELECT * FROM #FreeSpace
    

    ARGS: $VAR1 = [];

    Sat Oct 2 07:16:17 2010: RESULT: $VAR1 = [];

    Sat Oct 2 07:16:17 2010: SQL:

          SELECT 
              SUM(CAST(used AS BIGINT)) / 128
          FROM 
              [xxx].sys.sysindexes
          WHERE
              indid IN (0,1,255)
    

    ARGS: $VAR1 = [];

    Sat Oct 2 07:16:17 2010: RESULT: $VAR1 = [ '0' ];

    Sat Oct 2 07:16:18 2010: SQL:

          SELECT
              RTRIM(a.name), RTRIM(a.filename), CAST(a.size AS BIGINT),
              CAST(a.maxsize AS BIGINT), a.growth
          FROM
              [xxx].sys.sysfiles a
          JOIN
              [xxx].sys.sysfilegroups b
          ON
              a.groupid = b.groupid
    

    ARGS: $VAR1 = [];

    Sat Oct 2 07:16:18 2010: RESULT: $VAR1 = [ [ 'Data', 'F:\\Data\\xxx.mdf', '189312000', '-1', 0 ], [ 'data2', 'E:\\SQLSERVER\\DATA\\data2.ndf', '22478848', '32000000', 131072 ], [ 'data3', 'F:\\Data\\data3.ndf', '1982464', '-1', 16384 ], [ 'data4', 'F:\\Data\\data4.ndf', '1949696', '-1', 16384 ], [ 'casino_data5', 'F:\\Data\\data5.ndf', '1966080', '-1', 16384 ], [ 'data6', 'F:\\Data\\data6.ndf', '1982464', '-1', 16384 ], [ 'data7', 'E:\\SQLSERVER\\DATA\\data7.ndf', '131072', '-1', 131072 ] ];

    Sat Oct 2 07:16:18 2010: DESTROY DBD::MSSQL::Server::Database with handle null nu ll Sat Oct 2 07:16:18 2010: DESTROY DBD::MSSQL::Server::Database exit with handle nu ll null Sat Oct 2 07:16:18 2010: DESTROY DBD::MSSQL::Server with handle DBD::MSSQL::Serve r::Connection::Dbi DBI::db Sat Oct 2 07:16:18 2010: DESTROY DBD::MSSQL::Server exit with handle DBD::MSSQL:: Server::Connection::Dbi DBI::db Sat Oct 2 07:16:18 2010: disconnecting DBD with handle

    

    lausser Reply:
    October 2nd, 2010 at 13:55

    It’s ‘SELECT * FROM #FreeSpace’ which shows an empty result. This statement should return the free space of your filesystems (C:, D:,…). Please login manually to your database and try the folowing:

    IF object_id('tempdb..#FreeSpace') IS NULL CREATE TABLE #FreeSpace( Drive VARCHAR(10), MB_Free BIGINT )   DELETE FROM tempdb..#FreeSpace INSERT INTO tempdb..#FreeSpace EXEC master.dbo.xp_fixeddrives   SELECT * FROM tempdb..#FreeSpace

    The last statement should return some results. Maybe you have not the right privileges to execute master.dbo.xp_fixeddrives.

    At my testmachine it looks like:

    [nagios@nagsrv1 ~]$ tsql -H dbsrv10 -p 1433 -U nagios -P nagnag locale is "en_US.UTF-8" locale charset is "UTF-8" 1> exec master.dbo.xp_fixeddrives 2> go drive MB free C 121547 (return status = 0)

    

    Wel Reply:
    October 3rd, 2010 at 4:58

    @lausser, i have not yet issued those to our database. Will i be able to know the free space of my xxx database if im gonna do that?

    

    lausser Reply:
    October 3rd, 2010 at 11:26

    This will only show the free space on your filesystems. From the tracefile i saw, that this statemens returns an empty result. Issue this statement manually just to confirm that you have the necessary privileges.

    

    Wel Reply:
    October 4th, 2010 at 12:32

    @lausser, this is what my result using tsql

    drive MB free (return status = 0)

    I may not have the privileges, what should be the necessary privileges for my user in order for me to get the exact database free space?

58. Shalini Says:
    October 4th, 2010 at 16:01

    Hi, Can I use this plugin to monitor blocked processes, active transactions, logins per second, logouts per second,long running process count, long running process duration ? If yes, please let me know .

    

    lausser Reply:
    October 4th, 2010 at 16:45

    If these requirements are listed in the manual, you can monitor them. If not, there is a subdirectory contrib in the source-package, where you can find instructions how to add your own features to the plugin.

59. Alex Says:
    October 5th, 2010 at 9:39

    Does check_mssql_health work ok with the current generation of Sequel 2008 (web, standard, express, workgroup)?

60. Robson Says:
    October 24th, 2010 at 17:20

    Hello, My client need to chech if scheduler jobs of mysql whas running fine, and if they dont run I send an alert. Whith this plugin there is a way to verify jobs of mysql? thanks.

    

    lausser Reply:
    October 24th, 2010 at 17:26

    look into the contrib directory. you will find instructions how you can extend check_mssql_health to match custom requirements.

61. Andrew Bereszczak-Adams Says:
    October 25th, 2010 at 9:23

    Hi,

    In MS SS BOL under ‘Dynamic Management Views and Functions’ it says, “To query a dynamic management view or function requires SELECT permission on object and VIEW SERVER STATE or VIEW DATABASE STATE permission. This lets you selectively restrict access of a user or login to dynamic management views and functions. To do this, first create the user in master and then deny the user SELECT permission on the dynamic management views or functions that you do not want them to access. After this, the user cannot select from these dynamic management views or functions, regardless of database context of the user.”

    Do you have a list of dynamic views and functions used by each Nagios check? I would like to grant minimal permissions to my Nagios account based on the Nagios checks that I choose to use.

    Apologies if I have missed something, but I’ve read through the posts and not found the answer. Any help would be very much appreciated.

    Regards, Andrew

    

    lausser Reply:
    October 26th, 2010 at 15:15

    Unfortunately there is no such list. What you can do is - create a file /tmp/check_mssql_health.trace - run the plugin with different modes You will see some debugging-output in the trace-file, amongst it the used sql-statements. In fact, it should be only a small amount of different statements, only with varying parameters for the where-clauses. I hope this will help.

62. Hector Roman Says:
    November 2nd, 2010 at 15:28

    What is the Definitions for changing the default thresholds? EJ:

    Definition for online users

    define service{ use generic-service hostgroup_name sql_server service_description SQL Connected-Users check_command check_mssql_health!connected-users;150;200 }

    

    Hector Roman Reply:
    November 2nd, 2010 at 15:30

    @Hector Roman,

    sorry

    Default service

    check_command check_mssql_health!connected-users

    I Need

    check_command check_mssql_health!connected-users!150!200

    I tried to change the thresholds but without good result

    

    lausser Reply:
    November 2nd, 2010 at 15:34

    It depends on your command-definition for check_mssql_health. You need to add –warning $ARG2$ –critical $ARG3$

63. Hector Roman Says:
    November 2nd, 2010 at 15:47

    Gracias funciona correcto :)

64. linkme Says:
    November 10th, 2010 at 2:03

    A problem with arithmetic with large DBs? Ours is over 150G.

    ./check_mssql_health -server db2 -username=xxx -password=xxx –mode database-free –name linkme CRITICAL – database LinkMe has -3820.41% free space left | ‘db_linkme_free_pct’=-3820.41%;5:;2: ‘db_linkme_free’=-156484MB;204.80:;81.92:;0;4096.00 ‘db_linkme_allocated_pct’=4882.81%

    

    lausser Reply:
    November 24th, 2010 at 12:37

    Please create a trace-file with touch /tmp/check_mssql_health.trace, run the plugin again and mail me the file.

65. tcpdump Says:
    November 16th, 2010 at 19:09

    Hallo Gerhard,

    maximus respect erstmal fuer Deine Arbeit. Extrem fettes Plugin :-)

    Ich hatte allerdings so meine Probleme mit dem DBD::Sybase Modul. Mein Nagios laeuft auf Ubuntu 10.04 und lazy wie immer hab ich das Modul direkt aus den Reps installiert. Allerdings ist das nur 1.00 und damit funktionierts leider nicht … Geloest hab ichs wie folgt:

    nagsrv:~#SYBASE=/usr nagsrv:~#export SYBASE nagsrv:~#perl -MCPAN -e shell cpan[1]>_ force install DBD::Sybase ... returned OK

    Ich weiss, ist nicht ganz sauber so… Aber vielleicht hilfts ja jemand.

    An der Stelle noch mal 1000 Dank fuer die gute Arbeit.

    Gruesse tcpdump

66. Thomas Michael Engelke Says:
    November 18th, 2010 at 10:33

    Guten Morgen,

    es gibt definitiv ein Problem mit der Verarbeitung bei Angabe von Host und Port. Ich hatte dieselben Probleme wie viele Personen über mir (fehlgeschlagen mit “failed: (no error string)”). Ich habe jetzt den Server in der FreeTDS-Konfiguration hinterlegt und siehe da: Sofort geht’s. Ich bin erleichtert, dass es klappt, aber frustriert, dass es mich 2 Tage gekostet hat.

    In jedem Falle danke für das Plugin!

    

    Thomas Michael Engelke Reply:
    November 18th, 2010 at 17:05

    @Thomas Michael Engelke, Um es mal zu probieren, habe ich die oben angegebene Datei getoucht:

    touch /tmp/check_mssql_health.trace

    und diese während eines Neustarts von Nagios auf Meldungen überwacht. Bei einem neuen Start von Nagios bekomme ich nur folgende Meldung in der Datei:

    Thu Nov 18 16:02:09 2010: disconnecting DBD without handle

    Im Programm kommt es in jedem Falle zur Meldung

    CRITICAL – cannot connect to . DBI connect(‘:server=‘,’**’,…) failed: OpenClient message: LAYER = (0) ORIGIN = (0) SEVERITY = (78) NUMBER = (41) Message String: Unable to connect: Adaptive Server is unavailable or does not exist at /usr/lib/nagios/plugins/check_mssql_health line 2121

    Das Ganze funktioniert auf der Kommandozeile perfekt, aber eben aus Nagios heraus nicht. Gibt’s Ideen?

    

    Thomas Michael Engelke Reply:
    November 18th, 2010 at 17:07

    @Thomas Michael Engelke, Bäh, verschluckt Zeichen. In der oben angegebenen Meldung ist an beiden Stellen ein Servername zu sehen, ich habe ihn nur durch zwei Asteriske ersetzt, was anscheinend als bold interpretiert wird.

67. Hector Roman Says:
    December 18th, 2010 at 17:50

    I installed the plugin on multiple servers and it works fine, but now I am raising a new nagios and I have the following error message in some metric of plugins:

    metric error SQL batch-requests, SQL checkpoint-pages, SQL full-scans, etc. ** ePN / usr / lib / nagios / plugins / check_mssql_health: “printf () on closed filehandle STATE at (eval 20) line 1775

    There are other metrics that are working properly SQL latches-wait-time CRITICAL 18/12/2010 12:30:21 0d 0h 19m 35s 4 / 4 CRITICAL – latches Have to wait 5.34 ms avg

    locate the line that calls and only find a purpose “{” Ubuntu Server 10.4 Nagios 3.2.0 Any ideas?

    

    lausser Reply:
    December 18th, 2010 at 17:53

    /var/tmp/check_mssql_health cannot be created and written by the nagios user. Probably because the root user is the owner of this directory. Did you execute check_mssql_health as root and can you explain, why?

    

    Hector Roman Reply:
    December 21st, 2010 at 1:43

    @lausser, drwxr-xr-x 2 root root 4096 2010-12-18 12:09 check_mssql_health chown nagios:nagios check_mssql_health —> Problem resolved

    this was a clean install not know why the error, I had not previously presented, it is noteworthy that all services were up correctly with the exception of the CPU-IO-busy still throws the same error, thanks for your help teacher

68. Morten B Says:
    December 22nd, 2010 at 11:43

    Hi

    Using mode locks-timeout and other locks mode, will give alot of performance data, but Nagios seems to not like, example:

    OK – 0.0000 deadlocks / sec for _Total, 0.0000 deadlocks / sec for RID, 0.0000 deadlocks / sec for Page, 0.0000 deadlocks / sec for Object, 0.0000 deadlocks / sec for Metadata, 0.0000 deadlocks / sec for Key, 0.0000 deadlocks / sec for HoBT, 0.0000 deadlocks / sec for File, 0.0000 deadlocks / sec for Extent, 0.0000 deadlocks / sec for Database, 0.0000 deadlocks / sec for Application, 0.0000 deadlocks / sec for AllocUnit | _Total_deadlocks_per_sec=0.0000;1;5 RID_deadlocks_per_sec=0.0000;1;5 Page_deadlocks_per_sec=0.0000;1;5 Object_deadlocks_per_sec=0.0000;1;5 Metadata_deadlocks_per_sec=0.0000;1;5 Key_deadlocks_per_sec=0.0000;1;5 HoBT_deadlocks_per_sec=0.0000;1;5 File_deadlocks_per_sec=0.0000;1;5 Extent_deadlocks_per_sec=0.0000;1;5 Database_deadlocks_per_sec=0.0000;1;5 Application_deadlocks_per_sec=0.0000;1;5 AllocUnit_deadlocks_per_sec=0.0000;1;5

    Nagios says: **ePN /usr/lib64/nagios/plugins/check_mssql_health: “printf() on closed filehandle STATE at (eval 37) line 1647,”.

    Any suggestions are welcome, using version 1.5.8

    

    lausser Reply:
    December 22nd, 2010 at 12:08

    check_mssql_health needs to write to /var/tmp/check_mssql_health. Obviously this directory belongs to root. Did you run the plugin as root and can you explain why?

69. Morten B Says:
    December 28th, 2010 at 12:41

    Thank you…. My mistake, tested the plugin from shell before I configured it in Nagios.

    Morten

70. Paul Says:
    December 29th, 2010 at 9:41

    Hello! Sorry I’m kinda new Sybase and Nagios.

    I’m trying to connect to my server when this occurred:

    CRITICAL – cannot connect to HS01. install_driver(Sybase) failed: Can’t load ‘/usr/lib/perl5/site_perl/5.8.8/i386-linux-thread-multi/auto/DBD/Sybase/Sybase.so’ for module DBD::Sybase: libct.so.4: cannot open shared object file: No such file or directory at /usr/lib/perl5/5.8.8/i386-linux-thread-multi/DynaLoader.pm line 230. at (eval 14) line 3 Compilation failed in require at (eval 14) line 3. Perhaps a required shared library or dll isn’t installed where expected at ./check_mssql_health line 2121

    Am I missing something here?

    Thanks for the assistance!

    Best Regards,

71. Thierry Says:
    January 11th, 2011 at 14:35

    Hi,

    thank you for this very good plugin.

    I have a little problem with mode=sql and name2 parameter. When i try without name2, it works:

    ./check_mssql_health –hostname sqlserv –username nagios –password 12345 –mode sql –name select%20count%28%2A%29%20as%20Nb%20from%20master%2Esys%2Edatabases%20where%20state%20in%20%284%2C5%29 –warning 5 –critical 10 OK – select count(*) as nb from master.sys.databases where state in (4,5): 0 | ‘select’=0;5;10

    When i add name2, i have the following error: ./check_mssql_health –hostname sqlserv –username nagios –password 12345 –mode sql –name2 DbStatus –name select%20count%28%2A%29%20as%20Nb%20from%20master%2Esys%2Edatabases%20where%20state%20in%20%284%2C5%29 –warning 5 –critical 10 CRITICAL – output 0 not found

    Any idea ?

    Thanks for your assistance

    

    lausser Reply:
    January 11th, 2011 at 18:22

    I forgot to upload the bugfix version 1.5.8.1 Please discard the 1.5.8 and get the new one.

    

    lausser Reply:
    January 13th, 2011 at 18:27

    Has been fixed with the latest release.

72. Thibault Says:
    January 18th, 2011 at 17:56

    Hi, Your plugin is very good !

    But i have a probleme, when I do an SQL query , it work But when i want to test when the db is disconnected ( just a part of it) the plugin check the dbd that is normal, but it can’t find the good answer and write this :

    [root@localhost plugins]# ./check_mssql_health –hostname 10.0.0.1 –username ** –password ** –port 4956 –mode sql –name “SELECT [*] FROM []where []=’A'” –critical 52: -name2 REQUETESQL DBD::Sybase::st execute failed: Server message number=208 severity=16 state=1 line=1 server=\ text=Invalid object name ‘*’. OK – requetesql:

    That write Ok , but thats not true because the dbd is disconnected !

    Can you help me ?

    Thank you !

    

    lausser Reply:
    January 19th, 2011 at 20:02

    That’s strange. But there was a release 1.5.8 which had some problems in the mode sql. Can you try the the newest release?

73. Steadman Botha Says:
    January 19th, 2011 at 8:38

    Hi,

    Firstly, I’d like to say thanks for the plugin. -Its been very helpful in the short period that we have been using it.

    I’ve been able to get it working for most of the modes you have listed. One exception is the ‘batch-requests’ mode on one our servers. When I run the check_mssql_health command with –mode=batch-requests, I get the following error message:

    error_handler: Data-conversion resulted in overflow. UNKNOWN – unable to aquire counter data

    After googling around, it seems there is some sort of issue with freetds handling large numbers, but I can’t seem to find a solution. Do you have any suggestions?

    Thanks in advance Steadman

    

    lausser Reply:
    January 19th, 2011 at 20:05

    Sorry, i have no idea. You can create a tracefile with “touch /tmp/check_mssql_health.trace”, where check_mssql_health will write all the SQL-requests and the corresponding answers from the db. With the sqsh or tsql cmd line client you can probably find a short statement which shows this incorrect result. You may forward the error description to the freetds guys.

    

    Steadman Botha Reply:
    January 21st, 2011 at 2:00

    @lausser,

    Thanks for the tip. Using the output in the check_mssql_health.trace file, I was able to run the a tsql command. It turns out that freetds seems to be working. We ran the following command: SELECT cntr_value FROM sys.dm_os_performance_counters WHERE counter_name = ‘Batch requests/sec’ AND object_name = ‘SQLServer:SQL Statistics’

    The cntr_value was very large number, 1912924919. The DBA here tells me this machine gets hit quite hard. Other servers I looked at came back with only around 30 million. I’m thinking that since we did get a number, that maybe the issue is not with freetds after all.

    Steadman

    

    lausser Reply:
    January 21st, 2011 at 11:29

    In your freetds.cfg there should be an entry like dump file = /tmp/freetds.log

    Uncomment it and watch the debug file. What you also should do is to take the miniscript from and add your statement after the line with printf connected.

    my $sql = q{ SELECT cnrt_value.... and so on, but without the trailing semicilon }; DBI->trace(9); my $sth = $dbh->prepare($sql); my @result = $sth->execute(); use Data::Dumper; printf "result is %s\n", Data::Dumper::Dumper(\@result); $dbh->disconnect();

    If the number in the result is not the number you get with tsql, there’s a problem in DBD::Sybase. Now you have logging of freetds and (through DBI->trace) one level above logging of the DBD module. Let’s see what happens.

74. tae Says:
    January 30th, 2011 at 1:34

    ./check_mssql_health –hostname 184.106.77.67 –username nagios –password linux –mode cpu-busy

    In this commands, you give username and password to access the DB. What kind of access do they need on the DB?

    I’m getting this:

    [nagios@nagiospr01 libexec]$ ./check_mssql_health –hostname 172.17.40.41 –username nagios –password Nagios000 –mode io-busy CRITICAL – cannot connect to 172.17.40.41. DBI connect(‘;host=172.17.40.41;port=1433′,’nagios’,…) failed: Server message number=18456 severity=14 state=1 line=1 server=MS-DB-1 text=Login failed for user ‘nagios’. OpenClient message: LAYER = (0) ORIGIN = (0) SEVERITY = (78) NUMBER = (46) Server , database Message String: Login incorrect. at ./check_mssql_health line 2175

    

    lausser Reply:
    January 30th, 2011 at 3:46

    The preferred solution for this problem is to read the manual. Sorry for being rude, but you have to do your homework yourself.

    

    Glen Reply:
    February 15th, 2011 at 22:41

    @lausser,

    Receiving the same problem and would love to read the manual, but I don’t see where one is listed. I have read the INSTALL and README file included with the script but not seeing information regarding above error.

    I have used the above SQL script to apply the local windows user the correct permissions to the databases so I am not sure what I am missing.

    Also thank you for the script.

75. Gennadi Says:
    February 1st, 2011 at 4:55

    Hi Lausser,

    I am running check_mssql_health! –username=nagios –password=xxxx –port=1433 –database=DB –mode=cpu-busy –warning=80 –critical=90

    However the results I am getting far from the CPU utilization of the server. I am actually getting between 80~90% so often get critical notification, though machine CPU is fine below 20% I tried to find some documentation or explanation of what exactly mode cpu-busy and io-busy suppose to show, but could not find any, Could you be so kind just give me the short explanation of what I am looking at and why is different from the machine’s CPU utilization

    Thanks a lot, Gennadi

    

    lausser Reply:
    February 1st, 2011 at 12:37

    Well, it’s open source, so why not opening the plugin in en editor and do a search for CPU? Another possibility:

    touch /tmp/check_mssql_health.trace tail -f /tmp/check_mssql_health.trace

    This will show you the sql-statements when you run the plugin with –mode cpu-busy again.
76. Marian-Marcu Suchanek Says:
    February 3rd, 2011 at 18:35

    Hallo,

    wenn ich: “check_oracle_health –connect=rea –user=nagios –password=”nagios” –mode=tablespace-can-allocate-next” abfrage, erhalte ich den Error: “check_oracle_health timed out after 60 seconds”. Meine DB bräuchte mehr Zeit zum antworten, gibt es dafür eine Option?

    Vielen Dank im voraus!

    

    lausser Reply:
    February 9th, 2011 at 22:51

    –timeout oder/und die entspr. Optionen in der nagios.cfg

77. bess Says:
    February 11th, 2011 at 22:16

    Hi, The plugin works fine but there are some prerequisites. You can find the list of components to install on : http://www.xoowiki.com/Article/Shell/nagioscentreon-check_mssql_health-498.aspx

78. Ronald Prague Says:
    February 12th, 2011 at 9:42

    Could you post your command definitions for this? I’m able to run all the commands by hand just fine (with the exception of cpu-busy and io-busy).

    However, running them from inside nagios, gives me the following error: **ePN /usr/lib/nagios/plugins/check_mssql_health: “Use of uninitialized value $opt in string eq at /usr/share/perl/5.10/Getopt/Long.pm line 487,”.

    When I run cpu-busy or io-busy with the following command: ./check_mssql_health –server wonkab –username=nagiossqlusers –password=nagiospassword -mode cpu-busy

    I get the following output: =sp_monitortext=The procedure ‘sp_monitor’ cannot be executed within a transaction. at ./check_mssql_health line 2282.

    Any thoughts?

79. Mark Monaghan Says:
    February 24th, 2011 at 12:35

    Hi, I’m running v1.5.8.2 of the plugin, and v0.82 of freetds. I’ve installed the latest version of DBD::Sybase as well from the CPAN repository. I’m running v3.1.2 of Nagios, with the embedded perl interpreter installed, however, I’ve also made sure that check_mssql_health doesn’t use it by placing the -epn switch in at the start of the command script.

    I have a couple of problems with using the script in Nagios. The first problem is with certain database names. We have certain third party databases installed on our SQL servers with characters like this: data$base. I can get around this on the command line by placing the database name in single quotes (e.g. –name ‘data$base’) and it reads the database correctly.

    However, I’ve tried everything to get this to work in Nagios, including putting the database names (with and without quotes) in a variable, then running the service check against that variable, all without success. I know there has been a bug fix in a previous version to support these characters, but how to do I translate this into a nagios command?

    My second problem is that some databases are returning either an “UNKNOWN – unable to aquire counter data” or an “CRITICAL – unable to aquire database info” when running –mode transactions or –mode database-free queries. There’s no discernable pattern to why some databases work, and others show these errors. It’s nothing as simple as all databases running on the same server, or instance, exhibit these problems, as databases showing errors are running beside ones that are reporting correctly. When I run these commands from the command line under root, I get the correct information returned with no problems. Running under the nagios user, I get the same correct responses, but I also get the following error when running –mode transactions, however it still returns the correct data:

    printf() on closed filehandle STATE at ./check_mssql_health line 1826.

    Can anyone please help me? For 99% of the databases I’m monitoring, everything’s working as intended. I’m just not sure why some work, and some don’t.

    Thanks very much in advance for any help or pointers.

    

    lausser Reply:
    February 24th, 2011 at 12:43

    problem1: i don’t know. That’s a matter of placing the right amount of quotes in the command definitions so it ends up in the shell either as ‘data$base’ or data\$base.

    problem2: unable to aquire…. looks like a permission problem. Create the file /tmp/check_mssql_health.trace and look at the statements appearing here when you run the plugin. (delete fhe file afterwards, as it will grow as long as it exists). Run these sql-statements in a separate database session.

    problem3: printf on closes filehandle. I bet you ran the plugin as root once. Don’t you ever run plugins as root anymore! Run them as the nagios user. You created a directory /var/tmp/check_mssql_health which is written and read by the plugin (it stores information needed for the next run, for example to be able to calculate the delta of ever-rising counters in the database). Now when you run the plugin as nagios, you don’t have permissions on files in this directory. Make a ‘chown -R nagios:nagios /var/tmp/check_mssql_health’.

80. Mark Monaghan Says:
    February 24th, 2011 at 13:45

    Thanks for the amazingly q

81. Mark Monaghan Says:
    February 24th, 2011 at 13:48

    sigh. Keyboards. You’ve got to love them. :) Anyway….

    Thanks for the amazingly quick reply!

    Problem 1: I’ll continue to bash away at it. I didn’t think of using the \$ as I got it working on the command line using the quotes, so my thinking got locked into that path.

    Problem 2: I’ll look into creating the trace file and working my way through it.

    Problem 3: More than likely spot on in your assessment. I’ve made the required changes, and the problem has disappeared.

    Thanks once again! :)

82. nms Says:
    February 28th, 2011 at 12:43

    Hi,

    just one question regarding mode “database-backup-age”. Is there a chance to check specific databases for their backup-age and not “all” databases? To check only one you can give “-name “, but I couldn’t find any possibility to check like 2 or 3 databases.

    Thanks in advance,

    nms

    

    nms Reply:
    February 28th, 2011 at 13:22

    @nms, nevermind, I just looked more into the comments and found that regex-solution ;)

83. iamxto Says:
    March 2nd, 2011 at 18:39

    Hi

    First of all, thanks for your plugin.

    I have found a problem and I cannot go further. Could you help me?

    Im doing this test with the –mode=deadlock and I am getting this error:

    The result in nagios is (Return code of 127 is out of bounds – plugin may be missing)

    If I use database-free, cpu-busy, io-busy it works perfectly.

    If I launch this check in a shell i get this result:

    check_mssql_health –hostname SERVER –username .\\sqlnagioscheck –password test –port 2618 –mode=locks-waits OK – 0.0000 lock waits / sec for _Total, 0.0000 lock waits / sec for RID, 0.0000 lock waits / sec for Page, 0.0000 lock waits / sec for Object, 0.0000 lock waits / sec for Metadata, 0.0000 lock waits / sec for Key, 0.0000 lock waits / sec for HoBT, 0.0000 lock waits / sec for File, 0.0000 lock waits / sec for Extent, 0.0000 lock waits / sec for Database, 0.0000 lock waits / sec for Application, 0.0000 lock waits / sec for AllocUnit | _Total_lock_waits_per_sec=0.0000;100;500 RID_lock_waits_per_sec=0.0000;100;500 Page_lock_waits_per_sec=0.0000;100;500 Object_lock_waits_per_sec=0.0000;100;500 Metadata_lock_waits_per_sec=0.0000;100;500 Key_lock_waits_per_sec=0.0000;100;500 HoBT_lock_waits_per_sec=0.0000;100;500 File_lock_waits_per_sec=0.0000;100;500 Extent_lock_waits_per_sec=0.0000;100;500 Database_lock_waits_per_sec=0.0000;100;500 Application_lock_waits_per_sec=0.0000;100;500 AllocUnit_lock_waits_per_sec=0.0000;100;500

    Any idea? Regards.

84. Dan Says:
    March 6th, 2011 at 3:10

    Using the latest version I am attempting to pull a count from SQL2005

    ./check_mssql_health –hostname=ipAddress –username=username –password=password –name DBNAME –name2 OrderNumber –mode sql “SELECT count(*) FROM Orders WHERE OrderDate >= ’3/5/2011 1:00:00 PM’ AND OrderDate < ’3/5/2011 1:01:00 PM’”

    expected result should show a value of: 0

    Error Message: DBD::Sybase::st execute failed: Server message number=2812 severity=16 state=62 line=1 server=ServerName text=Could not find stored procedure ‘DNAME’. at ./check_mssql_health line 2212. Use of uninitialized value $value in numeric gt (>) at ./check_mssql_health line 1510. Use of uninitialized value $value in numeric gt (>) at ./check_mssql_health line 1511. OK – ordernumber:

    using check_mssql works (but it’s options are limiting) from: http://exchange.nagios.org/directory/Plugins/Databases/SQLServer/check_mssql/details

    Thanks. Dan

    

    Dan Reply:
    March 6th, 2011 at 4:43

    @Dan,

    Solved this myself after reading almost all above comments.

    ./check_mssql_health –hostname=ipAddress –username=username –password=password –mode sql –name “SELECT count(*) FROM DBNAME.dbo.Orders WHERE OrderDate >= ’3/5/2011 1:00:00 PM’ AND OrderDate < ’3/5/2011 2:01:00 PM’” –name2=ordernumber

    OK – ordernumber: 0 | ‘ordernumber’=0;1;5

    Whew. Awesome tool! Dan

    

    Dan Reply:
    March 6th, 2011 at 8:05

    @Dan,

    In case any one wants to rest of this here it is:

    I call a bash script as the nagios command.

    This script has all the funky date stuff because the datetime stamp used with this particular SQL2005 DB is not in (what I would say), the proper format. But, it is what it is so I had to deal with it. It does NOT deal with date time ranges that span one day to the day before, nor the month before: we will only be running this script during daylight hours anyway.

    What this does is let us know if our website shopping cart has had at least 1 order in the last hour. Which should be a clear indication that our shopping cart is properly functional.

    !/bin/bash

    month=$(echo $(date +%_m)) day=$(echo $(date +%_d)) year=$(echo $(date +%_Y)) hour=$(date +%l) minute=$(date +%M) second=$(date +%S) ampm=$(date +%p) timestamp=$(echo $hour:$minute:$second) lasthour=$(echo “$hour – 1″|bc)

    if [ $lasthour = 12 ]; then if [ $ampm = PM ]; then lastampm=AM else lastampm=PM fi else lastampm=$ampm fi

    lasttimestamp=$(echo $lasthour:$minute:$second)

    timeNow=”$month/$day/$year $timestamp $ampm” timeLast=”$month/$day/$year $lasttimestamp $lastampm”

    /usr/lib64/nagios/plugins/check_mssql_health –hostname=ipAddress –username=username –password=password –name2=hourlyorders –warning=1: –critical=1: –mode sql –name “SELECT count(*) FROM DBNAME.dbo.Orders WHERE OrderDate >= ‘$timeLast’ AND OrderDate < ‘$timeNow’”

85. Alex Funk Says:
    March 10th, 2011 at 21:55

    Thanks for the awesome plugin. It works like a charm. I had to call perl manually (Like Tapan suggested), but I’ll chalk that up to CentOS weirdness.

    Keep up the good work!

86. Mirza Dedic Says:
    March 12th, 2011 at 0:20

    Hi,

    I am trying to execute the following check to exclude some databases in the “database-backup-age” mode..

    Do I need to set:

    use_regexp_matching=1 ?

    I also have:

    illegal_object_name_chars=~!$%^&*|'"<>?,()= illegal_macro_output_chars=~$&|’”<>

    define service{ use generic-service host_name van-sql02 service_description Elapsed time in hours since a database was last backupped check_command check_van-sql02_backup!database-backup-age!24!48 process_perf_data 1 } define command{ command_name check_van-sql02_backup command_line $USER1$/check_mssql_health -t 10 –hostname 172.16.x.xxx –username app_xxxxx –password xxxxx –mode $ARG1$ —-warning $ARG2$ –critical $ARG3$ –name=”^(?!(tempdb))” –regexp }

    My notification output comes up as:

    Additional Info: (null)

    It works if manually ran from shell the shell.

87. Recomendaciones « Informatica Says:
    March 2nd, 2012 at 5:47

    [...] Nagios http://alexmoralessatorres.com/category/nagios/ http://sysengineers.wordpress.com/2009/11/25/adding-perfmon-counters-using-nrpe-and-centreon/ http://labs.consol.de/lang/de/nagios/check_mssql_health/ [...]

88. MSSQL Überwachung › NETWAYS Blog Says:
    September 3rd, 2012 at 9:27

    [...] Des weiteren gibt es von Gerhard Lauser das Plugin check_mssql_health mit Hilfe dessen man z.B. an Werte wie connection-time, connected-users, lock-waits oder deadlocks bekommt. Informationen und eine genaue Anleitung gibt’s hier. [...]

89. Nagios: Configure check_mssql_health on CentOS « Void Technology Says:
    February 11th, 2013 at 18:10

    [...] # Author: ppadial # http://labs.consol.de/nagios/check_mssql_health/ [...]