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.
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.