SQLServer

check_mssql

Write a Review

Description:

A PHP-based plugin to check MSSQL servers. Requires PHP-mssql support, usually in the form of a package named “php5-sybase” or similar.

Current Version

0.8.3

Last Release Date

2018-01-11

Compatible With

  • Nagios 3.x
  • Nagios 4.x
  • Nagios XI

Owner

Nagios Exchange

Copy Page Link Recommend Login to Add to Favorites
Project Files
FileDescription
check_mssql

PHP-based check_mssql

Project Notes
check_mssql, 0.8.3 This plugin checks various aspect of an MSSQL server. It will also execute queries or stored procedures and return results based on query execution times and expected query results. Options: -h, --help Print detailed help screen. -V, --version Print version information. -H, --hostname Hostname of the MSSQL server. -U, --username Username to use when logging into the MSSQL server. -P, --password Password to use when logging into the MSSQL server. -F, --cfgfile Read parameters from a php file, e. g. -p, --port Optional MSSQL server port. (Default is 1433). -I, --instance Optional MSSQL Instance -d, --database Optional DB name to connect to. -q, --query Optional query or SQL file to execute on MSSQL server. -l, --longquery Optional query or SQL file to execute on MSSQL server. The query is used for multiple line output only. By default Nagios will only read the first 4 KB. (MAX_PLUGIN_OUTPUT_LENGTH) --decode Reads the query -q in urlencoded format. Useful if special characters are in your query. --decodeonly Decode the query -q Prints the decoded query string and exits. --encode Encodes the query -q Prints urlencoded query and exits. -s, --storedproc Optional stored procedure to execute on MSSQL server. -r, --result Expected result from the specified query, requires -q. The query pulls only the first row for comparison, so you should limit yourself to small, simple queries. -w, --warning Warning threshold in seconds on duration of check -c, --critical Critical threshold in seconds on duration of check -W, --querywarning Query warning threshold -C, --querycritical Query critical threshold Example: check_mssql -H myserver -U myuser -P mypass -q /tmp/query.sql -c 10 -W 2 -C 5 Example: check_mssql -H myserver -U myuser -P mypass -q "SELECT COUNT(*) FROM mytable" -r "632" -c 10 -W 2 -C 5 Note: Warning and critical threshold values should be formatted via the Nagios Plugin guidelines. See guidelines here: https://nagios-plugins.org/doc/guidelines.html#THRESHOLDFORMAT Examples: 10 Alerts if value is > 10 30: Alerts if value < 30 ~:30 Alerts if value > 30 30:100 Alerts if 30 > value > 100 @10:200 Alerts if 30 >= value <= 100 @10 Alerts if value = 10 Copyright (c) 2008 Gary Danko (gdanko@gmail.com) 2012 Nagios Enterprises - Nicholas Scott (nscott@nagios.com) 2017 Nagios Enterprises - Jake Omann (jomann@nagios.com)
Reviews (13) Add a Review
Heterogeneous queries over multiple servers
by Barosch, May 31, 2019

If you try the create a query over multiple servers you get the following error message (from freetds): Heterogeneous queries require the ANSI_NULLS and ANSI_WARNINGS options to be set for the connection. This ensures consistent query semantics. Enable these options and then reissue your query. Adding $connection->query("SET ANSI_NULLS ON"); $connection->query("SET ANSI_WARNINGS ON;"); fixes the error. Greetings


Instance and port parameters not working
by shubell, October 31, 2017

If you want to connect to an other than default instance you need to set your mssql server to a static port. You also need to add a few things add line 137: $db_port = 1433; edit line 412: $db_dsn = "dblib:host={$db_host:$db_port};dbname={$db_name}";


Using Domain login
by paul.jobb, November 30, 2016

nice plugin, perfect for what I need Made the following modifications to allow for using a windows login instead of a SQL server account edited /etc/freetds.conf and added the following entries to the [global] section. tds version = 7.0 use ntlmv2 = yes (required in my environment anyways) You could also create a .freetds.conf file in the nagios home folder if you prefer. changed the regex slightly to allow for backslashes in username was // Validate the username if (isset($db_user)) { if (!preg_match("/^[a-zA-Z0-9-]{2,32}$/", $db_user)) { print "UNKNOWN: Invalid characters in the username. "; // exit(3); changed to // Validate the username if (isset($db_user)) { if (!preg_match("/^[a-zA-Z0-9-]{2,32}$/", $db_user)) { print "UNKNOWN: Invalid characters in the username. "; // exit(3); supply --username as follows check_mssql -H sqlserver --username DOMAINuser.id --password 12345


