Hi, I am looking for a way to combine alarm journal, audit trail and my own custom events into a single table from a single query. I have gotten 95% of the way but the last 5% is so convoluted i figured there might be a better way.
The issue is specifically with manual alarm journal queries. I can join alarm_event_data and alarm_events to get my associated data on alarm event occurrences, but only on “Active” events; “Cleared” and “Acknowledge” events with different ids (but with the same eventids as the “Active” alarm event), have no corresponding rows in the alarm journal.
The result is I can only get associated data for active events. I can potentially use the eventid to link acks to actives etc., but this seems really convoluted, I feel like I am missing something obvious here. I’d appreciate it if someone could point me in the right direction.
FYI Currently the SQL binding on the data of my powertable which displays the data looks like this (control_events is my own custom db table):
SELECT * FROM (
SELECT
eventtime,
description,
value,
status,
station
FROM public.control_events
UNION
SELECT
"EVENT_TIMESTAMP",
"ACTION",
"ACTION_VALUE",
"ACTOR",
NULL as varchar
FROM public.audit_events
WHERE "ACTION" <> 'tag write'
UNION
SELECT * FROM (
SELECT
public.alarm_events.eventtime,
a2.strValue,--public.alarm_event_data.propname,
CASE eventtype
WHEN 0 THEN 'Active'
WHEN 1 THEN 'Clear'
WHEN 2 THEN 'Acknowledged'
END as varchar,
public.alarm_events.source,
a1.strValue
FROM public.alarm_events
LEFT JOIN alarm_event_data as a1 ON (public.alarm_events.id = a1.id)
AND (a1.propname = 'Station')
LEFT JOIN alarm_event_data as a2 ON (public.alarm_events.id = a2.id)
AND (a2.propname = 'Description')
) AS query1
) AS query2
WHERE {Root Container.Power Table.timeWhereClause}
AND {Root Container.Power Table.stationWhereClause}
AND {Root Container.Power Table.descWhereClause}
ORDER BY eventtime DESC;
Thanks.
Andrew