SQL historian

SQL historian

Asset Health can read time-series history straight out of your own SQL database, such as Ignition's tag historian, a SCADA logger or your own logging table, through your on-site connector. It uses that history for trends, live values and thresholds, and never writes to your database.

Permissions: Connecting, editing and mapping a historian source needs DataMagik - Builder and the Asset Health licence. Anyone who can open the Asset Health screens can see the trends a historian feeds.

What you need first

• An on-site connector serving the site. See Connector setup.
• A local database connection to the historian database on that connector: SQL Server (2012 or later), PostgreSQL (9.4 or later) or MySQL (8.0 or later). See Local databases.
• The site linked to a connector site. If it is not, only connections that route to any connector are offered. Link it on the Sites screen.

Read-only, always

Nothing is ever written to your database. Every query the connector runs is a SELECT. Your connection's own read-only setting still applies, and on SQL Server that means the account's grants. The connector on your site runs every query, so the database credentials never leave it. Nothing is copied either: every trend is read again from your table.

Queries are also bounded: column names must be real columns of the table you picked, every value you type is sent as a parameter, and one read returns at most 10,000 rows over at most 400 days.

Connect a historian

Open Settings, then Historian sources, and select Connect a SQL historian. The wizard has five steps.

1. Database. Pick a local database Connection. Each shows its type, whether it is Read-only, and its health (Connected, Unreachable or Not checked). If none serves the site, use + New local DB connection, which opens /connectors/local-databases. Select Find tables.
2. Table. Every table is ranked by how much it looks like time-series history: a timestamp column, numeric values and a tag-name column. Each row shows Rows, Looks like (the layout and a score) and Why. Select Use this on the right one.
3. Columns. The newest rows are sampled, and every choice is filled in from what they show. Check them against the sample:
   • Layout: Narrow (a tag column and a value column, one reading per row) or Wide (one column per signal).
   • Timestamp column and Timestamp kind: date and time with no zone, date and time with zone, epoch milliseconds or epoch seconds. For a date and time with no zone, set the Stored time zone.
   • For a narrow table, the Tag column, Value column and an optional Quality column.
   • Poll every: how often new readings are folded into live values. Choose Not polled (trends only) to use the source for history only.
   Select Looks right · map signals. The source is saved as a draft, switched off, so nothing polls until the last step.
4. Map signals. For each tag or column, pick the asset or workcenter it belongs to, a signal name and a unit. Suggestions come from the tag or column names; change any of them, or leave one as Not mapped (skip). Signal names are lower-case letters, digits and underscores, starting with a letter. An asset takes named signals only. A workcenter can also take a standard role, such as temperature, by its name. Select Preview trends.
5. Preview and save. The last 24 hours of each mapped signal is read from your database and drawn as a trend. Hover over a trend, or use the arrow keys, to read a value. The page says what saving will do. Select Save historian source to save the mapping and switch the source on.

Ignition tag historian

Ignition stores history in partition tables named sqlt_data_…, with tag paths in sqlth_te. Discovery recognises these and labels them Ignition tag historian; pick any one partition. Every read then works out which partitions cover the time window, using Ignition's own partition list, and reads them together, so a trend can cross weekly or monthly partitions. Tags are named by their tag paths. Only readings of good quality are read. A text tag, such as a machine state, is read as text: its trend shows the latest value, and its live value is a word rather than a number.

What a historian feeds

• Trends. The Analytics screen's signal history and the asset detail are read from your historian only, every time.
• Live values. When a source polls, the newest reading of each mapped signal becomes that signal's live value, so equipment with no PLC connection still gets a live tile and a key signal on the line schematic.
• Thresholds. Historian-fed values are watched by threshold conditions like any other signal, so a historian value can open an incident and an insight. See Asset Health setup.

When the PLC and the historian both report the same signal, the PLC value is used while it is fresh. A historian value is used only when it is newer.

Polling and failures

The list on Historian sources shows each source's state, Last polled and when the next poll is due. If a poll fails, for example because the connector is offline or the table changed, the source shows Failing with the number of failed polls and the error. The poller tries again later, waiting longer after each failure, up to 30 minutes between attempts.

Things to know

• Many historians log a value only when it changes. A steady signal writes no new row, so its live value turns stale 15 minutes after its last logged change, even though the machine has not changed. Thresholds skip stale values.
• Polling looks for the newest reading. Rows that arrive late with older timestamps are not used for live values, but they do appear in trends.
• Deleting a source removes its mapped series, and their signals stop updating. Nothing in your database is touched.