In order to achieve this generically you need the ability to execute dynamic SQL. MSSQL has an exec() function where you can achieve this but it's not one query - it would be two at a minimum - one to create the sql string and one to execute the exec() function.
You might be able to do this with the XML functions in MSSQL too as there, you can pass a string to the xml function and it will execute the sql and return xml and then you can use the results.
Finally someone here wrote a script that generates views for each of the QOS - called qos_views_2.7.sql (or something similar)
The contents of a copy I have from 2017 is:
------------------------------------------------
--
-- QOS_Views SQL Script
--
-- Version: 2.7
-- Date 03 April 2012
--
-- Version history:
-- 1.0 Remko Dobber - This is the first release
-- 1.1 James Christensen - Added Origin field for better multi-tenancy support
-- 1.2 Adrian Spittles - Fixed issue that if the QOS name had a '-' in it the View would fail to create
-- 1.3 Adrian Spittles - Added Robot and Probe fields for backward compatibility support with QOS_Server probe
-- 1.4 Adrian Spittles - Fixed issue that if the QOS name had a '_' in it the View would fail to create
-- 1.5 Adrian Spittles - Fixed issue that if the QOS name had a '(' or a ')' in it the View would fail to create
-- 1.6 Adrian Spittles - Added Error trapping for Drop statement for Views that Don't Exist
-- 1.7 Adrian Spittles - Added Error trapping to catch RN tables that don't have samplestdev & samplerate fields
-- 1.8 James Christensen - Fixed issue that if the QOS name had a '%' or a ':' or a '.' in it the View would fail to create
-- 1.9 Adrian Spittles - Removed compressed and inserttime fields from the View, for NIS DB V4.8 or above, these are no longer avialable in NMS 5.60
-- 2.0 Adrian Spittles - Changed all Select Statement to be Non-Locking
-- 2.1 Adrian Spittles - Added 'samplemax' field into the view for NIS DB V4.8 or above & Error trapping to catch RN tables that don't have samplemax fields
-- 2.2 Adrian Spittles - Added 'samplemax' field into the view for NIS verisons prior to NIS DB V4.8
-- 2.3 Adrian Spittles - Added 'inserttime' alias for NIS DB V4.8 or above
-- 2.4 James Christensen - Added more meaningful print statements so that it can be determmined which create view try worked and changed the query to determine the NIS DB version
-- 2.5 James Christensen - Added some additional edits received from a customer (see the REPLACE staements). Also added the ability to get feedback from
-- the script when running it from the Service Level Manager SQL Query tool. NOTE: This does not seem to run from the UMP SLM portlet -> SQL Query tool.
-- 2.7 James Christensen - Replaced the individual character replacement blocks with REPLACE statements. Added the replacement of the following characters
-- (+,_-_,and __). Added the creation of a temporary table and the inserting of status messages for debugging. Added a final select from the S_QOS_DATA
-- table so the row count can be compared to the row count from the select from the temporary table to verify that all QOS objects were processed.
--
-- Description:
-- This sql procedure generates easy-to-use views in the NIS for accessing raw qos data.
-- The intention of this script is that is is used instead of the qos_server custom probe.
-- The Script creates views named V_qosname, for example V_QOS_CPU_USAGE.
--
-- **** Please note that this is not supported by Nimsoft Support. This Script is intended to
-- be used as is, feel free to modify for your own purpose. ****
--
-- Install Notes:
-- Check the name of your Nimsoft SLM database, it may be called NimbusSLM or NimsoftSLM
-- Modify the the first line of the Script "USE NimsoftSLM" to reflect the correct DB name.
--
-- Run the script in SQL Management Studio and it will create the views based on the current
-- contents of S_QOS_DATA.
--
-- The first time the script is run you will see a whole bunch of error similar to the one below,
-- this is normal as the views cannot be dropped as they have not been created before. Run the script
-- a second time and the errors should have gone away.
-- Error: "Cannot drop the view 'V_QOS_xxxxxxxxxx', because it does not exist..."
--
-- If new qos types are add added to the S_QOS_DATA table then this script will need to be rerun.
--
-- Usage Examples:
-- When you want to see the raw CPU data for a server called Apollo, use a query like this:
--
-- SELECT * FROM V_QOS_CPU_USAGE
-- WHERE source='Apollo'
--
-- To get the average, minimum and maximum values measured in the past hour, use this:
--
-- select AVG(samplevalue) as avg ,MIN(samplevalue) as min,MAX(samplevalue) as max
-- from V_QOS_CPU_USAGE
-- where sampletime>GETDATE()-'01:00:00'
-- order by 1 desc
--
-- Top 20 servers sorted by CPU Usage, assuming data is being collected every 5 mins
--
-- SELECT TOP (20) source, target, sampletime, samplevalue, samplestdev, samplerate
-- FROM V_QOS_CPU_USAGE
-- WHERE (sampletime > GETDATE() - '00:05:00') AND (source = target)
-- ORDER BY samplevalue DESC
--
------------------------------------------------
USE NimsoftSLM
GO
DECLARE @qos varchar(255), @pos int, @r_table varchar(255), @view varchar(255), @cview varchar(4096), @ver numeric (10,4), @msg varchar(255)
Create table view_temp (message varchar(255))
SELECT @ver = ModuleVersion FROM tbnVersion WITH (NOLOCK) where ModuleName = 'NIS_SLM'
DECLARE qos_cursor CURSOR FOR
SELECT DISTINCT qos,r_table FROM S_QOS_DATA WITH (NOLOCK)
ORDER BY 2
OPEN qos_cursor
-- Perform the first fetch.
FETCH NEXT FROM qos_cursor INTO @qos, @r_table
-- Check @@FETCH_STATUS to see if there are any more rows to fetch.
WHILE @@FETCH_STATUS = 0
BEGIN
-- Doctor up the QOS names removing and replacing characters that may cause issues
select @qos= REPLACE(@qos,'+','_PLUS_')
select @qos= REPLACE(@qos,'_-_','_')
select @qos= REPLACE(@qos,'__','_')
select @qos= REPLACE(@qos,'''','_')
select @qos= REPLACE(@qos,'(','_')
select @qos= REPLACE(@qos,')','_')
select @qos= REPLACE(@qos,'-','_')
select @qos= REPLACE(@qos,'%','')
select @qos= REPLACE(@qos,':','')
select @qos= REPLACE(@qos,'.','')
select @qos= REPLACE(@qos,'/','_')
-- Create view
SET @view = 'V_'+@qos
PRINT 'Working on view for ' +@qos
--Select 'Working on view for '+@qos
-- NMS V5.60 and above uses new DB version 4.800 and above. compressed & inserttime fields
-- are missing from this new format.
IF @ver < 4.8
BEGIN
SET @cview = 'CREATE VIEW '+@view+' AS '+
'SELECT Q.source,Q.target,Q.origin,Q.robot,Q.probe,D.sampletime,D.samplevalue,D.samplestdev,D.samplerate,D.samplemax,D.compressed,D.tz_offset,D.inserttime '+
'FROM S_QOS_DATA Q JOIN '+@r_table+' D WITH (NOLOCK) ON Q.table_id=D.table_id';
END
ELSE
BEGIN
SET @cview = 'CREATE VIEW '+@view+' AS '+
'SELECT Q.source,Q.target,Q.origin,Q.robot,Q.probe,D.sampletime,D.samplevalue,D.samplestdev,D.samplerate,D.samplemax,D.tz_offset,D.sampletime as inserttime '+
'FROM S_QOS_DATA Q JOIN '+@r_table+' D WITH (NOLOCK) ON Q.table_id=D.table_id';
END
BEGIN TRY
exec ('DROP VIEW '+@view)
END TRY
BEGIN CATCH
-- Suppress Error message for Views that don't exist
END CATCH
BEGIN TRY
-- Create the View, Catch tables that don't have samplemax field
exec (@cview);
PRINT 'Created View: ' + @view
Select @msg = 'Created View: ' + @view
Insert into view_temp VALUES (@msg)
END TRY
BEGIN CATCH
IF @ver < 4.8
BEGIN
SET @cview = 'CREATE VIEW '+@view+' AS '+
'SELECT Q.source,Q.target,Q.origin,Q.robot,Q.probe,D.sampletime,D.samplevalue,D.samplestdev,D.samplerate,D.compressed,D.tz_offset,D.inserttime '+
'FROM S_QOS_DATA Q JOIN '+@r_table+' D WITH (NOLOCK) ON Q.table_id=D.table_id';
END
ELSE
BEGIN
SET @cview = 'CREATE VIEW '+@view+' AS '+
'SELECT Q.source,Q.target,Q.origin,Q.robot,Q.probe,D.sampletime,D.samplevalue,D.samplestdev,D.samplerate,D.tz_offset,D.sampletime as inserttime '+
'FROM S_QOS_DATA Q JOIN '+@r_table+' D WITH (NOLOCK) ON Q.table_id=D.table_id';
END
BEGIN TRY
-- Create the View, Catch tables that don't have samplestdev & samplerate fields
exec (@cview);
PRINT 'Created View without samplemax: ' + @view
Select @msg = 'Created View without samplemax: ' + @view
Insert into view_temp VALUES (@msg)
END TRY
BEGIN CATCH
SET @cview = 'CREATE VIEW '+@view+' AS '+
'SELECT Q.source,Q.target,Q.origin,Q.robot,Q.probe,D.sampletime,D.samplevalue,D.tz_offset,D.sampletime as inserttime '+
'FROM S_QOS_DATA Q JOIN '+@r_table+' D WITH (NOLOCK) ON Q.table_id=D.table_id';
exec (@cview);
PRINT 'Created View without samplemax, samplestdev and samplerate: ' + @view
Select @msg = 'Created View without samplemax, samplestdev and samplerate: ' + @view
Insert into view_temp VALUES (@msg)
END CATCH
END CATCH
-- Useful Print statements for debuging this SQL procedure, just uncomment to use
-- PRINT @ver
-- PRINT 'qos: ' + @qos + ' ' + @r_table + ' ' + @view
-- PRINT @cview
-- This is executed as long as the previous fetch succeeds.
FETCH NEXT FROM qos_cursor INTO @qos, @r_table
END
CLOSE qos_cursor
DEALLOCATE qos_cursor
select * from view_temp
SELECT DISTINCT qos,r_table FROM S_QOS_DATA WITH (NOLOCK)
drop table view_temp
GO
Original Message:
Sent: 09-27-2021 02:10 AM
From: Steve B
Subject: Obtain QOS metrics from data via single query
Hi guys,
I've sumbled upon this knowledge page on how to extract QOS metrics directly from the database using two queries: How do I export data from UIM for QOS metrics for a time period?
Query one to find the r_table and table_id:
select source, target, r_table, table_id from S_QOS_DATA where qos='QOS_CPU_USAGE' and source ='asterix' and target = 'Total'
Second query utilising the above r_table and table_id numbers to obtain the series data:
select sampletime, samplevalue from RN_QOS_DATA_0014 where table_id = '9' order by sampletime DESC
Is there any way to get this in one query? I've spent hours googling and playing around with various methods to no avail.
Cheers