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

Products

• DX Unified Infrastructure Management (Nimsoft / UIM)
• CA Unified Infrastructure Management On-Premise (Nimsoft / UIM)
• CA Unified Infrastructure Management SaaS (Nimsoft / UIM)

Issue/Introduction

xxx, instance sample_server, long queries is not available for version

or

Profile <profile_name>, instance localhost, long queries is not available for version n/a

Environment

• Release: UIM 20x or later
• Component: UIM - SQLSERVER

Resolution

1. Increase the profile timeout

2. The user specified in the database connection was incorrectly specified.
     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 are the results when run manually?)

"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"

3. Please check if the user has 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. 

sqlserver probe - Access Prerequisites

    Double-check the following permissions with the db admin to be sure that they are 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