Monitor database query

[Naveed]

its not working

-bash: "1234": event not found

Giving above error. Moreover please confirm me one thing that why we did not added host IP address?

Thank you

[rkennedy]

Did you have something in your terminal before you pasted the command? I'm not sure where you saw that. You'll need to change the variables in --username, and also --password. It should have returned an error about a missing host -

Code: Select all

[root@localhost libexec]# /usr/local/nagios/libexec/check_mssql --username 'abcd' --password "1234" --database master --port 1433 --query "SELECT COUNT%28%2A%29 FROM sys.sysperfinfo" --result "Expected result" --decode --warning 50 --critical 200 --querywarning 50 --querycritical 200
UNKNOWN: The required hostname field is missing.

Adjust what I have put below, and run the command. Then, show us a screenshot of the full input / output.

Code: Select all

[root@localhost libexec]# /usr/local/nagios/libexec/check_mssql -H 1.2.3.4 --username 'abcd' --password "1234" --database master --port 1433 --query "SELECT COUNT%28%2A%29 FROM sys.sysperfinfo" --result "Expected result" --decode --warning 50 --critical 200 --querywarning 50 --querycritical 200

[Naveed]

Code: Select all

[root@Nagios-VM ~]# cd /usr/local/nagios/libexec/
[root@Nagios-VM libexec]#
[root@Nagios-VM libexec]# ./usr/local/nagios/libexec/check_mssql --username 'abc' --password "u!Dutch2016." --database master --port 1433 --query "SELECT COUNT%28%2A%29 FROM sys.sysperfinfo" --result "Expected result" --decode --warning 50 --critical 200 --querywarning 50 --querycritical 200
-bash: !Dutch2016.": event not found

[root@Nagios-VM libexec]#
[root@Nagios-VM libexec]# ./check_mssql -H 1.2.3.4 --username 'abc' --password 'u!Dutch2016.' --database master --port 1433 --query "SELECT COUNT%28%2A%29 FROM sys.sysperfinfo" --result "Expected result" --decode --warning 50 --critical 200 --querywarning 50 --querycritical 200
CRITICAL: Could not execute the query.

[rkennedy]

Just to make sure, did you change the 1.2.3.4 to your actual IP address?

[Naveed]

Yes, I did change the actual IP and run this command.

[Naveed]

Nagios experts,

Please assist!

Thanks

[tmcdonald]

Please note that our support hours are 9AM to 5PM Monday to Thursday, and 9AM to 2PM Fridays (Central Time). Forum posts made outside of those hours will incur a delay.

We can try adding some debug output to the script. Open up the check_mssql plugin in an editor and find this line (328 in my script):

$output_msg = "CRITICAL: Could not execute the $querytype.\n";

and add this below it:

print_r($query_data);

then save and re-run it as before, posting the output.

[Naveed]

After suggested modifications in plugin...
Still getting same error message


-bash-4.1# cd /usr/local/nagios/libexec/
-bash-4.1# ./check_mssql -H 1.2.3.4 --username 'Udbread' --password 'u!Dutch2016.' --database master --port 1433 --query "SELECT COUNT%28%2A%29 FROM sys.sysperfinfo" --result "Expected result" --decode --warning 50 --critical 200 --querywarning 50 --querycritical 200
CRITICAL: Could not execute the query.

Note. IP address was changed during the run of this query command

[rkennedy]

Please post your check_mssql plugin for us to look at.

[Naveed]

Posted
=========

Code: Select all

#!/usr/bin/php
<?php

