Cleanup Old Audit Records

1// Audit Record Cleanup Scheduled Job
2// Runs daily to remove old audit records based on retention policy
3
4try {
5    // Configuration: Define retention periods in days
6    var retentionPolicies = {
7        'sys_audit': 90,           // System audit records - 90 days
8        'sys_journal_field': 180, // Journal/work notes - 180 days  
9        'sys_history_line': 365,  // History records - 1 year
10        'sys_audit_delete': 30,   // Deletion audit - 30 days
11        'u_custom_audit': 60      // Custom audit table - 60 days
12    };
13
14    // Configuration: Batch size for deletion (to avoid performance issues)
15    var batchSize = 1000;
16
17    // Configuration: Enable/disable cleanup per table
18    var enabledTables = {
19        'sys_audit': true,
20        'sys_journal_field': true,
21        'sys_history_line': false,  // Disabled - keep all history
22        'sys_audit_delete': true,
23        'u_custom_audit': true
24    };
25
26    var totalDeleted = 0;
27    var processedTables = [];
28
29    // Process each table in retention policy
30    for (var tableName in retentionPolicies) {
31        if (!enabledTables[tableName]) {
32            gs.info('Audit Cleanup: Skipping disabled table: ' + tableName);
33            continue;
34        }
35
36        var retentionDays = retentionPolicies[tableName];
37        var deletedCount = cleanupTable(tableName, retentionDays, batchSize);
38        
39        if (deletedCount > 0) {
40            totalDeleted += deletedCount;
41            processedTables.push(tableName + ': ' + deletedCount + ' records');
42            gs.info('Audit Cleanup: Deleted ' + deletedCount + ' records from ' + tableName);
43        }
44    }
45
46    // Log summary
47    if (totalDeleted > 0) {
48        gs.info('Audit Cleanup Job completed. Total deleted: ' + totalDeleted + ' records across ' + processedTables.length + ' tables');
49        gs.eventQueue('audit.cleanup.completed', null, totalDeleted, processedTables.join(', '));
50    } else {
51        gs.info('Audit Cleanup Job completed. No records deleted.');
52    }
53
54    /**
55     * Clean up records from specified table
56     * @param {String} tableName - Name of table to clean
57     * @param {Number} retentionDays - Days to retain
58     * @param {Number} batchSize - Records to delete per batch
59     * @returns {Number} Number of records deleted
60     */
61    function cleanupTable(tableName, retentionDays, batchSize) {
62        try {
63            // Calculate cutoff date
64            var cutoffDate = new GlideDateTime();
65            cutoffDate.addDaysLocalTime(-retentionDays);
66
67            gs.info('Cleaning table: ' + tableName + ', cutoff date: ' + cutoffDate.getDisplayValue());
68
69            var totalDeleted = 0;
70            var hasMoreRecords = true;
71
72            // Process in batches to avoid timeout
73            while (hasMoreRecords) {
74                var gr = new GlideRecord(tableName);
75                
76                // Add date filter based on table structure
77                if (tableName === 'sys_audit' || tableName === 'sys_audit_delete') {
78                    gr.addQuery('sys_created_on', '<', cutoffDate);
79                } else if (tableName === 'sys_journal_field') {
80                    gr.addQuery('sys_created_on', '<', cutoffDate);
81                    // Only delete work notes/comments, not all journal entries
82                    gr.addQuery('element', 'IN', 'work_notes,comments,close_notes');
83                } else if (tableName === 'sys_history_line') {
84                    gr.addQuery('sys_created_on', '<', cutoffDate);
85                } else {
86                    // Custom table - assume standard sys_created_on field
87                    gr.addQuery('sys_created_on', '<', cutoffDate);
88                }
89
90                // Add additional filters for specific tables
91                if (tableName === 'sys_audit') {
92                    // Skip certain critical audit records
93                    gr.addQuery('tablename', 'NOT IN', 'sys_user,sys_user_role,sys_security_acl');
94                }
95
96                gr.setLimit(batchSize);
97                gr.query();
98
99                var batchCount = 0;
100                while (gr.next()) {
101                    gr.deleteRecord();
102                    batchCount++;
103                }
104
105                totalDeleted += batchCount;
106
107                // Check if we have more records to process
108                if (batchCount < batchSize) {
109                    hasMoreRecords = false;
110                }
111
112                // Add small delay between batches
113                if (hasMoreRecords) {
114                    gs.sleep(1000); // 1 second delay
115                }
116            }
117
118            return totalDeleted;
119
120        } catch (e) {
121            gs.error('Error cleaning table ' + tableName + ': ' + e.message);
122            return 0;
123        }
124    }
125
126    /**
127     * Archive records instead of deleting (alternative approach)
128     * @param {String} tableName - Source table
129     * @param {String} archiveTable - Archive table name
130     * @param {Number} retentionDays - Days to retain in source
131     */
132    function archiveRecords(tableName, archiveTable, retentionDays) {
133        try {
134            var cutoffDate = new GlideDateTime();
135            cutoffDate.addDaysLocalTime(-retentionDays);
136
137            var grSource = new GlideRecord(tableName);
138            grSource.addQuery('sys_created_on', '<', cutoffDate);
139            grSource.setLimit(batchSize);
140            grSource.query();
141
142            var archivedCount = 0;
143
144            while (grSource.next()) {
145                // Create archive record
146                var grArchive = new GlideRecord(archiveTable);
147                grArchive.initialize();
148
149                // Copy all fields
150                var fields = grSource.getFields();
151                for (var i = 0; i < fields.size(); i++) {
152                    var field = fields.get(i);
153                    var fieldName = field.getName();
154                    if (fieldName !== 'sys_id') {
155                        grArchive.setValue(fieldName, grSource.getValue(fieldName));
156                    }
157                }
158
159                grArchive.u_original_sys_id = grSource.sys_id.toString();
160                grArchive.u_archived_date = new GlideDateTime();
161
162                var archiveId = grArchive.insert();
163                if (archiveId) {
164                    grSource.deleteRecord();
165                    archivedCount++;
166                }
167            }
168
169            return archivedCount;
170
171        } catch (e) {
172            gs.error('Error archiving records from ' + tableName + ': ' + e.message);
173            return 0;
174        }
175    }
176
177} catch (e) {
178    gs.error('Audit Cleanup Job error: ' + e.message);
179    // Send alert for job failure
180    gs.eventQueue('audit.cleanup.failed', null, e.message, '');
181}