History tag table sqlth_te retired and fingerprint columns

Ignition
1

I’m trying to get a full list of all historic tags and thought the most efficient and most importantly fast method to do this would be to query the sqlth_te table. I don’t want to show invalid/no-longer-existent tags, so I’ve filtered out retired values that are non-NULL. Looking at the data however that was returned with this simple query, I noticed some rows returned that shouldn’t be there as the tags no longer exist but their retired field is still NULL.
For context:
Query:

SELECT tagpath FROM sqlth_te where retired IS NULL

Count: 31,902
Number of those tags that no longer exist: 14,536 (46%!!!)

I’m also wondering what the fingerprint column is for? I looked in the SDK and it’s not listed in the specs, but it seems to store the OPC-UA item path of the tag. However a lot of tags/rows are missing this and this is just NULL. What’s the reason for this? (this system was upgraded from 7.9.9 to 8 about 1.5yrs ago if it’s relevant).

https://docs.inductiveautomation.com/display/SE/Tag+History+Tables+Reference

2

There was a bug at one point in 7.9, I’m not sure of when/if it was cleared up, where historical tags were not marked as retired properly. Support helped me clear up a bunch of these back in 7.9.3. May not be relevant for this case.

3

That's what I'm doing at the moment :confused: Just triple checking they're not actually there...

4

We’ve had the same problem and we noticed that this post: [What Causes a History Tag to be Retired - #2 by PGriffith] doesn’t seem to be relevant anymore. In our project tags only get retired if the history is true and the tag gets deleted. If you disable the tag or turn history off and then delete the tag the retire column will stay null forever.

Which is a problem for us since the tag browser tree component will show these non-retired tags. Has anyone found a solution for this? Since now our only option seems to be to manually retire each tag in the database by adding a random number to the retire column.

5

Why add a random number and not the current unix time?
Definitely sounds like an issue. I have a script to find orphaned tags in sqlth_te and retire them via script.

6

Using the current unix time indeed makes more sense. Would you want to share this script?

7 
now = system.date.now()
now.time

Edit: Oh you probably meant the other script whoops, I’ll have to get back to you as i’m not at my laptop

1 Like
8

Yes it was, If you’d be able to share that it would be awesome.

9

I tend to use the latest retired stamp from the table.
So you can just do a

SELECT TOP 1 [retired] FROM sqlth_te ORDER BY [retired] DESC

And then update the record using that value in the script.