How to monitor azure paas db in nagios xi ?

[bsanjay]

Hi Pbroste,
We have upgraded the php to 7.4 and now nagios xi dashboard is working fine. We are facing an issue with mssql monitoring checks where few servers checks are working fine but Database checks are failing. I have attached the system profile, Check commands (working & not working) & azure pass db version. Please check and let us know if we are missing something,

Best Regards,
bsanjay

[pbroste]

Hello @bsanjay

Thanks for following up and providing the details. Want to stip down the commands that you sent over so that we are only passing what is required and add verbose as well. We will remove the Optional switches from the command:

Code: Select all

/usr/local/nagios/libexec/check_mssql_server.php --verbose -H yourhostaddresshere --checktype 'database' -U 'usernamehere' -P 'passwordhere' --instancename yourinstancenamehere --mode database --warning 10000 --critical 10000

Please also run the second command --mode time2connect with -vvv; so we can see the details on the results.

Thanks,
Perry

[bsanjay]

Hi pbroste,
Please find the requested check command output attached in PM.

Best Regards,
bsanjay

[pbroste]

Hello @bsanjay

Thanks for sending over the info, we see that the check is functioning all options specified are validated in debug. The 'time2connect' results are tested throughout the script.

Let's shake up the --warning and --critical numbers to see if 'time2connect' calculates different results:

Code: Select all

/usr/local/nagios/libexec/check_mssql_server.php -H yourhostaddresshere --checktype 'database' -U 'yourusernamehere' -P 'yourpasswordhere' --instancename 'yourinstancenamehere' -vvv --perftype default -p 1433 --mode time2connect --warning 30 --critical 60

1.035061 seconds does seem to be a long time to connect to your mssql database across the network.

Let's also have you run through a test:

Code: Select all

/usr/local/nagios/libexec/check_mssql_server.php -H yourhostaddresshere  --check type 'database' -U 'username' -P 'password' --instancename 'master' --perftype default -p 1433 --mode test --warning 10 --critical 20

Should see an example like this:

activetrans passed!
datasize passed!
logbytesflushed passed!
logcachehit passed!
logfilessize passed!
logfilesused passed!
logfileusage passed!
logflushes passed!
logflushwaits passed!
loggrowths passed!
logshrinks passed!
logtruncs passed!
logwait passed!
transpsec passed!
0/14 tests failed.

Thanks,
Perry

[bsanjay]

Hi pbroste,
Please find the check command output attached in PM. Looks like this is failing with query error, please do the needful.

Best Regards,
bsanjay

[pbroste]

Hello @bsanjay

Thanks for following up and sending over the results. On the one that did not work, we do see that it is complaining about the username and date/time.

ERROR: [MSSQLQuery::run_on_connection] Query (activetrans) Failed!! No Results! returnCode [] query [SELECT cntr_value as value, DATEDIFF(SECOND, '1970-01-01', GETUTCDATE()) as utctimestamp FROM sys.dm_os_performance_counters WHERE counter_name='Active Transactions' AND instance_name='xxxxx';] (2175)

Please verify that the system clock is synced across all instances:

Code: Select all

date
ls -l /etc/localtime
php -r 'echo date("D M j G:i:s T Y")."\n";'
grep "date.timezone =" /etc/php.ini
grep date.timezone /etc/php.ini
php -r "echo date_default_timezone_get();" #timezone
php -r " echo date('Y-m-d H:i:s');"        #date
php -r " echo gmdate('Y-m-d H:i:s');"      #time
mysql -h 127.0.0.1 -uroot -pnagiosxi -e 'SELECT NOW(); SELECT @@GLOBAL.time_zone, @@SESSION.time_zone;'

Next line permissions issue:

ERROR: [MSSQLQuery::run_on_connection] SQLSTATE[HY000]: General error: 20018 The user does not have permission to perform this action. [20018] (severity 16) [SELECT cntr_value as value, DATEDIFF(SECOND, '1970-01-01', GETUTCDATE()) as utctimestamp FROM sys.dm_os_performance_counters WHERE counter_name='Active Transactions' AND instance_name='xxxxxx';] (2176)

It is either the user account that is submitting the command or the -U "useraccountname' that is executing the command. Please running as root as test and also specify user by -U "yourhostnamehere\Yourusernamehere" on the 'check_mssql_server.php' command.

Also including my results from my test VM for your review to compare:

/usr/local/nagios/libexec/check_mssql_server.php -H 192.xxx.xxx.12 --checktype 'database' -U "localhostname\administrator" -P "XxXxXxXxX" --instancename mydatabaseinstance --mode time2connect --warning .30 --critical 1
OK: Time to connect was 0.013323s|time=0.013323s;.30;1;;

Results:

