Check MS SQL Stored Procedure

[jkinning]

I am looking to leverage Nagios to run some stored procedures within MSSQL server. I found this plugin

Code: Select all

https://exchange.nagios.org/directory/Plugins/Databases/SQLServer/check_mssql_sproc/details

which everytime I tried to run it I get "command not found" I discovered an updated version here

Code: Select all

https://github.com/brunocantisano/check_mssql_sproc_parameters

but that too gave me the same message.

My question is there another or easier way to get Nagios to connect to an MS SQL server, run a stored procedure, and alert based on the results?

[bolson]

For check_mssql_sproc please share the syntax that you are using to execute the command.

[jkinning]

Code: Select all

./check_mssql_sproc_parameters.pl -H sqldev_digitallog,4000 -u DigitalLogNagios -P detak_22e -p "dbo.uspDgtlGetErrorLogs @DgtlLogMinutes = 999999, @DgtlLogSourceId = 16, @DgtlLogLevel = 4" -w 1 -c 2 

Running this from the command line as the nagios user on the server. Once I get this working then I add it to the NagiosXI GUI.

[mcapra]

I can't even get check_mssql_sproc_parameters to connect to my SQL Server database:

Code: Select all

[root@nagios libexec]# ./check_mssql_sproc_parameters.pl -H 1.1.1.1,1433 -d database -u username -P password -p "get_gpa_stats_all @calendarID=57" -w 1 -c 2
DBI connect('Driver={ODBC Driver 11 for SQL Server};SERVER=1.1.1.1,1433','username',...) failed: [unixODBC][Driver Manager]Data source name not found, and no default driver specified (SQL-IM002) at ./check_mssql_sproc_parameters.pl line 71.
Error: Unable to connect to MS-SQL database!
[unixODBC][Driver Manager]Data source name not found, and no default driver specified (SQL-IM002)

Just some thoughts from playing around with this totally unrelated to what may ultimately be the solution.

check_mssql claims to be able to handle stored procedures:
https://exchange.nagios.org/directory/P ... ql/details

EDIT: Nevermind all that PDO stuff I wrote earlier. This seems to work:

Code: Select all

#!/usr/bin/php
<?php
  $dbh = new PDO("dblib:host=1.1.1.1;dbname=database", "username", "password");
  $sth = $dbh->prepare("EXEC get_gpa_stats_all @calendarID=57");
  $sth->execute();

  while($result = $sth->fetch(PDO::FETCH_ASSOC)) {
    var_dump($result);
  }
?>

I'll take a look at the implications for check_mssql if a solution isn't found by the time I'm out of meetings.

[jkinning]

I tried the other one, original, one and get the same "command not found" message.

Code: Select all

./check_mssql_sproc.pl -H sqldev_digitallog,4000 -u DigitalLogNagios -P detak_22e -p "dbo.uspDgtlGetErrorLogs @DgtlLogMinutes = 999999, @DgtlLogSourceId = 16, @DgtlLogLevel = 4" -w 1 -c 2

[bolson]

The error "Command not found" invariably means exactly what it says. Is check_mssql_sproc.pl in the directory from which you're running it?

[jkinning]

Yes, I am running it from /usr/local/nagios/libexec

-rwxrwxr-x 1 apache nagios 144301 Nov 12 2014 check_mrtg
-rwxrwxr-x 1 apache nagios 145354 Nov 12 2014 check_mrtgtraf
-rwxrwxr-x 1 apache nagios 17546 Oct 4 2016 check_mssql
-rwxrwxr-x 1 apache nagios 14446 Oct 4 2016 check_mssql_database.py
-rwxrwxr-x 1 apache nagios 20473 Oct 4 2016 check_mssql_server.py
-rwxr-xr-x 1 apache apache 6746 Aug 28 10:45 check_mssql_sproc_parameters.pl
-rwxrwxrwx 1 apache apache 5400 Aug 28 10:11 check_mssql_sproc.pl
-rwxrwxr-x 1 apache nagios 190904 Nov 12 2014 check_mysql
-rwxrwxr-x 1 apache nagios 122024 Jun 15 2016 check_mysql_health
-rwxrwxr-x 1 apache nagios 175374 Nov 12 2014 check_mysql_query
-rwxrwxr-x 1 apache nagios 157438 Nov 12 2014 check_nagios

[mcapra]

You might also check the output of which perl executed from both root and the nagios user to make sure the script's shebang is pointing to the correct executable.

[scottwilkerson]

Also, you are using a MSSQL user and not Windows domain users correct?

[jkinning]

You might also check the output of which perl executed from both root and the nagios user to make sure the script's shebang is pointing to the correct executable.

[root@nagiosl1t ~]# which perl
/usr/bin/perl
[nagios@nagiosl1t libexec]$ which perl
/usr/bin/perl

Also, you are using a MSSQL user and not Windows domain users correct?

Yes, I am using an AD service account with read rights to the MSSQL database.