Hello All,
I’m trying to implement a method for obtaining aggregate alarm data for specific tag folders (e.g. by ‘source’) but could also be adapted for other groupings (e.g. ‘displaypath’, etc.). I’ve accomplished similar by creating a UDT that contains a SQL tag that polls for the latest entry in the alarmevent table, which triggers a script to update memory tags holding the aggregate data… but I’m hoping to implement something that is a bit cleaner/more robust. I would much rather have the aggregate data updated in a polled fashion, rather than an event-driven fashion.
Ideally, I would like to see some more alarm expression functions (in addition to the isAlarmActive(), isAlarmActiveFiltered()) built into Ignition e.g.
- ActiveUnackedCount()
- ActiveAckedCount(source=,displaypath=)
- ClearedUnackedCount(source=,displaypath=)
- MaxActiveUnackedPriority(source=,displaypath=)
- MaxActiveAckedPriority(source=,displaypath=)
- MaxClearedUnackedPriority(source=,displaypath=)
WIth the addition of “named queries” – I’m thinking I can provide a cleaner workaround until the functions above are implemented…
/*
Prepare a single row of alarm & event info ActiveUnacked, ActiveAcked, ClearedUnacked, for a given alarm source path node
e.g. "prov:default:/tag:path/to/tag/folder"
Column info:
priority:
0 = Diagnostic
1 = Low
2 = Medium
3 = High
4 = Critical
eventtype:
0 = Active
1 = Clear
2 = Acknowledgement
eventflags:
Bit 0 (1) = System event (e.g. system startup, shutdown, etc.)
Bit 1 (2) = Shelved event (e.g. the alarm was shelved at the time of the event)
Bit 2 (4) = System acknowledgment (e.g. automatically acknowledged because of live-event overflow)
Bit 3 (8) = Acknowledge event (e.g. for events that are acknlowedged while still active)
Bit 4 (16) = Cleared event (e.g. the event has cleared, but not acknowledged yet)
Desired result set:
ActiveUnackedCount -- count(eventtype == 0)
ActiveAckedCount -- count(eventtype == 2 && eventflags.3)
ClearedUnackedCount --count(eventtype == 1 && eventflags.4)
MaxActiveUnackedPriority -- max(eventtype == 0)
MaxActiveAckedPriority -- max(eventtype == 2 && eventflags.3)
MaxClearedUnackedPriority -- max(eventtype == 1 && eventflags.4)
*/
SELECT
--need to inspect the event flags to get proper active/unacknowledged counts
last_entry.eventtype,
count(last_entry.eventtype),
max(last_entry.priority),
STRING_AGG(eventid,',') eventids
FROM (
--sub-query to get last event for each distinct souce...eventid
SELECT DISTINCT ON
--(source) * -- evaluate based on alarm source
(eventid) * -- evaluate based on eventid
FROM alarm_events
WHERE source LIKE :source
ORDER BY
--source,
eventid,
eventtime DESC
) last_entry
WHERE
last_entry.eventtype = 0
OR ((last_entry.eventtype = 2) AND (last_entry.eventflags & 8 = 8))
OR ((last_entry.eventtype = 1) AND (last_entry.eventflags & 16 = 16))
GROUP BY
eventtype
Then the idea would be to create an SQL tag for each folder of interest that uses this named query… clients can then reference/bind to the SQL tag…
The issue that I’m running into now, is that I can’t sort out how the “Alarm Status Table” decides what to display… I’m only getting three rows in the Alarm Status Table (no filters, choosing to display all types of events), while my SQL query is returning hundreds of rows… does anyone know how the Alarm Status Table component queries the alarmevent table? Hoping to adjust my query above so that it aligns with what the client would see in the alarm table…
Thanks in advance for any suggestions/info!
-Paul
