# Query Alarm Journal in SQL

**URL:** <https://forum.inductiveautomation.com/t/query-alarm-journal-in-sql/97462>\
**Category:** Ignition\
**Tags:** database, ignition81\
**Created:** [October 18, 2024, 2:41pm UTC](https://forum.inductiveautomation.com/t/query-alarm-journal-in-sql/97462 "2024-10-18T14:41:51Z")\
**Posts on this page:** 4\
**Page:** 1

<div class="post-metadata">

**Author:** ![Luke\_Reznecheck](https://avatars.discourse-cdn.com/v4/letter/l/b487fb/32.png) [@Luke\_Reznecheck](https://forum.inductiveautomation.com/u/Luke_Reznecheck)\
**Post date:** [October 18, 2024, 2:41pm UTC](https://forum.inductiveautomation.com/t/query-alarm-journal-in-sql/97462/1 "2024-10-18T14:41:51Z")

</div>

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.

---

<div class="post-metadata">

**Author:** ![Brad\_Fischer](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/brad_fischer/32/84857_2.png) [@Brad\_Fischer](https://forum.inductiveautomation.com/u/Brad_Fischer)\
**Post date:** [October 22, 2024, 2:58pm UTC](https://forum.inductiveautomation.com/t/query-alarm-journal-in-sql/97462/2 "2024-10-22T14:58:47Z")

</div>

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.

---

<div class="post-metadata">

**Author:** ![Luke\_Reznecheck](https://avatars.discourse-cdn.com/v4/letter/l/b487fb/32.png) [@Luke\_Reznecheck](https://forum.inductiveautomation.com/u/Luke_Reznecheck)\
**Post date:** [December 16, 2024, 6:01pm UTC](https://forum.inductiveautomation.com/t/query-alarm-journal-in-sql/97462/3 "2024-12-16T18:01:42Z")

</div>

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?

```python
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;

```

---

<div class="post-metadata">

**Author:** ![Luke\_Reznecheck](https://avatars.discourse-cdn.com/v4/letter/l/b487fb/32.png) [@Luke\_Reznecheck](https://forum.inductiveautomation.com/u/Luke_Reznecheck)\
**Post date:** [December 16, 2024, 6:12pm UTC](https://forum.inductiveautomation.com/t/query-alarm-journal-in-sql/97462/4 "2024-12-16T18:12:19Z")

</div>

Nevermind, I found it in the manual.  
[Alarm Event Properties Reference | Ignition User Manual](https://www.docs.inductiveautomation.com/docs/8.1/platform/alarming/configuring-alarms/alarm-event-properties-reference)

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](https://www.docs.inductiveautomation.com/docs/8.1/platform/alarming/alarm-journal#example---eventflags-property) 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.