Nagios SQL Plugin

[mindspring]

Hi There,

We are having an issue with monitoring SQL. The plugin is monitoring a number of SQL servers, most of which has no issue.
There are a few that have errors and we can't figure out what the problem might be.

The SQL and Windows versions are the same across the machines.

The SQL administrators have confirmed that there are no permission issues. I suspect the plugin is at fault but I can't see what to test as all the plugin needs is the DB instance, the user/pass and the database to monitor.

Any ideas what could generate these error messages?

Thanks

[tacolover101]

could you post the raw code format of your services and check_command's used here? i suspect there is something not behaving properly in the formatting.

[mindspring]

Sure, is this what you need?

Code: Select all

Command view: $USER1$/check_mssql_server.py -H $HOSTADDRESS$ $ARG1$

$ARG1$ :  -U 'sa' -P '*******' -I 'SAGEPROD' --averagewait --warning 250 --critical 450

[tgriep]

If the same check is working on some servers and not others, the issue could be caused by the MSSQL INSTANCE is listening on a different port than the default so you may need to monitor the MSSQL server using the port option.
To do this, remove the instance name option from the command and use the -p port option instead.

-p PORT, --port=PORT
Specify port.

Try that and let us know if that fixed the issue for you.

[mindspring]

Thanks, this is interesting.

I tried changing one of the services to the port and the messages returned were exactly for the same for the port vs the instance.

Code: Select all

File "/usr/local/nagios/libexec/check_mssql_server.py", line 469, in <module>
main()
File "/usr/local/nagios/libexec/check_mssql_server.py", line 435, in main
execute_query(mssql, options, host)
File "/usr/local/nagios/libexec/check_mssql_server.py", line 448, in execute_query
mssql_query.do(mssql)
File "/usr/local/nagios/libexec/check_mssql_server.py", line 284, in do
self.run_on_connection(connection)
File "/usr/local/nagios/libexec/check_mssql_server.py", line 271, in run_on_connection
self.query_result = cur.fetchone()[0]
TypeError: 'NoneType' object is unsubscriptable

I also found that the Connnection time probe works on this particular server, using the instance.

nag2.PNG

nag1.PNG

Any other ideas?

[tgriep]

There has been some recent changes to the MSSQL pluging that could fix the issue you are having.
Lets make sure you are running the latest plugins. Login to the XI GUI and go to the Admin > Manage Config Wizards menu, click on the Check for Updates button and if those wizards need to be updated, update them and see if the issue is resolved.

[mindspring]

Thanks, I tried the update but that unfortunately had no effect.

These are the error messages, they are different.

er1.PNG

er2.PNG

er3.PNG

Was there anything more I needed to do after the update of the plugins to refresh them?

[tgriep]

What version of MSSQL server are you running?
The MSSQL Database Size check from what I remember doesn't work on newer versions of MSSQL as the settings it needs have been removed by Microsoft.

The other errors are unusual and could be caused by a corrupt plugin, missing requirements or permission settings of the database.
Take a look at this document for setting the permissions of the MSSQL user account.
https://assets.nagios.com/downloads/nag ... ios-XI.pdf

Can you run the following command on the Nagios server and post the plugin here?

Code: Select all

/usr/local/nagios/libexec/check_mssql_server.py --help

You may have to change some settings in the /etc/freetds.conf file outlined in this KB article.
https://support.nagios.com/kb/article/n ... ailed.html
Try the above and post the results.

[mindspring]

Please see the output below.

Code: Select all

[root]@nagiosxi.sam.cpt ~] $ /usr/local/nagios/libexec/check_mssql_server.py --help
Usage: check_mssql_server.py -H hostname -U user -P password -T table --mode

