How to link tables to display QOS data and alarms

Document ID : KB000034712
Last Modified Date : 14/02/2018
Show Technical Document Details

Introduction

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.

This article will cover the most common tables used for reporting QOS information and Alarms.

NOTE:
UIM does not provide a complete database schema as this changes often.
Table NameDescriptionLink Fields
CM_COMPUTER_SYSTEMMain host system tablecs_id
CM_DEVICEDevice / Probe identification tablecs_id,dev_id
NAS_ALARMS Current open alarmsdev_id,
cm_configuration_itemRelational table for config itemsdev_id,ci_metric_id,ci_type
cm_configuration_item_metricRelational table for config itemsdev_id,ci_metric_id,
s_qos_dataQOS Header tableci_metric_id,table_id
cm_configuration_item_definitionConfiguration Item Definitionsci_type
RN_QOS_DATA_XXXXQOS metric Datatable_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.

?




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'


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

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

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



keywords:

external reports
data queries
qos
alarms
NAS
Metric
Metrics
Reporting
ODBC
Query