check_mssql

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}";

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

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";
}
}

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 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)) {
--------------

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;$criticaln"

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/

-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