Glide Script to determine the count and amount percentage of the first character of every sys_id of a table (This is to perfomance tune mass deletions) - Support and Troubleshooting

Glide Script to determine the count and amount percentage of the first character of every sys_id of a table (This is to perfomance tune mass deletions) Summary 1) This KB will help you determine amount percentage of the first character of every sys_id in one table utilizing glide scripting

2) Using the first letter or first number of the first character of a sys_id as a filter for multiple deletions as jobs for one table is one way to speed up the slow mass deletion process Instructions

1) Go to https:/ [instance_name] .service-now.com/sys.scripts.do 2) Run the below script: NOTES: i. Test Script on a Subprod First

ii. Make sure your on the global scope

GLIDE SCRIPT: function getFirstLetterStat () { //Specify Table: var ltable = 'incident'; var lquery = 'sys_idISNOTEMPTY';

//Initialize Array: var ccList = []; var uniqueArr = []; var arrayUtil = new ArrayUtil(); var ResultArr = [];

//Get Total Count OF Table var TotalCount = getTotalCount(ltable); // Identify DISTINCT FIRST LETTER OF SYS_ID var agg = new GlideAggregate(ltable); agg.addEncodedQuery( lquery ); agg.addAggregate('count','sys_id'); agg.query(); while (agg.next()) { agg.setWorkflow(false); ccList.push(agg.sys_id.substring(0, 1).toString()); } uniqueArr = arrayUtil.unique(ccList);

// Get Count And Count Percentage of each First Letter Of sys_id for (var i = 0; i { var fl_sys_id = uniqueArr[i]; var count = getCount(ltable,fl_sys_id); var percentage = ((parseInt(count) / parseInt(TotalCount)) * 100).toFixed(0); var percentage = percentage.toString() + '%' ResultArr.push("First Letter Of Sys_ID: " + fl_sys_id + " | Count: " + count + " | Percentage: " + percentage); }

//RESULT gs.print("\n" + "\n" + "------SEARCH SETTINGS:------" + "\n" + "Table: " + ltable + "\n" + "ENCODED QUERY: " + lquery + "\n" +"\n" + "-----------------------------RESULT------------------------------------------" + "\n" + ResultArr.join("\n"));

}

// It will get the count of First Letter Of Sys_id function getCount(table,field) { var aggf = new GlideAggregate(table); aggf.addQuery('sys_id','STARTSWITH',field); aggf.addAggregate('COUNT'); aggf.query(); if(aggf.next()) { return aggf.getAggregate('COUNT'); } }

// It will get the total count of the table: function getTotalCount(table) { var aggt = new GlideAggregate(table); aggt.addAggregate('COUNT'); aggt.query(); if(aggt.next()) { return aggt.getAggregate('COUNT'); } }

getFirstLetterStat (); Sample Result: NOTE: IGNORE THE 'compacting large row block' messages, it is expected behavior when extracting a lot of data. The Result is in the bottom where it says 'RESULT'.

[0:00:24.124] Script completed in scope global: scriptScript execution history available here [0:00:00.020] Compacting large row block (file.write: incident 10000 rows 160000 saveSize) [0:00:00.019] Compacting large row block (file.write: incident 10000 rows 160000 saveSize) [0:00:00.018] Compacting large row block (file.write: incident 10000 rows 160000 saveSize) [0:00:00.021] Compacting large row block (file.write: incident 10000 rows 160000 saveSize) [0:00:00.018] Compacting large row block (file.write: incident 10000 rows 160000 saveSize) [0:00:00.017] Compacting large row block (file.write: incident 10000 rows 160000 saveSize) [0:00:00.018] Compacting large row block (file.write: incident 10000 rows 160000 saveSize) [0:00:00.017] Compacting large row block (file.write: incident 10000 rows 160000 saveSize) [0:00:00.017] Compacting large row block (file.write: incident 10000 rows 160000 saveSize) [0:00:00.019] Compacting large row block (file.write: incident 10000 rows 160000 saveSize) [0:00:00.003] Expanding large row block (file.read: incident, 10000 rows, 160000 dataSize) [0:00:00.019] Compacting large row block (file.write: incident 10000 rows 160000 saveSize) [0:00:00.004] Expanding large row block (file.read: incident, 10000 rows, 160000 dataSize) [0:00:00.025] Compacting large row block (file.write: incident 10000 rows 160000 saveSize) [0:00:00.004] Expanding large row block (file.read: incident, 10000 rows, 160000 dataSize) [0:00:00.023] Compacting large row block (file.write: incident 10000 rows 160000 saveSize) [0:00:00.005] Expanding large row block (file.read: incident, 10000 rows, 160000 dataSize) [0:00:00.027] Compacting large row block (file.write: incident 10000 rows 160000 saveSize) [0:00:00.004] Expanding large row block (file.read: incident, 10000 rows, 160000 dataSize) [0:00:00.024] Compacting large row block (file.write: incident 10000 rows 160000 saveSize) [0:00:00.004] Expanding large row block (file.read: incident, 10000 rows, 160000 dataSize) [0:00:00.017] Compacting large row block (file.write: incident 10000 rows 160000 saveSize) [0:00:00.008] Expanding large row block (file.read: incident, 10000 rows, 160000 dataSize) [0:00:00.022] Compacting large row block (file.write: incident 10000 rows 160000 saveSize) [0:00:00.006] Expanding large row block (file.read: incident, 10000 rows, 160000 dataSize) [0:00:00.018] Compacting large row block (file.write: incident 10000 rows 160000 saveSize) [0:00:00.003] Expanding large row block (file.read: incident, 10000 rows, 160000 dataSize) [0:00:00.017] Compacting large row block (file.write: incident 10000 rows 160000 saveSize) [0:00:00.004] Expanding large row block (file.read: incident, 10000 rows, 160000 dataSize) *** Script: ------SEARCH SETTINGS:------ Table: incident ENCODED QUERY: sys_idISNOTEMPTY -----------------------------RESULT------------------------------------------ First Letter Of Sys_ID: a | Count: 6345 | Percentage: 6% First Letter Of Sys_ID: b | Count: 6449 | Percentage: 6% First Letter Of Sys_ID: c | Count: 6508 | Percentage: 6% First Letter Of Sys_ID: d | Count: 6308 | Percentage: 6% First Letter Of Sys_ID: e | Count: 6306 | Percentage: 6% First Letter Of Sys_ID: f | Count: 6333 | Percentage: 6% First Letter Of Sys_ID: 0 | Count: 6291 | Percentage: 6% First Letter Of Sys_ID: 1 | Count: 6276 | Percentage: 6% First Letter Of Sys_ID: 2 | Count: 6396 | Percentage: 6% First Letter Of Sys_ID: 3 | Count: 6429 | Percentage: 6% First Letter Of Sys_ID: 4 | Count: 6409 | Percentage: 6% First Letter Of Sys_ID: 5 | Count: 6375 | Percentage: 6% First Letter Of Sys_ID: 6 | Count: 6413 | Percentage: 6% First Letter Of Sys_ID: 7 | Count: 6362 | Percentage: 6% First Letter Of Sys_ID: 8 | Count: 6179 | Percentage: 6% First Letter Of Sys_ID: 9 | Count: 6339 | Percentage: 6%