Script to Delete large set of data

[Patrick DeCarl1]

*** I do not accept responsibility for any bad actions that come from using this script if you mess up..

By clicking helpful below, you accept the risk of running this script in your instance. 

*** 

Ever come across the need to delete large sets of data but the delete scripts will take days or months? Script created below with help of @Brian Q we now can spread out the delete job to more than one app node and workers. Breaking up the data needed to be deleted by the first char of the sys_id we can create a schedule job per app node and pre workers. By doing so we have more than 1 thread deleting records. Ex: I had client who needed to delete all 28 Mil records under cmdb_sam_sw_install table. The single script would take 65 days. Script below deleted data under 2 hours.  Again, use at your own risk. I do not take responsibility for any mistakes you make. I run script in background scripts. 

var jobName = 'SCCM Cleanup - Delete SW Table'; //update to match what you are deleting. 
var hex = ['0', '1', '2', '3', '4', '5', '6', '7', '8', '9', 'a', 'b', 'c', 'd', 'e', 'f']; //Used to break up records by the first char of the sys_id value
var pTable = 'cmdb_sam_sw_install';  //Change to the table you want to query
var pEncodedQuery = 'discovery_source=SCCM'; // Change to the query you might have. If nothing, leave black with ''

/*
Query below is going to query sys_cluster_state for online app nodes with flag any for schedulers
*/
var activeNodes = GlideRecord('sys_cluster_state');
activeNodes.setLimit(16);
activeNodes.addEncodedQuery('status=online^schedulers=any');
activeNodes.query();
var nodeCount = activeNodes.getRowCount();
var activeNodeIndex = 0;
while (activeNodes.next()) {
    // create sys_triggers per hex
    var hexForThisNode = [];
    while (hexForThisNode.length < Math.floor(16 / nodeCount)) {
        hexForThisNode.push(hex.shift());
    }
    if (activeNodeIndex < 16 % nodeCount) {
        hexForThisNode.push(hex.shift());
    }
    createJob(activeNodes.system_id, hexForThisNode, jobName, pTable, pEncodedQuery);
    activeNodeIndex++;
}

function createJob(systemID, hexValue, jobName, pTable, pEncodedQuery) {
    //create sys_triger record
    for (var i = 0; i < hexValue.length; i++) {
        var sysTri = new GlideRecord('sys_trigger');
        sysTri.initialize();
        sysTri.setValue('name', jobName + ' ' + hexValue[i]);
        sysTri.setValue('next_action', gs.now());
        sysTri.setValue('system_id', systemID);
        sysTri.setValue('script', getScript(hexValue[i],pTable,pEncodedQuery));
        sysTri.setValue('trigger_type', 0);
        sysTri.insert();
    }
}

/* 
Blew function is to build the script you want to run on each nodes. 
*/
function getScript(hexValue,pTable,pEncodedQuery) {
    
    var string = "";
    string += "var vTable = '"+pTable+"';" + "\n";
    string += "var vEncodedQuery = '"+pEncodedQuery+"^sys_idSTARTSWITH" + hexValue + "';" + "\n";

    string += "deleteBatch(vTable, vEncodedQuery);" + "\n";
    string += "function deleteBatch(vTable, vEncodedQuery) {" + "\n";
    string += "    gs.log('"+pTable+" Before Delete = "+hexValue+" : ' + getRecordCount(vTable, vEncodedQuery), 'SCCM Cleanup');" + "\n";
    string += "    try {" + "\n";
    string += "var gd = new GlideRecord(vTable);" + "\n";
    string += "gd.addEncodedQuery(vEncodedQuery);" + "\n";
    string += "gd.deleteMultiple();" + "\n";
    string += "  } finally {" + "\n";
    string += "     gs.log('"+pTable+" After Delete: "+hexValue+" ' + getRecordCount(vTable, vEncodedQuery), 'SCCM Cleanup');" + "\n";
    string += "   }" + "\n";
    string += "}" + "\n";

    string += "function getRecordCount(vTable, vEncodedQuery) {" + "\n";
    string += " var ga = new GlideAggregate(vTable);" + "\n";
    string += "   ga.addEncodedQuery(vEncodedQuery);" + "\n";
    string += "   ga.addAggregate('COUNT');" + "\n";
    string += "  ga.query();" + "\n";
    string += "   var rowCount = 0;" + "\n";
    string += "  if (ga.next())" + "\n";
    string += "       rowCount = ga.getAggregate('COUNT');" + "\n";
    string += "   return rowCount;" + "\n";
    string += "}" + "\n";

    return string;
}