Glide Script To Identify Excessive Record Updates by Users
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:
