ArchestrA - Disabled and Silenced Alarms

It can be very useful to disable or silence alarms in ArchestrA, for example during commissioning, testing and maintenance.  However when you inhibit or silence alarms on an object, it is handy to keep track of these as they won't come up in the alarm list.  The last thing that you want is an operator leaving the alarms disabled after testing and subsequently missing an important event.

Here is one method of keeping track of which objects have their alarms silenced/disabled/inhibited.

I got the idea from here, which performs a query on the alarms database on the historian.  The alarms database (A2ALMDB) records every write to a tag performed by a user, so in theory you can trawl the database for all writes done to tags involved in the alarm disabling process.  Note that by default this does not include tags that are written to in object scripts, so if you disable tags this way (which I used to) then this won't work out the the box.  I moved the logic from the object to the graphics for this reason, as graphics scripts are treated as user writes, not object writes.

The query from the post above was as follows

WITH dateorderedcommandevents AS 
(
    SELECT *, RNum=ROW_NUMBER() OVER (PARTITION BY [TagName] ORDER BY EventStamp DESC)
    FROM dbo.v_EventHistory
    WHERE [TagName] LIKE '%AlarmModeCmd'
    AND [Description] LIKE '%Write success%'
)
SELECT EventStamp, LEFT(TagName, LEN(TagName) - 13) , Area, Value, Operator, OperatorNode
FROM dateorderedcommandevents
WHERE RNum=1 AND NOT Value='Enable'

This doesn't cover all the use cases that I wanted, so I modified it (heavily) to the following:

Select EventStamp, LEFT(TagName, LEN(TagName) - CHARINDEX('.', REVERSE(TagName), 0)) as Item, 
RIGHT(TagName, CHARINDEX('.', REVERSE(TagName), 0) - 1) as Method, Value, UserFullName from
(
    SELECT Max(EventStamp) as ev, TagName as tn
    FROM dbo.v_EventHistory
    WHERE TagName LIKE '%AlarmInhibit' or TagName like '%AlarmModeCmd' or TagName like '%PlantState'
    AND [Description] LIKE '%Write success%'
    Group by TagName
) as tb1
left join dbo.v_EventHistory on EventStamp = tb1.ev and TagName = tb1.tn
Where not value = 'Running' and not value = 'Enable' and not value = 'false'

It searches for all tags that end in AlarmInhibit, PlantState, or AlarmModeCmd.  These are some of the ways that one can control alarms on objects and areas.  It filters out the list to only include the most recent write for each tag, and then checks the state of this tag.  Note that I have an additional plant state called "Testing" that I use to silence an entire area.  We can wrap this up in a SqlDataGrid object and present it as a list, or use it to set an alarm to remind the operator that there are inhibited alarms in the system.

UPDATE 24/1/17:  When using the Historian as the backing store for the alarms, the above query won't work as the Historian has several limitations when it comes to SQL query syntax.   The following, much longer and more cumbersome query will work.  If anyone comes up with a shorter version that works I'd be interested to see it.

select EventStamp, LEFT(TagName, LEN(TagName) - CHARINDEX('.', REVERSE(TagName), 0)) as Item, 
RIGHT(TagName, CHARINDEX('.', REVERSE(TagName), 0) - 1) as Method, Value, UserFullName as Operator from
(
    SELECT Max(EventStamp) as ev, TagName as tn from
    (
        SELECT  * FROM dbo.v_EventHistory WHERE TagName LIKE '%PlantState' 
        AND [Description] LIKE '%Write success%' and  EventStamp > '20160101'
        union 
        SELECT  * FROM dbo.v_EventHistory WHERE TagName LIKE '%AlarmModeCmd' 
        AND [Description] LIKE '%Write success%' and  EventStamp > '20160101' 
        union 
        SELECT  * FROM dbo.v_EventHistory WHERE TagName LIKE '%AlarmInhibit' 
        AND [Description] LIKE '%Write success%' and  EventStamp > '20160101'
    )
    tb3 group by TagName
)
as tb1 left join 
(
    select * from  dbo.v_EventHistory where EventStamp > '20160101'
)
tb2 on EventStamp = tb1.ev and TagName = tb1.tn Where not value = 'Running' and not value = 'Enable' and not value = 'false'";

| October 30th, 2016 | Posted in SCADA |

Leave a Reply