How to link tables to display QOS data and alarms

Document ID : KB000034712
Last Modified Date : 11/10/2018
Show Technical Document Details
Introduction:

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 9.0 And earlier
UMP 9.0 and earlier
Instructions:

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.


 
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.

Sample Output:

r_tabletable_idci_metric_idrobotsourceqostargetorigin
RN_QOS_DATA_00193725M8548CD0B25C27130396F9D2DF7FD8A55howeu01-e10496_ADhoweu01-E10496.UIMAD.UIMLAB.COMQOS_MEMORY_PHYSICAL_PERChoweu01-E10496.UIMAD.UIMLAB.COMPrimary_hub
RN_QOS_DATA_00193736MA164877923959122594FE973E1D6A93Bhoweu01-e10497_exch_2010SP3howeu01-E10497.UIMAD.UIMLAB.COMQOS_MEMORY_PHYSICAL_PERChoweu01-E10497.UIMAD.UIMLAB.COMPrimary_hub
RN_QOS_DATA_00194956M923A2BFED5CFE1119071E60F4A8A80D5howeu01-E17833howeu01-E17833QOS_MEMORY_PHYSICAL_PERChoweu01-E17833Primary_hub
RN_QOS_DATA_00195007M4A9C4E6FA84AEDBDD48F611B0CCDBB1BPrimaryUMPhoweu01-E17834QOS_MEMORY_PHYSICAL_PERChoweu01-E17834Primary_hub
RN_QOS_DATA_00195043MD6A292AC8BEA141865454A0112D4ECEFPrimaryCABIhoweu01-E17835QOS_MEMORY_PHYSICAL_PERChoweu01-E17835Primary_hub
RN_QOS_DATA_00193715M887952FBDD0A26FD5BADBCA16E3D43CFhoweu01-e7975DBServerhoweu01-E7975QOS_MEMORY_PHYSICAL_PERChoweu01-E7975Primary_hub
RN_QOS_DATA_001981MBCD2D1CAD7B373D74ECEE101DE6D92CBPrimaryUIMPrimaryUIMQOS_MEMORY_PHYSICAL_PERChoweu01-F3747PrimaryUIM_hub
RN_QOS_DATA_00191990MA4F786277D7C41589FAEEF88B3751D83howeu01-s4801howeu01-S4801QOS_MEMORY_PHYSICAL_PERChoweu01-S4801Origin_override
RN_QOS_DATA_00192907MA4F786277D7C41589FAEEF88B3751D83howeu01-s4801howeu01-S4801.ca.comQOS_MEMORY_PHYSICAL_PERChoweu01-S4801RobotOrigin2
RN_QOS_DATA_00193779M5E32D9848DF52ED01332ACD5D68D4B42howeu01-t10688howeu01-T10688QOS_MEMORY_PHYSICAL_PERChoweu01-T10688Primary_hub
RN_QOS_DATA_00191949M8E42998C602C66441DB508A642AA4E40SecondaryUIMhoweu01-U156920QOS_MEMORY_PHYSICAL_PERChoweu01-U156920Origin_override
RN_QOS_DATA_00195509M71A888F8C6B0DB2E5BD897C48C2AF78Elodsun69elodsun69eQOS_MEMORY_PHYSICAL_PERCMemoryPrimary_hub



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 output:

sourceqostargetr_tabletable_idci_metric_idprobeoriginci_nameci_typeci_description
PrimaryUIMQOS_LOGMON_VARIABLE_TRANSACTIONSECONDStestFormat.watcher test.TransSecRN_QOS_DATA_05144389M7B1AE22737293021B2E79366D344D680logmonPrimaryUIM_hubtestFormat-watcher test-TransSec1.2.5System.Log.File
PrimaryUIMQOS_EVL_COUNTallerrorsRN_QOS_DATA_05224882M17C7323C33ECAD74561E400CA64F883CntevlPrimaryUIM_huballerrors1.2.1System.Log.WindowsEvents
PrimaryUIMQOS_EVL_COUNTLocal_Admin_Logon_AdministratorRN_QOS_DATA_05224883M40F36CCEF7619C1A4FB0C13A74E99239ntevlPrimaryUIM_hubLocal_Admin_Logon_Administrator1.2.1System.Log.WindowsEvents
PrimaryUIMQOS_EVL_COUNTLocal_Admin_Logon_HCL-AdminRN_QOS_DATA_05224884MFBA43748A54B393AEBB464464BD01503ntevlPrimaryUIM_hubLocal_Admin_Logon_HCL-Admin1.2.1System.Log.WindowsEvents
howeu01-E10496.UIMAD.UIMLAB.COMQOS_MEMORY_PAGINGhoweu01-E10496.UIMAD.UIMLAB.COMRN_QOS_DATA_00173723MAD48CA6CFF21C7DCE76421886785969DcdmPrimary_hubPaging1.6System.Memory
howeu01-E10497.UIMAD.UIMLAB.COMQOS_MEMORY_PAGINGhoweu01-E10497.UIMAD.UIMLAB.COMRN_QOS_DATA_00173734M4D405DA55CDF9582A515639A05E3A7E4cdmPrimary_hubPaging1.6System.Memory
howeu01-E17833QOS_MEMORY_PAGINGhoweu01-E17833RN_QOS_DATA_00174954M9324BF35835446D1A6F9266440AB8B2EcdmPrimary_hubPaging1.6System.Memory


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 Output:

sourceqostargetr_tabletable_idci_metric_idprobeoriginci_nameci_typeci_descriptionsampletimesamplevalueExpr1
howeu01-E10496.UIMAD.UIMLAB.COMQOS_CPU_USAGEhoweu01-E10496.UIMAD.UIMLAB.COMRN_QOS_DATA_00133659ME01C7A16BBCC05204B830F78D7BE1D4BcdmPrimary_hubTotal1.5System.CPU03:11.00.443659
howeu01-E10496.UIMAD.UIMLAB.COMQOS_CPU_USAGEhoweu01-E10496.UIMAD.UIMLAB.COMRN_QOS_DATA_00133659ME01C7A16BBCC05204B830F78D7BE1D4BcdmPrimary_hubTotal1.5System.CPU08:11.00.223659
howeu01-E10496.UIMAD.UIMLAB.COMQOS_CPU_USAGEhoweu01-E10496.UIMAD.UIMLAB.COMRN_QOS_DATA_00133659ME01C7A16BBCC05204B830F78D7BE1D4BcdmPrimary_hubTotal1.5System.CPU13:11.00.293659
howeu01-E10496.UIMAD.UIMLAB.COMQOS_CPU_USAGEhoweu01-E10496.UIMAD.UIMLAB.COMRN_QOS_DATA_00133659ME01C7A16BBCC05204B830F78D7BE1D4BcdmPrimary_hubTotal1.5System.CPU18:11.00.183659
howeu01-E10496.UIMAD.UIMLAB.COMQOS_CPU_USAGEhoweu01-E10496.UIMAD.UIMLAB.COMRN_QOS_DATA_00133659ME01C7A16BBCC05204B830F78D7BE1D4BcdmPrimary_hubTotal1.5System.CPU23:11.00.233659
howeu01-E10496.UIMAD.UIMLAB.COMQOS_CPU_USAGEhoweu01-E10496.UIMAD.UIMLAB.COMRN_QOS_DATA_00133659ME01C7A16BBCC05204B830F78D7BE1D4BcdmPrimary_hubTotal1.5System.CPU28:11.00.243659
howeu01-E10496.UIMAD.UIMLAB.COMQOS_CPU_USAGEhoweu01-E10496.UIMAD.UIMLAB.COMRN_QOS_DATA_00133659ME01C7A16BBCC05204B830F78D7BE1D4BcdmPrimary_hubTotal1.5System.CPU33:11.00.353659
howeu01-E10496.UIMAD.UIMLAB.COMQOS_CPU_USAGEhoweu01-E10496.UIMAD.UIMLAB.COMRN_QOS_DATA_00133659ME01C7A16BBCC05204B830F78D7BE1D4BcdmPrimary_hubTotal1.5System.CPU38:11.00.243659
howeu01-E10496.UIMAD.UIMLAB.COMQOS_CPU_USAGEhoweu01-E10496.UIMAD.UIMLAB.COMRN_QOS_DATA_00133659ME01C7A16BBCC05204B830F78D7BE1D4BcdmPrimary_hubTotal1.5System.CPU43:11.00.283659
howeu01-E10496.UIMAD.UIMLAB.COMQOS_CPU_USAGEhoweu01-E10496.UIMAD.UIMLAB.COMRN_QOS_DATA_00133659ME01C7A16BBCC05204B830F78D7BE1D4BcdmPrimary_hubTotal1.5System.CPU48:11.00.233659
howeu01-E10496.UIMAD.UIMLAB.COMQOS_CPU_USAGEhoweu01-E10496.UIMAD.UIMLAB.COMRN_QOS_DATA_00133659ME01C7A16BBCC05204B830F78D7BE1D4BcdmPrimary_hubTotal1.5System.CPU53:11.00.263659



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.

Example Output:

namedomainoriginipnimidtime_origintime_arrivalarrivallevelseveritymessagesubsyssourcehostnameuser_tag1user_tag2
howeu01-E17833NULLPrimary_hub10.162.36.77YE88614082-0286735:29.035:30.01.54E+094majorAffiliatedDC-GAIA files: Execute dsi-newco.bat to send pending files 1 files, expected < 1Probe10.162.36.77howeu01-E17833NULLNULL
howeu01-E17833NULLPrimary_hub10.162.36.77YE88614082-0289635:41.035:41.01.54E+091informationcdm.exe: Process cdm.exe is running with process id = 7788.Process10.162.36.77howeu01-E17833NULLNULL
PrimaryUMPNULLPrimary_hub10.162.36.43YE88614082-3358603:04.003:06.01.54E+092warningAverage (3 samples) memory usage is now 84%, which is above the warning threshold (70%)Memory10.162.36.43PrimaryUMPNULLNULL
howeu01-E17833NULLPrimary_hub10.162.36.77YE88614082-2663041:11.041:12.01.54E+092warningAverage (3 samples) memory usage is now 78%, which is above the warning threshold (70%)Memory10.162.36.77howeu01-E17833NULLNULL
howeu01-E17833NULLPrimary_hub10.162.36.77YE88614082-3130221:11.021:12.01.54E+094majorAverage (3 samples) physical memory usage is now 95%, which is above the error threshold (95%)Memory10.162.36.77howeu01-E17833NULLNULL
howeu01-E17833NULLPrimary_hub10.162.36.77YE88614082-3467351:11.051:11.01.54E+093minorAverage (3 samples) paging is now 473.00 KB/sec, which is above the error threshold (400 KB/sec)Memory10.162.36.77howeu01-E17833NULLNULL