getting value of query
by oragain, December 31, 2014

You can actually easily modify the script in order to retrieve values from the query. I modified mine to retrieve only one value: in process_results, i changed $perfdata=.... to $perfdata = "value={$query_duration};0;"; There is no need for me to actually show static lines for warning and critical. i initially did the change for query, but due to the complexity of the query, just better to go through a stored proc to dodge nagios / centreon cleanup string functions. before the call to process_results i did the following changes that will allow you to retrieve a value for a procedure: //Custom modification by ######### if ($querytype == "stored procedure") { $stmt = mssql_init($oldstoredproc); $query_data = mssql_execute($stmt); $query_result="no result returned"; if (mssql_num_rows($query_data) > 0 ) { $row = mssql_fetch_row($query_data); $query_result = $row[0]; // cheating on process_results $query_duration = $query_result; $output_msg = "Value=$query_result."; } mssql_free_statement($stmt); $exit_code = 0; } // end of custom and at the beginning of the file, I changed to the following because mssql_execute does not need the exec part: // Add "exec" to the beginning of the stored proc if it doesnt exist. if (isset($storedproc)) { $oldstoredproc = $storedproc; if (substr($storedproc, 0, 5) != "exec ") { $storedproc = "exec $storedproc"; } }


Works, but not as expected.
by VoluAJ, February 28, 2014

I was under the assumption that I could take the value of the query back into Nagios, not just that it returns a static expected value. I'm querying a count of rows on a table and I need to know if that goes over X. Would also like he value returned to be graphed.


Bug when using 0 as expected result
by Inny, September 30, 2013

Bug: When you use '0' as expected result, you will always get status OK! Mentioned in first rating by mcouvran, February 17, 2011 You can use his workaround by adding 1 to the query (COUNT(*)+1) and adjusting your expected result accordingly, or: To fix this, look for: if ($querytype == "query" && isset($expected_result)) { Replace it with: if ($querytype == "query" && (!empty($expected_result) || $expected_result == 0)) {


stored proc result
by joshua.m.roberts, April 30, 2013

Great plugin. Works as expected. One thing that would be nice is to check the result of a stored proc just like what is done for a query.


Hostname check missing '_' character
by loopx, February 28, 2013

We are using some DNS name which contains the character '_'. Version 0.6.6 don't permit the usage of '_' and so, have to update it. To do so, update this line : --------------------- if (!preg_match("/^([a-zA-Z0-9-.]+)$/", $db_host)) { --------------------- by this one : -------------- if (!preg_match("/^([a-zA-Z0-9-._]+)$/", $db_host)) { --------------


Instance Login
by max_roessler, January 31, 2013

It works perfect for me. But is there any possibility to login with a database server that hase 2 or more instance?


added performance data ouput
by jkeife, August 31, 2012

I added some additional output that way i could feed the query times into pnp4nagios. Here's what I added to the $output_msg variable when testing just a connection to a database or running a query; |time=${query_duration}s;$warning;$criticaln"


Finally got it!
by prestigetech, January 31, 2012

This is an awesome plugin and it does exactly what we needed! Had some trouble at first getting this working, but finally got it. Wrote a set of instructions here... http://ptihosting.com/blog/it-blog/monitor-mssql-with-nagios/


php code missing
by PL, October 31, 2011

-s, --storedproc does not work as there is no code to handle this.


Bug with when expected 0
by mcouvran, February 28, 2011

When you expect 0 for request, the result will be always OK due to the empty function line 287. To test that check_mssql -H myserver -U myuser -P mypass -q "select count(*) from mytable" -r "0" -w 2 -c 5 You need to test this check_mssql -H myserver -U myuser -P mypass -q "select count(*) +1 from mytable" -r "1" -w 2 -c 5


Add a Review

You must be logged in to submit a review.

Thank you for your review!

Your review has been submitted and is pending approval.

Recommend

To:


From:


Thank you for your recommendation!

Your recommendation has been sent.

Page Sections
Project Overview Project Files Project Notes Reviews
Project Stats
Rating
4.1 (18)
Favorites
1
Views
197,646