Audit database schema

Auditing queries

Find the columns and table relationships behind NIM audit reports.

This reference covers the 26 core audit tables in the supplied SQLite schema export. It excludes f_ virtual tables, which are generated from filters and vary by installation. Start with the auditing query guide for example SQL and help choosing a starting table.

The Audit App examples use four useful starting points: ObjectMutations for attribute changes, Memberships for group changes, AppSessions for app use, and ResourceUpdates for resource changes. Join from there to lookup tables for names and to Users through the path shown below. Activities links directly to Users; app and resource records use Sessions to reach Users.

Relationship mapDirect link to Relationship map

Follow the path that matches your question. Arrows show a useful reading path, while the tables below identify the exact foreign-key columns. The smaller lookup tables and the standalone Version table are listed in the reference below.

App activity

Follow an app session through form activity, actions, and calls.

AppSessionsOne app visit

AppActivityForm activity

AppActivityActionsAction run

AppActivityActionCallsIndividual calls

AppSessions.SessionID → Sessions.ID → Users.ID identifies the actor. Use Apps, AppFormUnits, AppActions, and AppActionTypes for names. Result IDs at each level lead through AppResults to AppErrorCodes.

From app activity to target changesAppActivityVsObjectMutations and AppActivityVsMemberships connect an AppActivity row to the changes it caused.

Target changes

Follow an activity to object attributes or group memberships.

ActivitiesActor and event type

ObjectMutationsChanged object

AttributeUpdatesOld and new values

MembershipsGroup and member

Activities.UserID → Users.ID identifies the actor. Use ActivityTypes and Operations for event and operation names, Objects → SystemTables for target systems, and ValueTypes for attribute values. Both membership object IDs point to Objects.

Resource changes

Identify who changed a NIM resource.

UsersActor

SessionsSigned-in session

ResourceUpdatesResource and time

ResourceUpdates.ResourceTypeID → ResourceTypes.ID names the resource type; ResourceUpdates.OperationID → Operations.ID names the operation.

Start with the relevant event table, then use its declared joins below to build the SQL query.

In the tables below, PK marks a primary key and required means the column is declared NOT NULL. A column listed under Joins has a declared foreign key to the named table's ID. A blank join list means the export declares no foreign keys for that table.

Activity and target changesDirect link to Activity and target changes

Table	Columns and SQLite types	Declared joins
`Sessions`	`ID INTEGER PK`; `UserID INTEGER required`; `LoginDateTime TEXT`; `LogoutDateTime TEXT`; `IpAddress TEXT`; `UserAgent TEXT`	`UserID → Users.ID`
`Activities`	`ID INTEGER PK`; `ActivityTypeID INTEGER required`; `UserID INTEGER required`; `DateTime TEXT`	`ActivityTypeID → ActivityTypes.ID`; `UserID → Users.ID`
`ObjectMutations`	`ID INTEGER PK`; `ObjectID INTEGER required`; `OperationID INTEGER required`; `ActivityID INTEGER required`; `DateTime TEXT`	`ObjectID → Objects.ID`; `OperationID → Operations.ID`; `ActivityID → Activities.ID`
`AttributeUpdates`	`ID INTEGER PK`; `ObjectMutationID INTEGER required`; `AttributeName TEXT`; `ValueOld TEXT`; `ValueNew TEXT`; `ValueTypeID INTEGER required`; `OperationID INTEGER required`	`ObjectMutationID → ObjectMutations.ID`; `ValueTypeID → ValueTypes.ID`; `OperationID → Operations.ID`
`Memberships`	`ID INTEGER PK`; `GroupID INTEGER required`; `MemberID INTEGER required`; `OperationID INTEGER required`; `ActivityID INTEGER required`; `DateTime TEXT`	`GroupID → Objects.ID`; `MemberID → Objects.ID`; `OperationID → Operations.ID`; `ActivityID → Activities.ID`
`ResourceUpdates`	`ID INTEGER PK`; `OperationID INTEGER required`; `SessionID INTEGER required`; `ResourceTypeID INTEGER required`; `ResourceName TEXT`; `DateTime TEXT`	`OperationID → Operations.ID`; `SessionID → Sessions.ID`; `ResourceTypeID → ResourceTypes.ID`
`Objects`	`ID INTEGER PK`; `SystemTableID INTEGER required`; `KeyValue TEXT`; `DisplayName TEXT`	`SystemTableID → SystemTables.ID`

