Finging object wich caused last alarm

[IgorSivakov]

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

[AdamWoodland]

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]

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

 

[AdamWoodland]

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 · Accepted solution]

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]

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

[AdamWoodland]

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]

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]

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]

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..