Alarm Journal Query

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