How to get total alarm duration and occurrences by alarm

Hi,

I want to make a report querying an Alarm Journal that shows the top N more important alarms. Showing Total Active Duration and number of occurrences by display path.
I cound’t find a way to group by Alarm and get the Sum(ActiveDuration) , Count (*)

Is there a way to do it using Journal Query without having to do SQL query to the underlying alarm tables?

thanks

If I understand what you’re trying to do (and I assume you’re working in Ignition 7.8. ), this sounds like a perfect case for a nested query, with the SQL query as the parent and the Alarm Journal Query as the nested query. Is there some reason you’re avoiding this?

I want something like this


Sorting by alarms with the most active time duration (summatory of each occurence active duration)

I dont see how the nested query would resolve it.

Thanks

Okay, here’s how I did it…

My parent query is a SQL query: SELECT distinct displaypath FROM alarm_events where displaypath <> ""
I wanted to limit this to the dozen alarms I added a display path to – obviously you’ll change this query to suit your needs.

My child query is an alarm journal query, where the displaypath filter is {displaypath}

My table is set up like the attached screen shot, with Top N sorting, using a sorting key of alarm_journal.total.activeDurationMS / 60 / 1000 (ignore the warning that comes up), limited to the top 5 results. You can add some number formatting to the Active Minutes column if you don’t want to see the fractional portion of the minutes.

Thanks. That works :thumb_right:

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

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.

Any help… :question:

Travis has posted a template “Alarm Analysis” in the cloud template browser in category “Analysis”.
Have a look.


I’ve seen that, tried, but:

  1. It’s for mySQL and I’m using PostgreSQL.
  2. It’s ‘to much’ data on the screen. What we/I need is simple table with top10 (or more) most frequently alarms and their total duration’s.

Don’t get me wrong. I appreciate his work and I’m sure that Travis’s work is useful for many people, for us is just to much information…

Set the Power Table Source to a SQL Query and use the below.
I use MS SQL but this should work for PostGres as well. You can replace the dates with control values from the Window if you want to make it dynamic.

SELECT TOP 10 a.displaypath, SUM(DateDiff(second,a.eventtime ,COALESCE(c.eventtime, CURRENT_TIMESTAMP))) duration FROM alarm_events a LEFT JOIN alarm_events c ON c.eventid = a.eventid AND c.eventtype = 1 WHERE a.eventtime BETWEEN '12/4/2015 00:00:00' AND '12/4/2015 23:59:59' AND a.eventtype = 0 GROUP BY a.displaypath ORDER BY duration DESC, a.displaypath ASC

and if you want the most frequent alarms use:

SELECT TOP 10 a.displaypath, COUNT(*) total FROM alarm_events a WHERE a.eventtime BETWEEN '12/4/2015 00:00:00' AND '12/4/2015 23:59:59' AND a.eventtype = 0 GROUP BY a.displaypath ORDER BY total DESC

:slight_smile:

Sorry I didn’t actually look at the result set you wanted.
Here is the merged queries.

SELECT TOP 10 a.displaypath, SUM(DateDiff(second,a.eventtime ,COALESCE(c.eventtime, CURRENT_TIMESTAMP))) 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 '12/4/2015 00:00:00' AND '12/4/2015 23:59:59' AND a.eventtype = 0 GROUP BY a.displaypath ORDER BY duration DESC, a.displaypath ASC

[quote=“MMaynardUSG”]Sorry I didn’t actually look at the result set you wanted.
Here is the merged queries.

SELECT TOP 10 a.displaypath, SUM(DateDiff(second,a.eventtime ,COALESCE(c.eventtime, CURRENT_TIMESTAMP))) 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 '12/4/2015 00:00:00' AND '12/4/2015 23:59:59' AND a.eventtype = 0 GROUP BY a.displaypath ORDER BY duration DESC, a.displaypath ASC[/quote]
You got me on the right track. Thank you. :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;

Hi all,

I ported Travis’ good job to MSSQL and uploaded the template to the cloud.
You can find it as Alarm Analysis_MSSQL

Enjoy

I’m reviving this old thread that is actually helping me a lot.

I would like to do something similar, but with the label of the alarms.

I’d like to pass a beginDate, endDate and displayPath and get the top 10 occurence of labels and their sum of time associated.

Please help.