• State Suggested Answer
  • Replies 4 replies
  • Answers 1 answer
  • Subscribers 12 subscribers
  • Views 864 views
  • Users 0 members are here
Options
Related

How to query object attributes in the historian that still exist in the GR?

Vaibhav Pandey

I want to create a stored procedure that queries for all the tags in Historian that contain a specific string in the name such as "#PV", belong to a specific Area and also currently exist in the IDE.

I am using the "dbo._Tag" table and "dbo._TagExtendedProperty" table, but both these tables return all the historical tags including the deleted tags. I only want currently existing tags. I thought of using the "dbo.History" table with "QualityDetail" property set to "Good" but this table needs the tag names as parameters to work and so I can't use it to get all tags with contains a specific string like "#PV".

This is the query I am currently using: 

SELECT [TagName] as Tag,
[TagExtendedPropertyKey],
[PropertyNameKey],
CAST([PropertyValue] AS NVARCHAR) as AreaLocation,
[ChangeVersion]
FROM [Runtime].[dbo].[_TagExtendedProperty]
WHERE [TagName] LIKE ''%Ai1Scaled#PV''
AND [PropertyNameKey] = ''3'') AS datasource
WHERE [AreaLocation] LIKE ''%' + @Areaname + '%''

Parents
  • 0 in reply to Vaibhav Pandey

    There isn't a way to determine if a Historian tag currently exists in the Galaxy. This is because object renames and deletes are not echoed to Historian.

    The closest way to approximate this is based on the tags which have "recent" data--if an object was undeployed and renamed/deleted a week ago, the tags linked to the old name won't have any data in the last week. Of course, a setpoint that hasn't changed in a week, also won't have a new value in that time, but if the Engine is configured with "Reconnect as soon as possible & do not mark disconnects" left at the default of "unchecked", then tags for undeployed objects will have a "QualityDetail=24". We can adapt your query to consider this as:

    select t.TagName, AreaLLocation=CAST([PropertyValue] AS NVARCHAR), vValue, DateTime
    from TagExtendedPropertyInfo t
    inner remote join Live l on l.TagName = t.TagName
    where PropertyName='Location'
    and CAST([PropertyValue] AS NVARCHAR) like '%Ai1Scaled#PV%'
    and DateTime > dateadd(day,-2,getdate())
    and QualityDetail <> 24

    Adjust the age tolerance in the "dateadd" function to suite your needs.
Reply
  • 0 in reply to Vaibhav Pandey

    There isn't a way to determine if a Historian tag currently exists in the Galaxy. This is because object renames and deletes are not echoed to Historian.

    The closest way to approximate this is based on the tags which have "recent" data--if an object was undeployed and renamed/deleted a week ago, the tags linked to the old name won't have any data in the last week. Of course, a setpoint that hasn't changed in a week, also won't have a new value in that time, but if the Engine is configured with "Reconnect as soon as possible & do not mark disconnects" left at the default of "unchecked", then tags for undeployed objects will have a "QualityDetail=24". We can adapt your query to consider this as:

    select t.TagName, AreaLLocation=CAST([PropertyValue] AS NVARCHAR), vValue, DateTime
    from TagExtendedPropertyInfo t
    inner remote join Live l on l.TagName = t.TagName
    where PropertyName='Location'
    and CAST([PropertyValue] AS NVARCHAR) like '%Ai1Scaled#PV%'
    and DateTime > dateadd(day,-2,getdate())
    and QualityDetail <> 24

    Adjust the age tolerance in the "dateadd" function to suite your needs.
Children
No Data
© 2024 AVEVA Group Limited or its subsidiaries. All Rights Reserved.