• State Suggested Answer
  • Replies 4 replies
  • Answers 1 answer
  • Subscribers 12 subscribers
  • Views 865 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 Reply Children
  • 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.
  • 0 in reply to Vaibhav Pandey

    Hi, in addition to Elliotts comment, there are at least a possibility to get the Attributes/UDAs that has History enabled in the galaxy database.

    Perhaps it can move you closer to your goal.

    This list will return any attribute, deployed or undeployed so it is not to be taken as a list of a tag that is active and logged in Historian. Perhaps you can refine it or use it to combine with the data from the query provided by Elliott

    SELECT g.tag_name + '.' + p.primitive_name
    AS name, p.gobject_id, p.package_id, p.mx_primitive_id
    FROM dbo.gobject AS g
    INNER JOIN dbo.primitive_instance AS p
    ON p.gobject_id = g.gobject_id AND p.execution_group = 18
    INNER JOIN dbo.primitive_definition AS pd
    ON p.primitive_definition_id = pd.primitive_definition_id
    AND pd.primitive_name <> ''
    INNER JOIN dbo.package AS pkg
    ON p.gobject_id = pkg.gobject_id
    and p.package_id = pkg.package_id
    and pkg.package_id = g.checked_in_package_id
    where (g.is_template = 0) AND (g.namespace_id = 1)

© 2024 AVEVA Group Limited or its subsidiaries. All Rights Reserved.