Check MS SQL Stored Procedure

[cdienger]

Sounds good. Keep us posted.

[jkinning]

It sat there longer but then spit out this message.

Code: Select all

/check_mssql_sproc_parameters.pl -H sqldev_digitallog -u DigitalLogNagios -P fsanw_54d -d digital_log_d -p "dbo.uspDgtlGetErrorLogs @DgtlLogMinutes = 999999, @DgtlLogSourceId = 16, @DgtlLogLevel = 4" -w 1 -c 2
DBI connect('Driver=MySQL;SERVER=sqldev_digitallog;PORT=4000','DigitalLogNagios',...) failed: [unixODBC][MySQL][ODBC 5.1 Driver]Lost connection to MySQL server at 'reading initial communication packet', system error: 104 (SQL-08S01) at ./check_mssql_sproc_parameters.pl line 72
Error: Unable to connect to MS-SQL database!
[unixODBC][MySQL][ODBC 5.1 Driver]Lost connection to MySQL server at 'reading initial communication packet', system error: 104 (SQL-08S01)

[mcapra]

In the event that using SQL Server's authentication (instead of domain authentication) is an option, I haven't had much trouble using check_mssql:

Code: Select all

[root@nagios libexec]# ./check_mssql.php --hostname 1.1.1.1 --username username --password password --port 1443 --database database --query "EXEC some_sp @field1=2815, @field2=76;"

OK: Query duration=0.023742 seconds. Query result=2815 Query result=09 Query result=4.2199999999999998 Query result=45.5 Query result=3.9780000000000002 Query result=0 Query result=4.2199999999999998 Query result=3.9780000000000002 Query result=89 Query result=1 Query result=1 Query result=1 Query result=1|query_duration=0.023742s;

[cdienger]

Is SQL auth an option as mcapra suggested?

The new error makes me think there's a problem between machines. Are you able to telnet from XI to the sql server ?

Code: Select all

yum -y install telnet
telnet digitallog 4000

?

[jkinning]

telnet sqldev_digitallog 4000
Trying x.x.x.x...
Connected to sqldev_digitallog.
Escape character is '^]'.

[cdienger]

Is the check that mcapra an option for you? Failing this I think it'd be best if you opened a ticket by sending an email to [email protected] and we can set up a remote.

[jkinning]

Looks like it doesn't like the _ in the hostname. I have tried to use quotes but that doesn't matter either. This is our standard naming convention is what the DBA's are telling me.

Code: Select all

check_mssql -H sqldev_digitallog --username DigitalLogNagios --password fsanw_54d --port 4000 --database digital_log_d  --query "dbo.uspDgtlGetErrorLogs @DgtlLogMinutes = 999999, @DgtlLogSourceId = 16, @DgtlLogLevel = 4"
UNKNOWN: Invalid characters in the hostname.

I use the IP address and got an OK back. I'll validate that is alright and see if they can change something so I can get a Critical output. I'll keep you posted.

Code: Select all

check_mssql -H x.x.x.x --username DigitalLogNagios --password fsanw_54d --port 4000 --database digital_log_d  --query "dbo.uspDgtlGetErrorLogs @DgtlLogMinutes = 999999, @DgtlLogSourceId = 16, @DgtlLogLevel = 4"
OK: Query duration=0.035074 seconds.|query_duration=0.035074s;;

[mcapra]

It's actually hard-wired to look for IP addresses exclusively* EDIT: Nope, see @scottwilkerson post

Code: Select all

// 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);
}

The regex could either be loosened or removed all together to allow hostnames:

Code: Select all

[root@nagios ~]# /usr/local/nagios/libexec/check_mssql.php --hostname 1.1.1.1 --username username --password password --port 1443 --database database --query "EXEC some_sp @field1=2815, @field2=76;"
OK: Query duration=0.18552 seconds. Query result=2815 Query result=09 Query result=4.2199999999999998 Query result=45.5 Query result=3.9780000000000002 Query result=0 Query result=4.2199999999999998 Query result=3.9780000000000002 Query result=89 Query result=1 Query result=1 Query result=1 Query result=1|query_duration=0.18552s;

[root@nagios ~]# /usr/local/nagios/libexec/check_mssql.php --hostname somehost --username username --password password --port 1443 --database database --query "EXEC some_sp @field1=2815, @field2=76;"
OK: Query duration=0.029853 seconds. Query result=2815 Query result=09 Query result=4.2199999999999998 Query result=45.5 Query result=3.9780000000000002 Query result=0 Query result=4.2199999999999998 Query result=3.9780000000000002 Query result=89 Query result=1 Query result=1 Query result=1 Query result=1|query_duration=0.029853s;

[scottwilkerson]

It's actually hard-wired to look for IP addresses exclusively:

Code: Select all

    // 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);
    }

It's actually designed to be valid hostname chars

However, a subsequent specification (RFC 1123) permitted hostname labels to start with digits. No other symbols, punctuation characters, or white space are permitted. While a hostname may not contain other characters, such as the underscore character (_), other DNS names may contain the underscore.

https://en.wikipedia.org/wiki/Hostname

[mcapra]

Coffee helps

It's also worth mentioning that the regex won't match localhost and similar. I had to check the regex after receiving that error message when I was initially testing this.