How to monitor azure paas db in nagios xi ?

This support forum board is for support questions relating to Nagios xi, our flagship commercial network monitoring solution.
bsanjay
Posts: 86
Joined: Mon Apr 29, 2019 9:38 am

How to monitor azure paas db in nagios xi ?

Post by bsanjay »

Hello Team,
I am trying to configure monitoring for azure paas db using "Monitor a MSSQL Server" monitoring wizard but checks are failing with below error ERROR: [run_check] CRITICAL: Could not connect to dblib:host=sqls-tmportal-dev-ass-5.database.windows.net:1433;dbname=sqls-tmportal-dev-ass-5.database.windows.net;charset=UTF8 as nagios (Exception: SQLSTATE[01002] Adaptive Server connection failed (sqls-tmportal-dev-ass-5.database.windows.net:1433) (severity 9)).

Please let me know if it is possible to monitor azure paas db with nagios xi and if possible then please share the steps or documents for the same.

Best Regards,
bsanjay
User avatar
pbroste
Posts: 1288
Joined: Tue Jun 01, 2021 1:27 pm

Re: How to monitor azure paas db in nagios xi ?

Post by pbroste »

Hello @bsanjay

Thanks for reaching out about Azure MySQL monitoring and would like to find out what Nagios xi version you are running?

Please run the follow and provide the results:

Code: Select all

