# Alarm Analysis Queries

**URL:** <https://forum.inductiveautomation.com/t/alarm-analysis-queries/80657>\
**Category:** Ignition\
**Tags:** vision\
**Created:** [October 18, 2023, 6:29pm UTC](https://forum.inductiveautomation.com/t/alarm-analysis-queries/80657 "2023-10-18T18:29:50Z")\
**Posts on this page:** 2\
**Page:** 1

<div class="post-metadata">

**Author:** ![AJBerera](https://avatars.discourse-cdn.com/v4/letter/a/9fc29f/32.png) [@AJBerera](https://forum.inductiveautomation.com/u/AJBerera)\
**Post date:** [October 18, 2023, 6:29pm UTC](https://forum.inductiveautomation.com/t/alarm-analysis-queries/80657/1 "2023-10-18T18:29:50Z")

</div>

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

Alarms by count

```auto
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

```auto
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](https://us1.discourse-cdn.com/inductiveautomation/original/3X/9/f/9fc5adcc8b5eb8534abbec4a2a475faf75b64a54.png)

---

<div class="post-metadata">

**Author:** ![AJBerera](https://avatars.discourse-cdn.com/v4/letter/a/9fc29f/32.png) [@AJBerera](https://forum.inductiveautomation.com/u/AJBerera)\
**Post date:** [October 18, 2023, 6:51pm UTC](https://forum.inductiveautomation.com/t/alarm-analysis-queries/80657/2 "2023-10-18T18:51:13Z")

</div>

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.

```auto
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

```