Chapter 9 — Data and databases
Lesson map
Lesson 09 published. Map, example, EX025.
flowchart TD
N0["Learning outcomes"]
N1["9.1 The historian's role"]
N2["9.2 A minimal schema"]
N3["9.3 Aggregates hide events"]
N4["Synthesis"]
N0 --> N1 --> N2 --> N3 --> N4
N4 --> EX["Worked example"]
EX --> Q["Exercise EX025"]
Logical drawing
flowchart LR
N0["Learning outcomes"]
N1["9.1 The historian's role"]
N2["9.2 A minimal schema"]
N3["9.3 Aggregates hide events"]
N4["Synthesis"]
N0 --> N1 --> N2 --> N3 --> N4 --> Q["EX025"]
[Learning outcomes]
|
v
[9.1 The historian's role]
|
v
[9.2 A minimal schema]
|
v
[9.3 Aggregates hide events]
|
v
[Synthesis]
|
v
[Worked example] --> [Exercise EX025]
Learning outcomes
You will (1) model historian data as time, tag and value; (2) aggregate and filter with SQL in SQLite; (3) detect sampling gaps; and (4) explain why the historian is both a security asset and a security risk.
9.1 The historian's role
A process historian stores time-series data from controllers for trending, reporting and investigation. Because it sits between control and business networks, it is often the bridge an intruder uses — and also the best record an investigator has. NIST SP 800-82r3 recommends placing data that business users need into a DMZ copy so that enterprise users never query the control-zone historian directly.
At LDHP, HIST-1 sits at Purdue level 3. Billing and analytics users should read a DMZ replica, so a compromised office account never opens a session to the historian that also collects from PLC-H.
9.2 A minimal schema
Three columns — timestamp, tag, value — carry most historian content; production systems add quality codes and compression. SQLite, built into Python, is sufficient to learn the queries.
"""Lab 09: query a synthetic historian in an in-memory SQLite database."""
import sqlite3
db = sqlite3.connect(":memory:")
db.execute("create table h(ts integer, tag text, v real)")
rows = [(t, "TT-101", 90 + (t % 5)) for t in range(0, 60, 5)] + [(t, "PT-201", 9.5) for t in range(0, 60, 10)]
rows.append((35, "TT-101", 128.0))
db.executemany("insert into h values(?,?,?)", rows)
for tag, n, mx, mean in db.execute("select tag,count(*),max(v),round(avg(v),2) from h group by tag order by tag"):
print(f"{tag}: n={n} max={mx} mean={mean}")
print("excursions >120:", db.execute("select ts,v from h where tag='TT-101' and v>120").fetchall())
print("gap check PT-201 samples:", [r[0] for r in db.execute("select ts from h where tag='PT-201' order by ts")])
Run with python3 lab_hist.py. Verified output:
PT-201: n=6 max=9.5 mean=9.5
TT-101: n=13 max=128.0 mean=92.92
excursions >120: [(35, 128.0)]
gap check PT-201 samples: [0, 10, 20, 30, 40, 50]
The lab's table holds TT-101 and PT-201 samples with integer timestamps, enough to run the two questions an operator asks most: what was the extreme, and is anything missing. Maya's over-temperature answer came from one query over this table: select ts, v from h where tag='TT-101' and v > 120, which returned one row at t = 35, 128 °C, out of 13 samples examined. The hourly-mean dashboard had shown nothing unusual for the same shift.
9.3 Aggregates hide events
TT-101's mean of 92.92 °C looks normal; the maximum of 128.0 °C at t = 35 does not. Always pair an average with an extreme and a count. The gap check confirms PT-201 was sampled every 10 time units without loss; a missing timestamp in that list would be the first sign of a collection fault or deliberate suppression.
Maya's report states all three: one excursion to 128.0 °C at t = 35, 13 samples examined, and SIS-H's assumed 135 °C trip point not reached. The dashboard showing hourly means had answered "no over-temperature" to the same question.
Worked example (synthetic LDHP)
Maya is asked: "Was there any over-temperature last shift?" A dashboard showing hourly means says no. Her query select ts, v from h where tag='TT-101' and v > 120 returns one excursion at t = 35, 128 °C. She reports it with the count of samples examined (13) and notes that the SIS-H trip point (assumed 135 °C in this synthetic plant) was not reached.
Synthetic case — Lakeside District Heating Plant. Values, tags and the 135 °C trip point are invented for teaching.
Errors and limits
Historian compression (deadband or swinging-door) can drop points that did not change "enough", so short spikes may be absent. Timestamps may come from the historian, not the controller. Never grant write access to historian tables for analysis work.
Exercises
- EX025 — Counting examined samples. Why does the lab report n = 13 for TT-101 rather than 12? Rubric: 3 = 12 regular samples (0–55 step 5) plus the inserted excursion; 2 = notes extra row; 1 = guesses; 0 = none.
- EX026 — Query for a sampling gap. Write SQL that returns PT-201 timestamps where the next sample is more than 10 units later. Rubric: 3 = window function LEAD (or self-join) with ts difference > 10; 2 = correct idea, syntax slip; 1 = counts rows only; 0 = none.
- EX027 — Deadband compression and spikes. Explain how deadband compression could hide a brief excursion. Rubric: 3 = point stored only on change beyond deadband or exception/heartbeat, so a short spike between stored points can vanish; 2 = partial; 1 = "compression loses data" only; 0 = none.
Synthesis
Ask the historian for extremes, counts and gaps, not just averages — and treat its placement and permissions as security design.
Continue studying — chapter 09 of 40
← Chapter 08 (draft) · Course chapter index · Chapter 10 (draft) →