How to get total alarm duration and occurrences by alarm

jose.ramirez · November 8, 2015, 8:03pm

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

KathyApplebaum · November 9, 2015, 3:34pm

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?

jose.ramirez · November 9, 2015, 6:57pm

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

KathyApplebaum · November 9, 2015, 10:19pm

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.

jose.ramirez · December 1, 2015, 5:29pm

Thanks. That works

zxcslo · December 4, 2015, 1:10pm

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…

mazeyrat · December 4, 2015, 4:03pm

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

zxcslo · December 4, 2015, 6:19pm

I’ve seen that, tried, but:

It’s for mySQL and I’m using PostgreSQL.
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…

MMaynard · December 4, 2015, 6:31pm

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

MMaynard · December 4, 2015, 6:35pm

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

MMaynard · December 4, 2015, 6:56pm

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

zxcslo · December 5, 2015, 8:08am

[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.
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;

jose.ramirez · March 21, 2016, 9:36pm

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

vrautomation · November 12, 2020, 4:43am

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.

tordvd · February 10, 2021, 8:08am

Thanks to @zxcslo for this one (removed the dates for my convenience):

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:

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'

tordvd · February 10, 2021, 10:45am

Never mind, I figured it out.

Here is the query from @zxcslo augmented to filter for custom properties( customerId, in my case):

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

Lalillo1 · August 14, 2023, 6:10am

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:

However, when I try this query, I get this error:

Do you have some ideas or hints for me? I would very much appreciate it. Thank you.

Transistor · August 14, 2023, 6:21am

Please post formatted (</>) code rather than (or as well as) pictures of code. That way we can copy and edit in our answers.

Lalillo1 · August 14, 2023, 6:24am

Sorry, Thank you. Actually, I think I got it...
It worked for me in this way:

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;

Lalillo1 · August 14, 2023, 9:34pm

This one would be the way to display the duration in minutes:

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;