Memberships.GroupID and Memberships.MemberID both refer to Objects.ID: join Objects twice with different aliases to show the group and member names. AttributeUpdates belongs to an ObjectMutations row; use ObjectMutations.ObjectID to identify the changed object.

App activityDirect link to App activity

Table	Columns and SQLite types	Declared joins
`AppSessions`	`ID INTEGER PK`; `SessionID INTEGER required`; `AppID INTEGER required`; `StartDateTime TEXT required`; `EndDateTime TEXT`; `AppResultID INTEGER`	`SessionID → Sessions.ID`; `AppID → Apps.ID`; `AppResultID → AppResults.ID`
`AppActivity`	`ID INTEGER PK`; `AppSessionID INTEGER required`; `AppFormUnitID INTEGER`; `StartDateTime TEXT required`; `EndDateTime TEXT`; `AppResultID INTEGER`	`AppSessionID → AppSessions.ID`; `AppFormUnitID → AppFormUnits.ID`; `AppResultID → AppResults.ID`
`AppActivityActions`	`ID INTEGER PK`; `AppActivityID INTEGER required`; `ActionFsRef TEXT`; `ActionSeqNr INTEGER required`; `AppActionID INTEGER`; `StartDateTime TEXT required`; `EndDateTime TEXT`; `AppResultID INTEGER`	`AppActivityID → AppActivity.ID`; `AppActionID → AppActions.ID`; `AppResultID → AppResults.ID`
`AppActivityActionCalls`	`ID INTEGER PK`; `AppActivityActionID INTEGER required`; `CallSeqNr INTEGER required`; `CallDescription TEXT required`; `StartDateTime TEXT required`; `EndDateTime TEXT`; `AppResultID INTEGER`	`AppActivityActionID → AppActivityActions.ID`; `AppResultID → AppResults.ID`
`AppActivityVsObjectMutations`	`AppActivityID INTEGER required`; `ObjectMutationID INTEGER required`	`AppActivityID → AppActivity.ID`; `ObjectMutationID → ObjectMutations.ID`
`AppActivityVsMemberships`	`AppActivityID INTEGER required`; `MembershipID INTEGER required`	`AppActivityID → AppActivity.ID`; `MembershipID → Memberships.ID`
`AppActions`	`ID INTEGER PK`; `AppActionTypeID INTEGER required`; `Name TEXT required`	`AppActionTypeID → AppActionTypes.ID`
`AppResults`	`ID INTEGER PK`; `AppErrorCodeID INTEGER required`; `Message TEXT`	`AppErrorCodeID → AppErrorCodes.ID`
`AppFormUnits`	`ID INTEGER PK`; `FormName TEXT required`; `UnitID TEXT required`; `UnitDescription TEXT required`	—
`Apps`	`ID INTEGER PK`; `Name TEXT required`	—

The two AppActivityVs... tables are link tables. Neither declares its own ID or primary key in the export. Join through them to connect an app event to the target object mutation or membership change it caused.

Lookup and schema tablesDirect link to Lookup and schema tables

Table	Columns and SQLite types	Declared joins
`ActivityTypes`	`ID INTEGER PK`; `Name TEXT`	—
`AppActionTypes`	`ID INTEGER PK required`; `Name TEXT required`	—
`AppErrorCodes`	`ID INTEGER PK required`; `Error TEXT required`	—
`Operations`	`ID INTEGER PK`; `Name TEXT`	—
`ResourceTypes`	`ID INTEGER PK`; `Name TEXT`	—
`SystemTables`	`ID INTEGER PK`; `SystemName TEXT`; `TableName TEXT`	—
`Users`	`ID INTEGER PK`; `Name TEXT`	—
`ValueTypes`	`ID INTEGER PK`; `Name TEXT`	—
`Version`	`ID INTEGER PK`	—

The export also contains indexes and installation-specific f_ virtual tables. Those filter tables are outside this core audit schema reference.