Nagios SQL job monitoring

[sajid4u2c]

Hi All,

I have a requirement to monitor the SQL jobs in a windows server.
Searched the plugins in exchange.nagios.org and got the below one. Which looks suitable for my task.

https://exchange.nagios.org/directory/P ... bs/details

I have performed all the prequisites as mentioned in the site and executed the command from Nagios terminal.
But it is giving the below error. Could you please suggest.

Code: Select all

./check_nrpe -H Server_Name -t 30 -c check_mssql -a '-CheckType Jobs'

Critical: The following exception occurred while trying to enumerate the collection: "Failed to connect to server "Server_Name".|

Thanks

[scottwilkerson]

did you do these from the requirements

Requirement:

To allow NRPE service to connect to SQL server without user and password, add "local system" acount to MSSQL sysadmin.
Or change nsclient service "Log on as" to run with user that have sysadmin permission on MSSQL.

then restart nsclient++ ?

[sajid4u2c]

Hi Scott,

yes I did it. I changed the "Log on As" and assigned a user who has the SysAdmin role to the NRPE service.

Also, once i logged into the server and try to run the script from powershell, it gives the same error as below

PS C:\Program Files\NSClient++\scripts> .\check_mssql.ps1 'Jobs'
Critical: The following exception occurred while trying to enumerate the collection: "Failed to connect to server (server_name)".

[scottwilkerson]

the user needs to be a user that has access to the database server.

Does your login user have access to login to the MSSQL server? Does the sysadmin role you used?

[sajid4u2c]

Yes, I used the user name which has access to MSSQL server.

One note is , we have the instance name for the MSSQL server.
The user name used has access to the MSSQL server instance which has the naming convention as "servername\INST01"

Could this be an issue as we are passing only the server name and not the full instance name in the NRPE command ?

[scottwilkerson]

you likely will have to edit the check_mssql.ps1 script to account for the instance name

[sajid4u2c]

Hi Scott,

My bad. I haven't checked the plugin options in detail. The plugin already has the instance name to be supplied as argument.

So, when I execute the below command in the powershell of the target server, it does give me the failed SQL jobs.
Please find below:

Code: Select all

PS C:\Program Files\NSClient++\scripts> .\check_mssql.ps1 'Jobs' 'INST01'
Critical: Total failed: 12. Job: names of all jobs

However, When I run the NRPE check from Nagios server terminal, it gives the below error.

Code: Select all

./check_nrpe -H "server_name" -t 30 -c check_mssql -a '-CheckType Jobs -InstanceName INST01'
CHECK_NRPE: Invalid packet type received from server.

Below are the settings done in nsclient++.ini

[/settings/external scripts/scripts]
check_mssql=cmd /c echo scripts\check_mssql.ps1 $ARG1$ ; exit($lastexitcode) | powershell.exe -ExecutionPolicy Bypass -command -


; POWERSHELL WRAPPING -
ps1 = cmd /c echo scripts\%SCRIPT%%ARGS%; exit($lastexitcode) | powershell.exe -ExecutionPolicy Bypass -command -


I think I am missing out in supplying the proper args in the nsclient++.ini file.
Could you please have a look and correct me please.

[scottwilkerson]

change this

Code: Select all

[/settings/external scripts/scripts]
check_mssql=cmd /c echo scripts\check_mssql.ps1 $ARG1$ ; exit($lastexitcode) | powershell.exe -ExecutionPolicy Bypass -command -

to this

Code: Select all

[/settings/external scripts/scripts]
check_mssql=check_mssql.ps1

and this

Code: Select all

; POWERSHELL WRAPPING -
ps1 = cmd /c echo scripts\%SCRIPT%%ARGS%; exit($lastexitcode) | powershell.exe -ExecutionPolicy Bypass -command -

to this (was missing a space)

Code: Select all

; POWERSHELL WRAPPING - 
ps1 = cmd /c echo scripts\\%SCRIPT% %ARGS%; exit($lastexitcode) | powershell.exe -ExecutionPolicy Bypass -command -

Then restart nsclient++ and call like this

Code: Select all

./check_nrpe -H "server_name" -t 30 -c check_mssql -a 'Jobs' 'INST01'

[sajid4u2c]

Thanks a lot Scott !!!.

This works as below.

Code: Select all

./check_nrpe -H server_name -t 30 -c check_mssql -a '-CheckType DBStatus -InstanceName INST01 '
OK:  server_name\INST01 All Databases are online. [Total DataBases: 11]|

Code: Select all

./check_nrpe -H  server_name  -t 30 -c check_mssql -a '-CheckType ConTime -InstanceName INST01 '
OK:  server_name\INST01 Connect time=0.0012107 seconds, Version 12.0.4237|'Connect Time'=0.00121;3;5;0.00121

However, when the script is ran with "Jobs" as argument, it still gives me the error as below:

Code: Select all

 ./check_nrpe -H  server_name  -t 30 -c check_mssql -a '-CheckType Jobs -InstanceName INST01 '
CHECK_NRPE: Invalid packet type received from server.

We are using NRPE v2, so as the output line for "failed jobs" are many (>10) I think it is not returning the output.

Guess below thread is for the same issue and only option is that I need to upgrade my NRPE version to v3.

https://support.nagios.com/kb/article/n ... d-518.html

If there is any work around , it would be of great help.
Else, we can close this thread
Thanks again.

[scottwilkerson]

Oh ya that makes sense. I don't see another option at this time.

Closing.