A measurement result in MES, a maintenance record in CMMS, a quality approval entered for a batch: in a customer complaint, a recall investigation and an IATF audit, they’re all put on the table as evidence. If a record can be changed afterwards, if it isn’t clear who entered it or if timestamps don’t match each other, its value as evidence is gone. Below we cover audit trails, user accounts, time synchronization and data correction controls for assessing record integrity in production and maintenance systems, in the order IT, quality and OT owners will check them on the ground. If production data first needs to be measured and recorded correctly at the source of the audit trail, you can start with industrial data collection and IIoT integration.
Why is record integrity a quality issue?
ISO 9001 clause 7.5.3 requires documented information to be adequately protected and not to lose its integrity. IATF 16949 adds requirements on top of this such as traceability (8.5.2.1), total productive maintenance records (8.5.1.5) and record retention (7.5.3.2.1). None of these clauses uses the term “audit trail”. They all assume the record is reliable.
On the ground, record integrity problems usually surface through these scenarios:
- An out-of-tolerance measurement result was corrected after the batch shipped, on the grounds of an “entry error”; who made the correction and the original value aren’t visible.
- A machine’s periodic maintenance was closed as “completed” in CMMS, but the closing time doesn’t match the maintenance technician’s shift.
- In a recall investigation, there are unexplained minutes between the production time in MES and the test time on the test stand; which test record the part belongs to can’t be confirmed.
The root of the third scenario is often one thing: the test stand PC’s clock has been drifting on its own for months. Nobody looked.
The ALCOA+ principles widely used for data integrity in the pharmaceutical sector (records should be attributable, legible, contemporaneous, original and accurate; and also complete, consistent, enduring and available) aren’t a direct IATF requirement. But they’re a good control framework for assessing production records; each of the headings below corresponds to one of these principles.
Audit trail requirements
An audit trail is a record that users can’t change, showing who created, changed or deleted a record, when, what, from which value to which value and why. When assessing an existing MES or CMMS, or choosing a new one, ask these questions:
- In which tables or objects is the audit trail active? Are measurement results, batch status, quality decisions, recipe and master data changes covered?
- Is the original value preserved, or is only the fact that it “was changed” kept?
- Is the reason for change a required field?
- Can the application administrator turn off or delete the audit trail? Is this action logged separately?
- Is the audit trail kept as long as the record itself?
A situation you may come across: the audit trail module is in the license, but it was turned off during installation for performance reasons or left on in only a few tables. The “change history” tab on screen comes back empty. The first check is in which objects the audit trail is really turned on in the application’s admin console.
If the application’s built-in audit trail isn’t enough, a complementary control can be set up at the database layer. In Microsoft SQL Server, system-versioned temporal tables (temporal tables) automatically keep the previous version of a row in a history table on every update:
CREATE TABLE dbo.MeasurementResult (
MeasurementId INT IDENTITY PRIMARY KEY,
SerialNo NVARCHAR(40) NOT NULL,
Characteristic NVARCHAR(40) NOT NULL,
Value DECIMAL(12,4) NOT NULL,
UserName NVARCHAR(128) NOT NULL,
ChangeReason NVARCHAR(200) NULL,
ValidFrom DATETIME2 GENERATED ALWAYS AS ROW START NOT NULL,
ValidTo DATETIME2 GENERATED ALWAYS AS ROW END NOT NULL,
PERIOD FOR SYSTEM_TIME (ValidFrom, ValidTo)
)
WITH (SYSTEM_VERSIONING = ON (HISTORY_TABLE = dbo.MeasurementResultHistory));
-- Full change history for a serial number
SELECT SerialNo, Characteristic, Value, UserName, ChangeReason, ValidFrom, ValidTo
FROM dbo.MeasurementResult FOR SYSTEM_TIME ALL
WHERE SerialNo = N'A2411-000123'
ORDER BY ValidFrom;
Know this structure’s two limits. First, a temporal table doesn’t record who made the change on its own; the application must fill in the UserName and ChangeReason fields on every update. If the application connects with a single service account, the database sees only that account. Second, timestamps in the period columns are kept in UTC; they must be converted to local time in reporting. As highly privileged database users can turn versioning off, such actions should be monitored with a separate mechanism such as SQL Server Audit.
The problem of shared user accounts
Shared accounts such as “LINE1”, “OPERATOR” or “MAINTENANCE” are common on the shop floor. The reason is practical: signing out at shift change takes time, and typing a password with gloves on is hard. But a shared account empties the audit trail’s “who” question. If it isn’t known which operator made a quality decision, you can’t say the record is attributable or that information security compliance is achieved.
| Approach | Advantage | Limitation |
|---|---|---|
| Personal user + password | Full attributability | Slow on the shop floor; tendency to share passwords |
| Sign-in with card/RFID | Fast, personal | Card reader integration, lost card process |
| Shared station session + per-action card approval | Personal trail for critical actions | Which actions are critical must be defined |
| Shared account + shift roster | Easy | Weak attribution to individuals; only for low-risk screens |
The realistic goal isn’t to move the whole shop floor to personal sign-in overnight. If forced, passwords often end up on a piece of paper stuck to the side of the monitor. The goal is to make personal authentication mandatory for critical actions such as quality decisions, batch release, measurement corrections, recipe changes and maintenance closures. If shared accounts remain, their permissions should be narrowed so they don’t include these actions. For periodic review of accounts and permissions, the method in the article access review evidence also applies to MES and CMMS users.
Time synchronization and timestamps
A part’s production, test, labeling and shipping records are kept in different systems. If these systems’ clocks differ, you can’t put the records in order. If the clocks don’t agree, the trail breaks. Minimum rules for time integrity:
- All MES, CMMS, databases, test stand PCs, HMIs and PLCs (if supported) must synchronize from the same corporate NTP hierarchy. The NTP protocol is defined by RFC 5905.
- If the OT network can’t reach the internet because of segmentation, a local NTP server should be set up in the DMZ or on the OT side.
- The time zone must be explicit in records; preferably UTC is stored and local time is displayed. If data is shared with sites in countries that observe daylight saving time, on the night the clocks change the same hour happens twice or not at all; storing UTC removes this ambiguity.
- Clock drift should be monitored, with alarms raised for devices that exceed the set threshold.
Station PCs that aren’t domain members and have been left in a workgroup are the devices that drift most often; by default they try to reach a time server on the internet, can’t reach it from the OT network and silently drift. Commands you can use on Windows and Linux to check drift:
# Windows: sync source and status
w32tm /query /source
w32tm /query /status
# Windows: measure current drift against the NTP server
w32tm /stripchart /computer:ntp1.example.local /samples:5 /dataonly
# Linux (chrony): sync status and sources
chronyc tracking
chronyc sources -v
These outputs can be kept as evidence of the record integrity assessment. It makes sense to handle them together with the central log infrastructure; for log retention architecture, see the article central log retention architecture.
Controlling data corrections and bulk updates
No system guarantees error-free data entry; the need for corrections is normal. The problem is how the correction is made. The riskiest way is IT or the software vendor running an UPDATE directly in the database: the application’s audit trail doesn’t kick in, there’s no business unit approval and the change doesn’t appear in any record. Two minutes’ work in the console. And no trail at all.
Control table for correction scenarios:
| Scenario | Who does it | Required control |
|---|---|---|
| Single-record entry error | Authorized user, through the application | Reason field, original value preserved |
| Quality record for a shipped batch | Quality authority | Nonconformity assessment, second approval |
| Bulk correction (interface error, etc.) | IT, by script | Change record, business unit approval, before/after export |
| Master data change (product, recipe) | Master data owner | Change process, version history |
| Data intervention by the vendor for support | Vendor, supervised | Request record, session record, verification of the result |
For a bulk correction, take an export of the data before and after the change, attach the script to the change record and have the business unit verify the result. Keep accounts with direct database write permission to a minimum and log their use; “sa”-like accounts the vendor left behind for support are the first row on this list. A correction caused by an interface error may also indicate that the measurement data chain hasn’t been validated; this topic is covered in the article validating measurement and test software.
Record integrity assessment checklist
The questions below can be used in an annual assessment or internal audit of an MES or CMMS. Every “no” is an action record:
- Have the critical record types (measurement, quality decision, batch status, maintenance closure, recipe) been identified?
- Is the audit trail active for these record types, and does it preserve the original value?
- Is the reason for change mandatory?
- Who has permission to turn off or delete the audit trail, and are these actions monitored?
- Are critical actions performed with personal authentication? Are shared accounts’ permissions restricted?
- Are all production systems synchronized from the same NTP source, and is drift monitored?
- Is the time zone explicit in records?
- Have accounts with direct database write permission been listed and reviewed?
- Is there a change record and approval for each bulk correction in the last 12 months?
- Is the audit trail kept and archived for the record’s retention period?
Here we covered the accuracy of records in the live system. For the retention and archive side, see archiving PPAP and quality records, and for designing the chain from part to customer, traceability systems in manufacturing. To assess permission and record controls in production systems together, you can use our IT/OT compliance at manufacturing sites and access rights audit services.
Frequently asked questions
Should correcting records in MES be banned completely?
No. Entry errors are normal and must be correctable. What’s required is that the correction is made through the application, by an authorized user, with a reason given and the original value preserved. Records for products already shipped also require quality approval.
Are shared operator accounts a reason for a finding in an IATF audit?
IATF 16949 doesn’t explicitly require personal accounts; but if it can’t be shown who made critical quality decisions, traceability and record reliability are called into question. Apply personal authentication for critical actions and narrow the permissions of shared accounts.
Is synchronizing PLC and HMI clocks necessary?
It’s necessary if these devices produce records or the timestamps they produce are passed to MES. If the device doesn’t support NTP, an alternative is to generate the timestamp in MES or the data collection layer and document this design decision.
Does a database-level audit trail replace an application audit trail?
Not completely. The database layer shows what changed; which application user changed it and for what reason it can’t know unless the application passes that information on. The two layers complement each other.