Monitoring Governance Report queries

Products

• DX Unified Infrastructure Management (Nimsoft / UIM)
• CA Unified Infrastructure Management On-Premise (Nimsoft / UIM)
• CA Unified Infrastructure Management SaaS (Nimsoft / UIM)

Issue/Introduction

UIM Operator console monitoring governance report underlying queries can be used to confirm the MGR report results seen in the Operator Console user interface for QOS and Thresholds.

Environment

• DX UIM 23.4 CU4/CU5

Resolution

You can use the following queries to confirm/deny any missing data.

For the tabs "View by QoS" and "View by Threshold", here are the queries when no filters are selected in the UI and Monitoring Governance is selected as the main top-level group.

--View by QoS

  SELECT ccs.cs_id, ccs.name, monitoring_info.* 
 FROM 
 CM_COMPUTER_SYSTEM ccs LEFT JOIN
 (
     SELECT 
     qd.table_id, d.cs_id as devCsId, qd.r_table,
     qd.qos qosDataQos, qd.source qosDataSource, qd.target qosDataTarget,
     qd.robot qosDataRobot, qd.origin qosDataOrigin, qd.probe qosDataProbe,
     qd.samplerate ,sqs.sampletime, sqs.samplevalue, 
     (CASE WHEN (DATEDIFF(second,sqs.sampletime, CURRENT_TIMESTAMP)) <= (2*qd.samplerate + 300) THEN 1 ELSE 0 end) as isMonitored,
     ci.ci_name AS ciName 
     FROM CM_DEVICE d  WITH(NOLOCK)  
     JOIN CM_CONFIGURATION_ITEM ci  WITH(NOLOCK)  ON ci.dev_id = d.dev_id 
     JOIN CM_CONFIGURATION_ITEM_METRIC cim  WITH(NOLOCK)  ON cim.ci_id  = ci.ci_id 
     JOIN S_QOS_DATA qd  WITH(NOLOCK)  ON qd.ci_metric_id  = cim.ci_metric_id 
     LEFT JOIN S_QOS_SNAPSHOT sqs  WITH(NOLOCK)  ON sqs.table_id = qd.table_id 
 ) monitoring_info 
 ON ccs.cs_id = monitoring_info.devCsId 
 WHERE 1=1 
 and ccs.cs_id IN (SELECT cs_id from CM_GROUP_MEMBER) 
 and monitoring_info.isMonitored=1 
 order by monitoring_info.sampletime desc 
 

--View by Threshold

