Querying the UIM database directly for metrics
Article ID: 373555
Updated On:
Products
Issue/Introduction
How can I query the UIM database directly to obtain the raw metric samples for a given QoS/Source/Target/Probe combination?
Environment
DX UIM - Any Version
SQL Database backend
Resolution
In order to query the data directly we have to first understand some basics about how metrics are stored in terms of the database structures and how to query for this.
Correlating metrics with specific devices is a complex operation and is beyond the scope of this document - but if you have the information about a specific device and metric you are looking for, the following information can be helpful.
The main table we are concerned with will be S_QOS_DATA, which contains the information for each metric and has the information we need to pull them out individually.
QOS/SOURCE/TARGET/PROBE are going to be the most important columns in this table and additionally we need to look up the "r_table" value which tells us where the raw data is (RN_QOS_DATA_####) as well as the "table_id" column which tells us which values in that RN table belong to each system.
So - first I will cover the sort of "easy way" to get at this data with a few example queries that will get you started.
You can do a query like this to get a list of all the metrics for the ntperf probe ordered by robot-
select qos,source,target,robot,r_table,table_id from S_QOS_DATA where probe = 'ntperf' order by robot asc;
Or if you want all the metrics from a specific robot you can narrow that down e.g.
select qos,source,target,robot,r_table,table_id from S_QOS_DATA where probe = 'ntperf' and robot = 'robotname' order by robot asc;
Obtain the qos, source, target, table_id, and r_table values for the host/metric combination you are interested in. The output will look as follows:
Suppose we want to get at the Page Faults data (not the delta) for samplehost2, we need RN_QOS_DATA_0010 and table_id 152.
So now we can construct a query based on whatever time range is needed for that metric - so for example suppose we need everything since July 22nd 2024 starting at midnight:
select sampletime,samplevalue from RN_QOS_DATA_0010 where table_id = 152
AND (sampletime > '2024-07-22 00:00:00')
ORDER BY sampletime asc;
If you want let's say, the last hour:
select sampletime,samplevalue from RN_QOS_DATA_0010 where table_id = 152
AND sampletime >= DATEADD(hour, -1, GETDATE()) order by sampletime asc;
Last day:
select sampletime,samplevalue from RN_QOS_DATA_0010 where table_id = 152
AND sampletime >= DATEADD(day, -1, GETDATE()) order by sampletime asc
Last week:
select sampletime,samplevalue from RN_QOS_DATA_0010 where table_id = 152
AND sampletime >= DATEADD(day, -7, GETDATE()) order by sampletime asc
Sample Output:
So those are the basics and should get you at least on the right path - now for something a little better than that we can try to use dynamic SQL to build a query.
The below is a script that you can use to put in any values and get back the data. It is prepopulated with the same values for the QoS from ntperf as in the above example. The basic idea is you put in the probe, QoS, target, and source (obtained from S_QOS_DATA) into the clause at the top, and you can alter the clause at the bottom in a similar way as the examples above to get the dates you are interested in.
DECLARE @r_table NVARCHAR(100);
DECLARE @table_id INT;
DECLARE @sql NVARCHAR(MAX);
-- Assign values to variables based on the QoS we want to look at
SELECT @r_table = r_table, @table_id = table_id
FROM S_QOS_DATA
WHERE qos = 'QOS_WIN_PERF'
AND source = 'samplehost2.exampledomain.com'
AND target = 'Page Faults'
AND probe = 'ntperf';
-- Construct the dynamic SQL query -- alter the below to adjust the time range needed
SET @sql = N'
SELECT sampletime,samplevalue
FROM ' + QUOTENAME(@r_table) + N'
WHERE table_id = ' + CAST(@table_id AS NVARCHAR(20)) + N'
AND sampletime >= DATEADD(hour, -1, GETDATE())
ORDER BY sampletime asc;';
-- Execute the dynamic SQL query
EXEC sp_executesql @sql;
Output:
Feedback
