UIM what is the query used for the 'URLs by Response Time' report.

Products

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

Issue/Introduction

What is the query used for the 'URLs by Response Time' report, found at Reports > Network? 

Environment

• Release: 20.4 or higher
• Component: UIM - DATA_ENGINE

Resolution

declare @p1 int
exec sp_prepexec @p1 output,NULL,N'SELECT distinct r_table FROM s_qos_definition WHERE name = ''QOS_URL_RESPONSE'''
select @p1
exec sp_prepexec @p1 output,NULL,N'SELECT 
  TOP 25 qosdata.source SOURCE, 
  qosdata.target target, 
  avg(measurement.samplevalue) as AVG, min(measurement.samplevalue) as MIN, max(measurement.samplevalue) as MAX 
FROM s_qos_data qosdata    LEFT OUTER JOIN VN_QOS_DATA_0080 measurement 
    ON qosdata.table_id = measurement.table_id 
WHERE qosdata.qos = ''QOS_URL_RESPONSE'' 
      AND measurement.sampletime BETWEEN dateadd(month, -1, getdate()) AND getdate() 
      AND qosdata.origin IN (select * from (select distinct sqd.origin from S_QOS_DATA sqd union select distinct ccso.origin from CM_COMPUTER_SYSTEM_ORIGIN ccso union select distinct na.origin from NAS_ALARMS na) alias) 
GROUP BY source, target 
ORDER BY AVG DESC'
select @p1