SELECT 
qosInfo.name, 
qosInfo.qosDataQos, qosInfo.qosDataSource, qosInfo.qosDataTarget,qosInfo.ciName,
policyInfo.policyMetricQos, policyInfo.policyMetricSource, policyInfo.policyMetricTarget,policyInfo.policyMetricCiName,
qosInfo.qosDataOrigin, qosInfo.qosDataProbe,  
policyInfo.policyId, policyInfo.policyName,
policyInfo.policyThresholdId, policyInfo.thresholdOperator, policyInfo.thresholdSeverity, policyInfo.thresholdValue, 
policyInfo.metricPrecedence, policyInfo.thresholdType
FROM
(
 SELECT ccs.cs_id, ccs.name, monitoring_info.* 
 FROM 
 CM_COMPUTER_SYSTEM ccs LEFT JOIN
 (
     SELECT 
     qd.table_id, d.cs_id as devCsId, qd.r_table,
     qd.qos qosDataQos, qd.source qosDataSource, qd.target qosDataTarget,
     qd.robot qosDataRobot, qd.origin qosDataOrigin, qd.probe qosDataProbe,
     ci.ci_name AS ciName 
     FROM CM_DEVICE d  WITH(NOLOCK)  
     JOIN CM_CONFIGURATION_ITEM ci  WITH(NOLOCK)  ON ci.dev_id = d.dev_id 
     JOIN CM_CONFIGURATION_ITEM_METRIC cim  WITH(NOLOCK)  ON cim.ci_id  = ci.ci_id 
     JOIN S_QOS_DATA qd  WITH(NOLOCK)  ON qd.ci_metric_id  = cim.ci_metric_id 
 ) monitoring_info 
 ON ccs.cs_id = monitoring_info.devCsId 
 WHERE 1=1 
 and ccs.cs_id IN (SELECT cs_id from CM_GROUP_MEMBER)

) qosInfo
LEFT JOIN
(
   SELECT
   policyThreshold.id as policyThresholdId, policyTarget.targetId as policyTargetId,policyTarget.name as policyTargetName, policyTarget.type as policyTargetType,
   ssrv2profile.cs_id as ssrv2CsId, ssrv2devicegroup.cm_group_id as ssrv2cmGroupId, 
   cmgrpMem.grp_id AS policyTargetGroup,cmgrpMem.cs_id AS policyTargetGroupTypeCsId,
   ssrv2Profile.profileId as ssrv2profileId,
   policy.id as policyId, policy.name as policyName,
   policyMetric.qos as policyMetricQos, policyMetric.source as policyMetricSource,policyMetric.target as policyMetricTarget,
   policyThreshold.operator thresholdOperator, policyThreshold.severity thresholdSeverity, policyThreshold.value thresholdValue,
   policyMetric.metricPrecedence, policyThreshold.type thresholdType,
   policyMetric.ciName AS policyMetricCiName,policyMetric.probeName,    monitoringsystemdevrobot.robot AS monitoringSystemRobot
   FROM PolicyThreshold policyThreshold  WITH(NOLOCK)  
   LEFT JOIN POLICYCONDITION policyCondition  WITH(NOLOCK)  ON policyCondition.id=policyThreshold.condition_id
   LEFT JOIN POLICYMETRIC policyMetric  WITH(NOLOCK)  ON policyMetric.id=policyCondition.policyMetric_id
   LEFT JOIN POLICY policy  WITH(NOLOCK)  ON policy.id=policyCondition.policy_id
   LEFT JOIN POLICYTARGET policyTarget  WITH(NOLOCK)  ON policyCondition.policyTarget_id=policyTarget.id
   LEFT JOIN SSRV2PROFILE ssrv2profile  WITH(NOLOCK)  ON (policyMetric.profileId=ssrv2profile.profileId and policyTarget.type='TECHNOLOGY')
   LEFT JOIN SSRV2DEVICEGROUP ssrv2devicegroup  WITH(NOLOCK)  ON (ssrv2profile.group_id=ssrv2devicegroup.id)
   LEFT JOIN CM_GROUP_MEMBER cmgrpMem  WITH(NOLOCK)  ON (cmgrpMem.grp_id = policyTarget.targetId and policyTarget.type='GROUP')
   LEFT JOIN CM_COMPUTER_SYSTEM monitoringsystem  WITH(NOLOCK)  ON (policyTarget.type='TECHNOLOGY' AND monitoringsystem.cs_id=policyTarget.targetId)
   LEFT JOIN CM_DEVICE monitoringsystemdev  WITH(NOLOCK)  ON monitoringsystemdev.cs_id = monitoringsystem.cs_id
   LEFT JOIN CM_NIMBUS_ROBOT monitoringsystemdevrobot  WITH(NOLOCK)  ON monitoringsystemdevrobot.dev_id = monitoringsystemdev.dev_id
   WHERE policy.enabled =1 AND policyCondition.enabled  = 1
) policyInfo ON 
(
      (
        (policyInfo.policyTargetType='DEVICE' and (policyInfo.policyTargetId=qosInfo.cs_id or policyInfo.policyTargetName=qosInfo.qosDataRobot)) or
        (policyInfo.policyTargetType='TECHNOLOGY' and policyInfo.ssrv2profileId is not NULL AND (qosInfo.qosDataRobot=policyInfo.monitoringSystemRobot)) or
        (policyInfo.policyTargetType='GROUP' and policyInfo.policyTargetGroup is not null AND policyInfo.policyTargetGroupTypeCsId=qosInfo.cs_id)
      )
      AND upper(policyInfo.policyMetricQos)=upper(qosInfo.qosDataQos)
      AND policyInfo.probeName=qosInfo.qosDataProbe
      AND (policyInfo.policyMetricSource=qosInfo.qosDataSource or policyInfo.policyMetricSource='~.*')
      AND (qosInfo.qosdataTarget=policyInfo.policyMetricTarget or policyInfo.policyMetricTarget='~.*' or policyInfo.policyMetricTarget='{device.name}' OR ( qosInfo.qosdataTarget LIKE REPLACE(policyInfo.policyMetricTarget, '{device.name}', '%')))
      AND (policyInfo.policyMetricCiName IS NULL OR policyInfo.policyMetricCiName=''  OR policyInfo.policyMetricCiName=qosInfo.ciname OR PATINDEX (policyInfo.policyMetricCiName, qosInfo.ciname) !=0  )
)
WHERE 1=1 
order by qosInfo.name 
OFFSET 0 ROWS FETCH NEXT 100 ROWS ONLY