Is there a query for "stale" robots that will return robot names that have not collected OQS/Data for a given time?

Products

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

Issue/Introduction

We noticed that some robots stopped reporting CDM data and would like to identify the robots.  Even though these robots appear green and active, something has stopped them from sending data.

Now we wonder if there might be more robots with this problem.

How can we query for this situation where the QoS is not updated or has gone stale?

Is there a query that will return a list of robots the were reporting CDM data a week ago but have not reported data in the last hour?

Environment

Any UIM version

Resolution

The query below will return a list of robots that reported CDM data seven days back but have not reported data the last 24 hours.

SELECT distinct(robot)
FROM S_QOS_DATA AS qd
JOIN S_QOS_SNAPSHOT AS snap ON snap.table_id = qd.table_id
WHERE snap.[sampletime] > dateadd(day, -7, getdate())
and probe = 'cdm' and robot not in (SELECT distinct(robot)
FROM S_QOS_DATA AS qd
JOIN S_QOS_SNAPSHOT AS snap ON snap.table_id = qd.table_id
WHERE snap.[sampletime] > dateadd(day, -1, getdate())
AND probe = 'cdm')