/usr/local/nagios/libexec/check_mssql_server.php -H 192.xxx.xxx.12 --checktype 'database' -U "localhostname\administrator" -P "XxXxXxXxX" --instancename mydatabaseinstance --mode time2connect --warning .30 --critical 1 -vvv
DEBUG: [parse_specs] Adding verbosity... Original Log Level [300], New Log Level [100] (1175)
NOTICE: [parse_specs] Adding verbosity... Original Log Level [WARNING], New Log Level [DEBUG] (1176)
DEBUG: [parse_specs] Options
array (
'checktype' => 'database',
'hostname' => '192.xxx.xxx.12',
'username' => 'localhostname\\administrator',
'password' => 'XxXxXxXxX',
'instancename' => 'myinstancename',
'warning' => '.30',
'critical' => '1',
'verbose' =>
array (
0 => false,
1 => false,
2 => false,
),
'mode' => 'time2connect',
) (1222)
DEBUG: [Check::__construct] (1483)
DEBUG: [Check::run_check] (1491)
DEBUG: [Check::run_check] MODES[time2connect]array (
'help' => 'Time to connect to the database.',
'stdout' => 'Time to connect was @results',
'label' => 'time',
'unit' => 's',
) (1494)
DEBUG: [Check::run_check] instancename [myinstancename] (1497)
INFO: [Check::run_check] Connecting to odbc db_dsn [odbc:Driver=FreeTDS;Server=192.xxx.xxx.12;Port=1433;dbname=brimstone;charset=UTF8] (1517)
INFO: [Check::run_check] Successful connecting to 192.xxx.xxx.12 [0.014073] (1550)
DEBUG: [Check::run_check] *** No Mode specified or time2Connect was specified: connectionTime [0.014073] state [OK] (1567)
DEBUG: [BaseQuery::__construct] (1952)
DEBUG: [BaseQuery::__construct] query [] options (Array) stdout [Time to connect was @results] label [time] unit modifier [] queryType [] (1961)
DEBUG: [BaseQuery::process_results] (1977)
DEBUG: [BaseQuery::process_results] input queryDuration [0.014073] options (Array) state [OK] outputMsg [] (1978)
DEBUG: [BaseQuery::check_nagios_threshold] (2056)
DEBUG: [BaseQuery::check_nagios_threshold] threshold [.30], value [0.014073] (2058)
DEBUG: [BaseQuery::check_nagios_threshold] start [0] > end [.30] (2081)
DEBUG: [BaseQuery::check_nagios_threshold] inside (2090)
DEBUG: [BaseQuery::process_results] Check Warning Threshold: exitCode [0 ] state [OK] outputMsg [self::] (2009)
DEBUG: [BaseQuery::check_nagios_threshold] (2056)
DEBUG: [BaseQuery::check_nagios_threshold] threshold [1], value [0.014073] (2058)
DEBUG: [BaseQuery::check_nagios_threshold] start [0] > end [1] (2081)
DEBUG: [BaseQuery::check_nagios_threshold] inside (2090)
DEBUG: [BaseQuery::process_results] Check Critical Threshold: exitCode [0 ] state [OK] outputMsg [self::] (2023)
DEBUG: [BaseQuery::process_results] mappedResult: [Time to connect was 0.014073s] stdout [Time to connect was @results] queryResult [0.014073] (2034)
INFO: [BaseQuery::process_results] outputMsg: [OK: Time to connect was 0.014073s|time=0.014073s;.30;1;;
] (2047)
DEBUG: [BaseQuery::process_results] @state [OK] @stdout [Time to connect was @results] @label [time] @result [Time to connect was 0.014073s] @unit @warning [.30] @critical [1] (2048)
DEBUG: [nagios_exit] (3011)
OK: Time to connect was 0.014073s|time=0.014073s;.30;1;;

What 'check_mssql_Server.php' version and php version:

Code: Select all

cat /usr/local/nagiosxi/var/xiversion | grep full
sudo cat /etc/os-release
 sudo uname -a
sudo hostnamectl
cat /etc/os-release
uname -a
 hostnamectl
php -v
yum repolist
 /usr/local/nagios/libexec/check_mssql_server.php -V

Please let me know the results,
Perry

[bsanjay]

Hi pbroste,
I did PM you the required output.

Best Regards,
bsanjay

[pbroste]

Hello @bsanjay

Thanks for following up, did you also verify user permissions?

It is either the user account that is submitting the command or the -U "useraccountname' that is executing the command. Please running as root as test and also specify user by -U "yourhostnamehere\Yourusernamehere" on the 'check_mssql_server.php' command.

Thanks,
Perry

[pbroste]

Hello @bsanjay

I see that we had a colleague test this out and post it and wanted to forward this to you. He stated that he had to add --perftype default to get it to function. See example:

/usr/local/nagios/libexec/check_mssql_server.php -H nagiostest.database.windows.net --checktype 'server' -U 'yourusername' -P 'XXXXXXXX' --perftype default -p 1433 --mode avglatchwait --warning 100 --critical 200
OK: Average Latch Wait Time is 0.00001653 ms|averagewait=0.00001653ms;100;200;;

Hope that this helps you out,
Perry