This will show how to obtain the query used for populating a report or dashboard.
Note: this is for MS SQL.
Release : 20.3
Component : UIM - DATA_ENGINE
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 Profiler.
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