nmap -Pn -p XXXX X.X.X.X
rpm -qa | grep php
uname -a
cat /etc/*release
/usr/local/nagios/libexec/check_mssql -H X.X.X.X --username 'yourusername' --password 'yourpassword' --database 'master' --port xxxx --query 'SELECT+COUNT%28%2A%29+FROM+sys.sysperfinfo' --decode --warning '50' --critical '200' --querywarning '50' --querycritical '200' --result 'Expected result'
See here as well:

https://support.nagios.com/kb/article/nagios-xi-mssql-wizards-adaptive-server-connection-failed-735.html

Thanks,
Perry
bsanjay
Posts: 86
Joined: Mon Apr 29, 2019 9:38 am

Re: How to monitor azure paas db in nagios xi ?

Post by bsanjay »

Hi pbroste,
Please find the output of requested commands,

[rbadmin@azlprdnagapp01 ~]$ nmap -Pn -p 1433 10.53.0.70



Starting Nmap 6.47 ( http://nmap.org ) at 2021-09-17 11:30 UTC

Nmap scan report for 10.53.0.70

Host is up (0.16s latency).

PORT STATE SERVICE

1433/tcp open ms-sql-s



Nmap done: 1 IP address (1 host up) scanned in 0.21 seconds

[rbadmin@azlprdnagapp01 ~]$ rpm -qa | grep php

php-5.4.16-42.el7.x86_64

php-process-5.4.16-42.el7.x86_64

php-pear-HTML-Template-IT-1.3.0-2.el5.noarch

php-common-5.4.16-42.el7.x86_64

php-xml-5.4.16-42.el7.x86_64

php-pecl-ssh2-0.12-1.el7.x86_64

php-imap-5.4.16-7.el7.x86_64

php-cli-5.4.16-42.el7.x86_64

php-pgsql-5.4.16-42.el7.x86_64

php-mbstring-5.4.16-42.el7.x86_64

php-ldap-5.4.16-42.el7.x86_64

php-devel-5.4.16-42.el7.x86_64

php-mysqlnd-5.4.16-42.el7.x86_64

php-snmp-5.4.16-42.el7.x86_64

php-gd-5.4.16-42.el7.x86_64

php-pdo-5.4.16-42.el7.x86_64

php-pear-1.9.4-21.el7.noarch

php-mssql-5.4.16-7.el7.x86_64

php-mcrypt-5.4.16-7.el7.x86_64

[rbadmin@azlprdnagapp01 ~]$ uname -a

Linux azlprdnagapp01.idc.rb.net 3.10.0-514.28.1.el7.x86_64 #1 SMP Fri Jul 7 12:58:19 EDT 2017 x86_64 x86_64 x86_64 GNU/Linux

[rbadmin@azlprdnagapp01 ~]$ cat /etc/*release

NAME="Red Hat Enterprise Linux Server"

VERSION="7.3 (Maipo)"

ID="rhel"

ID_LIKE="fedora"

VERSION_ID="7.3"

PRETTY_NAME="Red Hat Enterprise Linux"

ANSI_COLOR="0;31"

CPE_NAME="cpe:/o:redhat:enterprise_linux:7.3:GA:server"

HOME_URL="https://www.redhat.com/"

BUG_REPORT_URL="https://bugzilla.redhat.com/"



REDHAT_BUGZILLA_PRODUCT="Red Hat Enterprise Linux 7"

REDHAT_BUGZILLA_PRODUCT_VERSION=7.3

REDHAT_SUPPORT_PRODUCT="Red Hat Enterprise Linux"

REDHAT_SUPPORT_PRODUCT_VERSION="7.3"

Red Hat Enterprise Linux Server release 7.3 (Maipo)

Red Hat Enterprise Linux Server release 7.3 (Maipo)

Also, tried the check command you provided but still getting the same error message.

Are there any user permission settings that needs to keep in mind while creating user in azure pass db ?

Best Regards,
bsanjay
User avatar
pbroste
Posts: 1288
Joined: Tue Jun 01, 2021 1:27 pm

Re: How to monitor azure paas db in nagios xi ?

Post by pbroste »

Hello @bsanjay

Thanks for following up with the results, appears that the firewall is open on port 1433 and we are able to hit that.

Try this and let me know:

Code: Select all

/usr/local/nagios/libexec/check_mssql -H  --username '<username>' --password '<yourpassword>' --database 'nagiosql' --port 1433 --query 'SELECT+COUNT%28%2A%29+FROM+sys.sysperfinfo' --decode
The option to use -I '<yourmsdatabaseinstance>'[/code] which will override the port.

Please let me know the results,
Perry
bsanjay
Posts: 86
Joined: Mon Apr 29, 2019 9:38 am

Re: How to monitor azure paas db in nagios xi ?

Post by bsanjay »

Hi pbroste,
Please find the output of the check command as requested,
[root@nagapp01 bin]# /usr/local/nagios/libexec/check_mssql -H sqls-myportal-prod-ass-5.database.windows.net -l 'sqls-myortal-prod-ass-5.database.windows.net' --username 'rbadmin' --password '<pwd>' --database 'BBSEHealth' --query 'SELECT+COUNT%28%2A%29+FROM+sys.sysperfinfo' --decode -v
PHP Notice: Undefined variable: column_name in /usr/local/nagios/libexec/check_mssql on line 540
PHP Notice: Undefined variable: query_result in /usr/local/nagios/libexec/check_mssql on line 540
OK: Query duration=0.323386 seconds.|query_duration=0.323386s;; ''=;
Long Output Query Failed


Also, we checked the connectivity between nagios & azure paas db as shown below and it's successfully connecting,
[root@nagapp01 bin]# sqlcmd -S sqls-myportal-prod-ass-5.database.windows.net -U rbadmin -d BBSEHealth -Q "SELECT TOP 5 DBCode FROM dbo.MappingDB;"
Password:
DBCode
--------------------------------------------------
4101017
4101028
4101092
4101122
4101127


Also, when we are running the run check command from CCM , we are getting below error,
[root@nagapp01 bin]# /usr/local/nagios/libexec/check_mssql_server.php -H sqls-myportal-prod-ass-5.database.windows.net --checktype 'server' -U 'rbadmin' -P '<pwd>' --perftype default -p 1433 --mode autoparamattempts --warning 100 --critical 200 -v [/b]
NOTICE: [parse_specs] Adding verbosity... Original Log Level [WARNING], New Log Level [NOTICE] (1176)
ERROR: [run_check] CRITICAL: Could not connect to dblib:host=sqls-tmportal-prod-ass-5.database.windows.net:1433;dbname=master;charset=UTF8 as rbadmin (Exception: SQLSTATE[01002] Adaptive Server connection failed (sqls-tmportal-prod-ass-5.database.windows.net:1433) (severity 9))
CRITICAL: Could not connect to dblib:host=sqls-tmportal-prod-ass-5.database.windows.net:1433;dbname=master;charset=UTF8 as rbadmin (Exception: SQLSTATE[01002] Adaptive Server connection failed (sqls-tmportal-prod-ass-5.database.windows.net:1433) (severity 9)).


[/b]Please find the content of /etc/freetds.conf file on nagios server below,
# $Id: freetds.conf,v 1.12 2007-12-25 06:02:36 jklowden Exp $
#
# This file is installed by FreeTDS if no file by the same
# name is found in the installation directory.
#
# For information about the layout of this file and its settings,
# see the freetds.conf manpage "man freetds.conf".

# Global settings are overridden by those in a database
# server specific section
[global]
# TDS protocol version
tds version = 7.2
; tds version = 8.0

# Whether to write a TDSDUMP file for diagnostic purposes
# (setting this to /tmp is insecure on a multi-user system)
; dump file = /tmp/freetds.log
; debug flags = 0xffff

# Command and connection timeouts
; timeout = 10
; connect timeout = 10

# If you get out-of-memory errors, it may mean that your client
# is trying to allocate a huge buffer for a TEXT field.
# Try setting 'text size' to a more reasonable limit
text size = 64512

# A typical Sybase server
[egServer50]
host = symachine.domain.com
port = 5000
tds version = 5.0

# A typical Microsoft server
[egServer70]
host = ntmachine.domain.com
port = 1433
tds version = 7.0


Best Regards,
bsanjay
bsanjay
Posts: 86
Joined: Mon Apr 29, 2019 9:38 am

Re: How to monitor azure paas db in nagios xi ?

Post by bsanjay »

Hi pbroste,
Please find the output of the check command as requested. It was failing with same error while running the command without database instance name. But with database instance name it failed with different error,

[root@nagapp01 bin]# /usr/local/nagios/libexec/check_mssql -H sqls-myportal-prod-ass-5.database.windows.net -l 'sqls-myortal-prod-ass-5.database.windows.net' --username 'rbadmin' --password '<pwd>' --database 'BBSEHealth' --query 'SELECT+COUNT%28%2A%29+FROM+sys.sysperfinfo' --decode -v
PHP Notice: Undefined variable: column_name in /usr/local/nagios/libexec/check_mssql on line 540
PHP Notice: Undefined variable: query_result in /usr/local/nagios/libexec/check_mssql on line 540
OK: Query duration=0.323386 seconds.|query_duration=0.323386s;; ''=;
Long Output Query Failed


Also, we checked the connectivity between nagios & azure paas db as shown below and it's successfully connecting,
[root@nagapp01 bin]# sqlcmd -S sqls-myportal-prod-ass-5.database.windows.net -U rbadmin -d BBSEHealth -Q "SELECT TOP 5 DBCode FROM dbo.MappingDB;"
Password:
DBCode
--------------------------------------------------
4101017
4101028
4101092
4101122
4101127


Also, when we are running the run check command from CCM , we are getting below error,
[root@nagapp01 bin]# /usr/local/nagios/libexec/check_mssql_server.php -H sqls-myportal-prod-ass-5.database.windows.net --checktype 'server' -U 'rbadmin' -P '<pwd>' --perftype default -p 1433 --mode autoparamattempts --warning 100 --critical 200 -v [/b]
NOTICE: [parse_specs] Adding verbosity... Original Log Level [WARNING], New Log Level [NOTICE] (1176)
ERROR: [run_check] CRITICAL: Could not connect to dblib:host=sqls-tmportal-prod-ass-5.database.windows.net:1433;dbname=master;charset=UTF8 as rbadmin (Exception: SQLSTATE[01002] Adaptive Server connection failed (sqls-tmportal-prod-ass-5.database.windows.net:1433) (severity 9))
CRITICAL: Could not connect to dblib:host=sqls-tmportal-prod-ass-5.database.windows.net:1433;dbname=master;charset=UTF8 as rbadmin (Exception: SQLSTATE[01002] Adaptive Server connection failed (sqls-tmportal-prod-ass-5.database.windows.net:1433) (severity 9)).


[/b]
Please find the content of /etc/freetds.conf file on nagios server below,
# $Id: freetds.conf,v 1.12 2007-12-25 06:02:36 jklowden Exp $
#
# This file is installed by FreeTDS if no file by the same
# name is found in the installation directory.
#
# For information about the layout of this file and its settings,
# see the freetds.conf manpage "man freetds.conf".

# Global settings are overridden by those in a database
# server specific section
[global]
# TDS protocol version
tds version = 7.2
; tds version = 8.0

# Whether to write a TDSDUMP file for diagnostic purposes
# (setting this to /tmp is insecure on a multi-user system)
; dump file = /tmp/freetds.log
; debug flags = 0xffff

# Command and connection timeouts
; timeout = 10
; connect timeout = 10

# If you get out-of-memory errors, it may mean that your client
# is trying to allocate a huge buffer for a TEXT field.
# Try setting 'text size' to a more reasonable limit
text size = 64512

# A typical Sybase server
[egServer50]
host = symachine.domain.com
port = 5000
tds version = 5.0

# A typical Microsoft server
[egServer70]
host = ntmachine.domain.com
port = 1433
tds version = 7.0


Best Regards,
bsanjay
User avatar
pbroste
Posts: 1288
Joined: Tue Jun 01, 2021 1:27 pm

Re: How to monitor azure paas db in nagios xi ?

Post by pbroste »

Hello @bsanjay

Thanks for following up with the results and additional details.

Let's try port number only so that we are not relying upon the --instance or --instancename to see if it will connect.
""From man-page""
-I, --instance Optional: MSSQL Instance. (Overrides port)
-p, --port Optional: MSSQL server port. (Default is 1433)
-i, --instancename Optional: specify the instance_name value, from the perf table.
(When run in --checktype 'database', default is 'master')
Let us know the results,
Perry
bsanjay
Posts: 86
Joined: Mon Apr 29, 2019 9:38 am

Re: How to monitor azure paas db in nagios xi ?

Post by bsanjay »

Hi Pbroste,
we have been trying with port number since the beginning and the error was already shared in my very first post. Please let me know if you need any other info related to this.

Best Regards,
bsanjay
bsanjay
Posts: 86
Joined: Mon Apr 29, 2019 9:38 am

Re: How to monitor azure paas db in nagios xi ?

Post by bsanjay »

Is there any dependency required for this plugin because we are able to connect the remote DB with sqlcmd command from cli (shared earlier). Please let us know if any dependency needs to be checked or any configuration file needs to be updated to make this work.

Thanks
User avatar
pbroste
Posts: 1288
Joined: Tue Jun 01, 2021 1:27 pm

Re: How to monitor azure paas db in nagios xi ?

Post by pbroste »

Hello @bsanjay

You are correct that it is puzzling that the command sqlcmd command works but the check_mssql script will not function.

The 'check_mssql' is a PHP script. We don't believe that there are no additional dependencies required but want to check to see if things match up to what we see.

What version PHP:

Code: Select all

php -v
And some php information:

Code: Select all

php -i > /tmp/phpinfo.txt
Please send the results via [PM] private message so we can see if things match up.

Thanks,
Perry