Database View For Audit Impersonation Trail ServiceNow has designed the impersonation tracking mechanism in such a way that when any record update occurs during impersonation, the sys_audit_identity table is leveraged to store information about the original user (the impersonator). This information is then referenced in the sys_audit record through a foreign key relationship, allowing a complete and accurate audit trail. Example Scenario: Abel Tuter impersonates Joe Employee and modifies an Incident record. 1. While impersonating, Abel modifies an incident record 2. A sys_audit_identity record is created with the following fields: Column Value source_id abel.tuter source_table sys_user type User user Abel Tuter sys_id 9f3a1d2c4b7e4890b8f27e56d1c9ac34 3. The sys_audit record is created as following: Column Value user 9f3a1d2c4b7e4890b8f27e56d1c9ac34 sys_created_by joe.employee 4. Thus, the audit trail accurately shows that Joe Employee performed the change, but it was done while being impersonated by Abel Tuter. Creating a Unified View Using a Database View To view both sys_audit and sys_audit_identity details on a single page, a Database View can be created that joins these two tables. This allows administrators or auditors to quickly correlate the impersonated and impersonating users in one consolidated report. Steps to Create the Database View : 1. Navigate to: System Definition → Database Views 2. Click “New” and create a record with: Name: audit_impersonation_viewLabel: Audit Impersonation ViewApplication: Global 3. Under Related Links → View Tables, add two entries: Table Order Left Join Variable Prefix Where Clause sys_audit 100 true audit sys_audit_identity 200 true sai sai_sys_id = audit_user This join ensures to return all the sys_audit records with and without impersonation details. If impersonation was involved, then impersonation details (from sys_audit_identity) are fetched for every such audit record 4. Save/Update the Database View record. Testing the View 1. Click “Try It” under Related Links to preview the results. 2. You can now access this view via: https://<instance>.service-now.com/nav_to.do?uri=audit_impersonation_view_list.do 3. View Overview This view displays combined audit data, including: a) Table name, Field name, Document key, Old/New values, Created By (from sys_audit) b) Impersonating user/ AI Agent details (from sys_audit_identity) — if impersonation/ AI Agent was involved 4. By default, the view includes all records from sys_audit, including those created: a) Without impersonation b) With impersonation c) With AI involvement 5. User (sai_user) Field Condition on User Field Meaning of User Field Empty The change was made normally — no impersonation, no AI involvement. Non-empty, Type = User The value represents the impersonating user (the original user performing the impersonation). Non-empty, Type = AI The value represents the AI Agent involved in the change. 6. Created By (audit_sys_created_by) Field Condition on Type Meaning of Created By (audit_sys_created_by) User Represents the impersonated user. This is the account under which the action was recorded, typically indicating that another user performed the activity on behalf of this account through impersonation. AI Represents the user who invoked the AI agent to perform the change. In this case, the action was carried out by an AI agent, but the record identifies the user who initiated the AI-driven operation. Empty Represents the normal user. This indicates that the change was made directly by a user, with no impersonation and no AI involvement. Examples:a) To view audit records that were recorded during impersonation, one can simply set the filters like: Type = User, and User(sai_user) Is Not Empty b) To view audit records that were recorded without any involvement of AI or impersonating user, set the filters like: User (sai_user) is Empty Recommended Practice: Handling Large sys_audit Tables Because sys_audit can be extremely large, creating a Database View that joins the entire table may be resource intensive and can lead to slowness of instance. To ensure optimal performance: 1. After creating the audit_impersonation_view, avoid directly opening it without filters. 2. Instead, type the following in the navigator: audit_impersonation_view.FILTER 3. This opens the view without an initial result set, allowing filters to be applied first. Opening with FILTERS is commonly used for tables with large data like sys_audit as well and not very specific to this view. 4. Use filters such as: a. audit.sys_created_on → after last 3 months, or b. audit.tablename=’incident’ and audit.document_key=’incident_sys_id’ OR Any other relevant condition to reduce the dataset before executing the join. The goal of this point is to ensure that the View scans the least number of rows in the DB so that the results appear quicker. 5. This approach minimizes database load and ensures efficient query performance. 6. If there is slowness observed in opening the view, one could create an index on sys_audit.user column Summary 1. Creating a database view that joins sys_audit and sys_audit_identity provide a single, consolidated view of impersonation activity in ServiceNow. 2. This approach allows administrators to quickly see who was impersonated (sys_created_by) and who performed the action (sai.user). 3. To maintain performance, always apply filters before querying large datasets—such as filtering sys_created_on or a specific documentkey. 4. With this view, organizations gain complete visibility into impersonation trails while keeping system performance optimized.