############################################################################
#
# check_mssql - Checks various aspect of MSSQL servers
#
# Version 0.6.6, Copyright (c) 2008 Gary Danko <[email protected]>
# Version 0.7.1                2013 Nicholas Scott <[email protected]>
# Notes:
#
#   Version 0.1.0 - 2008/08/14
#   Initial release. Accepts hostname, username, password, port,
#   database name, and an optional query to run.
#
#   Version 0.2.0 - 2008/08/15
#   You can now execute a query or stored procedure and report
#   on expected results. Queries should be simplistic since
#   only the first row is returned.
#
#   Version 0.2.2 - 2008/08/18
#   Nothing major. Just a couple of cosmetic fixes.
#
#   Version 0.5.0 - 2008/09/29
#   Major rewrite. No new functionality. RegEx added to
#   validate command line options.
#
#   Version 0.6.0 - 2008/10/23
#   Allows the user to specify a SQL file with --query
#
#   Version 0.6.3 - 2008/10/26
#   Removed the -r requirement with -q.
#
#   Version 0.6.4 - 2008/10/31
#   Fixed a bug that would nullify an expected result of "0"
#
#   Version 0.6.5 - 2008/10/31
#   Minor fix for better display of error output.
#
#   Version 0.6.6 - 2008/10/31
#   Prepends "exec " to --storedproc if it doesn't exist.
#
#   Version 0.6.7 - 2012/07/05
#   Enabled instances to be used
#
#   Version 0.6.8 - 2012/08/30
#   Enabled returning of perfdata
#   Warning and crits may be decimal values
#
#   Version 0.6.9 - 2013/01/03
#   Fixed minor exit code bug
#
#   Version 0.7.0 - 2013/04/16
#   Added ability to make ranges on query results
#
#   Version 0.7.1 - 2013/06/17
#   Fixed bug with query ranges
#
#   Version 0.7.2 - 2014/11/20
#   Fixed to comply with Nagios threshold guidelines
#
#   Version 0.7.3 - 2015/02/11
#   Patch from D.Berger
#   1. the warning/critical defaults weren't as documented,
#   2. the warning and critical variables would be referenced undefined if not provided on the command line
#   3. the output wouldn't (always) include OK/WARNING/CRITICAL due to bad logic
#   4. the query result perf data wouldn't include warning/critical thresholds
#
#   This plugin will check the general health of an MSSQL
#   server. It will also report the query duration and allows
#   you to set warning and critical thresholds based on the
#   duration.
#
#   Requires:
#       yphp_cli-5.2.5_1 *
#       yphp_mssql-5.2.5_1 *
#       freetds *
#
# License Information:
# This program is free software; you can redistribute it and/or modify
# it under the terms of the GNU General Public License as published by
# the Free Software Foundation; either version 2 of the License, or
# (at your option) any later version.
#
# This program is distributed in the hope that it will be useful,
# but WITHOUT ANY WARRANTY; without even the implied warranty of
# MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE.  See the
# GNU General Public License for more details.
#
# You should have received a copy of the GNU General Public License
# along with this program; if not, write to the Free Software
# Foundation, Inc., 675 Mass Ave, Cambridge, MA 02139, USA.
#
############################################################################

$progname = "check_mssql";
$version = "0.7.3";
$warning = "";
$critical = "";

// Parse the command line options
for ($i = 1; $i < $_SERVER['argc']; $i++) {
    $arg = $_SERVER["argv"][$i];
    switch($arg) {
        case '-h':
        case '--help':
            help();
        break;

        case '-V':
        case '--version':
            version();
        break;

        case '-I':
        case '--instance':
            $db_inst = check_command_line_option($_SERVER["argv"][$i], $i);
        break;

        case '-H':
        case '--hostname':
            $db_host = check_command_line_option($_SERVER["argv"][$i], $i);
        break;

        case '-u':
        case '-U':
        case '--username':
            $db_user = check_command_line_option($_SERVER["argv"][$i], $i);
        break;

        case '-P':
        case '--password':
            $db_pass = check_command_line_option($_SERVER["argv"][$i], $i);
        break;

        case '-p':
        case '--port':
            $db_port = check_command_line_option($_SERVER["argv"][$i], $i);
        break;

        case '-d':
        case '--database':
            $db_name = check_command_line_option($_SERVER["argv"][$i], $i);
        break;

        case '--decode':
            $decode = true;
        break;

        case '-q':
        case '--query':
            $query = check_command_line_option($_SERVER["argv"][$i], $i);
            $querytype = "query";
        break;

        case '-s':
        case '--storedproc':
            $storedproc = check_command_line_option($_SERVER["argv"][$i], $i);
            $querytype = "stored procedure";
        break;

        case '-r':
        case '--result':
            $expected_result = check_command_line_option($_SERVER["argv"][$i], $i);
        break;

        case '-W':
        case '--querywarning':
            $query_warning = check_command_line_option($_SERVER["argv"][$i], $i);
        break;

        case '-C':
        case '--querycritical':
            $query_critical = check_command_line_option($_SERVER["argv"][$i], $i);
        break;

        case '-w':
        case '--warning':
            $warning = check_command_line_option($_SERVER["argv"][$i], $i);
        break;

        case '-c':
        case '--critical':
            $critical = check_command_line_option($_SERVER["argv"][$i], $i);
        break;
    }
}

