Page 1 of 2

Plugin to monitor MSSQL DB Mirroring- not working

PostPosted: Wed Jul 11, 2018 6:59 am
by sannatar16
Hello all,

I am trying to monitor MSSQL mirroring status from nagios core. When the mirror status is diconnected or suspended, it should give an alert critical.
I tried this plugin (https://exchange.nagios.org/directory/P ... ng/details) with sa account, It always gives Critical and it seems that it is not able to fetch the row. I used Dumptrace to see the logs, and found nothing, attached the log for reference.
Could anyone please help if you come across this or any other method to monitor mirror status.

I am also using check_mssql_health plugin so i have installed dbd::sybase and other dependent modules.

Thanks in advance.!!

Regards,
Sannatar.

Re: Plugin to monitor MSSQL DB Mirroring- not working

PostPosted: Wed Jul 11, 2018 2:50 pm
by scottwilkerson
Can you show the command you are running and the error? (ok to obscure the username)

Also note if you have special chars in the username/password you will likely have to wort to escape them

Re: Plugin to monitor MSSQL DB Mirroring- not working

PostPosted: Thu Jul 12, 2018 8:55 am
by sannatar16
Hi scott,

Thanks for replying.

I ran the query from Db and it is working fine, I ran Dumptrace also from the command line, it seems the query returns nothing. I always get Critical with no values.

In case if I give the wrong password I am getting "Adaptive server connection failed" So I think it is nothing to do with the special characters in password.

Regards,
sannatar

Re: Plugin to monitor MSSQL DB Mirroring- not working

PostPosted: Thu Jul 12, 2018 2:01 pm
by scottwilkerson
So it works from the command line, can you also run it from the command line as the nagios user?

Code: Select all
su nagios -c '/usr/local/nagios/libexec/check_dbmirroring.pl YOUR ARGS HERE'


And if that works can you show the command config in nagios and the service definition (replacing any sensitive information)

Re: Plugin to monitor MSSQL DB Mirroring- not working

PostPosted: Fri Jul 13, 2018 9:39 am
by sannatar16
Hi,
Please find the output of the command. It returns command not found.
I haven't created the command,services for this yet, as it is not working in command line itself.

Re: Plugin to monitor MSSQL DB Mirroring- not working

PostPosted: Fri Jul 13, 2018 10:19 am
by mcapra
Double check the command you've copied from @scottwilkerson and try it again. You're missing the full path to the plugin (presumably, /usr/local/nagios/libexec/check_dbmirroring.pl).

When you su to the nagios user, you may be executing commands from the path of that user's home directory. I would wager check_dbmirroring.pl doesn't exist in that directory, so the full path must be provided to the -c argument.

Re: Plugin to monitor MSSQL DB Mirroring- not working

PostPosted: Fri Jul 13, 2018 3:57 pm
by cdienger
Thanks for the assist, @mcapra!

Re: Plugin to monitor MSSQL DB Mirroring- not working

PostPosted: Mon Jul 16, 2018 6:36 am
by sannatar16
Sorry my bad. I have corrected the command using the full path.

Please find the command output. I ran the script on both Principal and mirrored servers using sa account. Initially i got util.pm error, I changed the lib path after that the output still says Critical.

[root@XXXXXX services]# su nagios -c "/usr/local/nagios/libexec/check_dbmirroring.pl -H 10.X.X.X -d XXXXXXX -u sa -p XXXXXXXXXX
Can't locate utils.pm in @INC (@INC contains: /usr/lib/nagios/plugins /usr/local/lib64/perl5 /usr/local/share/perl5 /usr/lib64/perl5/vendor_perl /usr/share/perl5/vendor_perl /usr/lib64/perl5 /usr/share/perl5 .) at /usr/local/nagios/libexec/check_dbmirroring.pl line 10.
BEGIN failed--compilation aborted at /usr/local/nagios/libexec/check_dbmirroring.pl line 10.
[root@XXXXXX services]# su nagios -c "/usr/local/nagios/libexec/check_dbmirroring.pl -H 10.X.X.Y -d XXXXXXX -u sa -p XXXXXXXXXX"
Can't locate utils.pm in @INC (@INC contains: /usr/lib/nagios/plugins /usr/local/lib64/perl5 /usr/local/share/perl5 /usr/lib64/perl5/vendor_perl /usr/share/perl5/vendor_perl /usr/lib64/perl5 /usr/share/perl5 .) at /usr/local/nagios/libexec/check_dbmirroring.pl line 10.
BEGIN failed--compilation aborted at /usr/local/nagios/libexec/check_dbmirroring.pl line 10.

After changing the lib path:

[root@XXXXXX libexec]# su nagios -c "/usr/local/nagios/libexec/check_dbmirroring.pl -H 10.X.X.X -d XXXXX -u sa -p XXXXXXXXX"
CRITICAL - Check your mirroring settings
[root@DXXXXXX libexec]# su nagios -c "/usr/local/nagios/libexec/check_dbmirroring.pl -H 10.X.X.Y -d XXXXX -u sa -p XXXXXXXXXX"
CRITICAL - Check your mirroring settings

Re: Plugin to monitor MSSQL DB Mirroring- not working

PostPosted: Tue Jul 17, 2018 7:51 am
by mcapra
No one here wrote this plugin, so it's tough to say what the original intention of the plugin was. All of the below is based on my own knowledge of Perl and TSQL.

Though this plugin was written against SQL Server 2005, I don't think the underlying sys tables haven't changed much though so that shouldn't matter.

All that plugin does is run this query:
Code: Select all
SELECT d.name, m.mirroring_role_desc, m.mirroring_state_desc
                       FROM sys.database_mirroring m
                       JOIN sys.databases d ON m.database_id = d.database_id
                       WHERE mirroring_state_desc IS NOT NULL AND name = '<the -d value>'


It then compares the role and state (m.mirroring_role_desc, m.mirroring_state_desc) of that query and checks to see if role is "PRINCIPAL" and state is "SYNCHRONIZED". If that query returns anything else, the plugin returns a CRITICAL state.

Troubleshooting probably starts with running that query yourself and seeing what it churns out.

Re: Plugin to monitor MSSQL DB Mirroring- not working

PostPosted: Tue Jul 17, 2018 9:50 am
by cdienger
Thanks for the input, @mcapra.

@sannatar16, run the query as suggested and let us know if there is anything further we can help with.