Search Exchange

Search All Sites

Nagios Live Webinars

Let our experts show you how Nagios can help your organization.

Contact Us

Phone: 1-888-NAGIOS-1
Email: sales@nagios.com

Login

Remember Me

check_mssql

Rating
18 votes
Favoured:
1
Current Version
0.8.3
Last Release Date
2018-01-11
Compatible With
  • Nagios 3.x
  • Nagios 4.x
  • Nagios XI
Hits
167696
Files:
FileDescription
check_mssqlPHP-based check_mssql
Network Monitoring Software - Download Nagios XI
Log Management Software - Nagios Log Server - Download
Netflow Analysis Software - Nagios Network Analyzer - Download
A PHP-based plugin to check MSSQL servers. Requires PHP-mssql support, usually in the form of a package named "php5-sybase" or similar.
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)
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
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}";
bypaul.jobb, November 29, 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 DOMAIN\user.id --password 12345
byoragain, December 10, 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";
}
}
byVoluAJ, February 27, 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.
byInny, September 1, 2013
1 of 1 people found this review helpful
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)) {
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.
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)) {
--------------
bymax_roessler, January 17, 2013
It works perfect for me.
But is there any possibility to login with a database server that hase 2 or more instance?
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;$critical\n"
byprestigetech, January 20, 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/
byPL, October 22, 2011
-s, --storedproc does not work as there is no code to handle this.
bymcouvran, February 17, 2011
1 of 1 people found this review helpful
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