How to link tables to display QOS data and alarms

Products

• DX Unified Infrastructure Management (Nimsoft / UIM)

Issue/Introduction

This article will cover the most common tables used for reporting QOS information and Alarms.
When using external reporting tools that are connecting directly to the database it is helpful to have information about the tables and how they connect.

Environment

UIM 20.x

Resolution

NOTE:
UIM does not provide a complete database schema as this changes often.

Table Name	Description	Link Fields
CM_COMPUTER_SYSTEM	Main host system table	cs_id
CM_DEVICE	Device / Probe identification table	cs_id,dev_id
NAS_ALARMS	Current open alarms	dev_id,
cm_configuration_item	Relational table for config items	dev_id,ci_metric_id,ci_type
cm_configuration_item_metric	Relational table for config items	dev_id,ci_metric_id,
s_qos_data	QOS Header table	ci_metric_id,table_id
cm_configuration_item_definition	Configuration Item Definitions	ci_type
RN_QOS_DATA_XXXX	QOS metric Data	table_id

Note:
The RN table numbers will be different for each client depending on the amount of QOS gathered.
The S_QOS_DATA tables list the RN table that the detail is stored in, this is stored in the r_table field but these two tables are linked on the table_id.

 

Additional Information

 

Procedure

Below are some sample queries to return data:

Example 1:
SELECT sqd.r_table,
sqd.table_id,
sqd.ci_metric_id,
sqd.robot,
sqd.source,
sqd.qos,
sqd.target,
sqd.origin
FROM cm_computer_system s,
cm_device d,
cm_configuration_item ci,
cm_configuration_item_metric cim,
s_qos_data sqd
WHERE s.cs_id = d.cs_id
AND d.dev_id = ci.dev_id
AND ci.ci_id = cim.ci_id
AND cim.ci_metric_id = sqd.ci_metric_id
AND ( sqd.probe = 'cdm' OR
sqd.probe = 'rsp' OR
sqd.probe = 'net_connect' )
AND sqd.qos = 'QOS_MEMORY_PHYSICAL_PERC'

NOTE:
These query shows the header infomration for the net_connect probe and the QOS QOS_MEMORY_PHYSICAL_PERC that is being collected and from what systems.

example 2:
SELECT qd.source, qd.qos, qd.target, qd.r_table, qd.table_id, qd.ci_metric_id, qd.probe, qd.origin, ci.ci_name, cid.ci_type, cid.ci_description
FROM cm_configuration_item_definition cid,
cm_configuration_item_metric cim,
s_qos_data qd,
cm_configuration_item ci,
cm_device d,
cm_computer_system cs
WHERE cs.cs_id = d.cs_id
AND d.dev_id = ci.dev_id
AND cim.ci_metric_id = qd.ci_metric_id
AND ci.ci_type = cid.ci_type
AND ci.ci_id = cim.ci_id
ORDER BY cid.ci_description,
qd.probe,
qd.qos,
qd.target,
qd.source

NOTE:
This returns a full list of header information on all QOS being store in the system

Example 3:
SELECT qd.source, qd.qos, qd.target, qd.r_table, qd.table_id, qd.ci_metric_id, qd.probe, qd.origin, ci.ci_name, cid.ci_type, cid.ci_description, rn15.sampletime,
rn15.samplevalue, rn15.table_id AS Expr1
FROM CM_COMPUTER_SYSTEM AS cs INNER JOIN
CM_DEVICE AS d ON cs.cs_id = d.cs_id INNER JOIN
CM_CONFIGURATION_ITEM AS ci ON d.dev_id = ci.dev_id INNER JOIN
CM_CONFIGURATION_ITEM_DEFINITION AS cid ON ci.ci_type = cid.ci_type INNER JOIN
S_QOS_DATA AS qd INNER JOIN
CM_CONFIGURATION_ITEM_METRIC AS cim ON qd.ci_metric_id = cim.ci_metric_id ON ci.ci_id = cim.ci_id INNER JOIN
RN_QOS_DATA_0015 AS rn15 ON qd.table_id = rn15.table_id
WHERE (qd.qos = 'QOS_CPU_USAGE')
ORDER BY cid.ci_description, qd.probe, qd.qos, qd.target, qd.source

NOTE:
This query returns data for QOS call QOS_CPU_USAGE from DATA table 0015, RN_QOS_DATA_0015 AS rn15 returned from the above queries ( NOT SHOWN IN SAMPLE DATA)

Example 4:
SELECT cm.name, cm.domain, cm.origin, cm.ip, nas.nimid, nas.time_origin, nas.time_arrival, nas.arrival, nas.[level], nas.severity, nas.message, nas.subsys, nas.source,
nas.hostname, nas.user_tag1, nas.user_tag2
FROM CM_COMPUTER_SYSTEM AS cm INNER JOIN
CM_DEVICE AS dev ON cm.cs_id = dev.cs_id INNER JOIN
NAS_ALARMS AS nas ON dev.dev_id = nas.dev_id

NOTE:
Provide a listing of current open alarms and computer system information.