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
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?
You are grouping by
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 source, MAX(eventtime) AS eventtime_max, eventtype
WHERE eventtype = 0
GROUP BY source
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)
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?
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?
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.
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).
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!
Thanks. I obviously read wrong. Works like a champ!