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

Offical Documentation

Official Docs: