By Josh Tafoya, Technical Trainer
(This is the fourth in a series of training blogs about automating your account comments for faster alarm handling. Links to the other blogs in the series are available at the end.)
Last week, we used a bit of programming to allow Manitou to automatically log alarms if a disarm came in within 60 seconds. This week, I’m going to borrow heavily from training that Caryn Morgan has done in the past. If you have not already done so, I encourage you to sign up for Coffee with Manitou, (Select “Coffee with Manitou” in the “Interests” section) which will allow access to all of Bold’s recent Coffee sessions, including Caryn’s series on Enhanced Action Patterns.
According to the comments above, we want to have Low Battery alarms disregarded (logged to history) unless a power loss came in during the past four hours. Before ManitouNEO and Enhanced Action Patterns, this would not have been possible. But Enhanced Action Patterns gives us, among other things, the ability to query the Manitou database, and alter the behavior of the Action Pattern based on the results of that query.
In this case, we want to query the database to see if there were any Power Loss alarms in the past four hours. For those with SQL knowledge, that might look something like:
SELECT COUNT (*) FROM CLOG201805 WHERE SERIALNO = <THIS CUSTOMER’S SERIALNO> AND STCODE = “AT” AND LOGDATE>=DATEADD(HH,-4, <GMT TIME NOW>)
It’s pretty easy to get into the weeds when learning SQL, but the obvious problem right away is that there is no built-in SQL command to get this customer’s serial number. We accomplish this by using a tag built into Manitou. We replace <THIS CUSTOMER’S SERIALNO> with {ME}. This will work any time you’re using queries in Enhanced Action Patterns. Now our query should look like
SELECT COUNT (*) FROM CLOG201805 WHERE SERIALNO = {ME} AND STCODE = “AT” AND LOGDATE>=DATEADD(HH,-4, <GMT TIME NOW>)
I know there is a SQL function for getting the GMT Time (GETUTCDATE), but we created the tag {DU} to simplify that a bit. So, replacing the appropriate string with the new tag will have our query looking like
SELECT COUNT (*) FROM CLOG201805 WHERE SERIALNO = {ME} AND STCODE = “AT” AND LOGDATE>=DATEADD(HH,-4, “{DU}”)
You may or may not see the problem with specifying this month’s CLOG table in our query: what if we want to run this query in six months? If we keep specifying May of 2018, we will only get results from this month. So, there’s another function to indicate this month’s CLOG table. When we want to query the current month, we can use CLOG{0}, which means our query will look like this
SELECT COUNT (*) FROM CLOG{0} WHERE SERIALNO = {ME} AND STCODE = “AT” AND LOGDATE>=DATEADD(HH,-4, “{DU}”)
You may think we are done at this point, but not quite. The next challenge is that the beginning of each month provides an instance where the most recent four-hour period (or fraction of a four-hour period) to be queried exists in the previous month. So, from 00:00:00 on the first of any month until 03:59:59, we will need to query the previous month as well. Adding this is accomplished by using the CLOG{1} tag in place of the actual name of the table. When that is done, our query should look something like
SELECT COUNT (*) FROM CLOG{0} WHERE SERIALNO = {ME} AND STCODE = “AT” AND LOGDATE>=DATEADD(HH,-4, “{DU}”)+(SELECT COUNT (*) FROM CLOG{1} WHERE SERIALNO = {ME} AND STCODE = “AT” AND LOGDATE>=DATEADD(HH,-4, “{DU}”))
Now we can count how many times in the past four hours a power loss came in on this same account.
I will now include the rest of the action pattern (steps 1 through 9). Then I will add comments below explaining the meaning of each step.
- SET: @VERIFY= SELECT (SELECT COUNT (*) FROM CLOG{0} WHERE SERIALNO = {ME} AND STCODE = “AT” AND LOGDATE>=DATEADD(HH,-4, “{DU}”)+(SELECT COUNT (*) FROM CLOG{1} WHERE SERIALNO = {ME} AND STCODE = “AT” AND LOGDATE>=DATEADD(HH,-4, “{DU}”))
- LOG: AT EVENT CODE TRIPPED @VERIFY TIMES IN THE LAST 4 HOURS.
- IF @VERIFY>=1
- CONTACT CUSTOMER AT SITE
- CONTACT CUSTOMER KEYHOLDER
- CLOSE ALARM WITH RESOLUTION __
- ELSE
- CLOSE ALARM WITH RESOLUTION AU
- END IF
Step 1 is our query, and the SET statement is identifying it as a variable called @VERIFY
Step 2 is adding an entry into the log stating how many times the AT event tripped in the last 4 hours
Step 3 begins an IF statement (if we saw AT once or more in the last four hours)
Step 4 is a normal Contact Customer command
Step 5 is a normal Contact Keyholder command
Step 6 is a normal Close Alarm Command
Step 7 is the beginning of the ELSE statement (meaning anything other than >= 1… or zero)
Step 8 is a normal Close alarm command
Step 9 is the end of the IF statement. It won’t work unless it is ended properly.
Wow… I know that’s a lot to try to assimilate in such a short amount of time! There’s a lot to take in and process. But it’s the kind of example that most monitoring centers have a need for, and therefore it makes sense to learn it. I’ve lost count of the number of times before ManitouNEO was released when somebody asked how to do this exact thing. Now I don’t have to tell them it’s not possible!
I want to take this chance again to thank Caryn for the work she has done on Enhanced Action Patterns, including making sure those of us at Bold are able to understand them.
We’ve crossed another item off of our comments list, so it now looks like this:
Check back next week for more solutions!
This is the fourth in a series of training blogs discussing how to automate comments for better efficiency and fewer errors. See the previous entries here:
Programming and General Schedules