Is there a query 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 identified the robots.

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')