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.
UIM 20.x
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 |
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.