Name
global.CMDBTableCleanup
Description
Generalized class to clean up records from tables that do not cascade delete CI s that have been removed
Script
var CMDBTableCleanup = Class.create();
CMDBTableCleanup.prototype = {
initialize: function(limitProperty, tableToClean, targetColumnName,
targetClassColumn, jobContextName) {
this.limit = 10000;
var limit = gs.getProperty(limitProperty);
// if the sys property contains a valid limit value, set it to the limit
if ( !isNaN(limit) && limit > 0) {
this.limit = limit;
}
this.tableToClean = tableToClean;
this.targetColumnName = targetColumnName;
this.targetClassColumn = targetClassColumn;
this.jobContextName = jobContextName;
this.sysId = 'sys_id';
},
//delete records from this.tableToClean if the target record no longer exists
cleanRecords: function () {
var prevIterationSysId = this._getLastIterationId();
var gr = new GlideRecord(this.tableToClean);
gr.addOrderBy(this.sysId);
//Add where clause to fetch the next batch
if (prevIterationSysId) {
gr.addQuery(this.sysId, '>', prevIterationSysId);
}
gr.setLimit(this.limit);
gr.query();
var targetSysId = "";
var lastReadSysId = "";
var targetTableMap = {};
var visitedTargetSysIds = {};
var count = 0;
while (gr.next()) {
targetSysId = gr.getValue(this.targetColumnName);
if(!targetSysId)
targetSysId = 'NULL';
var targetTable = gr.getValue(this.targetClassColumn);
if(!targetTable && this.tableToClean == "cmdb_datasource_last_update")
targetTable = "cmdb_ci";
// get the absolute base in case we do a class switch for cmdb CI's
targetTable = GlideDBObjectManager.getAbsoluteBase(targetTable);
if(!targetTableMap[targetTable]) {
targetTableMap[targetTable] = [];
targetTableMap[targetTable].push(targetSysId);
visitedTargetSysIds[targetSysId] = true;
} else if (!visitedTargetSysIds[targetSysId]){
targetTableMap[targetTable].push(targetSysId);
visitedTargetSysIds[targetSysId] = true;
}
count++;
lastReadSysId = gr.getValue(this.sysId);
//Find and delete once we have seen 100k targetSysIds
//resets the map so that we won't consume more than ~6.5Mb memory.
if(count == 100000) {
this._findAndDeleteInvalidTargetSysIds(targetTableMap, count);
targetTableMap = {};
visitedTargetSysIds = {};
count = 0;
}
}
//process the leftover target sys ids
this._findAndDeleteInvalidTargetSysIds(targetTableMap, count);
if (this._isEndOfTable(lastReadSysId)) {
this._setLastIterationId('');
gs.log("CMDBTableCleanup: " + this.tableToClean + ", reached end of the table. Resetting the loop.");
} else {
this._setLastIterationId(lastReadSysId);
}
},
_findAndDeleteInvalidTargetSysIds: function(targetTableMap, rowsReadCount) {
var batchSize = 100;
var invalidTargetSysIds = this._findInvalidTargetSysIds(batchSize, targetTableMap);
//gs.log("CMDBTableCleanup: " + this.tableToClean + ", Rows read: " + rowsReadCount + ", Invalid sysids in this run: " + invalidTargetSysIds.length);
for (var j = 0; j * batchSize < invalidTargetSysIds.length; j++) {
this._deleteRecords(invalidTargetSysIds.slice(j * batchSize, (j + 1) * batchSize));
}
},
_findInvalidTargetSysIds: function(batchSize, targetTableMap) {
var arrayUtil = new ArrayUtil();
var invalidTargetSysIds = [];
//find invalidTargetSysIds by querying target tables in batches
for (var key in targetTableMap) {
//handle the case where target table was deleted
if(!gs.tableExists(key)) {
invalidTargetSysIds = invalidTargetSysIds.concat(targetTableMap[key]);
continue;
}
var targetSysIds = targetTableMap[key];
var validTargetSysIds = [];
for (var i = 0; i * batchSize < targetSysIds.length; i++) {
var targetGr = new GlideRecord(key);
targetGr.addQuery("sys_id", "IN", targetSysIds.slice(i * batchSize, (i + 1) * batchSize));
targetGr.query();
while(targetGr.next())
validTargetSysIds.push(targetGr.getValue("sys_id"));
}
invalidTargetSysIds = invalidTargetSysIds.concat(arrayUtil.diff(targetSysIds, validTargetSysIds));
}
return invalidTargetSysIds;
},
_deleteRecords: function(deleteIds) {
var gr = new GlideRecord(this.tableToClean);
gr.addQuery(this.targetColumnName, 'IN', deleteIds);
gr.deleteMultiple();
},
_getLastIterationId: function() {
var gr = this._getContextRecord();
gr.next();
return gr.getRowCount() == 1 ? gr.getValue('value') : '';
},
_setLastIterationId: function(sysId) {
var gr = this._getContextRecord();
if (gr.hasNext()) {
while (gr.next()) {
gr.setValue('value', sysId);
gr.update();
}
} else {
gr.setValue('key', this.jobContextName);
gr.setValue('value', sysId);
gr.insert();
}
},
_getContextRecord: function() {
var gr = new GlideRecord('cmdb_job_context');
gr.addQuery('key', this.jobContextName);
gr.query();
return gr;
},
_isEndOfTable: function(sysId) {
var gr = new GlideRecord(this.tableToClean);
gr.addQuery(this.sysId, '>', sysId);
gr.addOrderBy(this.sysId);
gr.setLimit(1);
gr.query();
return !gr.hasNext();
},
type: 'CMDBTableCleanup'
};
Sys ID
ddab599273631110dc50c3ed8ff6a7be