Support forum for Nagios Core, Nagios Plugins, NCPA, NRPE, NSCA, NDOUtils and more. Engage with the community of users including those using the open source solutions.
I am having a time trying to get this working. I have a MSSQL 2012 and 2016 server that I am trying to check by running some custom queries. One is for checking blocking sessions. Our DBA's have a few others but this is the first time we have been trying to use Nagios to replace IPSentry. If anyone has successful done that I'd love to hear from you.
Anyways, here is the query they provided but all I am getting back is UNKNOWN - Could not interpret server response.
SELECT db.name DBName, tl.request_session_id, wt.blocking_session_id, OBJECT_NAME(p.OBJECT_ID) BlockedObjectName, tl.resource_type, h1.TEXT AS RequestingText, h2.TEXT AS BlockingTest, tl.request_mode FROM sys.dm_tran_locks AS tl INNER JOIN sys.databases db ON db.database_id = tl.resource_database_id INNER JOIN sys.dm_os_waiting_tasks AS wt ON tl.lock_owner_address = wt.resource_address INNER JOIN sys.partitions AS p ON p.hobt_id = tl.resource_associated_entity_id INNER JOIN sys.dm_exec_connections ec1 ON ec1.session_id = tl.request_session_id INNER JOIN sys.dm_exec_connections ec2 ON ec2.session_id = wt.blocking_session_id CROSS APPLY sys.dm_exec_sql_text(ec1.most_recent_sql_handle) AS h1 CROSS APPLY sys.dm_exec_sql_text(ec2.most_recent_sql_handle) AS h2
Can check_mssql check custom queries? Or will I need to create a PowerShell script to run this and then have Nagios call the PowerShell script? Our DBA's said they probably have a few more custom queries they need to have monitored as well to ensure the MSSQL databases are running smoothly and not impacting anything.
check_mssql -H <server> -U '<domain\username>' -P '<password>' -p 4000 -t 60 -Q "SELECT db.name ,tl.request_session_id,wt.blocking_session_id,p.OBJECT_ID,tl.resource_type,h1.TEXT ,h2.TEXT ,tl.request_mode FROM sys.dm_tran_locks AS tl INNER JOIN sys.databases db ON db.database_id = tl.resource_database_id INNER JOIN sys.dm_os_waiting_tasks AS wt ON tl.lock_owner_address = wt.resource_address INNER JOIN sys.partitions AS p ON p.hobt_id = tl.resource_associated_entity_id INNER JOIN sys.dm_exec_connections ec1 ON ec1.session_id = tl.request_session_id INNER JOIN sys.dm_exec_connections ec2 ON ec2.session_id = wt.blocking_session_id CROSS APPLY sys.dm_exec_sql_text(ec1.most_recent_sql_handle) AS h1 CROSS APPLY sys.dm_exec_sql_text(ec2.most_recent_sql_handle) AS h2"
UNKNOWN - Could not interpret server response.
I tried a less complex one, keep in mind these all were provided by a DBA as I am not a SQL expert by any stretch.
check_mssql -H <server> -U '<domain\username>' -P '<password>' -p 4000 -t 60 -Q 'select * sysprocesses p1, sysprocesses p2 where p1.blocked > 0 and p2.spid = p1.blocked'
UNKNOWN - Could not interpret server response.
But this one did work but it doesn't really do anything thus wondering if I am able to run complex queries.
check_mssql -H <server> -U '<domain\username>' -P '<password>' -p 4000 -t 60 -Q 'select * from sysprocesses' OK - Query returned 1 rows. |nr_rows=1;50;100
I have also tried substituting the ' with " as well and still receive the same results. I was reading on another blog about using a .sql file and use the path of the .sql file to pass. It didn't really say clearly what check nor show any examples so I wasn't sure if it was the check_mssql plugin or a different one or if and how I can execute the .sql file. Do I place that on the Nagios server or modify the NSClient++ ini file to point to the .sql statement? Then if new .sql files are developed how does all that work. Thus, it would be nice if I could figure out a method to run the query directly from the Nagios server as I believe that would make everyone's life easier. Sometimes the DBA's can be a bit grouchy.
@jkinney, can you upload or link to the version of check_mssql you are using? Since anybody could name a plugin that, I want to be sure I am testing the correct plugin. At the moment, I do not have access to MSSQL, so it might be a while before I can actually test.
I was given these from a peer to use. Both are Perl scripts but the check_mssql_health I could get some values but unable to run against custom queries. (see attached)
But this one did work but it doesn't really do anything thus wondering if I am able to run complex queries.
check_mssql -H <server> -U '<domain\username>' -P '<password>' -p 4000 -t 60 -Q 'select * from sysprocesses' OK - Query returned 1 rows. |nr_rows=1;50;100
This is good, it's confirming you can contact the server and perform a custom query.
I tried a less complex one, keep in mind these all were provided by a DBA as I am not a SQL expert by any stretch.
check_mssql -H <server> -U '<domain\username>' -P '<password>' -p 4000 -t 60 -Q 'select * sysprocesses p1, sysprocesses p2 where p1.blocked > 0 and p2.spid = p1.blocked'
UNKNOWN - Could not interpret server response.
So I suspect one of the issues could be that this slightly more complicated query may be having issues with one of the characters. For example > might be treated as a redirect causing unintended issues. Because you know from the past test that custom queries are working, I feel like this is going to be a trial and error processing by making the query slightly more complicated each time. I think this is your best approach at the moment.
As of May 25th, 2018, all communications with Nagios Enterprises and its employees are covered under our new Privacy Policy.
It there a way I can escape the character so it is rendered as a > and not redirect? I thought if I quoted the query it would just execute the string as is but it appears that may not be the case.
