Migrating Tag History in Ignition's Tag Historian

The Ignition Tag Historian module is a powerful tool that efficiently stores tag history in designated SQL tables. This data can be accessed through scripting, reporting, and historical bindings and has built-in features such as automatic purging and partitioning to avoid overloading the database. The module also allows for easy viewing of tag trends by allowing users to drag and drop tags on Vision's Easy Chart and Perspective's Power Chart components.

Despite the great capabilities of the Tag Historian, the format in which data is stored can complicate the process of directly querying these tables. As a result, migrating tag history from one tag to another after say, renaming or moving a tag, may prove challenging. These types of migrations may need to be performed after reorganizing the tag structure of a project or replacing a sensor while maintaining continuous tag history data.

The Example

For simplicity's sake, we will go over an example in which a new tag is added to a project under these conditions:

1. The old and new tags are standalone tags not found in any UDTs.
2. The tag group of the tags did not change.
3. Neither the gateway nor the gateway name changed.
4. Only one gateway is used.

It's worth noting that tag history can still be migrated when some or none of the above conditions apply! There are several nuances each of the conditions requires that one must consider when performing migration. Knowledge of all Ignition built-in historian tables is key to ensuring a successful migration for more complex conditions. Due to the simplicity of the example used, we will only focus on two tables.

Let's start off with a single tag called "TagX". We want to transfer the history of this tag to a new tag, "TagY", that we'll introduce later. According to this Power Chart image, TagX has been active since at least 12:40PM and has been holding at a steady value of 2.

Tag Historian Tables

We can find this tag in one of Ignition's historian tables, sqlth_te. This contains all of the non-data information about a tag. The information in this table is a great starting point to understanding the other tables created by the historian. The most important columns are:

• id: A unique identifier for a tag
• tagpath: The tag path (without the provider), all lowercase
• scid: id of the tag group that tag is in
• created: An epoch timestamp of when the tag began storing tag history
• retired: An epoch timestamp of when the tag stopped storing tag history or was deleted

We also have the tables in which the history is actually stored. If monthly partitioning is set up, the name of the table will look something like sqlt_data_A_B_C where A is the driver id or gateway identifier, B is the year, and C is the month. In this example, our data table is sqlt_data_1_2022_11, so we know that this data was taken in November of 2022. The columns here log the actual values of the tags storing history. The columns in this table are:

• tagid: The id from the sqlth_te table
• ___value: The value recorded for the tag
• dataintegrity: The quality code of the value recorded
• t_stamp: The epoch timestamp at which the value was recorded

We can also see TagX, which has a tagid of 5, started at a value of 0, and then transitioned to a value of 2 at 12:40PM.

Adding TagY

Next, we introduce a new tag called "TagY", assigned a tagid of 6 by Ignition, that begins collecting information at 12:52.

From 12:52 to about 12:53, both tags are reading a value of 2. The sensor for TagX goes offline just before 12:53, leaving only TagY online. At about 12:58, TagY then drops down to 1.

Performing the History Migration

To transfer the history of TagX to TagY, all we have to do is run a simple update query to replace the tagpath of TagX with the tagpath of TagY. The following script examples use Ignition's script console to query the database. Because these scripts are only querying the database, one may use whatever query tool they prefer.

The sqlth_te table now looks like this.

While sql_data_1_2022_11 will still look the same, the tag history of TagX has transferred over to TagY. The history for TagY now goes beyond 12:52PM, its start time and TagX is no longer in the tag browser for the Power Chart component. We can also see that Ignition continues to collect and store history for TagY when the value rises to 3 at 1:11PM.

Learn more about DMC's Ignition expertise, and contact us to get started on your next HMI, SCADA, or MES project!