Vision alarm analysis template - Alarms by [time]

Cory.grube · June 3, 2021, 3:27pm

While using the Vision alarm analysis template, I noticed some confusing behavior with the alarms by hour of day/day/day of week bar chart.

It appears that this chart simultaneously plots both duration of alarms AND alarm count. I don’t think this intentional, and it can provide some some confusing graphics.

Example: With the date range set to all of today, I can see that 3 alarms came in, and remain active through now. But in the bar chart, it looks like 103k+ alarms are shown as becoming active at 1am. This is actually a total duration of 103k+ seconds, which doesn’t make sense in context of an alarms by [time] chart.

The alarmsBySelQuery internal property drives this query. I’ve simply removed the SUM(...) duration portion to eliminate the duration column of the dataset entirely

Original alarmsBySelQuery expression

switch({Alarm Analysis.dbType},

"MYSQL", "MSSQL", "POSTGRES", 

"SELECT " + 
{Alarm Analysis.Alarms By Selection.Selection.groupBy} + " label, " + 
"	COUNT(*) total, " + 
"	SUM(TIMESTAMPDIFF(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 '" + dateFormat({Alarm Analysis.startDate}, "yyyy-MM-dd HH:mm:ss") + "' AND '" + dateFormat({Alarm Analysis.endDate}, "yyyy-MM-dd HH:mm:ss") + "' " +  
"	AND a.eventtype = 0 " +  
"GROUP BY " + 
"	" + {Alarm Analysis.Alarms By Selection.Selection.groupBy} + " " +  
"ORDER BY " + 
"	" + {Alarm Analysis.Alarms By Selection.Selection.groupBy} + " ASC",
	
"SELECT " + 
{Alarm Analysis.Alarms By Selection.Selection.groupBy} + " label, " + 
"	COUNT(*) total, " + 
"	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 '" + dateFormat({Alarm Analysis.startDate}, "yyyy-MM-dd HH:mm:ss") + "' AND '" + dateFormat({Alarm Analysis.endDate}, "yyyy-MM-dd HH:mm:ss") + "' " +  
"	AND a.eventtype = 0 " +  
"GROUP BY " + 
"	" + {Alarm Analysis.Alarms By Selection.Selection.groupBy} + " " + 
"ORDER BY " + 
"	" + {Alarm Analysis.Alarms By Selection.Selection.groupBy} + " ASC",

"SELECT " + 
{Alarm Analysis.Alarms By Selection.Selection.groupBy} + " AS label, " + 
"	COUNT(*) total, " + 
"	SUM(EXTRACT(EPOCH FROM (COALESCE(c.eventtime, CURRENT_TIMESTAMP) - a.eventtime))) duration " + 
"FROM " + 
"	alarm_events a " +  
"		LEFT JOIN alarm_events c ON c.eventid = a.eventid AND c.eventtype = 1 " +  
"WHERE " +  
"	a.eventtime " +  
"		BETWEEN '" + dateFormat({Alarm Analysis.startDate}, "yyyy-MM-dd HH:mm:ss") + "' AND '" + dateFormat({Alarm Analysis.endDate}, "yyyy-MM-dd HH:mm:ss") + "' " +  
"	AND a.eventtype = 0 " +  
"GROUP BY " + 
"	" + {Alarm Analysis.Alarms By Selection.Selection.groupBy} + " " +  
"ORDER BY " + 
"	" + {Alarm Analysis.Alarms By Selection.Selection.groupBy} + " ASC",

"SELECT 1")

Revised alarmsBySelQuery expression