// Error out if mssql support is not present.
if (!function_exists('mssql_connect')) {
    print "UNKNOWN: MSSQL support is not installed on this server.\n";
    exit(3);
}

// If no options are set, display the help
if ($_SERVER['argc'] == 1) { 
    print "$progname: Could not parse arguments\n"; 
    usage(); 
    exit; 
}

// Determine if the query is a SQL file or a text query
if (isset($query)) {
    if (file_exists($query)) {
        $query = file_get_contents($query);
    }
}

if (isset($query) and isset($decode)) {
    $query = urldecode($query);
}

// Add "exec" to the beginning of the stored proc if it doesnt exist.
if (isset($storedproc)) {
    if (substr($storedproc, 0, 5) != "exec ") {
        $storedproc = "exec $storedproc";
    }
}

// Do not allow both -q and -s
if (isset($query) && isset($storedproc)) {
    print "UNKNOWN: The -q and -s switches are mutually exclusive. You may not select both.\n";
    exit(3);
}

// -r demands -q and -q demands -r
if (isset($expected_result) && !isset($query)) {
    print "UNKNOWN: The -r switch requires the -q switch. Please specify a query.\n";
    exit(3);
}

// Validate the hostname
if (isset($db_host)) {
    if (!preg_match("/^([a-zA-Z0-9-]+[\.])+([a-zA-Z0-9]+)$/", $db_host)) {
        print "UNKNOWN: Invalid characters in the hostname.\n";
        exit(3);
    }
} else {
    print "UNKNOWN: The required hostname field is missing.\n";
    exit(3);
}

// Validate the port
if (isset($db_port)) {
    if (!preg_match("/^([0-9]{4,5})$/", $db_port)) {
        print "UNKNOWN: The port field should be numeric and in the range 1000-65535.\n";
        exit(3);
    }
} else {
    $db_port = 1433;
}

// 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.\n";
        exit(3);
    }
} else {
    print "UNKNOWN: You must specify a username for this DB connection.\n";
    exit(3);
}

// Validate the password
if (empty($db_pass)) {
    print "UNKNOWN: You must specify a password for this DB connection.\n";
    exit(3);
}

// Validate the warning threshold
if (!empty($warning) && !preg_match("/^[0-9]\d*(\.\d+)?$/", $warning)) {
    print "UNKNOWN: Invalid warning threshold.\n";
    exit(3);
}

// Validate the critical threshold
if (!empty($critical) && !preg_match("/^[0-9]\d*(\.\d+)?$/", $critical)) {
    print "UNKNOWN: Invalid critical threshold.\n";
    exit(3);
}

// Is warning greater than critical?
if (!empty($warning) && !empty($critical) && $warning > $critical) {
    $exit_code = 3;
    $output_msg = "UNKNOWN: warning value should be lower than critical value.\n";
    display_output($exit_code, $output_msg);
}

