I would like to have an alarm when a transaction group has not updated a table in x minutes. I know that I can use handshaking on the transaction group to write out values to tags on success or failure. I’d like to monitor and alarm if the group is disabled or paused for more than x minutes for instance if my group is supposed to run at the top of the hour, then alarm if the database timestamp is more than 61 minutes older than now(). I tried to configure this on a tag; the value datatype is date and the query returns date of the last SQL update. The setpoint was bound as now(1000). But no alarm no matter how I set the mode. Any ideas on how to tackle this in a simple way? thank you.
Paul
Hi Paul,
You can get the minutes since the last timestamp in a Query Tag, then set your alarm for > 60.
You didn’t mention what DB you’re using, so YMMV. Here’s some examples to get you started.
(Note: The MySQL example is from memory, since I don’t use it here at this time, so that one is untested.)
MySQL
SELECT TIMESTAMPDIFF(MINUTE, t_stamp, NOW())
FROM table
ORDER BY t_stamp DESC
LIMIT 1
PostgreSQL:
SELECT extract(EPOCH FROM(NOW() - t_stamp)) / 60
FROM table
ORDER BY t_stamp DESC
LIMIT 1
SQL Server
SELECT TOP 1 datediff(mi, t_stamp, CURRENT_TIMESTAMP)
FROM table
ORDER BY t_stamp DESC
1 Like
Hello Jordan.
thank you for the thorough reply. I am using MS SQL; took the example as my model and voila! I now am able to very easy configure monitoring on this transaction group action/inaction. Simple and functional solution, thank you,
Paul
1 Like