Alarm Analysis Queries

Ignition
1

I have some queries that used to be from the vision demo project (maybe they still are being used)

Alarms by count

With TopItems AS  
(
SELECT displaypath 
, COUNT(*) cnt 
, ROW_Number() OVER (ORDER BY count(id) DESC) AS NUM
FROM alarm_events a 
WHERE eventtime	
BETWEEN '2023-07-09 09:40:24.253' AND  '2023-07-12 09:40:24.253'
AND Priority > 0
AND eventtype = 0
AND a.displaypath != ''
GROUP BY displaypath) 
SELECT displaypath, COALESCE(cnt,0) cnt 
FROM TopItems
WHERE NUM <= 5

Union ALL

SELECT 'Other', COALESCE(SUM(cnt),0) 
From TopItems  
WHERE NUM > 5 
ORDER BY cnt DESC, displaypath ASC

Alarms by Duration

SELECT TOP 5
a.displaypath,
SUM(DATEDIFF(MINUTE, 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 '2023-10-15 09:40:24.253' AND  '2023-10-18 09:40:24.253'
AND a.priority > 0
AND a.eventtype = 0
AND a.displaypath != ''  
GROUP BY
a.displaypath
ORDER BY
duration DESC, a.displaypath ASC

I also have some associated data PartNumber and LotNumber as strings on my alarms and I would like to be able to filter this query down to use these too. I am tempted to give up on the SQL solution and manipulate this data in python to get what I want. I can see that the id coulmn in alarm_event_data is going to have to be matched up with the alarm_events id column but I was wondering if anyone has done these modifications to these queries before and feels like sharing..

image

2

This was looking more difficult at first glace, but I think this might work for the alarms by count query and the same join and where clauses work for the other queries too.

With TopItems AS  
(
SELECT a.displaypath 
, COUNT(a.id) cnt 
, ROW_Number() OVER (ORDER BY count(a.id) DESC) AS NUM
FROM alarm_events a 
INNER JOIN  alarm_event_data d on d.id = a.id
WHERE eventtime	
BETWEEN '2023-07-09 09:40:24.253' AND  '2023-07-12 09:40:24.253'
AND a.Priority > 0
AND a.eventtype = 0
AND a.displaypath != ''
AND a.id in (SELECT id from alarm_event_data WHERE propname = 'PartNumber' AND strvalue = 'CRYPART')
AND a.id in (SELECT id from alarm_event_data WHERE propname = 'LotNumber' AND strvalue = 123)
AND d.propname = 'eventValue'
GROUP BY displaypath) 

SELECT displaypath, COALESCE(cnt,0) cnt 
FROM TopItems
WHERE NUM <= 5

Union ALL

SELECT 'Other', COALESCE(SUM(cnt),0) 
From TopItems  
WHERE NUM > 5 
ORDER BY cnt DESC, displaypath ASC