This article provides a method to pinpoint which users are excessively updating the same record, leading to potential performance issues due to the overflow of the sys_journal_field table with a Glide Script.

Why the Glide Script is Necessary

• Performance Issues: Users experience slow response times when opening cases, problems, change records, etc., primarily due to significant data extraction from the sys_journal_field table.
• Worknotes Storage: The sys_journal_field table houses worknotes for each record, which can become excessive due to numerous updates, each adding a new worknote.
• Identifying Excessive Updates: Although the 'Updates' column in the list view shows the last user to make an update, it does not reveal the full extent of updates made by various users.
• Text Index Events: These events can track updates made by users to records, but are limited to capturing data for only 6 - 7 days due to retention policies.

Caution: Always test scripts in a sub-production environment first.

Script to Retrieve Top 100 Most Redundantly Updated Records

Ensure you're running the script with global scope.

// Function to get count of redundant record updates by table, record, and user
function GetUpdatedRecords() {
    var instanceName = gs.getProperty('instance_name');
    var count = new GlideAggregate('sysevent');
    // Uncomment below for a specific date range, comment the subsequent line
    // var strEncodedQuery = "queue=text_index^state=processed^parm2=update^sys_created_onBETWEENjavascript:gs.dateGenerate('2021-09-29','00:00:00')@javascript:gs.dateGenerate('2021-10-06','23:59:59')";
    // For all records, uncomment below and comment the above line
    var strEncodedQuery = "queue=text_index^state=processed^parm2=update";
    count.addEncodedQuery(strEncodedQuery);
    count.addAggregate('COUNT');
    count.groupBy('table');
    count.groupBy('instance');
    count.groupBy('sys_created_by');
    count.addHaving('COUNT', '>', 1);
    count.orderByAggregate('COUNT');
    // Uncomment below to limit the record list
    // count.setLimit(10);
    count.query();
    while (count.next()) {
        var x_count = count.getAggregate('COUNT');
        var x_table = count.table.getDisplayValue();
        var x_instance = 'https://' + instanceName + '.service-now.com/' + x_table + '.do?sys_id=' + count.instance.getDisplayValue();
        var x_created_by = count.sys_created_by.getDisplayValue();
        gs.info("Count: {0} | {1} | {2} | {3}", [x_count, x_table, x_instance, x_created_by]);
    }
}

GetUpdatedRecords();

Sample Results

The output format is: Count Of Updates | Source Table | Updated Record Full Link | User_Name That Made the Update

For example:

Labels:

• Analytics and Reports
• Best Practices
• Scripting and Coding