Finding any rows where the latest event time is over a specified period

Question

The relates to OpenNMS where I'm trying to write an automation in SQL. I have a system which sends events to a table every time the backup succeeds. I want to detect if the backup is overdue for any given node. So for example the (simplified) table looks like:

nodeid, eventid, eventuei, eventtime
  1   ,  1     , backupOk, 09:20 15/12/09
  2   ,  2     , backupOk, 09:25 15/12/09
  3   ,  3     , backupOk, 09:30 15/12/09
  1   ,  4     , backupOk, 09:20 16/12/09
  2   ,  5     , backupOk, 09:25 16/12/09
  2   ,  6     , backupOk, 09:25 17/12/09
  3   ,  7     , backupOk, 09:30 17/12/09

So what I need is a list of nodeid's where the backup is overdue by 24 hours (and I guess where no backup has occurred at all, although there are nodes in this database that don't get backed up (as they are different types of node)).

Answer 1

The following SQL is for Oracle but I'll assume there are similar time-handling facilities in PostgreSQL:

SELECT E.NODEID, E.LAST_BACKUP_TIME
  FROM (SELECT NODEID, MAX(EVENTTIME) AS LAST_BACKUP_TIME
          FROM BACKUP_EVENTS
          WHERE EVENTUEI = 'backupOk'
          GROUP BY NODEID) E
  WHERE E.LAST_BACKUP_TIME < SYSDATE - INTERVAL '2' DAY;

This will report on nodes where a backup hasn't been run in the past 48 hours.

Share and enjoy.

Answer 2

Getting list of nodes that were not backed up - from your schema that you showed us - is not possible.

Getting list of nodes that are 24 hours overdue is trivial:

select nodeid, max(eventtime)
from your_table
group by nodeid
having max(eventtime) < now() - '24 hours'::interval