Ready select for report - spectrum protect 8.1.7

[nakumko]

select \
f.NODE_NAME, \
n.CONTACT, \
e.SCHEDULE_NAME, \
e.status, \
e.result, \
max(DAYS(current_timestamp) - DAYS(f.BACKUP_END)) as FAIL_SINCE \
from events e, filespaces f, nodes n \
where f.node_name=e.node_name \
and f.node_name=n.node_name \
and (e.status='Missed' or e.status='Failed') \
and n.LOCKED='NO' \
group by f.NODE_NAME,n.CONTACT, e.SCHEDULE_NAME,e.status,e.result \
having max(DAYS(current_timestamp) - DAYS(f.BACKUP_END))>1 \
order by f.NODE_NAME, e.SCHEDULE_NAME, FAIL_SINCE DESC;

 

[nakumko]

Update for the last 24h

select \
f.NODE_NAME, \
n.CONTACT, \
TO_CHAR(CHAR(e.scheduled_start),'YYYY-MM-DD HH24:MI:SS') as SCHEDULED_START, \
e.SCHEDULE_NAME, \
e.status, \
e.result, \
max(DAYS(current_timestamp) - DAYS(f.BACKUP_END)) as FAIL_SINCE \
from events e, filespaces f, nodes n \
where f.node_name=e.node_name \
and f.node_name=n.node_name \
and (e.status='Missed' or e.status='Failed') \
and n.LOCKED='NO' \
and e.SCHEDULED_START>current_timestamp-24 hours \
group by f.NODE_NAME,n.CONTACT, SCHEDULED_START, e.SCHEDULE_NAME,e.status,e.result \
having max(DAYS(current_timestamp) - DAYS(f.BACKUP_END))>1 \
order by f.NODE_NAME, e.SCHEDULE_NAME, FAIL_SINCE DESC;