// Attempt to connect to the server
$time_start = microtime(true);
$db_connstr = $db_host;
if ( isset($db_inst) ) $db_connstr .= "\\$db_inst";
else $db_connstr .= ":$db_port";
if (!$connection = @mssql_connect( $db_connstr , $db_user, $db_pass)) {
    $exit_code = 2;
    $output_msg = "CRITICAL: Could not connect to $db_connstr as $db_user.\n";
    display_output($exit_code, $output_msg);
} else {
    $time_end = microtime(true);
    $query_duration = round(($time_end - $time_start), 6);
    // Exit now if no query or stored procedure is specified
    if (empty($storedproc) && empty($query)) {
        $output_msg = "Connect time=$query_duration seconds.";
        process_results($query_duration, $warning, $critical, $output_msg);
    }
}

$exit_code = 0;
$state = "OK";

if (empty($db_name)) {
    $exit_code = 3;
    $output_msg = "UNKNOWN: You must specify a database with the -q or -s switches.\n";
    display_output($exit_code, $output_msg);
}

// Attempt to select the database
if(!@mssql_select_db($db_name, $connection)) {
    $exit_code = 2;
    $output_msg = "CRITICAL: Could not connect to $db_name on $db_host.\n";
    display_output($exit_code, $output_msg);
}

// Attempt to execute the query/stored procedure
$time_start = microtime(true);
if (!$query_data = @mssql_query("$query")) {
    $exit_code = 2;
    $output_msg = "CRITICAL: Could not execute the $querytype.\n";
	print_r($query_data);
    display_output($exit_code, $output_msg);
} else {
    $time_end = microtime(true);
    $query_duration = round(($time_end - $time_start), 6);
    $output_msg = "Query duration=$query_duration seconds.";
}

$result_perf_data = null;
if ($querytype == "query" && (isset($expected_result) || isset($query_warning) || isset($query_critical))) {
    if (mssql_num_rows($query_data) > 0 ) {
        while ($row = mssql_fetch_row($query_data)) {
            $column_name = mssql_field_name($query_data, 0);
            $query_result = $row[0];
        }
    }

    if ((isset($query_warning) || isset($query_critical))) {
        $result_perf_data .= "'{$column_name}'={$query_result};{$query_warning};{$query_critical}";
    
        if (!empty($warning)) {
            switch (check_nagios_threshold($query_warning, $query_result)) {
                case 3:
                    $exit_code = 3;
		    $state = _("ERROR: In range threshold START:END, START must be less than or equal to END");
                case 1:
		    $state = "WARNING";
		    $exit_code = 1;
		    $output_msg = "Query result $query_result was higher than query warning threshold.";
            }
        }
    
        if (!empty($critical)) {
            switch (check_nagios_threshold($query_critical, $query_result)) {
                    case 3:
                $exit_code = 3;
                $state = _("ERROR: In range threshold START:END, START must be less than or equal to END");
                    case 1:
                $state = "CRITICAL";
                $exit_code = 2;
                $output_msg = "Query result $query_result was higher than query critcal threshold.";
                }
            }
    } else {
        if ($query_result == $expected_result) {
	    $output_msg = "Query results matched, query duration=$query_duration seconds.";
        } else {
            $exit_code = 2;
            $output_msg = "CRITICAL: Query expected \"$expected_result\" but got \"$query_result\".";
        }
    }
}
process_results($query_duration, $warning, $critical, $state, $output_msg, $exit_code, $result_perf_data);

//-----------//
// Functions //
//-----------//

// Function to validate a command line option
function check_command_line_option($option, $i) {
    // If the option requires an argument but one isn't sent, bail out
    $next_offset = $i + 1;
    if (!isset($_SERVER['argv'][$next_offset]) || substr($_SERVER['argv'][$next_offset], 0, 1) == "-") {
            print "UNKNOWN: The \"$option\" option requires a value.\n";
    exit(3);
    } else {
            ${$option} = $_SERVER['argv'][++$i];
            return ${$option};
    }
}

