Store Machine States in SQL: Build a Data Historian with Peakboard

Peakboard detects every state change at the machine – from RUN to STOP, from SETUP to RUN – and writes it automatically, with a timestamp, into a SQL database. From this state history, run times, downtime durations and OEE values can be calculated directly, without a single figure being entered by hand. That is the Peakboard approach to a real data historian: a lean database schema, a robust stored procedure, clever SQL queries.

What is a data historian and why do I need one?

A data historian is a database that continuously stores machine states and process data with a timestamp. Unlike a simple dashboard that shows only the current state, the historian builds a complete state history: machine X was in state RUN from 06:03 to 07:41, then in state STOP until 08:15, then RUN again.

From this basis, all the important metrics can be derived: total run time per shift, downtime by cause, OEE over weeks, MTBF (Mean Time Between Failures) and MTTR (Mean Time To Repair). Peakboard builds this historian directly on a SQL database, without any additional software.

The database schema: two tables

Two tables are needed for the basic setup:

The first table Machines holds one row per machine with the current state and machine name. It is the fast lookup for the visualization: which machine is running right now, which is stopped?

The second table MachineStateHistory is the real heart of the historian. It contains one row per state change:

• ID — primary key
• TS — timestamp of the state change
• MachineName — machine name
• State — new state (e.g. RUN, STOP, SETUP)

Every time a machine changes its state, a new row is added. Nothing is ever overwritten. The complete history is preserved.

The stored procedure: SetNewState

Instead of sending UPDATE and INSERT directly from Peakboard, a stored procedure encapsulates the whole logic. On the call, the machine name and new state are passed. The SP then updates the current state in the Machines table and at the same time writes a new entry into MachineStateHistory:

CREATE PROCEDURE [dbo].[SetNewState]
  @MachineName nvarchar(20),
  @State nvarchar(20)
AS
BEGIN
  UPDATE Machines SET state=@State WHERE machinename=@MachineName
  INSERT INTO MachineStateHistory (TS, MachineName, State)
  VALUES(getdate(), @MachineName, @State)
END

Why a stored procedure instead of direct commands? Three reasons: the logic lives in a single place. The operation is atomic – either both writes succeed or neither does. And a single database call from Peakboard is enough instead of two separate commands.

In Peakboard the SP is triggered via a building-block call – for example when an OPC UA signal reports a state change or the operator enters a reason on the touchscreen.

Calculating run times: the SQL LEAD command

The historian table stores timestamps – but not yet durations. To calculate downtime and run times, each record needs the timestamp of the next state change of the same machine. That is exactly what the SQL LEAD command does:

SELECT MachineName, State, TS as TimeStart,
  LEAD(TS) OVER (PARTITION BY MachineName ORDER BY TS) AS TimeEnd
FROM MachineStateHistory

PARTITION BY MachineName ensures that LEAD only jumps within the same machine. The result: every row now has a start time and an end time.

In the next step, DATEDIFF converts the time span into minutes:

WITH results AS (
  SELECT MachineName, State, TS as TimeStart,
    LEAD(TS) OVER (PARTITION BY MachineName ORDER BY TS) AS TimeEnd
  FROM MachineStateHistory
)
SELECT *, DATEDIFF(mi, TimeStart, TimeEnd) as TimeSpanInMinutes
FROM results;

With this result set, Peakboard can aggregate via a dataflow: the sum of all RUN minutes for machine A, the sum of all STOP minutes, frequency by state. From this you get the availability component of the OEE value directly.

From the database to the dashboard

Peakboard reads the SQL result as a data source and processes it further via a dataflow. A dataflow can group the raw data by machine and state, calculate the minute sums and present them as a simple metric on the dashboard. No separate BI tool, no manual aggregation, no delay.

The advantage of this approach over off-the-shelf MES systems: the database schema is self-defined and fully controllable. The stored procedure can be extended at any time – with additional states, with shift IDs, with order references. Peakboard grows with you.

Without your own SQL maintenance: historization via the Peakboard Hub

The SQL route gives you full control over schema and queries – but it assumes you operate and maintain a database yourself. If you do not want that, or if you need to bring machine data from several boxes and sites together centrally, the Peakboard Hub takes over the historization.

The Peakboard Hub stores machine, sensor and manually captured data centrally, instead of only displaying it momentarily. Two building blocks take on the tasks that, in the SQL approach, are handled by your own tables and the stored procedure:

• Peakboard Hub Lists are central data tables that all connected boxes read and write at the same time. This is where the state history lands, without you creating a database schema yourself.
• Peakboard Hub Flows are central processes that you define once and run on the Peakboard Hub server – for example capturing every state change, bundling many machine messages or writing metrics back to ERP and MES.

Better or worse is the wrong question here – it is about effort and reach. A single line with its own database is quickly mapped in SQL. But as soon as several boxes, several plants or continuously captured data come together, the Peakboard Hub takes the administration off your hands: from central data storage to the automatic rollout across the entire device fleet.

Connection to OEE and machine monitoring

The data historian is the data foundation for all further analyses. The OEE metric needs run times and downtime data – the historian supplies both. Downtime tracking uses the state history for pattern analysis over weeks. And machine monitoring shows the real-time status – the historian stores everything that happens in real time for later analysis.

If you want to evaluate the historized data not only in Peakboard but also in deeper reports, you can use the SQL database directly as a data source for Power BI – Peakboard shows the live view on the shop floor, Power BI delivers the weekly evaluation for management.