• State Suggested Answer
  • Replies 4 replies
  • Answers 1 answer
  • Subscribers 12 subscribers
  • Views 866 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

    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)

Reply
  • 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)

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