Vision alarm analysis template - Alarms by [time]

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.

1 Like

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

image

  • 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.

1 Like