How to get total alarm duration and occurrences by alarm

Ignition
1

How can this be done, not in the Reporting, but in the normal or power table on the screen/window?

I’m using Journal Table to display alarm history to the user.
Sometimes is very useful to see, for example, top 10 most occurring alarms and total active duration for this alarms. Or to see all alarms sorted by their occurrences and active duration time. Some kind of quick alarm statistics.
I’m banging my head about this for quite a while with no success.

2

If you already have the individual alarm details in a dataset, you can use the view() expression function in the Simulation Aids module to reprocess it into a summary dataset, with grouping, summing, and pivoting features.
Bind it to a table, with the query text in an edit field for quick testing.

3

[quote=“pturmel”]If you already have the individual alarm details in a dataset, you can use the view() expression function in the Simulation Aids module to reprocess it into a summary dataset, with grouping, summing, and pivoting features.
Bind it to a table, with the query text in an edit field for quick testing.[/quote]
You have lost me completely… :confused:

4

Hmm. Looking closer, this is a bit tricky. First, the alarm journal component doesn’t let you bind to the full list of alarms – just the dataset for the selected alarm. But it does have an ‘alarms’ property, which is a dataset of the displayed alarms. Just like the getDataset() method of the list from the system.alarm.queryJournal() script function.
So, first you have to get the alarms into a bindable dataset. A custom dataset property ‘alarmData’ on your table with an objectScript() expression can retrieve that unbindable property:objectScript('binding.target.parent.getComponent("Alarm Journal").alarms', now(1000))Then you can process that dataset into a descending list of frequencies by binding the table’s ‘data’ property with the view() expression:view("Select Source, len(EventState) As Times "+ "Where EventState==0 "+ "Group By Source "+ "Order By -Times", {Root Container.Table.alarmData})The view() expression function doesn’t have a LIMIT clause yet. I’ll add that to my to-do list. :slight_smile:
The durations are even more tricky. Let me think about that.

5

@MMaynardUSG put me on the right track… :thumb_right:
This is for PostgreSQL:

SELECT a.displaypath, SUM(DATE_PART('day', c.eventtime - a.eventtime) * 86400 + DATE_PART('hour', c.eventtime - a.eventtime) * 3600 + DATE_PART('minute', c.eventtime - a.eventtime) * 60 + DATE_PART('second', c.eventtime - a.eventtime)) duration, COUNT(*) total FROM alarm_events a LEFT JOIN alarm_events c ON c.eventid = a.eventid AND c.eventtype = 1 WHERE a.eventtime BETWEEN '03.12.2015 00:00:00' AND '04.12.2015 23:59:59' AND a.eventtype = 0 GROUP BY a.displaypath ORDER BY total DESC LIMIT 10;
https://inductiveautomation.com/forum/viewtopic.php?p=54465#p54465