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 Login to Claim
Nagios CSP

Meet The New Nagios Core Services Platform

Built on over 25 years of monitoring experience, the Nagios Core Services Platform provides insightful monitoring dashboards, time-saving monitoring wizards, and unmatched ease of use. Use it for free indefinitely.

Download Now

Monitoring Made Magically Better

  • Nagios Core on Overdrive
  • Powerful Monitoring Dashboards
  • Time-Saving Configuration Wizards
  • Open Source Powered Monitoring On Steroids
  • And So Much More!
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
Helpful?    
Report
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}";
Helpful?    
Report
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
Helpful?    
Report
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";
}
}
Helpful?    
Report
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.
Helpful?    
Report
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)) {
1 of 1 found this review helpful.
Helpful?  1  0
Report
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.
Helpful?    
Report
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)) {
--------------
Helpful?    
Report
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?
Helpful?    
Report
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"
Helpful?    
Report
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/
Helpful?    
Report
php code missing
by PL, October 31, 2011
-s, --storedproc does not work as there is no code to handle this.
Helpful?    
Report
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
1 of 1 found this review helpful.
Helpful?  1  0
Report
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,600