# How to get total alarm duration and occurrences by alarm **URL:** \ **Category:** General Discussion\ **Created:** [November 8, 2015, 8:03pm UTC](https://forum.inductiveautomation.com/t/how-to-get-total-alarm-duration-and-occurrences-by-alarm/10758 "2015-11-08T20:03:44Z")\ **Posts on this page:** 20\ **Page:** 1
**Author:** ![jose.ramirez](https://avatars.discourse-cdn.com/v4/letter/j/7feea3/32.png) [@jose.ramirez](https://forum.inductiveautomation.com/u/jose.ramirez)\ **Post date:** [November 8, 2015, 8:03pm UTC](https://forum.inductiveautomation.com/t/how-to-get-total-alarm-duration-and-occurrences-by-alarm/10758/1 "2015-11-08T20:03:44Z")
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 ---
**Author:** ![KathyApplebaum](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/kathyapplebaum/32/7261_2.png) [@KathyApplebaum](https://forum.inductiveautomation.com/u/KathyApplebaum)\ **Post date:** [November 9, 2015, 3:34pm UTC](https://forum.inductiveautomation.com/t/how-to-get-total-alarm-duration-and-occurrences-by-alarm/10758/2 "2015-11-09T15:34:09Z")
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? ---
**Author:** ![jose.ramirez](https://avatars.discourse-cdn.com/v4/letter/j/7feea3/32.png) [@jose.ramirez](https://forum.inductiveautomation.com/u/jose.ramirez)\ **Post date:** [November 9, 2015, 6:57pm UTC](https://forum.inductiveautomation.com/t/how-to-get-total-alarm-duration-and-occurrences-by-alarm/10758/3 "2015-11-09T18:57:12Z")
I want something like this ![](https://us1.discourse-cdn.com/inductiveautomation/original/2X/6/61a07701a61c47245661c1548d7a30ad07f0ff39.PNG) 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 ---
**Author:** ![KathyApplebaum](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/kathyapplebaum/32/7261_2.png) [@KathyApplebaum](https://forum.inductiveautomation.com/u/KathyApplebaum)\ **Post date:** [November 9, 2015, 10:19pm UTC](https://forum.inductiveautomation.com/t/how-to-get-total-alarm-duration-and-occurrences-by-alarm/10758/4 "2015-11-09T22:19:32Z")
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. ![](https://us1.discourse-cdn.com/inductiveautomation/original/2X/e/e896761170e04f44b76e9991a5b0d6cfd5ea4106.png) ---
**Author:** ![jose.ramirez](https://avatars.discourse-cdn.com/v4/letter/j/7feea3/32.png) [@jose.ramirez](https://forum.inductiveautomation.com/u/jose.ramirez)\ **Post date:** [December 1, 2015, 5:29pm UTC](https://forum.inductiveautomation.com/t/how-to-get-total-alarm-duration-and-occurrences-by-alarm/10758/5 "2015-12-01T17:29:34Z")
Thanks. That works ![:thumb_right:](https://us1.discourse-cdn.com/inductiveautomation/original/1X/2026e28c0c425034352c1b75807edf956adc75f4.gif ":thumb\_right:") ---
**Author:** ![zxcslo](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/zxcslo/32/61610_2.png) [@zxcslo](https://forum.inductiveautomation.com/u/zxcslo)\ **Post date:** [December 4, 2015, 1:10pm UTC](https://forum.inductiveautomation.com/t/how-to-get-total-alarm-duration-and-occurrences-by-alarm/10758/6 "2015-12-04T13:10:36Z")
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… ❓ ---
**Author:** ![mazeyrat](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/mazeyrat/32/10955_2.png) [@mazeyrat](https://forum.inductiveautomation.com/u/mazeyrat)\ **Post date:** [December 4, 2015, 4:03pm UTC](https://forum.inductiveautomation.com/t/how-to-get-total-alarm-duration-and-occurrences-by-alarm/10758/7 "2015-12-04T16:03:31Z")
Travis has posted a template “Alarm Analysis” in the cloud template browser in category “Analysis”. Have a look. ![](https://us1.discourse-cdn.com/inductiveautomation/original/2X/7/7018d9f0900404f1547b348921537aab59fa9a82.png) ---
**Author:** ![zxcslo](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/zxcslo/32/61610_2.png) [@zxcslo](https://forum.inductiveautomation.com/u/zxcslo)\ **Post date:** [December 4, 2015, 6:19pm UTC](https://forum.inductiveautomation.com/t/how-to-get-total-alarm-duration-and-occurrences-by-alarm/10758/8 "2015-12-04T18:19:55Z")
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… ---
**Author:** ![MMaynard](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/mmaynard/32/3366_2.png) [@MMaynard](https://forum.inductiveautomation.com/u/MMaynard)\ **Post date:** [December 4, 2015, 6:31pm UTC](https://forum.inductiveautomation.com/t/how-to-get-total-alarm-duration-and-occurrences-by-alarm/10758/9 "2015-12-04T18:31:14Z")
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` ---
**Author:** ![MMaynard](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/mmaynard/32/3366_2.png) [@MMaynard](https://forum.inductiveautomation.com/u/MMaynard)\ **Post date:** [December 4, 2015, 6:35pm UTC](https://forum.inductiveautomation.com/t/how-to-get-total-alarm-duration-and-occurrences-by-alarm/10758/10 "2015-12-04T18:35:53Z")
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 ` 🙂 ---
**Author:** ![MMaynard](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/mmaynard/32/3366_2.png) [@MMaynard](https://forum.inductiveautomation.com/u/MMaynard)\ **Post date:** [December 4, 2015, 6:56pm UTC](https://forum.inductiveautomation.com/t/how-to-get-total-alarm-duration-and-occurrences-by-alarm/10758/11 "2015-12-04T18:56:17Z")
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` ---
**Author:** ![zxcslo](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/zxcslo/32/61610_2.png) [@zxcslo](https://forum.inductiveautomation.com/u/zxcslo)\ **Post date:** [December 5, 2015, 8:08am UTC](https://forum.inductiveautomation.com/t/how-to-get-total-alarm-duration-and-occurrences-by-alarm/10758/12 "2015-12-05T08:08:52Z")
[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:](https://us1.discourse-cdn.com/inductiveautomation/original/1X/2026e28c0c425034352c1b75807edf956adc75f4.gif ":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;` ---
**Author:** ![jose.ramirez](https://avatars.discourse-cdn.com/v4/letter/j/7feea3/32.png) [@jose.ramirez](https://forum.inductiveautomation.com/u/jose.ramirez)\ **Post date:** [March 21, 2016, 9:36pm UTC](https://forum.inductiveautomation.com/t/how-to-get-total-alarm-duration-and-occurrences-by-alarm/10758/13 "2016-03-21T21:36:26Z")
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 ---
**Author:** ![vrautomation](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/vrautomation/32/11155_2.png) [@vrautomation](https://forum.inductiveautomation.com/u/vrautomation)\ **Post date:** [November 12, 2020, 4:43am UTC](https://forum.inductiveautomation.com/t/how-to-get-total-alarm-duration-and-occurrences-by-alarm/10758/14 "2020-11-12T04:43:40Z")
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. ---
**Author:** ![tordvd](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/tordvd/32/11525_2.png) [@tordvd](https://forum.inductiveautomation.com/u/tordvd)\ **Post date:** [February 10, 2021, 8:08am UTC](https://forum.inductiveautomation.com/t/how-to-get-total-alarm-duration-and-occurrences-by-alarm/10758/15 "2021-02-10T08:08:59Z")
Thanks to @zxcslo for this one (removed the dates for my convenience): ```auto 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.eventtype = 0 GROUP BY a.displaypath ORDER BY total DESC LIMIT 100; ``` Anyone knows how to only query those occurences with a specific property? `alarm_event_data.propname = 'customerId' and alarm_event_data.strvalue = '1'` My SQL skills cant handle both left and inner join at the same time… This one works just fine, so i kind of need to combine the two: ```auto SELECT alarm_events.displaypath, FROM alarm_event_data INNER JOIN alarm_events ON alarm_event_data.id = alarm_events.id where alarm_event_data.propname = 'customerId' and alarm_event_data.strvalue = '1' ``` ---
**Author:** ![tordvd](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/tordvd/32/11525_2.png) [@tordvd](https://forum.inductiveautomation.com/u/tordvd)\ **Post date:** [February 10, 2021, 10:45am UTC](https://forum.inductiveautomation.com/t/how-to-get-total-alarm-duration-and-occurrences-by-alarm/10758/16 "2021-02-10T10:45:54Z")
Never mind, I figured it out. Here is the query from @zxcslo augmented to filter for custom properties( `customerId`, in my case): ```auto 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)) AS duration, COUNT(*) AS total FROM alarm_events a LEFT JOIN alarm_events c ON c.eventid = a.eventid AND c.eventtype = 1 INNER JOIN alarm_event_data ON alarm_event_data.id = a.id WHERE a.eventtype = 0 and alarm_event_data.propname = 'customerId' and alarm_event_data.strvalue = :customerId and a.eventtime BETWEEN TO_TIMESTAMP('2021-02-10 09:30:20','YYYY-MM-DD HH24:MI:SS') AND TO_TIMESTAMP('2021-02-10 12:30:20','YYYY-MM-DD HH24:MI:SS') GROUP BY a.displaypath, alarm_event_data.propname, alarm_event_data.strvalue ORDER BY total DESC ``` ---
**Author:** ![Lalillo1](https://avatars.discourse-cdn.com/v4/letter/l/a6a055/32.png) [@Lalillo1](https://forum.inductiveautomation.com/u/Lalillo1)\ **Post date:** [August 14, 2023, 6:10am UTC](https://forum.inductiveautomation.com/t/how-to-get-total-alarm-duration-and-occurrences-by-alarm/10758/17 "2023-08-14T06:10:29Z")
> [@tordvd](#): > > ```auto > ORDER BY total DESC > > ``` Hello, I find this post useful... however, I am trying to use the source to filter... and I would like to use something like this: ![image](https://us1.discourse-cdn.com/inductiveautomation/original/3X/3/1/31765f785483ee821ff71a36c293b7fdef18399e.png) However, when I try this query, I get this error: ![image](https://us1.discourse-cdn.com/inductiveautomation/original/3X/c/2/c29d467fc6c1442da45bdbb6b7b4be6f6c2104bb.png) Do you have some ideas or hints for me? I would very much appreciate it. Thank you. ---
**Author:** ![Transistor](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/transistor/32/3380_2.png) [@Transistor](https://forum.inductiveautomation.com/u/Transistor)\ **Post date:** [August 14, 2023, 6:21am UTC](https://forum.inductiveautomation.com/t/how-to-get-total-alarm-duration-and-occurrences-by-alarm/10758/18 "2023-08-14T06:21:19Z")
Please post formatted (\) code rather than (or as well as) pictures of code. That way we can copy and edit in our answers. ---
**Author:** ![Lalillo1](https://avatars.discourse-cdn.com/v4/letter/l/a6a055/32.png) [@Lalillo1](https://forum.inductiveautomation.com/u/Lalillo1)\ **Post date:** [August 14, 2023, 6:24am UTC](https://forum.inductiveautomation.com/t/how-to-get-total-alarm-duration-and-occurrences-by-alarm/10758/19 "2023-08-14T06:24:43Z")
Sorry, Thank you. Actually, I think I got it... It worked for me in this way: ```auto SELECT a.source, 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)) AS duration, COUNT(*) AS total FROM alarm_events a LEFT JOIN alarm_events c ON c.eventid = a.eventid AND c.eventtype = 1 WHERE a.eventtype = 0 and a."source" like 'prov:xxxxx:/tag:xxxxx/xxxxx/Messages/Alarms/%' GROUP BY a.source ORDER BY total DESC LIMIT 100; ``` ---
**Author:** ![Lalillo1](https://avatars.discourse-cdn.com/v4/letter/l/a6a055/32.png) [@Lalillo1](https://forum.inductiveautomation.com/u/Lalillo1)\ **Post date:** [August 14, 2023, 9:34pm UTC](https://forum.inductiveautomation.com/t/how-to-get-total-alarm-duration-and-occurrences-by-alarm/10758/20 "2023-08-14T21:34:37Z")
> [@tordvd](#): > > `a.eventtime BETWEEN TO_TIMESTAMP('2021-02-10 09:30:20','YYYY-MM-DD HH24:MI:SS') AND TO_TIMESTAMP('2021-02-10 12:30:20','YYYY-MM-DD HH24:MI:SS')` This one would be the way to display the duration in minutes: ```auto SELECT a.source, a.displaypath, cast(Sum(Date_Part('day', c.eventtime - a.eventtime) * 1440 + Date_Part('hour', c.eventtime - a.eventtime) * 60 + Date_Part('minute', c.eventtime - a.eventtime) + Date_Part('second', c.eventtime - a.eventtime)*(1/60)) AS DECIMAL(10,2)) AS duration, COUNT(*) AS total FROM alarm_events a LEFT JOIN alarm_events c ON c.eventid = a.eventid AND c.eventtype = 1 WHERE a.eventtype = 0 and a.source like 'prov:xxxxx:/tag:xxxxx/xxxx/xxxxx/Alarms/%' and a.eventtime >= '2023-08-10 09:30:20' AND a.eventtime <= '2023-08-14 12:30:20' GROUP BY a.source, a.displaypath ORDER BY duration DESC LIMIT 10; ``` [Next page](https://forum.inductiveautomation.com/t/how-to-get-total-alarm-duration-and-occurrences-by-alarm/10758.md?page=2)