Search Exchange
Search All Sites
Nagios Live Webinars
Let our experts show you how Nagios can help your organization.Login
New ListingsDirectory Tree
check_mssql
Current Version
0.8.3
Last Release Date
2018-01-11
Compatible With
- Nagios 3.x
- Nagios 4.x
- Nagios XI
Owner
Hits
195695
Files:
| File | Description |
|---|---|
| check_mssql | PHP-based check_mssql |
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)
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)
byBarosch, May 7, 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
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
byshubell, October 17, 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}";
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
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";
}
}
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.
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)) {
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)) {
byjoshua.m.roberts, April 23, 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.
byloopx, February 11, 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)) {
--------------
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?
But is there any possibility to login with a database server that hase 2 or more instance?
byjkeife, August 2, 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;$critical\n"
|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/
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.
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
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