// Function to process the results
function process_results($query_duration, $warning, $critical, $state, $output_msg, $exit_code = null, $result_perf_data=null) {
    
    if (!$query_duration) {
        $response['result_code'] = 3;
        $response['output'] = "UNKNOWN: Could not perform query";
    } 
    
    $result_code = 0;
    $result_prefix = "OK:";
    
    if (!empty($warning)) {
        switch (check_nagios_threshold($warning, $query_duration)) {
            case 3:
                $exit_code = 3;
                $state = _("ERROR: In range threshold START:END, START must be less than or equal to END");
            case 1:
                $state = "WARNING";
                $exit_code = 1;
        }
    }
    
    if (!empty($critical)) {
        switch (check_nagios_threshold($critical, $query_duration)) {
            case 3:
                $exit_code = 3;
                $state = _("ERROR: In range threshold START:END, START must be less than or equal to END");
            case 1:
                $state = "CRITICAL";
                $exit_code = 2;
        }
    }

    $statdata = "$state: $output_msg";
    $perfdata = "query_duration={$query_duration}s;{$warning};{$critical}";
    if($result_perf_data !== NULL) {
        $perfdata .= " $result_perf_data";
    }
    $output_msg = "{$statdata}|{$perfdata}\n";
    display_output($exit_code, $output_msg);
}


function check_nagios_threshold($threshold, $value) {
    $inside = ((substr($threshold, 0, 1) == '@') ? true : false);
    $range = str_replace('@','', $threshold);
    $parts = explode(':', $range);
    
    if (count($parts) > 1) {
        $start = $parts[0];
        $end = $parts[1];
    } else {
        $start = 0;
        $end = $range;
    }

    if (substr($start, 0, 1) == "~") {
        $start = -999999999;
    }
    if (empty($end)) {
        $end = 999999999;
    }
    if ($start > $end) {
        return 3;
    }
    if ($inside > 0) {
        if ($start <= $value && $value <= $end) {
            return 1;
        }
    } else {
        if ($value < $start || $end < $value) {
            return 1;
        }
    }

    return 0;
}

// Function to display the output
function display_output($exit_code, $output_msg) {
    print $output_msg;
    exit($exit_code);
}

// Function to display usage information
function usage() {
    global $progname, $version;
    print <<<EOF
Usage: $progname -H <hostname> --username <username> --password <password>
       [--port <port> | --instance <instance>] [--database <database>] 
       [--query <"text">|filename] [--storeproc <"text">] [--result <text>] 
       [--warning <warn time>] [--critical <critical time>] [--help] [--version]
       [--querywarning <integer>] [--querycritical <integer>]

EOF;
}

// Function to display copyright information
function copyright() {
    global $progname, $version;
    print <<<EOF
Copyright (c) 2008 Gary Danko ([email protected])
          2012 Nicholas Scott ([email protected])
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.

EOF;
}

// Function to display detailed help
function help() {
    global $progname, $version;
    print "$progname, $version\n";
    copyright();
    print <<<EOF

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.
 -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 against the MSSQL server.
 -s, --storedproc
    Optional stored procedure to execute against the 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 
    Accepts decimal values, note however that there must be at least a 
    leading 0. Example, .0023 is not a valid entry, but 0.0023 is.
 -c, --critical
    Critical threshold in seconds on duration of check
    Accepts decimal values, note however that there must be at least a 
    leading 0. Example, .0023 is not a valid entry, but 0.0023 is.
 -W, --querywarning
    Warning threshold for the query IF IT IS NUMERIC, otherwise, this
    will be ignored.
 -C, --querycritical
    Critical threshold for the query IF IT IS NUMERIC, otherwise, this
    will be ignored.

Example: $progname -H myserver -U myuser -P mypass -q /tmp/query.sql -w 2 -c 5
Example: $progname -H myserver -U myuser -P mypass -q "select count(*) from mytable" -r "632" -w 2 -c 5
Send any questions regarding this utility to [email protected] or [email protected].

EOF;
    exit(0);
}

// Function to display version information
function version() {
    global $version;
    print <<<EOF
$version

EOF;
    exit(0);
}
?>