switch({Alarm Analysis.dbType},

"MYSQL", "MSSQL", "POSTGRES", 

"SELECT " + 
{Alarm Analysis.Alarms By Selection.Selection.groupBy} + " label, " + 
"	COUNT(*) total " + 
"FROM " + 
"	alarm_events a " +  
"		LEFT JOIN alarm_events c ON c.eventid = a.eventid AND c.eventtype = 1 " +  
"WHERE " +  
"	a.eventtime " +  
"		BETWEEN '" + dateFormat({Alarm Analysis.startDate}, "yyyy-MM-dd HH:mm:ss") + "' AND '" + dateFormat({Alarm Analysis.endDate}, "yyyy-MM-dd HH:mm:ss") + "' " +  
"	AND a.eventtype = 0 " +  
"GROUP BY " + 
"	" + {Alarm Analysis.Alarms By Selection.Selection.groupBy} + " " +  
"ORDER BY " + 
"	" + {Alarm Analysis.Alarms By Selection.Selection.groupBy} + " ASC",
	
"SELECT " + 
{Alarm Analysis.Alarms By Selection.Selection.groupBy} + " label, " + 
"	COUNT(*) total " +
"FROM " + 
"	alarm_events a " +  
"		LEFT JOIN alarm_events c ON c.eventid = a.eventid AND c.eventtype = 1 " +  
"WHERE " +  
"	a.eventtime " +  
"		BETWEEN '" + dateFormat({Alarm Analysis.startDate}, "yyyy-MM-dd HH:mm:ss") + "' AND '" + dateFormat({Alarm Analysis.endDate}, "yyyy-MM-dd HH:mm:ss") + "' " +  
"	AND a.eventtype = 0 " +  
"GROUP BY " + 
"	" + {Alarm Analysis.Alarms By Selection.Selection.groupBy} + " " + 
"ORDER BY " + 
"	" + {Alarm Analysis.Alarms By Selection.Selection.groupBy} + " ASC",

"SELECT " + 
{Alarm Analysis.Alarms By Selection.Selection.groupBy} + " AS label, " + 
"	COUNT(*) total " +
"FROM " + 
"	alarm_events a " +  
"		LEFT JOIN alarm_events c ON c.eventid = a.eventid AND c.eventtype = 1 " +  
"WHERE " +  
"	a.eventtime " +  
"		BETWEEN '" + dateFormat({Alarm Analysis.startDate}, "yyyy-MM-dd HH:mm:ss") + "' AND '" + dateFormat({Alarm Analysis.endDate}, "yyyy-MM-dd HH:mm:ss") + "' " +  
"	AND a.eventtype = 0 " +  
"GROUP BY " + 
"	" + {Alarm Analysis.Alarms By Selection.Selection.groupBy} + " " +  
"ORDER BY " + 
"	" + {Alarm Analysis.Alarms By Selection.Selection.groupBy} + " ASC",

"SELECT 1")

End result is a table that looks like you’d expect:

Note that I haven’t tested the MySQL or MSSQL portions of the expression (though it’s just a line + comma removal so it’s likely fine).

Edit: on reflection I can see where someone could find value in this, though I think I still wouldn’t want it in my situation. If I did want it, I’d probably make a new query that only pulls duration, duplicate the bar chart to show the new query’s data, and use a toggle button to switch between the two.

Cory.grube · June 3, 2021, 6:17pm

Made a few other quality of life improvements that others might find useful.

Made both #1 and #2 have line breaks between raw number and parenthetical
Reformatted #2 to use HH:mm:ss format instead of duration in seconds.

New expression for #1

"<HTML><p style='text-align:center;'>" +
numberFormat(
	toInt(try({Alarm Analysis.mostFrequent}["total"], 0)),
	"###0") + 

"<BR>(" + 
	
numberFormat(
	try(toInt(try({Alarm Analysis.mostFrequent}["total"], 0)) / toInt(sum({Alarm Analysis.alarmSummary}, "total")), 0),
	"##0.#%") + 

")" + 
"</p></HTML>"

New expression for #2

"<HTML><p style='text-align:center;'>" +
numberFormat(floor(toInt(try({Alarm Analysis.longestDuration}["total"], 0)) / 3600), "00") + ":" +
numberFormat(floor((toInt(try({Alarm Analysis.longestDuration}["total"], 0)) % 3600) / 60), "00") + ":" + 
numberFormat(((toInt(try({Alarm Analysis.longestDuration}["total"], 0)) % 3600) % 60), "00") + 

"<BR>(" + 
	
numberFormat(
	try(toInt(try({Alarm Analysis.longestDuration}["total"], 0)) / toInt(sum({Alarm Analysis.alarmSummary}, "totalTime")), 0),
	"##0.#%") + 

")" + 
"</HTML>"

I’m planning to do a similar format change on the pie chart that lists alarms by duration, I just need to figure out the right way to do it when I have time.

zxcslo · July 18, 2021, 2:46pm

Try this:
https://forum.inductiveautomation.com/t/pie-chart-labels/37259/7

Cory.grube · July 19, 2021, 2:13pm

Great, thank you! I never did get around to it on my own time, so this will definitely help make it an easier process.

Bart.van.Deurzen · January 11, 2023, 9:29pm

The y-axis label is simply wrong. I mean, this is not a matter of confusion, it's just wrong.
Thank you for posting this, I hope somebody fixes the template.

djgilson · November 22, 2023, 8:15pm

I have a question on filtering the alarms. We have multiple plants with all the alarms going into one MSSQL DB. How can we filter the alarms used by the Alarm Analysis project to just point to a specific plant or department?
We set up a sperate alarm journal profile with a table name for the first area we want to use this. The template pulls in all alarms from the entire DB. Any way to filter it to the data table we want to use?

Thanks, Duane