Data base Alert - instance sample_server, long queries is not available for version - sqlserver probe

Products

• DX Unified Infrastructure Management (Nimsoft / UIM)

Issue/Introduction

SW01689\INST07, instance sample_server, long queries is not available for version

Environment

Release : 9.0.2

Component : UIM - SQLSERVER

Resolution

1 - Increase the profile timeout

2 - The user specified   in the database connection was incorrectly specified in those cases. It was  DOMAIN.xx.yy\username when it   should have been DOMAIN\username. The user was verified using the xp_logininfo sql command

This is the query used for long_queries: ( what is the results?)

l"SELECT r.session_id spid, s.host_process_id hostid, s.host_name, s.login_name loginname, s.program_name, (convert(dec(15,3),r.cpu_time))/1000 cpu_time, (convert(dec(15,3),r.total_elapsed_time))/1000 elapsed_time,   convert(varchar(3000),isNull(SUBSTRING (st.text,1,3000),'n/a')) as sql_text FROM sys.dm_exec_sessions s, sys.dm_exec_requests r CROSS APPLY sys.dm_exec_sql_text(r.sql_handle) as st where  s.session_id = r.session_id and r.status = 'running' and s.session_id <> @@SPID"

Can you please check if the user has the permission to run this query?

In older versions, the probe ignored the configured user and used the administrator user by default to establish the connection with the database, hence you did not see this problem. 

https://techdocs.broadcom.com/content/broadcom/techdocs/us/en/ca-enterprise-software/it-operations-management/ca-unified-infrastructure-management-probes/GA/alphabetical-probe-articles/sqlserver-sql-server-monitoring/sqlserver-sql-server-monitoring-release-notes.html#concept.dita_b49428046e49ef6a121825f32deba3fc96c0ff27_AccessPrerequisites

Just for confirmation can you please double check for the following permissions with the db admin to be sure we have that in place

 

Right-click on the table and go to Properties > Permissions Tab.
Select Add and browse for the user to grant permission.
Click OK.
Select the permission type under the Grant column. User is mapped for the required permission. User mapping is required for the following tables: 
master.sys.databases
 
master.dbo.sysperfinfo
 
msdb.dbo.sysjobsteps
 
msdb.dbo.sysjobs
 
msdb.dbo.syscategories
 
msdb.dbo.log_shipping_monitor_secondary
 
msdb.dbo.log_shipping_monitor_primary
 
msdb.dbo.sysjobhistory
 
.sys.database_files
 
.sys.partitions
 
.sys.allocation_units
 
.sys.internal_tables
 
.sys.filegroups