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

Products

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

Issue/Introduction

A given customer noticed that one of 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.

Environment

• UIM - any version

Resolution

--This will display a list of all QOS tables that have data within/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 FOR SELECT sqs.table_id, qos, source, target, v_table FROM S_QOS_SNAPSHOT sqs 

INNER JOIN S_QOS_DATA sqd ON sqs.table_id = sqd.table_id WHERE sqs.sampletime > DATEADD(hh, -24, GETDATE())

OPEN CurQosList

FETCH NEXT FROM CurQosList INTO @table_id, @qos, @source, @target, @v_table WHILE @@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 END

CLOSE CurQosList

DEALLOCATE CurQosList

SELECT * FROM @qos_data;