Query to identify snmpcollector profiles where QOS Data is not being collected during the last 15 minutes

Products

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

Issue/Introduction

Request from the team for a query to identify snmpcollector profiles where QOS Data is not being collected during the last 15 minutes

Environment

• DX UIM 23.4 CU6

Cause

• query guidance

Resolution

SNMP Collector profile configuration is stored locally in the probe's internal H2 database. However, the QoS data generated by the probe is stored in the UIM database.

The following query can be used to identify snmpcollector profiles and their associated targets that have not generated QoS data during the last 15 minutes. This can help identify profiles that may no longer be collecting QOS data as expected.

SELECT DISTINCT 
    def.source AS Profile,
   def.target AS Profile_Device, 
    qos,   
   MAX(snap.sampletime) AS Last_Sample
FROM S_QOS_DATA def
LEFT JOIN S_QOS_SNAPSHOT snap ON snap.table_id = def.table_id
WHERE def.probe = 'pollagent'
GROUP BY def.source,  def.target, qos
HAVING MAX(snap.sampletime) < DATEADD(MINUTE, -15, GETDATE())
OR MAX(snap.sampletime) IS NULL;
--ORDER BY Last_Sample DESC

Notes:

• The query returns profile name, target, QoS metric, and the most recent sample time.

• The time window can be adjusted as needed. For example:

  Last 1 hour: replace 15 minutes with 60 minutes.

  Last 24 hours: replace 15 minutes with 1440 minutes.

• If only profile names are required, the SELECT and GROUP BY clauses can be simplified accordingly.

• This query is intended to identify profiles whose QoS data has stopped arriving in the UIM database, regardless of the profile configuration stored in the probe's local H2 database.