Matching Record Counts in sys_audit Table with Activity Stream in ServiceNow Tickets

[Bernie_BeAr]

Hello everyone,

I appreciate your time in reading and responding to my query.

I am currently working on retrieving historical activities for each ticket in ServiceNow. I noticed that in the sys_audit table, changes to all fields are stored separately. However, in the activity stream, updates made to multiple fields at once are considered as a single update and hence, result in only one record. This discrepancy results in a difference in the count of changes - for instance, a ticket may show only 10 changes in the activity stream, but there could be 70 corresponding records in the sys_audit table.

My team is interested in retrieving these activities and ensuring that the count of activities they obtain matches the count displayed on the ticket.

Any guidance on how to achieve this would be greatly appreciated. Thank you in advance for your help!

[Amit Gujarathi · Accepted solution]

HI @Bernie_BeAr ,
I trust you are doing great.

Here’s a high-level approach:

1. Query the sys_audit Table: Fetch records from the sys_audit table for the specific ticket. You can filter based on the documentkey field, which links to the ticket ID.

2. Group Changes by Timestamp: The sys_audit table includes a timestamp for each change. Group changes that occur at the same timestamp. This grouping will mimic the activity stream's behavior of consolidating changes made in a single update.

3. Count the Grouped Changes: Count each group of changes as one record, similar to how the activity stream counts a set of changes made together.

4. Compare with Activity Stream: Now that you have processed the sys_audit data, compare the count with the activity stream's count to ensure they match.

var ticketSysId = 'YOUR_TICKET_SYS_ID'; // Replace with actual ticket sys_id
var auditGr = new GlideRecord('sys_audit');
auditGr.addQuery('documentkey', ticketSysId);
auditGr.orderBy('sys_created_on'); // Order by timestamp
auditGr.query();

var changesByTimestamp = {};
while (auditGr.next()) {
    var timestamp = auditGr.sys_created_on.toString();
    if (!changesByTimestamp[timestamp]) {
        changesByTimestamp[timestamp] = [];
    }
    changesByTimestamp[timestamp].push(auditGr.getValue('fieldname'));
}

var consolidatedCount = Object.keys(changesByTimestamp).length;
gs.info('Consolidated Change Count: ' + consolidatedCount);