SQL Error

[sneha.irali]

Hi Team,

While I enable the Longest transaction parameter I get below error:

What would cause this error, how do we remediate it.

[nagios@SPSLNAGIOS ~]$ /usr/local/nagios/libexec/check_mssql_server.php -H 192.168.16.104 --checktype 'server' -U 'hpmon' -P 'Password@789' --perftype default -p 8043 --mode longesttrans --warning 5 --critical 10
ERROR: [MSSQLQuery::run_on_connection] Query (longesttrans) 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='Longest Transaction Running Time' AND instance_name='';] (2175)
ERROR: [MSSQLQuery::run_on_connection] SQLSTATE[HY000]: General error: 20018 The user does not have permission to perform this action. [20018] (severity 16) [(null)] (2176)
CRITICAL: longesttrans query failed. This could be caused by your sysperfinfo not containing the proper entries for this query, and you may need to delete this service check.

[gsmith]

Hi,

The first thing to look at is:

ERROR: [MSSQLQuery::run_on_connection] SQLSTATE[HY000]: General error: 20018 The user does not have permission to perform this action. [20018] (severity 16) [(null)] (2176)

Does the user running the query have access to all the tables and permissions to use all the options required by the query?

You could try logging in as root and running the query.

Let me know what you find out.

Thanks

[sneha.irali]

Hi,

As checked with SQL Admin, the privilages are appropriate ( also compared with the working DB) both are same.

Attached the snap for reference.

What would be the next possible thing to check upon.

I would also like to know if SQL DB 2008R2 is compatible with Nagios or no..?

[gsmith]

Hi

Looks good, thanks for checking. I am not sure if SQL DB 2000R2 is compatible
with Nagios in terms of being monitored by Nagios.

The perftype you have chosen is "default" which looks in the sys.dm_os_performance_counters table found in SQL Server 2008+

You can see this in the error message from the plugin as well:
[SELECT cntr_value as value, DATEDIFF(SECOND, '1970-01-01', GETUTCDATE()) as utctimestamp FROM sys.dm_os_performance_counters WHERE counter_name='Longest Transaction Running Time' AND instance_name='';

Please see what happens when you remove the perftype option entirely.

Thanks

[sneha.irali]

Please see what happens when you remove the perftype option entirely. - how do I do this..?

[gsmith]

Hi,

Sorry about that.

Change:
/usr/local/nagios/libexec/check_mssql_server.php -H 192.168.16.104 --checktype 'server' -U 'hpmon' -P 'Password@789' --perftype default -p 8043 --mode longesttrans --warning 5 --critical 10

to:
/usr/local/nagios/libexec/check_mssql_server.php -H 192.168.16.104 --checktype 'server' -U 'hpmon' -P 'Password@789' -p 8043 --mode longesttrans --warning 5 --critical 10

Let me know what the results are please.

Thanks

[sneha.irali]

Error remains same:

[root@SPSLNAGIOS libexec]# ./check_mssql_server.php -H 192.168.16.104 --checktype 'server' -U 'hpmon' -P 'Password@789' -p 8043 --mode longesttrans --warning 5 --critical 10
ERROR: [MSSQLQuery::run_on_connection] Query (longesttrans) 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='Longest Transaction Running Time' AND instance_name='';] (2175)
ERROR: [MSSQLQuery::run_on_connection] SQLSTATE[HY000]: General error: 20018 The user does not have permission to perform this action. [20018] (severity 16) [(null)] (2176)
CRITICAL: longesttrans query failed. This could be caused by your sysperfinfo not containing the proper entries for this query, and you may need to delete this service check.
[root@SPSLNAGIOS libexec]#

[gsmith]

Hi,

Please give this command a try:

Code: Select all

/usr/local/nagios/libexec/check_mssql_server.php -H 192.168.16.104 --checktype 'server' -U 'hpmon' -P 'Password@789' --perftype deprecated -p 8043 --mode longesttrans --warning 5 --critical 10

Let me know how that works for you.

Thanks

[sneha.irali]

Hi gsmith,

its still the same:

[root@SPSLNAGIOS ~]# /usr/local/nagios/libexec/check_mssql_server.php -H 192.168.16.104 --checktype 'server' -U 'hpmon' -P 'Password@789' --perftype deprecated -p 8043 --mode longesttrans --warning 5 --critical 10
ERROR: [MSSQLQuery::run_on_connection] Query (longesttrans) Failed!! No Results! returnCode [] query [SELECT cntr_value as value, DATEDIFF(SECOND, '1970-01-01', GETUTCDATE()) as utctimestamp FROM sys.sysperfinfo WHERE counter_name='Longest Transaction Running Time' AND instance_name='';] (2175)
ERROR: [MSSQLQuery::run_on_connection] SQLSTATE[HY000]: General error: 20018 The user does not have permission to perform this action. [20018] (severity 16) [(null)] (2176)
CRITICAL: longesttrans query failed. This could be caused by your sysperfinfo not containing the proper entries for this query, and you may need to delete this service check.

[gsmith]

hi


I'd like to verify which table is being used for the system performance issue so please do the following:

login to mssql as user hpmon and password Password@789
then run:

Code: Select all

select count(*) from sys.sysperfinfo;
select count(*) from sys.dm_os_performance_counters

Thanks