Skip to main content

Audit Trail

The Audit Trail screen is the header view of the database-level audit. One line per (Application, Transaction date, Operation, Schema, Table, User). Each row counts how many records were touched in a given audited operation on a connected application — INSERT, UPDATE, DELETE or TRUNCATE.

It complements Activity log (which captures usage tracking) with low-level database mutations — the kind of trace SOX-style controls expect.

At a glance​

Nomasx-1 · Database · Audit TrailAPPTRANSACTION DATEOPERATIONSCHEMATABLEUSERCOUNT122026-05-14 09:42UPDATEPRODDTAF0411JDOE42122026-05-14 02:14INSERTPRODDTAF4101SVC_BATCH12 481122026-05-13 18:51DELETEPRODDTAF0411Z1DBA_OPS3 102122026-05-13 11:08UPDATEPRODDTAF0101MSMITH7

Goal of the view​

For every audited database mutation on a connected application:

  • The header. Date, operation type, schema, table, user, row count. Enough to identify the operation without yet reading every changed column.
  • Volume signal. Count surfaces mass updates and deletes — typical inputs of a SOX walkthrough.
  • Drill-down entry point. A click into a row opens the Audit Lookup screen with the before / after column values.

Columns​

ColumnSourceWhat it tells you
Application IDAUD_APPS_ID — application identifier.The connected application.
Transaction dateAUD_DT_TRANSACTION — timestamp.When the operation hit the database.
OperationAUD_OPERATION — INSERT / UPDATE / DELETE / TRUNCATE.What was done.
SchemaAUD_SEG_OWNER — database schema.Where the table lives.
TableAUD_SEG_NAME — table name.Object touched.
UserAUD_USER — database account.Who executed the operation at the DB level.
CountAUD_COUNT — number of rows affected.Volume of the operation.

Field-level diff — expandable rows​

Every row expands via the native chevron on the left to a BEFORE / AFTER diff at the column level. Column values are parsed on demand from the stored DML statement (AUDIT_TRAIL_QUERY) — the diff is built when you expand the row, not stored twice. Consequences:

  • The values table can be purged to reclaim space, and the diff still reconstructs from the DML alone.
  • Only the columns that changed are highlighted; unchanged columns are shown greyed out for context (and can be hidden through the row toolbar).
  • INSERT shows all persisted values as AFTER; DELETE shows them as BEFORE; UPDATE shows both, with changed cells outlined.

The summary view replaces the previous nested grid — expandable sub-rows are native, so keyboard navigation and column resizing behave like the rest of the grid.

Auditing scope — Audit Columns tab​

Each connected application decides how much of each audited table is indexed into the values table via the Audit Columns tab on the application editor. Three modes per table:

Rule set on the tableEffect
No row for the tableDefault — every column of the row is indexed into AUDIT_TRAIL_VALUES.
One or more column rowsOnly the listed columns are indexed. Other columns still appear in the on-demand diff (parsed from AUDIT_TRAIL_QUERY), just not searchable in the values table.
A single *SQL* rowJournal-only — the full DML statement is retained in AUDIT_TRAIL_QUERY and nothing is indexed into AUDIT_TRAIL_VALUES. Use it on the widest tables where per-column search is not needed.

The full DML statement is always retained in AUDIT_TRAIL_QUERY, so nothing is lost regardless of the rule set. The tab is the lever to keep the values table's volume in check on wide tables (a F0911 row would otherwise index tens of columns per operation).

Purge and rebuild jobs​

Two Nomaflow jobs sit alongside this screen to keep the history in shape:

JobWhat it does
Audit trail purgeDeletes rows from AUDIT_TRAIL_HEADER (and cascading values) older than a cutoff date. The DML statement, the header and its values are all removed — use this to enforce a retention policy.
Rebuild valuesRe-parses AUDIT_TRAIL_QUERY for a chosen date range and re-populates AUDIT_TRAIL_VALUES from the raw DML. Useful after enlarging the Audit Columns rule on a table (previously-indexed rows get the freshly-listed columns without re-collecting from source), or after a values purge.

Both jobs run under Nomaflow with progress in the run log and reach into the standard audit-trail scope (connectors and applications).

Tips & best practices​

  • Filter on Operation = DELETE in the audit period — every deletion has to be justifiable.
  • Expand the row before opening Audit Lookup. For a single-record diff, the expanded row already shows every changed column; Audit Lookup is the drill-down for a multi-record UPDATE where you want to walk each affected row.
  • Filter on a User to retrieve the database-level activity of a specific account. Match against the corresponding Activity log row to see whether the change was driven through the application or directly on the database.
  • A Count above the day's typical range for the same table is the kind of anomaly worth a question to the DBA team.
  • Trim wide tables via Audit Columns. On a F0911-style table, list only the columns you would ever search on — the values table stays small, and the field-level diff still shows everything from the parsed DML.
  • Run the purge on a schedule aligned with your retention policy; use Rebuild values after enlarging the audit scope of a table to backfill the values that would otherwise be missing.
  • The screen is read-only for everyone except an administrator. Editable entry points exist (audit_trail_post / audit_trail_put) but are intended for the Nomasx-1 ingestion pipeline, not manual data entry.