Welcome to the new Schneider Electric Community

It's your place to connect with experts and peers, get continuous support, and share knowledge.

  • Explore the new navigation for even easier access to your community.
  • Bookmark and use our new, easy-to-remember address (community.se.com).
  • Get ready for more content and an improved experience.

Contact SchneiderCommunity.Support@se.com if you have any questions.

Close
Invite a Co-worker
Send a co-worker an invite to the Exchange portal.Just enter their email address and we’ll connect them to register. After joining, they will belong to the same company.
Send Invite Cancel
84247members
353346posts

Finging object wich caused last alarm

EcoStruxure Geo SCADA Expert Forum

Find out how SCADA systems and networks, like EcoStruxure Geo SCADA Expert, help industrial organizations maintaining efficiency, processing data for smarter decision making with IoT, RTU and PLC devices.

Solved
IgorSivakov
Lieutenant
Lieutenant
0 Likes
9
1361

Finging object wich caused last alarm

Is there a way to find and object in a group, which caused last alarm? I need to display some data on this object for more than 100 plants, so I want to do this in one SQL-query


Accepted Solutions
IgorSivakov
Lieutenant
Lieutenant
0 Likes
0
1328

Re: Finging object wich caused last alarm

It's not quite what I wanted, because I need a list of plants with active alarms. Anyway, our IT department helped me with this task and it's most complicated query I've erer saw. It still has one flaw - if two alarms in one group happened in exact same moment, query will return multiple rows on than group, but it's fine by me. 

SELECT 
    Query.ID,
    DISTINCT(Query.Name),
    Query.AlarmState,
    Query.AlarmLastUpdateTime,
    Query.AlarmId,
    Query.Message,
    Query.FullName,
    Query.MaxSubConditionActiveTime
FROM (
    SELECT 
        FilteredGroup.Id AS ID,
        FilteredGroup.Name,
        FilteredGroup.AlarmState,
        FilteredGroup.AlarmLastUpdateTime,
        CAlarm.Id AS AlarmId,
        CAlarm.Message,
        MaxObject.FullName,
        MaxObject.MaxSubConditionActiveTime
    FROM (
        SELECT
            CGroup.ID,
            CGroup.Name,
            CGroup.AlarmState,
            CGroup.AlarmLastUpdateTime
        FROM CGroup 
        WHERE
            CGroup.AlarmState > 0
            AND CGroup.Name LIKE '%ВНС%'
            AND CGroup.Name NOT LIKE '%(с)%'
    ) FilteredGroup
    JOIN (
        SELECT
            CDBObject.ID,
            MAX(CAlarm.SubConditionActiveTime) as MaxSubConditionActiveTime,
            CDBObject.Fullname
        FROM CAlarm
        JOIN CDBObject
            ON CDBObject.Id = CAlarm.Id
        WHERE
            CAlarm.Message <> ''
        GROUP BY
            CDBObject.ID,
            CDBObject.FullName
    ) AS MaxObject ON FullName LIKE '%' || FilteredGroup.Name || '%'
    JOIN CAlarm
        ON MaxObject.Id = CAlarm.Id
        AND MaxObject.MaxSubConditionActiveTime = CAlarm.SubConditionActiveTime
) Query
JOIN (
    SELECT 
        FilteredGroup.Id,
        MAX(MaxObject.MaxSubConditionActiveTime) AS MaxSubConditionActiveTime
    FROM (
        SELECT
            CGroup.ID,
            CGroup.Name
        FROM CGroup 
        WHERE
            CGroup.AlarmState > 0
            AND CGroup.Name LIKE '%ВНС%'
            AND CGroup.Name NOT LIKE '%(с)%'
    ) FilteredGroup
    JOIN (
        SELECT
            CDBObject.ID,
            MAX(CAlarm.SubConditionActiveTime) as MaxSubConditionActiveTime,
            CDBObject.Fullname
        FROM CAlarm
        JOIN CDBObject
            ON CDBObject.Id = CAlarm.Id
        WHERE
            CAlarm.Message <> ''
        GROUP BY
            CDBObject.ID,
            CDBObject.FullName
    ) AS MaxObject ON FullName LIKE '%' || FilteredGroup.Name || '%'
    JOIN CAlarm
        ON MaxObject.Id = CAlarm.Id
        AND MaxObject.MaxSubConditionActiveTime = CAlarm.SubConditionActiveTime
    GROUP BY FilteredGroup.Id
) AnnotatedQuery
    ON Query.Id = AnnotatedQuery.Id
    AND Query.MaxSubConditionActiveTime = AnnotatedQuery.MaxSubConditionActiveTime
