Not possible in the AST, but possible with a custom query on the history table.
I use this for postgre (note: it may be able to be made more efficient):
CREATE VIEW vw_alarm_history AS
SELECT
MAX(t1.id) AS id,
t1.eventid,
MAX(t1.source::text) AS source,
MAX(t1.displaypath::text) AS displaypath,
MAX(t1.priority) AS priority,
MAX(t1.eventtime) FILTER (WHERE t1.action = 'active'::text) AS active,
MAX(t1.eventtime) FILTER (WHERE t1.action = 'acked'::text) AS acked,
MAX(t1.eventtime) FILTER (WHERE t1.action = 'shelved'::text) AS shelved,
MAX(t1.eventtime) FILTER (WHERE t1.action = 'cleared'::text) AS cleared,
MAX(t1.propvalue::text) FILTER (WHERE t1.propname::text = 'ackMode'::text) AS ackmode,
MAX(t1.propvalue::text) FILTER (WHERE t1.propname::text = 'ackUser'::text) AS ackuser,
MAX(t1.propvalue::text) FILTER (WHERE t1.propname::text = 'activePipeline'::text) AS activepipeline,
MAX(t1.propvalue::text) FILTER (WHERE t1.propname::text = 'Area'::text) AS area,
MAX(t1.propvalue::text) FILTER (WHERE t1.propname::text = 'Category'::text) AS category,
MAX(t1.propvalue::text) FILTER (WHERE t1.propname::text = 'label'::text) AS label,
MAX(t1.propvalue::text) FILTER (WHERE t1.propname::text = 'name'::text) AS name,
MAX(t1.propvalue::text) FILTER (WHERE t1.propname::text = 'ParentDevice'::text) AS parentdevice,
MAX(t1.propvalue::text) FILTER (WHERE t1.propname::text = 'setpointA'::text) AS setpointa,
MAX(t1.propvalue::text) FILTER (WHERE t1.propname::text = 'UserGroup'::text) AS usergroup,
EXTRACT(EPOCH FROM (MAX(t1.eventtime) FILTER (WHERE t1.action = 'cleared'::text) - MAX(t1.eventtime) FILTER (WHERE t1.action = 'active'::text))) as active_time_s,
EXTRACT(EPOCH FROM (MAX(t1.eventtime) FILTER (WHERE t1.action = 'cleared'::text) - MAX(t1.eventtime) FILTER (WHERE t1.action = 'active'::text)))::double precision * '00:00:01'::interval as active_time
FROM (
SELECT
ae.id,
ae.eventid,
ae.source,
ae.displaypath,
CASE
WHEN ae.eventflags = 2 THEN 'shelved'::text
WHEN ae.eventtype = 0 THEN 'active'::text
WHEN ae.eventtype = 1 THEN 'cleared'::text
WHEN ae.eventtype = 2 THEN 'acked'::text
ELSE '??'::text
END AS action,
CASE ae.priority
WHEN 0 THEN 'Diagnostic'::text
WHEN 1 THEN 'Low'::text
WHEN 2 THEN 'Medium'::text
WHEN 3 THEN 'High'::text
WHEN 4 THEN 'Critical'::text
ELSE 'INVALID'::text
END AS priority,
ae.eventtype,
ae.eventflags,
ae.eventtime,
aed.propname,
COALESCE(aed.strvalue::text, aed.floatvalue::text, aed.intvalue::text) as propvalue
FROM
alarm_events ae
LEFT JOIN alarm_event_data aed ON aed.id = ae.id
) t1
GROUP BY t1.eventid
ORDER BY (MAX(t1.id)) DESC
Edit:
This one removes the sub-query and one of the CASE statements. It's slightly faster.
CREATE VIEW vw_alarm_history AS
SELECT
MAX(ae.id) AS id,
ae.eventid,
MAX(ae.source) AS source,
MAX(ae.displaypath) AS displaypath,
MAX(CASE ae.priority
WHEN 0 THEN 'Diagnostic'
WHEN 1 THEN 'Low'
WHEN 2 THEN 'Medium'
WHEN 3 THEN 'High'
WHEN 4 THEN 'Critical'
ELSE 'INVALID'
END) AS priority,
-- Action timestamps
MAX(ae.eventtime) FILTER (WHERE ae.eventflags != 2 AND ae.eventtype = 0) AS active,
MAX(ae.eventtime) FILTER (WHERE ae.eventtype = 2) AS acked,
MAX(ae.eventtime) FILTER (WHERE ae.eventflags = 2) AS shelved,
MAX(ae.eventtime) FILTER (WHERE ae.eventtype = 1) AS cleared,
-- Properties (pivot the key-value pairs)
MAX(COALESCE(aed.strvalue, aed.floatvalue::text, aed.intvalue::text)) FILTER (WHERE aed.propname = 'ackMode') AS ackmode,
MAX(COALESCE(aed.strvalue, aed.floatvalue::text, aed.intvalue::text)) FILTER (WHERE aed.propname = 'ackUser') AS ackuser,
MAX(COALESCE(aed.strvalue, aed.floatvalue::text, aed.intvalue::text)) FILTER (WHERE aed.propname = 'activePipeline') AS activepipeline,
MAX(COALESCE(aed.strvalue, aed.floatvalue::text, aed.intvalue::text)) FILTER (WHERE aed.propname = 'Area') AS area,
MAX(COALESCE(aed.strvalue, aed.floatvalue::text, aed.intvalue::text)) FILTER (WHERE aed.propname = 'Category') AS category,
MAX(COALESCE(aed.strvalue, aed.floatvalue::text, aed.intvalue::text)) FILTER (WHERE aed.propname = 'label') AS label,
MAX(COALESCE(aed.strvalue, aed.floatvalue::text, aed.intvalue::text)) FILTER (WHERE aed.propname = 'name') AS name,
MAX(COALESCE(aed.strvalue, aed.floatvalue::text, aed.intvalue::text)) FILTER (WHERE aed.propname = 'ParentDevice') AS parentdevice,
MAX(COALESCE(aed.strvalue, aed.floatvalue::text, aed.intvalue::text)) FILTER (WHERE aed.propname = 'setpointA') AS setpointa,
MAX(COALESCE(aed.strvalue, aed.floatvalue::text, aed.intvalue::text)) FILTER (WHERE aed.propname = 'UserGroup') AS usergroup,
-- Calculated fields
EXTRACT(EPOCH FROM (
MAX(ae.eventtime) FILTER (WHERE ae.eventtype = 1) -
MAX(ae.eventtime) FILTER (WHERE ae.eventflags != 2 AND ae.eventtype = 0)
)) AS active_time_s,
(MAX(ae.eventtime) FILTER (WHERE ae.eventtype = 1) -
MAX(ae.eventtime) FILTER (WHERE ae.eventflags != 2 AND ae.eventtype = 0)) AS active_time
FROM alarm_events ae
LEFT JOIN alarm_event_data aed ON aed.id = ae.id
GROUP BY ae.eventid
ORDER BY MAX(ae.id) desc;