Options:
  -h, --help            show this help message and exit

  Required Options:
    -H HOSTNAME, --hostname=HOSTNAME
                        Specify MSSQL Server Address
    -U USER, --user=USER
                        Specify MSSQL User Name
    -P PASSWORD, --password=PASSWORD
                        Specify MSSQL Password

  Optional Connection Information:
    -I INSTANCE, --instance=INSTANCE
                        Specify instance
    -p PORT, --port=PORT
                        Specify port.

  Nagios Plugin Information:
    -w WARNING, --warning=WARNING
                        Specify warning range.
    -c CRITICAL, --critical=CRITICAL
                        Specify critical range.

  Mode Options:
    --pagelooks         Page Lookups Per Second
    --batchreq          Batch Requests / Sec
    --lockwaits         Lockwaits / Sec
    --pagereads         Page Reads / Sec
    --totalpages        Total Pages (Cumulative)
    --readahead         Readahead Pages / Sec
    --pagewrites        Page Writes / Sec
    --stolenpages       Stolen Pages
    --targetpages       Target Pages
    --fullscans         Full Scans / Sec
    --locktimeouts      Lock Timeouts / Sec
    --test              Run tests of all queries against the database.
    --freepages         Free Pages (Cumulative)
    --bufferhitratio    Buffer Cache Hit Ratio
    --averagewait       Average Wait Time (ms)
    --time2connect      Time to connect to the database.
    --checkpoints       Checkpoint Pages / Sec
    --lockrequests      Lock Requests / Sec
    --pagelife          Page Life Expectancy
    --databasepages     Database Pages
    --deadlocks         Deadlocks / Sec
    --cachehit          Cache Hit Ratio
    --pagesplits        Page Splits / Sec
    --lockwait          Lock Wait Time (ms)
    --lazywrites        Lazy Writes / Sec
    --sqlcompilations   SQL Compilations / Sec

I still haven't fixed the main issue but on the plus side we managed to figure out that special characters in passwords caused the below issue. We removed the special characters from the password and that fixed some of the issues.

er2.PNG

So any probes that were giving this error above now has the same unscriptable error.

I still need to try some more of your suggestions.

[mindspring]

I tried both instance and port with the same results. See below

Code: Select all

[root]@nagiosxi.sam.cpt ~] $ /usr/local/nagios/libexec/check_mssql_server.py -H  hostname -U 'uuuuuuuuuuuu' -P 'zzzz'  -I 'DB'  --lockwait --warning 2000 --         
Traceback (most recent call last):
  File "/usr/local/nagios/libexec/check_mssql_server.py", line 469, in <module>
    main()
  File "/usr/local/nagios/libexec/check_mssql_server.py", line 435, in main
    execute_query(mssql, options, host)
  File "/usr/local/nagios/libexec/check_mssql_server.py", line 448, in execute_query
    mssql_query.do(mssql)
  File "/usr/local/nagios/libexec/check_mssql_server.py", line 284, in do
    self.run_on_connection(connection)
  File "/usr/local/nagios/libexec/check_mssql_server.py", line 271, in run_on_connection
    self.query_result = cur.fetchone()[0]
TypeError: 'NoneType' object is unsubscriptable

Code: Select all

[root]@nagiosxi.sam.cpt ~] $ /usr/local/nagios/libexec/check_mssql_server.py -H hostname -U 'uuuuuuuuuuuu' -P 'zzzz' -p 1433  --lockwait --warning 2000 --        
Traceback (most recent call last):
  File "/usr/local/nagios/libexec/check_mssql_server.py", line 469, in <module>
    main()
  File "/usr/local/nagios/libexec/check_mssql_server.py", line 435, in main
    execute_query(mssql, options, host)
  File "/usr/local/nagios/libexec/check_mssql_server.py", line 448, in execute_query
    mssql_query.do(mssql)
  File "/usr/local/nagios/libexec/check_mssql_server.py", line 284, in do
    self.run_on_connection(connection)
  File "/usr/local/nagios/libexec/check_mssql_server.py", line 271, in run_on_connection
    self.query_result = cur.fetchone()[0]
TypeError: 'NoneType' object is unsubscriptable