ORDER BY Query.ID, Query.MaxSubConditionActiveTime DESC

 

Condition to filter groups (lines 29-30 and 60-61): 

CGroup.Name LIKE '%ВНС%'
AND CGroup.Name NOT LIKE '%(с)%'

 

 

See Answer In Context

9 Replies 9
AdamWoodland
Commander Commander
Commander
0 Likes
8
1350

Re: Finging object wich caused last alarm

The CAlarm table will be able to give you this info, just JOIN it with CAlarmObject to filter on FullName to the group you need, and then sort by the preferred column (and can do a TOP (1) for just the first row)

IgorSivakov
Lieutenant
Lieutenant
0 Likes
7
1344

Re: Finging object wich caused last alarm

I can't quite understang how I should do this. 

For now, I have querry, which gives me list of plants with alarms. How can I join it with multiple queries, that return top(1) from CAlarm? My SQL knowledge is not so good, so I don't understand how it should work

 Q1.png

AdamWoodland
Commander Commander
Commander
0 Likes
6
1334

Re: Finging object wich caused last alarm

You'll want something like:

 

SELECT TOP( 1 )
	O.FULLNAME, A.SUBCONDITIONACTIVETIME
FROM
	CALARM AS "A" LEFT JOIN CALARMOBJECT AS "O" ON A.ID = O.ID
WHERE
	O.FULLNAME LIKE 'Test.%' AND (A.STATE = 4 OR A.STATE = 2)
ORDER BY
	"SubConditionActiveTime" DESC

 

It basically queries for the latest currently active (i.e. not cleared) alarm in the "Test" group. Note this might not be the latest alarm if another alarm was raised more recently and is already cleared. If you do need the most recent alarm to be active no matter what its current state is you'll have to use CAlarmSummary (or worse CDBEventJournal) but its a similar type of query.

IgorSivakov
Lieutenant
Lieutenant
0 Likes
0
1329

Re: Finging object wich caused last alarm

It's not quite what I wanted, because I need a list of plants with active alarms. Anyway, our IT department helped me with this task and it's most complicated query I've erer saw. It still has one flaw - if two alarms in one group happened in exact same moment, query will return multiple rows on than group, but it's fine by me. 

SELECT 
    Query.ID,
    DISTINCT(Query.Name),
    Query.AlarmState,
    Query.AlarmLastUpdateTime,
    Query.AlarmId,
    Query.Message,
    Query.FullName,
    Query.MaxSubConditionActiveTime
FROM (
    SELECT 
        FilteredGroup.Id AS ID,
        FilteredGroup.Name,
        FilteredGroup.AlarmState,
        FilteredGroup.AlarmLastUpdateTime,
        CAlarm.Id AS AlarmId,
        CAlarm.Message,
        MaxObject.FullName,
        MaxObject.MaxSubConditionActiveTime
    FROM (
        SELECT
            CGroup.ID,
            CGroup.Name,
            CGroup.AlarmState,
            CGroup.AlarmLastUpdateTime
        FROM CGroup 
        WHERE
            CGroup.AlarmState > 0
            AND CGroup.Name LIKE '%ВНС%'
            AND CGroup.Name NOT LIKE '%(с)%'
    ) FilteredGroup
    JOIN (
        SELECT
            CDBObject.ID,
            MAX(CAlarm.SubConditionActiveTime) as MaxSubConditionActiveTime,
            CDBObject.Fullname
        FROM CAlarm
        JOIN CDBObject
            ON CDBObject.Id = CAlarm.Id
        WHERE
            CAlarm.Message <> ''
        GROUP BY
            CDBObject.ID,
            CDBObject.FullName
    ) AS MaxObject ON FullName LIKE '%' || FilteredGroup.Name || '%'
    JOIN CAlarm
        ON MaxObject.Id = CAlarm.Id
        AND MaxObject.MaxSubConditionActiveTime = CAlarm.SubConditionActiveTime
) Query
JOIN (
    SELECT 
        FilteredGroup.Id,
        MAX(MaxObject.MaxSubConditionActiveTime) AS MaxSubConditionActiveTime
    FROM (
        SELECT
            CGroup.ID,
            CGroup.Name
        FROM CGroup 
        WHERE
            CGroup.AlarmState > 0
            AND CGroup.Name LIKE '%ВНС%'
            AND CGroup.Name NOT LIKE '%(с)%'
    ) FilteredGroup
    JOIN (
        SELECT
            CDBObject.ID,
            MAX(CAlarm.SubConditionActiveTime) as MaxSubConditionActiveTime,
            CDBObject.Fullname
        FROM CAlarm
        JOIN CDBObject
            ON CDBObject.Id = CAlarm.Id
        WHERE
            CAlarm.Message <> ''
        GROUP BY
            CDBObject.ID,
            CDBObject.FullName
    ) AS MaxObject ON FullName LIKE '%' || FilteredGroup.Name || '%'
    JOIN CAlarm
        ON MaxObject.Id = CAlarm.Id
        AND MaxObject.MaxSubConditionActiveTime = CAlarm.SubConditionActiveTime
    GROUP BY FilteredGroup.Id
) AnnotatedQuery
    ON Query.Id = AnnotatedQuery.Id
    AND Query.MaxSubConditionActiveTime = AnnotatedQuery.MaxSubConditionActiveTime
