Question:
How can I return the active alarms in Elipse E3 via database records?
Solution:
In order to return the active alarms in Elipse E3 (acknowledged or not), you must execute the following query:
SELECT * FROM Alarms RIGHT JOIN (SELECT * FROM (SELECT Min(EventCLSID) AS EventCLSID, SUM(OutTimeDbl) AS OutTimeDbl FROM Alarms WHERE InTime > DATEADD(hh, -48, GETDATE()) GROUP BY EventCLSID)tbA WHERE OutTimeDbl = 0)tbB ON Alarms.EventCLSID = tbB.EventCLSID
In the figure below, you can see the query’s result:
Now, let’s see how this happens.
First of all, the first query is used for grouping the CLSIDs from the last 48 hours, and then adding the data from OutTimeDbl parameter. That way, we will know all the alarms recorded during this period.
SELECT Min(EventCLSID) AS EventCLSID, SUM(OutTimeDbl) AS OutTimeDbl FROM Alarms WHERE InTime > DATEADD(hh, -48, GETDATE()) GROUP BY EventCLSID
Then, in the second part we will separate only the CLSIDs whose values from OutTimeDbl parameter is zero, which means this alarm hasn’t left its condition yet:
SELECT * FROM (SELECT Min(EventCLSID) AS EventCLSID, SUM(OutTimeDbl) AS OutTimeDbl FROM Alarms WHERE InTime > DATEADD(hh, -48, GETDATE()) GROUP BY EventCLSID)tbA WHERE OutTimeDbl = 0
Finally, the last query (the first in this article) returns the alarms whose CLSIDs are the same as the ones returned in the query above. Therefore, we have the registers of alarms that still haven’t left the active condition.
In order to return only alarms that remain active when they get in, you must create a filter with Acked parameter values, as seen below:
SELECT * FROM Alarms RIGHT JOIN
(SELECT * FROM (SELECT Min(EventCLSID) AS EventCLSID, SUM(OutTimeDbl) AS OutTimeDbl FROM Alarms WHERE InTime > DATEADD(hh, -48, GETDATE())
GROUP BY EventCLSID)tbA
WHERE OutTimeDbl = 0)tbB ON Alarms.EventCLSID = tbB.EventCLSID
WHERE Acked = 0
Query’s result:
NOTE: In this article, we used Microsoft SQL Server as the database for creating the queries.
Related articles:
