Query to see the most current QOS data in the UIM database

Products

• DX Unified Infrastructure Management (Nimsoft / UIM)

Issue/Introduction

It is often helpful to see whether QOS is being inserted into the database and to see how old it is.  It is possible to use DrNimbus to look at current data, but this can take time and pulls QOS data from farther downstream than the database.  The table s_qos_snapshot keeps a running update of current QOS, but does not specify the probe, QOS, target, etc. of this data. The following query pulls the current information from s_qos_snapshot and correlates it with identifying information from s_qos_data.  This allows users to see whether the QOS exists in the database and to see when it was last inserted.

Environment

Release:
Component: UIMDEG

Resolution

-- This SQL Server query will show the most current QOS from any probe using data from s_qos_snapshot and s_qos_data. The following "ss." columns are from s_qos_snapshot and the "sd." columns are from s_qos_data.

SELECT ss.table_id, ss.sampletime, ss.samplevalue, sd.ci_metric_id, sd.qos, sd.target, sd.probe, sd.r_table
FROM S_QOS_SNAPSHOT ss
JOIN S_QOS_DATA sd on ss.table_id = sd.table_id

-- The following WHERE filter is an example which will limit the results of the above statements. This can be changed to reflect any of the columns and cells found in s_qos_data

--WHERE probe = '<probe name>' and target = '<target string>'

--Alternatively, you can filter by date/time and order by sampletime.

select
distinct(ccs.name),
sqd.origin,
sqd.robot,
sqd.probe,
sqd.source as QoS_Source,
ccs.name as QoS_Device_Name,
sqd.qos QoS_Name,
sqs.samplevalue,
sqs.sampletime,
ccs.OS_name,
ccs.os_version,
ccs.os_description
from S_QOS_DATA sqd
INNER JOIN CM_CONFIGURATION_ITEM_METRIC ccim on ccim.ci_metric_id = sqd.ci_metric_id
INNER JOIN CM_CONFIGURATION_ITEM cci on cci.ci_id = ccim.ci_id
INNER JOIN CM_DEVICE cmd on cmd.dev_id = cci.dev_id
INNER JOIN CM_COMPUTER_SYSTEM ccs on ccs.cs_id = cmd.cs_id
JOIN S_QOS_SNAPSHOT sqs with(nolock) on sqs.table_id = sqd.table_id
where sqs.sampletime > dateadd(day, -30, getdate())
--and ccs.os_description <> 'NULL'
order by sqs.sampletime ASC