Audit Log Archive Manager

1(function executeJob() {
2
3    gs.info("Audit Log Archive Manager: Starting execution");
4
5    // === CONFIGURATION ===
6    var RETENTION_DAYS = 90;         // Keep 90 days in main table
7    var BATCH_SIZE = 1000;          // Process records in batches
8    var ARCHIVE_TABLE = "u_audit_archive";  // Archive table (create if missing)
9
10    // === STEP 1: Create archive table if it doesn't exist ===
11    var archiveCheck = new GlideRecord('sys_db_object');
12    archiveCheck.addQuery('name', ARCHIVE_TABLE);
13    archiveCheck.query();
14    if (!archiveCheck.hasNext()) {
15        gs.info("Audit Log Archive Manager: Archive table does not exist - skipping archive step");
16    }
17
18    // === STEP 2: Archive old records ===
19    var archiveCount = 0;
20    var cutoffDate = new GlideDateTime();
21    cutoffDate.addDays(-RETENTION_DAYS);
22
23    var auditRecords = new GlideRecord('sys_audit');
24    auditRecords.addQuery('sys_created_on', '<', cutoffDate.getValue());
25    auditRecords.addQuery('tablename', '!=', 'sys_audit');
26    auditRecords.setLimit(BATCH_SIZE);
27    auditRecords.orderBy('sys_created_on');
28    auditRecords.query();
29
30    while (auditRecords.next()) {
31        try {
32            var archiveRecord = new GlideRecord(ARCHIVE_TABLE);
33            archiveRecord.initialize();
34            
35            // Copy all fields from audit record
36            var fieldNames = auditRecords.getFields();
37            while (fieldNames.hasNext()) {
38                var field = fieldNames.next();
39                var fieldName = field.getName();
40                if (fieldName != 'sys_id') {
41                    archiveRecord.setValue(fieldName, auditRecord.getValue(fieldName));
42                }
43            }
44            archiveRecord.setValue('archived_date', new GlideDateTime().getValue());
45            archiveRecord.insert();
46            auditRecords.deleteRecord();
47            archiveCount++;
48        } catch (e) {
49            gs.warn("Audit Log Archive Manager: Failed to archive record - " + e.getMessage());
50        }
51    }
52
53    // === STEP 3: Log results ===
54    gs.info("Audit Log Archive Manager: Archived " + archiveCount + " records to " + ARCHIVE_TABLE);
55
56    // === STEP 4: Purge orphaned attachments from old deleted records ===
57    var purgeCount = 0;
58    var purgeCutoff = new GlideDateTime();
59    purgeCutoff.addDays(-(RETENTION_DAYS * 2)); // Purge attachments from records older than 180 days
60
61    var attachRecords = new GlideRecord('sys_attachment');
62    attachRecords.addQuery('sys_created_on', '<', purgeCutoff.getValue());
63    attachRecords.addQuery('table_name', 'NOT IN', 'incident,problem,change_request,task');
64    attachRecords.setLimit(BATCH_SIZE);
65    attachRecords.query();
66
67    while (attachRecords.next()) {
68        try {
69            attachRecords.deleteRecord();
70            purgeCount++;
71        } catch (e) {
72            gs.warn("Audit Log Archive Manager: Failed to purge attachment - " + e.getMessage());
73        }
74    }
75
76    gs.info("Audit Log Archive Manager: Purged " + purgeCount + " orphaned attachments");
77    gs.info("Audit Log Archive Manager: Execution completed successfully");
78
79})(current, previous);