Query Alarm Journal in SQL

Ignition
,
1

Good Day,
I am looking to do a direct query of the Alarm Journal in SQL to extract alarm history information to an external system. I am currently unsure how to join the two tables (dbo.alarm_event_data & dbo.alarm_events) to get a query result that contains the event Time, DisplayPath, Label, Event State, Priority,state, etc. Basically an Alarm Journal result directly through SQL.

The alarm_events table has the eventtime, displaypath, priority, eventtype, but I am not sure how to join this to the event_data table.

Greatly appreciate any assistance.

2

The bulk of alarm information is stored in the dbo.alarm_event table while the dbo.alarm_event_data tables stores unique information pertaining to each event.

Here are some example entries for an alarm:
dbo.alarm_event table:

id eventid source priority eventtype eventflags eventtime
294969 d7629f56-c925-459f-9834-9ee9fd8ea64e prov:default:/tag:AlarmDemo/PV:/alm:High High 4 0 0 2024-10-22 09:36:41.419
294973 d7629f56-c925-459f-9834-9ee9fd8ea64e prov:default:/tag:AlarmDemo/PV:/alm:High High 4 2 0 2024-10-22 09:39:43.996
294975 d7629f56-c925-459f-9834-9ee9fd8ea64e prov:default:/tag:AlarmDemo/PV:/alm:High High 4 1 24 2024-10-22 09:39:52.845

dbo.alarm_event_data table:

id propname dtype intvalue floatvalue strvalue
294969 setpointA 1 50
294969 eventValue 1 52
294969 Maintenance Team 2 Delta
294973 setpointA 1 50
294973 ackUser 2 usr:admin
294973 ackNotes 2 Accurate alarm. Corrected process by decreasing inlet pressure.
294973 Maintenance Team 2 Delta
294975 setpointA 1 50
294975 eventValue 1 36.5
294975 Maintenance Team 2 Delta

The "High High" alarm became active as logged in the alarm_event table under id 294969, which shows this is a High priority alarm (priority = 4) and eventtype = 0 denotes an unacknowledged alarm. The alarm_event_data table includes 3 entries for the same id: the tag value (eventValue) of 52 exceeded the setpoint (setpointA) value of 50. The last entry is for a custom property "Maintenance Team" I defined on the alarm. It has the value "Delta".

Event id 294973 captures the event being acknowledged and event id 294975 captures the event being cleared. Note that the active, ack, and clear events are all tied together in the alarm_event table under eventid.

Combining these to send to Pi is most likely a matter of using a SQL JOIN statement, but the varying number of entries in alarm_event_data means the query needs to be structured and Pi needs to understand the incoming values.

3

Ok thank you for this.
I now have the following query returning data in a better format. However, where can I find the full list of eventtype IDs, eventflags, priority values?

SELECT 
    ae.*,
    MAX(CASE WHEN aed.propname = 'alarmAreas' THEN aed.strvalue END) AS alarmAreas,
    MAX(CASE WHEN aed.propname = 'label' THEN aed.strvalue END) AS label,
    MAX(CASE WHEN aed.propname = 'eventValue' THEN CAST(aed.intvalue AS TEXT) END) AS eventValue,
    MAX(CASE WHEN aed.propname = 'priority' THEN CAST(aed.intvalue AS TEXT) END) AS priority, 
	MAX(CASE WHEN aed.propname = 'ackUser' THEN aed.strvalue END) AS ackUser
FROM 
    public.alarm_events ae
LEFT JOIN 
    public.alarm_event_data aed
ON 
    ae.eventtype = aed.id
GROUP BY 
    ae.id, ae.eventid, ae.source, ae.displaypath, ae.priority, ae.eventtype, ae.eventflags, ae.eventtime;
4

Nevermind, I found it in the manual.
Alarm Event Properties Reference | Ignition User Manual

The current overall state of the alarm. States include:

  • Clear and Unacked (0)
  • Clear and Acked (1)
  • Active and Unacked (2)
  • Active and Acked (3)

It looks like the eventtype matches this pattern.
The transitional state that caused the current event. States include:

  • Active (0)
  • Clear (1)
  • Acknowledged (2)
  • 4: An alarm was enabled
  • 5: An alarm was disabled

Priorities:

  • Diagnostic (0)
  • Low (1)
  • Medium (2)
  • High (3)
  • Critical (4)

EventFlags
A numeric bitmask flag field providing additional information about the event. See the example below for how to adjust the eventflags value into a more readable format.

  • Bit 0: System Event - One of the designated system events. (System Startup, System Shutdown)
  • Bit 1: Shelved Event - The alarm was "shelved" at the time that the event occurred. Shelving alarms does not prevent execution, so if the journal is configured to store shelved events, they will be stored even if they're not sent to the notification system, or shown to users.
  • Bit 2: System Acknowledgement - When the "live event limit" (defined in general alarm settings) is exceeded, the system will automatically acknowledge overflow events, and the acknowledgment event will have this flag set.
  • Bit 3: Acknowledge Event - The event was acknowledged at the time of the event. For events that are cleared after being acknowledged.
  • Bit 4: Cleared Event - The event was cleared at the time of the event. For alarms that are acknowledged after being cleared.
  • Bit 5: Enabled - Signifies that the enabled state on the alarm was changed.