A script to automatically clean up sys_audit_delete and sys_audit_relation records


Description

The Audit Deleted Records [sys_audit_delete] and Audit Relationship Changes [sys_audit_relation] tables are used to store information about deleted records and their relationships to other records.  These two tables facilitate an "Undelete" capability.  That is, if you delete a record, you can restore it by finding the records in the Audit Deleted Records [sys_audit_delete] list, opening the form, and clicking on the Undelete Record UI action.  Unchecked, these tables can grow very large and can cause wider performance issues within a ServiceNow instance. At some point, we might need to consider whether a record deleted many years ago will ever be restored, and is there any value in maintaining these audit records.

You can disable these records from being generated in the first place by setting the corresponding table's dictionary attribute no_audit_delete.  However, oftentimes you may want to audit these records, but only retain them for a certain period of time.  This article proposes a script that will purge records for a specific table from the sys_audit_delete (and it's corresponding sys_audit_relation records) that are older than a set number of days.

Caveat: As a ServiceNow customer, you are always the data owner and controller of the information you store in a ServiceNow instance. You decide what information is to be stored, how it is to be used, and how long it is retained. We do not delete or modify your data and only process data in accordance with our contractual obligations and the configuration of your instance(s). We keep 28 days of backup. When you delete data from your ServiceNow instance, the deletion will take 28 days to be cycled out of a backup of that instance. Any script provided is for use at your own risk and should be fully tested in a sub production instance before being used in production.

Resolution

The steps are straightforward:

Note iconNote: We recommend creating an index to support this job first (see the Additional Information section below).
  1. Navigate to the System Definition > Scheduled Jobs module.
  2. Click on the New button.
  3. Click on the Automatically run a script of your choosing link.
  4. Enter the following information:
    • Name: Purge sys_audit_delete for sc_recurring_rollup after 7 days
    • Run: Periodically
    • Repeat Interval: 1 minute
    • Run this script:

      var vTableName = 'sc_recurring_rollup';
      var vDaysAgo = 7;
      PurgeAuditDeletedRecords(vTableName, vDaysAgo);

      function PurgeAuditDeletedRecords(pTableName, pDaysAgo) {
          
          // Set total number of records to delete and maximum size of each batch (recommend leaving this as 100)
          var pRecordsToDelete = parseInt(2000);
          var pBatchSize = parseInt(100);
          
          // Check table name and delta are set, generate encoded query, set up variables
          if ((pTableName) && (pDaysAgo)) {
              var v_encoded_query = 'sys_created_on<javascript:gs.daysAgoStart(' + pDaysAgo + ')^tablename=' + pTableName;
              var pDeleteCount = parseInt(0);
              
              // Loop deleting records
              while (pDeleteCount < pRecordsToDelete) {

               // Check if we need to reduce pBatchSize
               if ((pDeleteCount + pBatchSize) > pRecordsToDelete) { pBatchSize = pRecordsToDelete - pDeleteCount }
               
               // Find records in sys_audit_delete
               var gr = new GlideRecord('sys_audit_delete');
               gr.addEncodedQuery(v_encoded_query);
               gr.setLimit(pBatchSize);         
               gr.query();
               
               // Exit if there are no records to delete
               if (gr.getRowCount() == 0) { break }
               
               // Increment delete counter
               pDeleteCount += parseInt(gr.getRowCount());
               
               // Build array of sys_audit_delete sys_ids to be removed
               var pTargetSysids = [];
               while (gr.next()) { pTargetSysids.push(gr.sys_id.toString()) }
                        
               // Delete referencing records from sys_audit_relation
               gr = new GlideRecord('sys_audit_relation');
               gr.addQuery('audit_delete', 'IN', pTargetSysids);
               gr.query();
               gr.deleteMultiple();
               
               // Delete records from sys_audit_delete
               gr = new GlideRecord('sys_audit_delete');
               gr.addQuery('sys_id', 'IN', pTargetSysids);
               gr.query();
               gr.deleteMultiple();
              
              }
              
              // Log work done         
              gs.log('Deleted ' + pDeleteCount + ' records for ' + pTableName + ' from sys_audit_delete (and related sys_audit_relation records)', 'PurgeAuditDeletedRecords');

          }
      }

      You can modify the Name and Repeat Interval values as needed, as well as the variables in the script, vTableName and vDaysAgo.
  5. Click on the Submit button.
  6. Repeat steps 2-6 for all other tables for which you want to clean up from the sys_audit_delete/sys_audit_relation tables.  Different jobs per table allow you to control the number of days you want to keep.
  7. You can monitor the logs to see the details from the script by navigating to the /syslog_list.do?sysparm_query=source=PurgeAuditDeletedRecords

Additional Information

Adding an index on the Audit Deleted Records [sys_audit_delete] table on the ( tablename, sys_created_on ) fields will make the script more efficient.