Page 1 of 3
Nagios SQL Plugin
Posted: Mon Aug 28, 2017 8:14 am
by 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
Re: Nagios SQL Plugin
Posted: Mon Aug 28, 2017 8:22 am
by 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.
Re: Nagios SQL Plugin
Posted: Mon Aug 28, 2017 9:52 am
by 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
Re: Nagios SQL Plugin
Posted: Mon Aug 28, 2017 1:48 pm
by 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.
Re: Nagios SQL Plugin
Posted: Tue Aug 29, 2017 3:59 am
by 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?
Re: Nagios SQL Plugin
Posted: Tue Aug 29, 2017 10:00 am
by 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.
Re: Nagios SQL Plugin
Posted: Thu Aug 31, 2017 5:16 am
by 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?
Re: Nagios SQL Plugin
Posted: Thu Aug 31, 2017 8:41 am
by 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.
Re: Nagios SQL Plugin
Posted: Tue Oct 10, 2017 4:15 am
by 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.
Re: Nagios SQL Plugin
Posted: Tue Oct 10, 2017 4:27 am
by 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