UIM How to get the queries used for OC.

Products

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

Issue/Introduction

This will show how to obtain the query used for populating a report or dashboard. 

Note: this is for MS SQL. 

Environment

Release : 20.3

Component : UIM - DATA_ENGINE

Resolution

The example used is for Reports > Network > URLs by Response Time. 
Have OC open and ready to select the report or dashboard. 
Open Microsoft SQL Server Management Studio > Tools > SQL Server Provider. 
In SQL Server Provider > File > New Trace > set to save. 
Click to open the report. 
After loaded In SQL Server Provider > File > Stop Trace. 
Find something to search on in SQL Server Provider, idealy a qos name. 
When selected the query shows up in the bottom and can be copied from there. 

In the example 'URLs by Response Time' there are two:
declare @p1 int
set @p1=11
exec sp_prepexec @p1 output,NULL,N'SELECT distinct r_table FROM s_qos_definition WHERE name = ''QOS_URL_RESPONSE'''
select @p1

declare @p1 int
set @p1=12
exec sp_prepexec @p1 output,NULL,N'SELECT 
  TOP 10 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(day, -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

In this case to be used as a query some clean up is necessary since the 'set' command will not be used.
It becomes one query:
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 10 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(day, -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