ORDER BY Query.ID, Query.MaxSubConditionActiveTime DESC

 

Condition to filter groups (lines 29-30 and 60-61): 

CGroup.Name LIKE '%ВНС%'
AND CGroup.Name NOT LIKE '%(с)%'

 

 

BevanWeiss
Spock
Spock
0 Likes
4
1294

Re: Finging object wich caused last alarm

Is the join needed?

Couldn't this work

 

SELECT TOP( 1 )
	ID->FULLNAME as "FullName", SUBCONDITIONACTIVETIME
FROM
	CALARM
WHERE
	ID->FULLNAME LIKE 'Test.%' AND (STATE = 4 OR STATE = 2)
ORDER BY
	SubConditionActiveTime DESC

 


Lead Control Systems Engineer for Alliance Automation (VIC).
All opinions are my own and do not represent the opinions or policies of my employer, or of my cat..
AdamWoodland
Commander Commander
Commander
0 Likes
3
1287

Re: Finging object wich caused last alarm

Yeah, you're right Bevan, CAlarm.Id is a reference to CAlarmObject so your version of the query will work and is more tidy. It is mostly laziness on my behalf and doing JOINs out of habit as things like the CDBEventJournal aren't a reference 🙂

 

Regarding Igor's query, CAlarm.Message <> '' may not be the best either, (A.STATE = 4 OR A.STATE = 2) might work better, or (A.State > 1) if that's what you're after. How much performance you get by tweaking some of those things are up to you and would depend on how frequently you run them. Play and see which performance works best for you and still gets you the relevant data.

IgorSivakov
Lieutenant
Lieutenant
0 Likes
2
1284

Re: Finging object wich caused last alarm

As I said - I need a list of groups whith a name of an alarm object attached to each, so I can't just go with "select top(1) ...". My goal here is to get nesessary data for dispatcher's mimic. It's the same mimic, I asked about recently here Embedded list format

Now I just need to make my own alarm messages for it, and it will be quite a pain.

Anyway. thanks for commets, everyone!

geoffpatton
Commander
Commander
0 Likes
1
1281

Re: Finging object wich caused last alarm

For each plant You could run a Structured Text program that Queries the Top(1) for that plant and have it write to a Data Table.

Then Query the Data Table to show the most recent alarm of each plant.

 

Don't run your ST programs to often.

 

Calculation String Points may work instead of a Data Table. Then Query the String Points to show the most recent alarm of each plant.

BevanWeiss
Spock
Spock
0 Likes
0
1270

Re: Finging object wich caused last alarm

I'm not sure I'd go with ST Logic here.. since the performance wouldn't scale very well.  Having 100+ logic programs executing rapidly would really bring a system to its knees.

 

I probably would have gone with a nested query and a LEFT OUTER JOIN..

 

i.e. 

SELECT G.FullName
FROM CGroup AS G
LEFT OUTER JOIN
(
  SELECT TOP(1) ID->FullName, SubConditionActiveTime
  FROM CAlarm
  WHERE FullName LIKE G.FullName || '.%'
) AS A
WHERE G.ParentGroupName->FullName = 'Plants'

 

Not sure if my syntax is quite right here.. but something like this will work I'm sure..


Lead Control Systems Engineer for Alliance Automation (VIC).
All opinions are my own and do not represent the opinions or policies of my employer, or of my cat..