Skip to main content

Audit identity changes with queries

Identity audit trail

Query recorded target-system changes for audit reports, notifications, and operational review.

NIM records target-system operations in its SQLite audit database at C:\ProgramData\Tools4ever\NIM\sysdata\nimDb.db. You can use audit-query results in notification templates or explore the database with DB Browser for SQLite.

What NIM records​

NIM records target changes made by jobs, scheduled tasks, and applications: timestamps, initiating activity, affected systems and objects, attribute old/new values, and group-membership changes.

info

Mapping actions launched manually from a mapping’s Run tab are not written to the audit database or back to Vault data.

To include source-system data in a query, create a filter that joins source and target data. NIM exposes filters as SQL tables prefixed with f_.

Create an auditing query​

Select the audit data​

  1. Go to Output > Auditing Queries, select Add, enter a query name, and select Create.
  2. Write SQLite in SQL Query. Use the Tables and Columns selectors to discover available audit fields.
  3. Hold Ctrl while selecting Insert beside a table to preview its values in the result pane.
Step 1 of 3

Example queries​

Recent attribute changes​

SELECT ST.SystemName AS System, OP.Name AS Operation, O.DisplayName AS Name,
  AU.AttributeName AS Attribute, AU.ValueOld AS Old, AU.ValueNew AS New,
  datetime(OM.DateTime, 'localtime') AS OperationDateTime, O.KeyValue AS ID
FROM AttributeUpdates AU
INNER JOIN ObjectMutations OM ON OM.ID = AU.ObjectMutationID
INNER JOIN Operations OP ON OP.ID = OM.OperationID
INNER JOIN Objects O ON O.ID = OM.ObjectID
INNER JOIN SystemTables ST ON ST.ID = O.SystemTableID
WHERE datetime(OM.DateTime, 'localtime') > datetime('now', '-1 day')
ORDER BY OM.DateTime DESC;

Attribute changes for one record​

Replace <Key Column Value> with a target record key, such as an Active Directory objectGUID.

SELECT ST.SystemName, OP.Name AS Operation, O.DisplayName, AU.AttributeName,
  AU.ValueOld, AU.ValueNew, datetime(OM.DateTime, 'localtime') AS OperationDateTime
FROM AttributeUpdates AU
INNER JOIN ObjectMutations OM ON OM.ID = AU.ObjectMutationID
INNER JOIN Operations OP ON OP.ID = OM.OperationID
INNER JOIN Objects O ON O.ID = OM.ObjectID
INNER JOIN SystemTables ST ON ST.ID = O.SystemTableID
WHERE O.KeyValue = '<Key Column Value>'
ORDER BY OM.DateTime DESC;

Recent membership changes​

SELECT st.SystemName, Op.Name AS Operation, datetime(M.DateTime, 'localtime') AS OperationDateTime,
  GrpObj.DisplayName AS GroupName, MemObj.DisplayName AS MemberName
FROM Memberships M
INNER JOIN Operations Op ON Op.ID = M.OperationID
INNER JOIN Objects GrpObj ON GrpObj.ID = M.GroupID
INNER JOIN SystemTables st ON st.ID = GrpObj.SystemTableID
INNER JOIN Objects MemObj ON MemObj.ID = M.MemberID
WHERE datetime(M.DateTime, 'localtime') > datetime('now', '-1 day');

Audit database reference​

Application actions include the initiating user, application, timestamp, description, target objects, and any relevant attribute or membership changes. Set meaningful action descriptions in apps so an auditor can understand what occurred without reconstructing the workflow.

The following diagram shows the audit database tables and relationships available to auditing queries.

Edit, copy, rename, or remove a query​

Select Edit Auditing Query to update SQL or parameters, Copy Object to create a numbered copy, Rename Object to change its name, or Remove Auditing Query and confirm to delete an unused query.

Last updated on