Page 1 of 2
MSSQL check that will return query value as perf data
Posted: Thu Jun 19, 2014 9:17 am
by vAJ
I'm in need of a check to run a query against a MSSQL DB and alert/chart the value returned.
Several checks out there are great at showing response times, SQL stats, etc. I've attempted to modify the check_mssql, but I'm not having much success.
Any ideas to help me get this knocked out?
Re: MSSQL check that will return query value as perf data
Posted: Thu Jun 19, 2014 2:53 pm
by sreinhardt
Have you taken a look at the mssql query wizard that is included with XI by default? I guess I'm assuming that this is for your XI system, correct me if I am wrong there.
http://exchange.nagios.org/directory/Ad ... rd/details
Re: MSSQL check that will return query value as perf data
Posted: Thu Jun 19, 2014 3:24 pm
by vAJ
Yes, the XI wizard only charts/alerts the response time of the query. I'm looking to monitoring the integer value returned by the query. Response time in addition to that would be fine, similar to check_http where it shows time & size.
Re: MSSQL check that will return query value as perf data
Posted: Thu Jun 19, 2014 4:02 pm
by vAJ
So, maybe the Wizard changed with 2014 and I'm looking at something new here?
The query I need to run tells me how many records were written to given table in the last X minutes.
I want it to return a low value and warn if >15, critical >30. But I usually expect around 1-5.
The "expected result" field here in the wizard appears to want a higher expected value or it ignores it?
-Andrew
Re: MSSQL check that will return query value as perf data
Posted: Thu Jun 19, 2014 4:21 pm
by sreinhardt
Is it that the plugin is ignoring it, or the wizard is? Once you've gone through the wizard could you provide the command within the service definition that it is giving? I just want to be sure we are getting the same things.
Re: MSSQL check that will return query value as perf data
Posted: Thu Jun 19, 2014 4:21 pm
by vAJ
- Charts
I've got the query running successfully with the MS SQL Query wizard, but as you can see, it's only charting the response time...
Re: MSSQL check that will return query value as perf data
Posted: Thu Jun 19, 2014 4:23 pm
by vAJ
Code: Select all
COMMAND: /usr/local/nagios/libexec/check_mssql -H sqldbc1*********.com --username nagiossqluser --password '*************' --database DataCenters_Logs --port 1433 --query 'SELECT count\(\*\) 'PaymentProcessingErrors' FROM DataCenters_Logs.dbo.DataVaultErrorLog WITH \(NOLOCK\) WHERE ErrorTime \> DATEADD\(MI,-5,getdate\(\)\)' --result "1" --decode --warning 1 --critical 2 --querywarning 3 --querycritical 5 --result 0
Re: MSSQL check that will return query value as perf data
Posted: Thu Jun 19, 2014 4:26 pm
by vAJ
I can tell you that the wizard used double quotes instead of singles, also adds special chars in the spaces...
CCM won't run this as a test, but once it's committed, the engine runs it just fine.
Re: MSSQL check that will return query value as perf data
Posted: Fri Jun 20, 2014 11:07 am
by tmcdonald
Can you go to the Advanced tab of the service view and see what perfdata is being returned? If you want to graph the actual SQL result it will need to be in the perfdata.
Re: MSSQL check that will return query value as perf data
Posted: Fri Jun 20, 2014 1:33 pm
by vAJ
Code: Select all
Performance Data: query_duration=0.053464s;1;2;0;