Alarm events SQL query

Ignition
,
1

I was looking into the need for multiple filters on alarm status. Correct me if I’m wrong, but you can only have one? So I thought I would get all active alarms from a SQL query. Running a test in the office shows 160ish alarms when I use the alarm status table or bind alarm status function to a table. If I run a SQL query, something like this:

SELECT source, eventid, max(eventtime) as eventtime, eventtype 
FROM alarm_events
WHERE eventtype = 0
group by source, eventid, eventtime, eventtype
order by eventtime desc

I get over 4000. What am I missing on this? I thought eventtype of 0 is only active?

2

You are grouping by eventid and eventtime. Every active event will have a unique eventid and, almost always, a unique eventtime. So they will never group together. You need to remove them from your group by. Also, be aware that the eventid in your select will not be very useful since you do not know which eventid it will return due to the group by. If you are looking for the latest eventid for a given alarm, you would need to incorporate a JOIN or sub-query to accurately get that information in a single query.

Edit: Also, your group by eventtype is redundant since you are only selecting records where eventtype=0. You can remove that from your group by unless you plan to allow other event types to be returned in this query in the future.

Edit 2: Something like this is what I think you’re looking for:

SELECT
	alarm_events_max.source,
	alarm_events.eventid,
	alarm_events_max.eventtime_max,
	alarm_events_max.eventtype
FROM
	(
		SELECT source, MAX(eventtime) AS eventtime_max, eventtype
		FROM alarm_events
		WHERE eventtype = 0
		GROUP BY source
	) alarm_events_max
	LEFT JOIN alarm_events
	ON (alarm_events_max.source = alarm_events.source
		AND alarm_events_max.eventtime_max = alarm_events.eventtime
		AND alarm_events_max.eventtype = alarm_events.eventtype)
ORDER BY
	alarm_events_max.eventtime_max DESC;
3

Thanks @WillMT10. The query I posted above was something I stole from another post on this forum. It was one of a few I tried.

I did run your query, I’m now closer to the actual number (getting 580). But, what I just noticed is I’m getting the results of alarms for tags that no longer exist. I’m guessing the alarm status must take into account only “good” tags?

4

The alarm_events table will hold a history of all your alarms (if you viewed it in the alarm journal and scrolled back to when these tags fired an alarm, you would see them in this component). That query I posted is also grabbing a record for every alarm that has ever had an alarm, even if it is no longer active. If you want to only get “currently active alarms” you would need to modify that query further…and it will start to get a little more complex. Any reason you aren’t using the system.alarm.queryStatus() scripting function?

5

I was hoping for a way to have multiple source/path filters. Typically we arrange things in a logical order so one filter is all we need, but there have been times where tags were all over the place and if we had the ability to filter on 3-4 paths it would be desirable. This isn't a huge thing for me, just one thing on my list to look in to.

6

From the documentation:

List[String] path - A list of possible source paths to search at. The wildcard "" may be used. Works the same as the source argument, and either can be used. [optional]
List[String] source - A list of possible source paths to search at. The wildcard "" may be used. Works the same as the path argument, and either can be used. [optional]

You can pass in multiple paths/sources as lists. Would this not work? I'm only bringing this up because the query is going to get more complex and harder to maintain (and potential query speed performance problems if your alarm_events table grows large).

1 Like
7

I’ll give it a try. I honestly didn’t try because I had read it wasn’t possible. If it works, that would be awesome!

1 Like
8

Thanks. I obviously read wrong. Works like a champ!

1 Like