I want to create a zabbix alert for sending email to users if no report ran in past 2 hrs

Question

We have zabbix setup in our unix system. And report is connected to DB2 Database where zabbix alert is setup.Now I want to write a query which will invoke this alert when no records is seen in the output.

That means query will check if anything ran in past 2 hr and return those record.If nothing is there as output it will invoke alert.

select from_tz(to_timestamp(substr(lastmodifytime,1,19), 'YYYY MM DD HH24 MI 
SS'),'GMT') at time zone 'US/Pacific',
objectid, parentid, typeid, ownerID, LastModifyTime
from BODEV.CMS_INFOOBJECTS7 c
where to_date(substr(lastmodifytime,1,19), 'YYYY MM DD HH24 MI SS') > 
sysdate - 2/60/60/24
order by lastmodifytime desc;

Answer 1

If your fractional seconds have leading zeroes (i.e. 2019 01 22 13 01 18 002 and not 2019 01 22 13 01 18 2), then ... where lastmodifytime > to_char(current timestamp - 2 hour, 'YYYY MM DD HH24 MI FF3').

Use the line with substr otherwise.

select *
from table(values
  to_char(current timestamp - 3 hour, 'YYYY MM DD HH24 MI FF3')
--, to_char(current timestamp - 1 hour, 'YYYY MM DD HH24 MI FF3')
) c (lastmodifytime)
where 
lastmodifytime > to_char(current timestamp - 2 hour, 'YYYY MM DD HH24 MI FF3')
--substr(lastmodifytime, 1, 19) > to_char(current timestamp - 2 hour, 'YYYY MM DD HH24 MI')
fetch first 1 row only
optimize for 1 row
;

Uncomment the 2-nd row to have the result indicating that something ran in past 2 hours.