Tag Browse Tree - How to Remove Old Tags From Historical Tree

Ignition
1

[v8.1.18]

I accidentally enabled the tag historian on a few tags in my project. I’ve since corrected this and disabled historian on them. However, they still show up in my historical tag browse tree and I’d like them gone!

In the past I’ve accomplished this by deleting all the sqlt… tables in the database and restarting the historian module. I’d like to do this more surgical this time. If I could just filter them out of the tag browse tree that would work as well (though some are sole contents of folders that also shouldn’t be visible).

I don’t care about the data that was historized accidentally - it can either be deleted or left to rot in the stlt_data_… tables until it gets pruned (there isn’t that many rows). Most of these tags already have a timestamp in their “retired” colum (sqlth_te table) and I need to disable the historian from the remainder that aren’t retired yet.

I’ve tried deleting the rows from the sqlt_data_… tables , deleting their row from sqlth_te, and restarting the historian module. This didn’t seem to work.

What is the correct way to do this?

EDIT: I used (I’m using PostgreSQL):

delete FROM sqlt_data_1_2022_07
where tagid in (SELECT id FROM sqlth_te where retired is not null)
2 Likes
2

This should be sufficient I think. I often clean things up with something along the lines of

delete from sqlt_data_X_XXXX_XX
inner join sqlth_te
where tagid = id and retired is not null

then

`delete from sqlth_te where retired is not null`
3

I was thinking the same thing, Pascal, but the sqlth_te table doesn’t tell you if the tag has history enabled or not. That’s stored somewhere in the tag database. Your SQL code will delete history where the tags have been retired but OP’s tags are still active. It’s only history has been disabled.

While we’re on the topic - does anyone know what the “th_te” in the table name means?

4

Right.
Still, it’s the same concept he tried, and it usually works for me. Deleting entries in the historian tables should be sufficient to clean things up.

‘tag historian’ and ‘table entry’ ?

5

Nah, the table has nothing to do with history!

6

Well it’s listed in the tag history tables:
https://docs.inductiveautomation.com/display/DOC80/Ignition+Auto-Generated+SQL+Tables#IgnitionAutoGeneratedSQLTables-TagHistory

7

Maybe the better question is where does the tag browse tree decide what tags should be shown in historical mode? From the sqltxxx tables or somewhere else?

8

When a Tag History is enabled an entry is made in the sqlth_te table, when the History is disabled the tag is marked as “retired” (this also occurs if the tag name is changed, so anything that results in a new tag path). The Tag Browse Tree pulls all records from this table, it doesn’t differentiate between retired and non-retired tags. The only way (that I have found) to get the tags to not show up in the component is to remove the record from sqlth_te. You should also remove all of those records from the actual partition tables as well, although strictly not required its just good practice.

9

As far as I can tell, the table only exists as part of the Tag Historian. Tags that have never had history enabled will not be included in the table.

10

Ah! That makes sense then. @pascal.fragnoud is correct.

Thank you both.

11

It looks like the earlier deletions worked, I just hadn’t turned off tag history on the tags like I thought I did.

Thanks!

12

Having the same kind of issue with MSSQL tables but removing the NOT NULLS from the sqlt_data tables and the sqlth_te table did not get rid of them. Many were found before deleting them but others are stuck as their retired value is NULL.

	DELETE d
	FROM sqlt_data_1_2024_10 AS d
	INNER JOIN sqlth_te AS t
	ON d.tagid = t.id
WHERE t.retired IS NOT NULL;

-------
delete from sqlth_te where retired is not null

If the ones that should not be there all have NULL in the retired column how can you find and delete them all?

I can manually see that there should not be a DataHall210 folder and (I think) use the delete query from above changing WHERE clause but there may be more or a might delete the wrong ones.

WHERE tagpath LIKE 'PHX/Phase2/Electrical/CP700/DataHall210%';

I've done the NOT NULL stuff before but not sure why these did not get marked retired. Somehow system.tag.queryTagHistory is finding some of them with a value of 0 no mater the date rage.