Tech Tip: UIM - How to determine if QOS data is being updated for all tables

Document created by danst04 Employee on Mar 13, 2017
Version 1Show Document
  • View in full screen mode

Document ID:  TEC1516536
Last Modified Date:  01/09/2017
Show Technical Document Details


How to determine if QOS data is being updated for all tables.


A given customer noticed that one their tables for a given probe only contained QOS data for the last 24 hours each and every day and this was unexpected. Therefore they requested a means to check the data in their tables.

- UIM 8.x

This will display a list of all QOS tables that have data for only the last 24 hours.

DECLARE @qos VARCHAR(255),         @sampletime DATETIME = NULL,         @source VARCHAR(255),         @sql NVARCHAR(255),         @table_id INT,         @target VARCHAR(255),         @v_table VARCHAR(255);DECLARE @qos_data TABLE (     table_id INT,     qos VARCHAR(255),     source VARCHAR(255),     target VARCHAR(255) );DECLARE CurQosList CURSOR READ_ONLY FAST_FORWARD FORSELECT sqs.table_id, qos, source, target, v_table FROM S_QOS_SNAPSHOT sqsINNER JOIN S_QOS_DATA sqd ON sqs.table_id = sqd.table_idWHERE sqs.sampletime > DATEADD(hh, -24, GETDATE())OPEN CurQosListFETCH NEXT FROM CurQosList INTO @table_id, @qos, @source, @target, @v_tableWHILE @@FETCH_STATUS = 0 BEGIN         SET @sql = 'SELECT TOP 1 @sampletime = sampletime FROM ' + @v_table + ' WHERE table_id = ' + CAST(@table_id AS VARCHAR(16)) + ' AND sampletime < DATEADD(hh, -24, GETDATE())';    EXEC sp_executesql @sql, N'@sampletime DATETIME OUTPUT', @sampletime = @sampletime OUTPUT;          IF @sampletime IS NULL BEGIN        INSERT INTO @qos_data (table_id, qos, source, target)         VALUES (@table_id, @qos, @source, @target);    END         SET @sampletime = NULL;         FETCH NEXT FROM CurQosList     INTO @table_id, @qos, @source, @target, @v_table ENDCLOSE CurQosListDEALLOCATE CurQosList SELECT * FROM @qos_data;
2 people found this helpful