Contents
Count Event Queries
Counting BA events in the last 60 minutes
Customers use this query to make a decision based on the number of burglar alarms received within a given period. They will often use the Select and Case to decide what to do with one, two, and more than two events.
SELECT (SELECT COUNT(*) FROM CLOG{0} WHERE STCODE="BA2" AND EVTYPE = 1 AND ZONE={ZN} AND SERIALNO={ME} AND LOGDATE >= DATEADD(MINUTE,-60,GETUTCDATE())
+
(SELECT COUNT(*) FROM CLOG{1} WHERE STCODE="BA2" AND EVTYPE = 1 AND ZONE={ZN} AND SERIALNO={ME} AND LOGDATE >= DATEADD(MINUTE,-60,GETUTCDATE())))
Counting Bypass Events in the last 72 hours
SELECT (SELECT COUNT(*) FROM CLOG{0} WHERE STCODE="*B" AND EVTYPE in (1, 0, 9) AND SERIALNO={ME} AND LOGDATE >= DATEADD(HH,-72,GETUTCDATE())
+
(SELECT COUNT(*) FROM CLOG{1} WHERE STCODE="*B" AND EVTYPE in (1, 0, 9) AND SERIALNO={ME} AND LOGDATE >= DATEADD(HH,-72,GETUTCDATE())))
Looking for test signal (generator test) in the last 30 days
This is used most often with a Reminder and Post processing actions.
SELECT (SELECT COUNT(*) FROM CLOG{0} WHERE STCODE="RP" AND EVTYPE = 0 AND SERIALNO={ME} AND LOGDATE >= DATEADD(DAY,-30,GETUTCDATE())
+
(SELECT COUNT(*) FROM CLOG{1} WHERE STCODE="RP" AND EVTYPE = 0 AND SERIALNO={ME} AND LOGDATE >= DATEADD(DAY,-30,GETUTCDATE())))
Counting the Network troubles in the last 60 minutes
This is used most often with post processing and CanCancel to make sure Network Troubles aren't running away.
SELECT (SELECT COUNT(*) FROM CLOG{0} WHERE STCODE="NT" AND EVTYPE IN (0, 1, 9) AND SERIALNO={ME} AND LOGDATE >= DATEADD(MINUTE,-60,GETUTCDATE())
+
(SELECT COUNT(*) FROM CLOG{1} WHERE STCODE="NT" AND EVTYPE IN (0, 1, 9) AND SERIALNO={ME} AND LOGDATE >= DATEADD(MINUTE,-60,GETUTCDATE())))
Looking at if there is a AC Trouble (AT)
This is most often used on a Low Battery event (YT) to see if that low battery is in proximity to an AC Trouble event. This can imply that the system is about to fail.
SELECT (SELECT COUNT(*) FROM CLOG{0} WHERE STCODE = "AT" AND EVTYPE = 1 AND SERIALNO={ME} AND LOGDATE>=DATEADD(HH,-4, "{DU}"))
+
(SELECT COUNT(*) FROM CLOG{1} WHERE STCODE = "AT" AND EVTYPE = 1 AND SERIALNO={ME} AND LOGDATE>=DATEADD(HH,-4, "{DU}"))
Check for contacts made in the last 7 days
This is most often used with a Late to Test where the query looks at the CLOG Contacts and if the contact was successful.
SELECT (SELECT COUNT(*) FROM CLOG{0} WHERE SERIALNO={ME} AND STCODE="*LT" AND EVTYPE = 1 AND LOGDATE>=DATEADD(DD,-7,"{DU}") AND EVTYPE=3 AND QUAL=1 AND QUAL7=1)
+
(SELECT COUNT(*) FROM CLOG{1} WHERE SERIALNO={ME} AND STCODE="*LT" AND EVTYPE = 1 AND LOGDATE>=DATEADD(DD,-7,"{DU}") AND EVTYPE=3 AND QUAL=1 AND QUAL7=1)
Looking for Communications Failures on a Late to Test or Late to Close
SELECT (SELECT COUNT(*) FROM CLOG{0} WHERE STCODE="YC" AND EVTYPE = 1 AND SERIALNO={ME} AND LOGDATE >= DATEADD(HH,-24,GETUTCDATE())
+
(SELECT COUNT(*) FROM CLOG{1} WHERE STCODE="YC" AND EVTYPE = 1 AND SERIALNO={ME} AND LOGDATE >= DATEADD(HH,-24,GETUTCDATE())))
