Name
sn_cmdb_int_util.RecordRemoval
Description
An API to run after doing a full load of data that will query sys_object_source for stale records and delete or soft delete them. This should only be used after running a full data load and transformation. The transformation should not map to source_recency_timestamp as it may not update all records in sys_object_source if it does. If an integration can be multi-instance the sourceNativeKeyFilter needs to be specified so that the record remover/updater doesn t remove records from the same source but a different instance.
Script
var RecordRemoval = Class.create();
RecordRemoval.prototype = {
initialize: function() {
this.BATCH_SIZE = gs.getProperty("sn_cmdb_int_util.record_removal_batch_size", 500);
},
/*
* Queries the sys_object_source table
* filtering by name=discoverySource, target_table=targetTable, last_scan<runStartDateTime, and source_feed=sourceFeed
* iterates through the results of the query batching deletes of the records by using the sn_cmdb_int_util.record_removal_batch_size property
*
* @param discoverySource {String} - the discovery source to filter by
* @param runStartDateTime {GlideDateTime} - the start of the transform run; used to filter last_scan
* @param targetTable {String} - the target table to delete records from
* @param sourceNativeKeyFilter {String} - Optional, a filter string the source native key must start with. Used for multi-instance.
* @param sourceFeed {String} - Optional, the name of the source feed for further filtering by source_feed
*
* @returns {Array} - an array of strings containing the sys_ids of the records deleted
*/
removeRecords: function(discoverySource, runStartDateTime, targetTable, sourceNativeKeyFilter, sourceFeed, queryType) {
if (gs.nil(discoverySource) || gs.nil(runStartDateTime) || gs.nil(targetTable)) {
Throw("RecordRemoval.removeRecords: not all mandatory parameters have been specified");
}
var sourceGr = this._querySysObjectSource(discoverySource, runStartDateTime, targetTable, sourceNativeKeyFilter, sourceFeed, queryType);
var allSysIds = [];
var batchSysIds = [];
while (sourceGr.next()) {
if (batchSysIds.length < this.BATCH_SIZE) {
batchSysIds.push(sourceGr.target_sys_id.toString());
}
if (batchSysIds.length == this.BATCH_SIZE) {
allSysIds.push(batchSysIds);
this._deleteRecords(targetTable, batchSysIds);
batchSysIds = [];
}
}
if (batchSysIds.length > 0) {
allSysIds.push(batchSysIds);
this._deleteRecords(targetTable, batchSysIds);
}
return allSysIds;
},
/*
* Queries the sys_object_source table
* filtering by name=discoverySource, target_table=targetTable, last_scan<runStartDateTime, and source_feed=sourceFeed
* iterates through the results of the query batching updates of the records by using the sn_cmdb_int_util.record_removal_batch_size property
*
* @param discoverySource {String} - the discovery source to filter by
* @param runStartDateTime {GlideDateTime} - the start of the transform run; used to filter last_scan
* @param targetTable {String} - the target table to delete records from
* @param targetField {String} - The field name in the target table to update
* @param targetFieldValue {String} - The value of the targetField to update
* @param sourceNativeKeyFilter {String} - Optional, a filter string the source native key must start with. Used for multi-instance.
* @param sourceFeed {String} - Optional, the name of the source feed for further filtering by source_feed
*
* @returns {Array} - an array of strings containing the sys_ids of the records deleted
*/
updateRecords: function(discoverySource, runStartDateTime, targetTable, targetField, targetFieldValue, sourceNativeKeyFilter, sourceFeed) {
if (gs.nil(discoverySource) || gs.nil(runStartDateTime) || gs.nil(targetTable) || gs.nil(targetField) || gs.nil(targetFieldValue)) {
Throw("RecordRemoval.updateRecords: not all mandatory parameters have been specified");
}
var sourceGr = this._querySysObjectSource(discoverySource, runStartDateTime, targetTable, sourceNativeKeyFilter, sourceFeed);
var allSysIds = [];
var batchSysIds = [];
while (sourceGr.next()) {
if (batchSysIds.length < this.BATCH_SIZE) {
batchSysIds.push(sourceGr.target_sys_id.toString());
}
if (batchSysIds.length == this.BATCH_SIZE) {
allSysIds.push(batchSysIds);
this._updateRecords(targetTable, batchSysIds, targetField, targetFieldValue);
batchSysIds = [];
}
}
if (batchSysIds.length > 0) {
allSysIds.push(batchSysIds);
this._updateRecords(targetTable, batchSysIds, targetField, targetFieldValue);
}
return allSysIds;
},
_querySysObjectSource: function(discoverySource, runStartDateTime, targetTable, sourceNativeKeyFilter, sourceFeed, queryType) {
var dateEncodedQuery = "last_scan<" + runStartDateTime.toString();
var sourceGr = new GlideRecord("sys_object_source");
sourceGr.addQuery("name", discoverySource);
sourceGr.addQuery("target_table", targetTable);
if (!gs.nil(sourceFeed)) {
sourceGr.addQuery("source_feed", sourceFeed);
}
if (!gs.nil(sourceNativeKeyFilter)) {
if(gs.nil(queryType)) {
sourceGr.addQuery("id", "CONTAINS", sourceNativeKeyFilter);
}
else {
sourceGr.addQuery("id", queryType, sourceNativeKeyFilter);
}
}
sourceGr.addEncodedQuery(dateEncodedQuery); // last_scan<=javascript:gs.dateGenerate('2022-11-29','08:44:45')
sourceGr.query();
return sourceGr;
},
_deleteRecords: function(targetTable, targetSysIds) {
gs.debug("RecordRemoval._deleteRecords: deleting records in:" + targetTable + " with sys_ids in:" + targetSysIds);
var gr = new GlideRecord(targetTable);
gr.addQuery("sys_id", targetSysIds);
gr.query();
gr.deleteMultiple();
},
_updateRecords: function(targetTable, targetSysIds, targetField, targetValue) {
gs.debug("RecordRemoval._updateRecords: updating:" + targetTable + "." + targetField + " to:" + targetValue + " for sys_ids:" + targetSysIds);
var gr = new GlideRecord(targetTable);
gr.addQuery("sys_id", targetSysIds);
gr.query();
gr.setValue(targetField, targetValue);
gr.updateMultiple();
},
type: 'RecordRemoval'
};Sys ID
8c0f9c9f77a35110a52b1bfaae5a99d0