{"id":11111,"date":"2020-07-14T10:29:46","date_gmt":"2020-07-14T13:29:46","guid":{"rendered":"http:\/\/kb.elipse.com.br\/en\/?p=11111"},"modified":"2020-07-14T10:29:46","modified_gmt":"2020-07-14T13:29:46","slug":"kb-108744-how-to-return-the-active-alarms-in-elipse-e3-via-database","status":"publish","type":"post","link":"https:\/\/kb.elipse.com.br\/en\/kb-108744-how-to-return-the-active-alarms-in-elipse-e3-via-database\/","title":{"rendered":"KB-108744: Returning Elipse E3’s active alarms via database."},"content":{"rendered":"

Question:<\/strong><\/h2>\n

How can I return the active alarms in Elipse E3<\/a> via database records?<\/p>\n

Solution:<\/strong><\/h2>\n

In order to return the active alarms\u00a0 in Elipse E3 (acknowledged or not), you must execute the following query:<\/p>\n

SELECT * FROM Alarms RIGHT JOIN \r\n(SELECT * FROM (SELECT Min(EventCLSID) AS EventCLSID, SUM(OutTimeDbl) AS OutTimeDbl FROM Alarms\r\nWHERE InTime > DATEADD(hh, -48, GETDATE()) \r\nGROUP BY EventCLSID)tbA \r\nWHERE OutTimeDbl = 0)tbB ON Alarms.EventCLSID = tbB.EventCLSID<\/pre>\n
 <\/p>\n
In the figure below, you can see the query’s result:<\/p>\n
\"retornar<\/p>\n
Now, let’s see how this happens.<\/p>\n
First of all, the first query is used for grouping the CLSIDs from the last 48 hours, and then adding the data from\u00a0 OutTimeDbl <\/em>parameter. That way, we will know all the alarms recorded during this period.<\/p>\n
 <\/p>\n
SELECT Min(EventCLSID) AS EventCLSID, SUM(OutTimeDbl) AS OutTimeDbl FROM Alarms\r\nWHERE InTime > DATEADD(hh, -48, GETDATE())\r\nGROUP BY EventCLSID\r\n<\/pre>\n
Then, in the second part we will separate only the CLSIDs whose values from\u00a0 OutTimeDbl<\/em> parameter is zero, which means this alarm hasn’t left its condition yet:<\/p>\n
 <\/p>\n
SELECT * FROM (SELECT Min(EventCLSID) AS EventCLSID, SUM(OutTimeDbl) AS OutTimeDbl FROM Alarms\r\nWHERE InTime > DATEADD(hh, -48, GETDATE())\r\nGROUP BY EventCLSID)tbA\r\nWHERE OutTimeDbl = 0\r\n\r\n<\/pre>\n
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.<\/p>\n
In order to return only alarms that remain active when they get in, you must create a filter with Acked<\/em> parameter values, as seen below:<\/p>\n
 <\/p>\n
SELECT * FROM Alarms RIGHT JOIN \r\n(SELECT * FROM (SELECT Min(EventCLSID) AS EventCLSID, SUM(OutTimeDbl) AS OutTimeDbl FROM Alarms WHERE InTime > DATEADD(hh, -48, GETDATE()) \r\nGROUP BY EventCLSID)tbA \r\nWHERE OutTimeDbl = 0)tbB ON Alarms.EventCLSID = tbB.EventCLSID\r\nWHERE Acked = 0\r\n\r\n<\/strong><\/span><\/pre>\n
Query’s result:\u00a0<\/span><\/p>\n

\n\"consulta<\/strong><\/span><\/p>\n
NOTE:<\/strong> In this article, we used Microsoft SQL Server as the database for creating the queries.<\/p>\n
Related articles:<\/h3>\n
\n