Name

sn_int_studio.CmdbRecommendations

Description

Utility scripts for the CMDB recommendation table and CMDB recommendation API.

Script

var CmdbRecommendations = Class.create();
CmdbRecommendations.prototype = {
  initialize: function() {
  	this.CMDB_INST_APPLICATION_FEED = "cmdb_inst_application_feed";
  	this.SYS_DATA_SOURCE = "sys_data_source";
  	this.CMDB_INST_TEMPLATE_STATE = "sn_int_studio_template_state";
  	this.CMDB_INST_ENTITY = "cmdb_inst_entity";
  	this.SYS_RTE_EB_DEFINITION = "sys_rte_eb_definition";
  	this.SYS_RTE_EB_ENTITY = "sys_rte_eb_entity";
  	this.SYS_RTE_EB_FIELD = "sys_rte_eb_field";

  	this.SYS_IMPORT_SET = "sys_import_set";
  	this.SYS_ID = "sys_id";
  	this.TABLE_NAME = "table_name";
  	this.COLUMN_LABEL = "column_label";
  	this.DATA_SOURCE = "data_source";
  	this.STATE = "state";
  	this.LOADED = "loaded";
  	this.APPLICATION_FEED_ID = "application_feed_id";
  	this.FIELD = "field";

  	this.CLASS_RECOMMENDATION_TABLE = "sn_cmdb_int_util_class_recommendation";
  	this.ATTRIBUTE_RECOMMENDATION_TABLE = "sn_cmdb_int_util_attribute_recommendation";
  	this.CLASS_RECOMMENDATION_STATE_TABLE = "sn_cmdb_int_util_class_recommendation_state";
  	this.ATTRIBUTE_RECOMMENDATION_STATE_TABLE = "sn_cmdb_int_util_attribute_recommendation_state";
  	this.RECOMMENDATION_SOURCE_COUNT_TABLE = "sn_cmdb_int_util_recommendation_source";

  	this.RECOMMENDATION_TABLE_SOURCE = "source";
  	this.RECOMMENDATION_TABLE_SOURCE_COLUMN_NAME = "source_field";
  	this.RECOMMENDATION_TABLE_CONCEPTUAL_CLASS = "conceptual_class";
  	this.RECOMMENDATION_TABLE_TARGET_CLASS = "target_class";
  	this.RECOMMENDATION_TABLE_TARGET_FIELD = "target_field";
  	this.RECOMMENDATION_TABLE_IS_LOOKUP = "is_lookup";
  	this.RECOMMENDATION_TABLE_TYPE = "type";
  	this.ATTRIBUTE_RECOMMENDATION = 'attribute_recommendation';
  	this.CLASS_RECOMMENDATION = 'class_recommendation';
  	this.IS_ACTIVE = 'is_active';
  	this.ENTITY_MAPPING = 'entity_mapping';

  	this.SYS_DICTIONARY = "sys_dictionary";
  	this.ELEMENT = "element";
  	this.NAME = "name";
  	this.APPLIED = 'applied';

  	this.SYS_DB_OBJECT = "sys_db_object";
  	this.LABEL = "label";

  	this.COUNT = "COUNT";
  	this.SYS_PREFIX = "sys_";
  	this.U_PREFIX = "u_";
  	this.IMPORT_ENTITY_NAME = "Import";
  	this.arrayUtil = new global.ArrayUtil();
  },

  getApplicationFeed: function(applicationFeedId) {
  	if (!applicationFeedId) {
  		return new sn_ws_err.BadRequestError(gs.getMessage("No application feed"));
  	}
  	var gr = new GlideRecord(this.CMDB_INST_APPLICATION_FEED);
  	if (gr.get(applicationFeedId)) {
  		return gr;
  	}
  	return null;
  },

  getImportSetTableName: function(dataSourceSysId) {
  	if (!dataSourceSysId) {
  		return new sn_ws_err.BadRequestError(gs.getMessage("No data source id"));
  	}
  	var gr = new GlideRecord(this.SYS_IMPORT_SET);
  	gr.addQuery(this.DATA_SOURCE, dataSourceSysId);
  	gr.addQuery(this.STATE, this.LOADED);
  	gr.query();
  	if (gr.hasNext()) {
  		gr.next();
  		return gr.getValue(this.TABLE_NAME);
  	}
  	return new sn_ws_err.BadRequestError(gs.getMessage("No import set table name found for data source"));
  },

  getImportSetTableColumns: function(tableName) {
  	if (!tableName) {
  		return new sn_ws_err.BadRequestError(gs.getMessage("No table name"));
  	}
  	var columns = [];
  	var gr = new GlideRecord(this.SYS_DICTIONARY);
  	gr.addQuery(this.NAME, tableName);
  	gr.query();
  	while (gr.next()) {
  		var name = gr.getValue(this.ELEMENT);
  		if (!name || name.startsWith(this.SYS_PREFIX)) {
  			continue;
  		}
  		/*if (name.startsWith(this.U_PREFIX)) {
  			name = name.substring(2);
  		}*/
  		columns.push(name);
  	}
  	return columns;
  },
  
  getImportEntityColumns: function(feedId) {
  	var columns = [];
  	
  	var gr = new GlideRecord(this.CMDB_INST_ENTITY);
  	gr.addQuery(this.SYS_RTE_EB_DEFINITION, feedId);
  	gr.addQuery(this.NAME, this.IMPORT_ENTITY_NAME);
  	gr.query();
  	var importEntityId = '';
  	if (gr.next()) {
  		importEntityId = gr.getValue(this.SYS_ID);
  	}
  	
  	gr = new GlideRecord(this.SYS_RTE_EB_FIELD);
  	gr.addQuery(this.SYS_RTE_EB_ENTITY, importEntityId);
  	gr.query();
  	while (gr.next()) {
  		var fieldPath = gr.getValue(this.FIELD);
  		var fieldPathParts = fieldPath.split('.');
  		var field = fieldPathParts[fieldPathParts.length - 1];
  		if (columns.indexOf(field) < 0) {
  			columns.push(field);
  		}
  	}
  	return columns;
  },

  getRecommendedClassesNoSource: function(applicationFeedId, columns) {
  	return this.getRecommendedClasses(applicationFeedId, null, columns);
  },

  getRecommendedClasses: function(applicationFeedId, source, columns) {
  	var stateMap = this._getClassRecommendationState(applicationFeedId);
  	var recommendedCmdbClasses = [];
  	var classCountMap = {};
  	var classToSourceFieldMap = {};

  	var classGr = new GlideRecord(this.CLASS_RECOMMENDATION_TABLE);
  	classGr.addQuery(this.RECOMMENDATION_TABLE_SOURCE_COLUMN_NAME, columns);
  	classGr.addQuery(this.IS_ACTIVE, 'true');
  	classGr.addNotNullQuery(this.RECOMMENDATION_TABLE_CONCEPTUAL_CLASS);
  	classGr.query();
  	while (classGr.next()) {
  		var classMappingId = classGr.getValue(this.SYS_ID);
  		var conceptualClass = classGr.getValue(this.RECOMMENDATION_TABLE_CONCEPTUAL_CLASS);
  		var sourceField = classGr.getValue(this.RECOMMENDATION_TABLE_SOURCE_COLUMN_NAME);
  		var countedSources = [];
  		var attrGr = new GlideRecord(this.ATTRIBUTE_RECOMMENDATION_TABLE);
  		attrGr.addQuery(this.CLASS_RECOMMENDATION, classMappingId);
  		attrGr.query();
  		var attrMappingIds = [];
  		while (attrGr.next()) {
  			attrMappingIds.push(attrGr.getValue(this.SYS_ID));
  		}
  		var sourceGr = new GlideRecord(this.RECOMMENDATION_SOURCE_COUNT_TABLE);
  		sourceGr.addQuery(this.ATTRIBUTE_RECOMMENDATION, 'IN', attrMappingIds.join(','));
  		if (source) {
  			sourceGr.addQuery(this.RECOMMENDATION_TABLE_SOURCE, source);
  		}
  		sourceGr.query();
  		while (sourceGr.next()) {
  			var linkedSource = sourceGr.getValue(this.RECOMMENDATION_TABLE_SOURCE);
  			if (this.arrayUtil.indexOf(countedSources, linkedSource) < 0) {
  				countedSources.push(linkedSource);
  			}
  		}
  		if (countedSources.length == 0) {
  			continue;
  		}
  		if (!classCountMap[conceptualClass]) {
  			classCountMap[conceptualClass] = 0;
  		}
  		classCountMap[conceptualClass] += countedSources.length;
  		
  		if (!classToSourceFieldMap[conceptualClass]) {
  			classToSourceFieldMap[conceptualClass] = [];
  		}
  		if (classToSourceFieldMap[conceptualClass].indexOf(sourceField) < 0) {
  			classToSourceFieldMap[conceptualClass].push(sourceField);
  		}
  	}

  	var keys = Object.keys(classCountMap);
  	for (var i = 0; i < keys.length; i++) {
  		var recommendedClass = keys[i];
  		var dbGr = new GlideRecord(this.SYS_DB_OBJECT);
  		dbGr.addQuery(this.NAME, recommendedClass);
  		dbGr.query();
  		if (dbGr.next()) {
  			var recommendedCmdbClass = {};
  			recommendedCmdbClass.cmdbClass = recommendedClass;
  			recommendedCmdbClass.classLabel = dbGr.getValue(this.LABEL);
  			recommendedCmdbClass.state = stateMap[recommendedClass] || '';
  			recommendedCmdbClass.count = classCountMap[recommendedClass];
  			recommendedCmdbClass.percentage = (classCountMap[recommendedClass] / columns.length * 100).toFixed(2);
  			recommendedCmdbClass.classSysId = dbGr.getValue(this.SYS_ID);
  			recommendedCmdbClass.sourceFieldsMatched = classToSourceFieldMap[recommendedClass];
  			recommendedCmdbClasses.push(recommendedCmdbClass);
  		}
  	}
  	recommendedCmdbClasses.sort(function(a, b) {
  		var condition = b.count - a.count;
  		if (condition != 0) {
  			return condition;
  		}
  		if (a.state !== 'applied' && b.state === 'applied') {
  			return -1;
  		} else if (a.state === 'applied' && b.state !== 'applied') {
  			return 1;
  		}
  		if(a.classLabel < b.classLabel) { 
  			return -1; 
  		}
  		if(a.classLabel > b.classLabel) { 
  			return 1; 
  		}
  		return 0;
  	});

  	return recommendedCmdbClasses;
  },

  _getClassRecommendationState: function(applicationFeedId) {
  	var stateMap = {};
  	var gr = new GlideRecord(this.CLASS_RECOMMENDATION_STATE_TABLE);
  	gr.addQuery(this.CMDB_INST_APPLICATION_FEED, applicationFeedId);
  	gr.query();
  	while (gr.next()) {
  		var className = gr.getValue(this.RECOMMENDATION_TABLE_CONCEPTUAL_CLASS);
  		var state = gr.getValue(this.STATE);
  		stateMap[className] = state;
  	}
  	return stateMap;
  },

  getRecommendedAttributes: function(applicationFeedId, source, cmdbClasses, columns) {
  	var stateMap = this._getAttributeRecommendationState(applicationFeedId);
  	var recommendedAttributes = {};
  	if (source) {
  		this._addExistingRecommendations(source, cmdbClasses, columns, recommendedAttributes, stateMap);
  	}
  	this._addExistingRecommendations(null, cmdbClasses, columns, recommendedAttributes, stateMap);
  	this._addExactMatches(cmdbClasses, columns, recommendedAttributes);
  	for (var key in recommendedAttributes) {
  		recommendedAttributes[key].sort(function(a, b) {
  			if (a.count && b.count) {
  				var condition = b.count - a.count;
  				if (condition != 0) {
  					return condition;
  				}
  			}
  			if (a.state != 'applied' && b.state == 'applied') {
  				return -1;
  			} else if (a.state == 'applied' && b.state != 'applied') {
  				return 1;
  			}
  			if(a.fieldLabel < b.fieldLabel) { 
  				return -1; 
  			}
  			if(a.fieldLabel > b.fieldLabel) { 
  				return 1; 
  			}
  			return 0;
  		});
  	}
  	return recommendedAttributes;
  },

  _getAttributeRecommendationState: function(applicationFeedId) {
  	var stateMap = {};
  	var gr = new GlideRecord(this.ATTRIBUTE_RECOMMENDATION_STATE_TABLE);
  	gr.query();
  	while (gr.next()) {
  		if ((gr.entity_mapping.sys_rte_eb_definition + '') == applicationFeedId) {
  			var attrId = gr.getValue(this.ATTRIBUTE_RECOMMENDATION);
  			var state = gr.getValue(this.STATE);
  			stateMap[attrId] = state;
  		}
  	}
  	return stateMap;
  },

  _addExistingRecommendations: function(source, cmdbClasses, columns, recommendedAttributes, stateMap) {
  	this._removePreviousMappings(columns, recommendedAttributes);
  	var classMappingIds = [];
  	var classGr = new GlideRecord(this.CLASS_RECOMMENDATION_TABLE);
  	classGr.addQuery(this.RECOMMENDATION_TABLE_SOURCE_COLUMN_NAME, columns);
  	classGr.addQuery(this.IS_ACTIVE, 'true');
  	if (cmdbClasses && cmdbClasses.length > 0) {
  		var tableHierarchy = [];
  		for (var i = 0; i < cmdbClasses.length; i++) {
  			var table = new GlideTableHierarchy(cmdbClasses[i]);
  			tableHierarchy.push(table.getTables());
  		}
  		classGr.addQuery(this.RECOMMENDATION_TABLE_CONCEPTUAL_CLASS, 'IN', tableHierarchy.toString());
  	}
  	classGr.query();
  	while (classGr.next()) {
  		classMappingIds.push(classGr.getValue(this.SYS_ID));
  	}
  	var attrGr = new GlideRecord(this.ATTRIBUTE_RECOMMENDATION_TABLE);
  	attrGr.addQuery(this.CLASS_RECOMMENDATION, classMappingIds);
  	attrGr.addQuery(this.IS_ACTIVE, 'true');
  	attrGr.query();
  	while (attrGr.next()) {
  		var sourceField = attrGr.class_recommendation.source_field + '';
  		var attrMappingId = attrGr.getValue(this.SYS_ID);
  		var conceptualClass = attrGr.class_recommendation.conceptual_class + '';
  		var targetTable = attrGr.getValue(this.RECOMMENDATION_TABLE_TARGET_CLASS);
  		var targetField = attrGr.getValue(this.RECOMMENDATION_TABLE_TARGET_FIELD);
  		var isLookup = attrGr.getValue(this.RECOMMENDATION_TABLE_IS_LOOKUP);
  		
  		var elementGr = this._getElementGlideRecord(targetTable, targetField);
  		if (elementGr == null) {
  			continue;
  		}
  		var fieldLabel = elementGr.getValue(this.COLUMN_LABEL);
  		var fieldSysId = elementGr.getValue(this.SYS_ID);

  		var recommendedCmdbAttribute = {};
  		var sourceGa = new GlideAggregate(this.RECOMMENDATION_SOURCE_COUNT_TABLE);
  		sourceGa.addQuery(this.ATTRIBUTE_RECOMMENDATION, attrMappingId);
  		if (source) {
  			sourceGa.addQuery(this.RECOMMENDATION_TABLE_SOURCE, source);
  			recommendedCmdbAttribute.match = 'source';
  		} else {
  			recommendedCmdbAttribute.match = 'other';
  		}
  		sourceGa.addAggregate('COUNT');
  		sourceGa.query();
  		if (sourceGa.next()) {
  			recommendedCmdbAttribute.count = Number(sourceGa.getAggregate('COUNT'));
  			if (recommendedCmdbAttribute.count == 0) {
  				continue;
  			}
  		} else {
  			continue;
  		}

  		recommendedCmdbAttribute.recommendationId = attrMappingId;
  		recommendedCmdbAttribute.conceptualClass = conceptualClass;
  		recommendedCmdbAttribute.targetTable = targetTable;
  		recommendedCmdbAttribute.targetField = targetField;
  		recommendedCmdbAttribute.isLookup = isLookup;
  		recommendedCmdbAttribute.fieldLabel = fieldLabel;
  		recommendedCmdbAttribute.state = stateMap[attrMappingId] || '';
  		recommendedCmdbAttribute.fieldSysId = fieldSysId;
  		
  		var dbGr = new GlideRecord(this.SYS_DB_OBJECT);
  		dbGr.addQuery(this.NAME, targetTable);
  		dbGr.query();
  		if (dbGr.next()) {
  			recommendedCmdbAttribute.targetTableLabel = dbGr.getValue(this.LABEL);
  		}

  		if (!recommendedAttributes[sourceField]) {
  			recommendedAttributes[sourceField] = [];
  		}
  		recommendedAttributes[sourceField].push(recommendedCmdbAttribute);
  	}
  },

  _addExactMatches: function(cmdbClasses, columns, recommendedAttributes) {
  	this._removePreviousMappings(columns, recommendedAttributes);
  	var gr = new GlideRecord(this.SYS_DICTIONARY);
  	gr.addQuery(this.ELEMENT, columns);
  	gr.addQuery(this.NAME, cmdbClasses);
  	gr.query();
  	while (gr.next()) {
  		var table = gr.getValue(this.NAME);
  		var field = gr.getValue(this.ELEMENT);
  		var columnLabel = gr.getValue(this.COLUMN_LABEL);
  		var fieldSysId = gr.getValue(this.SYS_ID);
  		
  		if (recommendedAttributes[field]) {
  			var isDuplicate = false;
  			var recommendationList = recommendedAttributes[field];
  			for (var i = 0; i < recommendationList.length; i++) {
  				var currentRecommendation = recommendationList[i];
  				if (currentRecommendation.isLookup == '0' && currentRecommendation.targetField == field) {
  					isDuplicate = true;
  					break;
  				}
  			}
  			if (isDuplicate) {
  				continue;
  			}
  		}
  		
  		if (!recommendedAttributes[field]) {
  			recommendedAttributes[field] = [];
  		}

  		var recommendedCmdbAttribute = {};
  		recommendedCmdbAttribute.conceptualClass = table;
  		recommendedCmdbAttribute.targetTable = table;
  		recommendedCmdbAttribute.targetField = field;
  		recommendedCmdbAttribute.isLookup = '0';
  		recommendedCmdbAttribute.match = 'exact';
  		recommendedCmdbAttribute.fieldLabel = columnLabel;
  		recommendedCmdbAttribute.isApplied = false;
  		recommendedCmdbAttribute.fieldSysId = fieldSysId;
  		
  		var dbGr = new GlideRecord(this.SYS_DB_OBJECT);
  		dbGr.addQuery(this.NAME, table);
  		dbGr.query();
  		if (dbGr.next()) {
  			recommendedCmdbAttribute.targetTableLabel = dbGr.getValue(this.LABEL);
  		}

  		recommendedAttributes[field].push(recommendedCmdbAttribute);
  	}
  },

  _getElementGlideRecord: function(targetTable, targetField) {
  	if (!targetTable || !targetField || targetField.startsWith(this.SYS_PREFIX)) {
  		return null;
  	}
  	var gr = new GlideRecord(this.SYS_DICTIONARY);
  	gr.addQuery(this.NAME, targetTable);
  	gr.addQuery(this.ELEMENT, targetField);
  	gr.query();
  	if (gr.hasNext()) {
  		gr.next();
  		return gr;
  	}
  	return null;
  },

  _removePreviousMappings: function(columns, recommendedAttributes) {
  	for (var key in recommendedAttributes) {
  		columns.splice(columns.indexOf(key), 1);
  	}
  },

  changeClassRecommendationState: function(applicationFeedId, conceptualClass, state) {
  	var gr = new GlideRecord(this.CLASS_RECOMMENDATION_STATE_TABLE);
  	gr.addQuery(this.CMDB_INST_APPLICATION_FEED, applicationFeedId);
  	gr.addQuery(this.RECOMMENDATION_TABLE_CONCEPTUAL_CLASS, conceptualClass);
  	gr.query();
  	if (gr.next()) {
  		gr.setValue(this.STATE, state);
  		return gr.update();
  	}
  	gr.initialize();
  	gr.setValue(this.CMDB_INST_APPLICATION_FEED, applicationFeedId);
  	gr.setValue(this.RECOMMENDATION_TABLE_CONCEPTUAL_CLASS, conceptualClass);
  	gr.setValue(this.STATE, state);
  	return gr.insert();
  },
  
  deleteClassRecommendationState: function(applicationFeedId, conceptualClass) {
  	var gr = new GlideRecord(this.CLASS_RECOMMENDATION_STATE_TABLE);
  	gr.addQuery(this.CMDB_INST_APPLICATION_FEED, applicationFeedId);
  	gr.addQuery(this.RECOMMENDATION_TABLE_CONCEPTUAL_CLASS, conceptualClass);
  	gr.query();
  	if (gr.next()) {
  		var re = gr.deleteRecord();
  		if (!re) {
  			return this._getFailReturnObject(gs.getMessage('Fail to delete class recommendation state'), '');
  		} else {
  			return this._getSuccessReturnObject();
  		}
  	}
  	return this._getTerminateReturnObject(gs.getMessage('Cannot find class recommendation state'), '');
  },

  changeAttributeRecommendationState: function(entityMappingId, recommendationId, state) {
  	var gr = new GlideRecord(this.ATTRIBUTE_RECOMMENDATION_STATE_TABLE);
  	gr.addQuery(this.ENTITY_MAPPING, entityMappingId);
  	gr.addQuery(this.ATTRIBUTE_RECOMMENDATION, recommendationId);
  	gr.query();
  	if (gr.next()) {
  		gr.setValue(this.STATE, state);
  		return gr.update();
  	}
  	gr.initialize();
  	gr.setValue(this.ENTITY_MAPPING, entityMappingId);
  	gr.setValue(this.ATTRIBUTE_RECOMMENDATION, recommendationId);
  	gr.setValue(this.STATE, state);
  	return gr.insert();
  },
  
  deleteAttributeRecommendationState: function(entityMappingId, recommendationId) {
  	var gr = new GlideRecord(this.ATTRIBUTE_RECOMMENDATION_STATE_TABLE);
  	gr.addQuery(this.ENTITY_MAPPING, entityMappingId);
  	gr.addQuery(this.ATTRIBUTE_RECOMMENDATION, recommendationId);
  	gr.query();
  	if (gr.next()) {
  		var re = gr.deleteRecord();
  		if (!re) {
  			return this._getFailReturnObject(gs.getMessage('Fail to delete attribute recommendation state'), '');
  		} else {
  			return this._getSuccessReturnObject();
  		}
  	}
  	return this._getTerminateReturnObject(gs.getMessage('Cannot find attribute recommendation state'), '');
  },
  
  deleteAttributeRecommendationStateWithEmptyEntityMapping: function() {
  	var gr = new GlideRecord(this.ATTRIBUTE_RECOMMENDATION_STATE_TABLE);
  	gr.addQuery(this.ENTITY_MAPPING, '');
  	gr.query();
  	while (gr.next()) {
  		var re = gr.deleteRecord();
  		if (!re) {
  			return this._getFailReturnObject(gs.getMessage('Fail to delete attribute recommendation state'), '');
  		}
  	}
  	return this._getSuccessReturnObject();
  },

  addRecommendation: function(source, conceptualClass, sourceField, targetField, targetClass, isLookup, type) {
  	var classMappingId = '';
  	var gr = new GlideRecord(this.CLASS_RECOMMENDATION_TABLE);
  	gr.addQuery(this.RECOMMENDATION_TABLE_CONCEPTUAL_CLASS, conceptualClass);
  	gr.addQuery(this.RECOMMENDATION_TABLE_SOURCE_COLUMN_NAME, sourceField);
  	gr.query();
  	if (gr.next()) {
  		classMappingId = gr.getValue(this.SYS_ID);
  	} else {
  		gr.initialize();
  		gr.setValue(this.RECOMMENDATION_TABLE_CONCEPTUAL_CLASS, conceptualClass);
  		gr.setValue(this.RECOMMENDATION_TABLE_SOURCE_COLUMN_NAME, sourceField);
  		gr.setValue(this.RECOMMENDATION_TABLE_TYPE, type);
  		gr.setValue(this.IS_ACTIVE, 'true');
  		classMappingId = gr.insert();
  	}

  	var attrMappingId = '';
  	gr = new GlideRecord(this.ATTRIBUTE_RECOMMENDATION_TABLE);
  	gr.addQuery(this.CLASS_RECOMMENDATION, classMappingId);
  	gr.addQuery(this.RECOMMENDATION_TABLE_TARGET_FIELD, targetField);
  	gr.addQuery(this.RECOMMENDATION_TABLE_TARGET_CLASS, targetClass);
  	gr.addQuery(this.RECOMMENDATION_TABLE_IS_LOOKUP, isLookup);
  	gr.query();
  	if (gr.next()) {
  		attrMappingId = gr.getValue(this.SYS_ID);
  	} else {
  		gr.initialize();
  		gr.setValue(this.CLASS_RECOMMENDATION, classMappingId);
  		gr.setValue(this.RECOMMENDATION_TABLE_TARGET_FIELD, targetField);
  		gr.setValue(this.RECOMMENDATION_TABLE_TARGET_CLASS, targetClass);
  		gr.setValue(this.RECOMMENDATION_TABLE_IS_LOOKUP, isLookup);
  		gr.setValue(this.RECOMMENDATION_TABLE_TYPE, type);
  		gr.setValue(this.IS_ACTIVE, 'true');
  		attrMappingId = gr.insert();
  	}

  	gr = new GlideRecord(this.RECOMMENDATION_SOURCE_COUNT_TABLE);
  	gr.initialize();
  	gr.setValue(this.RECOMMENDATION_TABLE_SOURCE, source);
  	gr.setValue(this.ATTRIBUTE_RECOMMENDATION, attrMappingId);
  	return gr.insert();
  },

  deleteRecommendation: function(source, conceptualClass, sourceField, 
  								targetField, targetClass, isLookup, deleteClass) {
  	// This method is only for REST exposure
  	// It provides a way to delete single recommendation record through REST call
  	var classMappingId = '';
  	var classGr = new GlideRecord(this.CLASS_RECOMMENDATION_TABLE);
  	classGr.addQuery(this.RECOMMENDATION_TABLE_CONCEPTUAL_CLASS, conceptualClass);
  	classGr.addQuery(this.RECOMMENDATION_TABLE_SOURCE_COLUMN_NAME, sourceField);
  	classGr.query();
  	if (classGr.next()) {
  		classMappingId = classGr.getValue(this.SYS_ID);
  	} else {
  		return this._getFailReturnObject(gs.getMessage('Cannot find class recommendation'), '');
  	}

  	var attrMappingId = '';
  	var attrGr = new GlideRecord(this.ATTRIBUTE_RECOMMENDATION_TABLE);
  	attrGr.addQuery(this.CLASS_RECOMMENDATION, classMappingId);
  	attrGr.addQuery(this.RECOMMENDATION_TABLE_TARGET_FIELD, targetField);
  	attrGr.addQuery(this.RECOMMENDATION_TABLE_TARGET_CLASS, targetClass);
  	attrGr.addQuery(this.RECOMMENDATION_TABLE_IS_LOOKUP, isLookup);
  	attrGr.query();
  	if (attrGr.next()) {
  		attrMappingId = attrGr.getValue(this.SYS_ID);
  	} else {
  		return this._getFailReturnObject(gs.getMessage('Cannot find attribute recommendation'), '');
  	}

  	var sourceGr = new GlideRecord(this.RECOMMENDATION_SOURCE_COUNT_TABLE);
  	sourceGr.addQuery(this.RECOMMENDATION_TABLE_SOURCE, source);
  	sourceGr.addQuery(this.ATTRIBUTE_RECOMMENDATION, attrMappingId);
  	sourceGr.query();
  	if (sourceGr.next()) {
  		sourceGr.deleteRecord();
  		attrGr.deleteRecord();
  		if (deleteClass) {
  			classGr.deleteRecord();
  		}
  		return this._getSuccessReturnObject();
  	} else {
  		return this._getFailReturnObject(gs.getMessage('Cannot find source count'), '');
  	}
  },
  
  deleteClassRecommendation: function(entityMappingId, source, conceptualClass, sourceFields) {
  	// Get class recommendation record IDs and type
  	var classRecommendationIds = [];
  	var manualClassRecommendationIds = [];
  	var classGr = new GlideRecord(this.CLASS_RECOMMENDATION_TABLE);
  	classGr.addQuery(this.RECOMMENDATION_TABLE_CONCEPTUAL_CLASS, conceptualClass);
  	classGr.addQuery(this.RECOMMENDATION_TABLE_SOURCE_COLUMN_NAME, sourceFields);
  	classGr.query();
  	while (classGr.next()) {
  		var id = classGr.getValue(this.SYS_ID);
  		var type = classGr.getValue(this.RECOMMENDATION_TABLE_TYPE);
  		if (type == 'manual') {
  			manualClassRecommendationIds.push(id);
  		}
  		classRecommendationIds.push(id);
  	} 
  	
  	if (classRecommendationIds.length == 0) {
  		return this._getTerminateReturnObject(gs.getMessage('Cannot find class recommendation'), '');
  	}
  	
  	// Get attribute recommendation IDs
  	var manualAttrIdToClassId = {};
  	var attrRecommendationIds = [];
  	var attrGr = new GlideRecord(this.ATTRIBUTE_RECOMMENDATION_TABLE);
  	attrGr.addQuery(this.CLASS_RECOMMENDATION, classRecommendationIds);
  	attrGr.query();
  	while (attrGr.next()) {
  		var id2 = attrGr.getValue(this.SYS_ID);
  		var type2 = attrGr.getValue(this.RECOMMENDATION_TABLE_TYPE);
  		var classId = attrGr.getValue(this.CLASS_RECOMMENDATION);
  		attrRecommendationIds.push(id2);
  		if (type2 == 'manual') {
  			manualAttrIdToClassId[id2] = classId;
  		}
  	}
  	var manualAttrRecommendationIds = Object.keys(manualAttrIdToClassId);
  	for (var i = 0; i < attrRecommendationIds.length; i++) {
  		var delResult = this._deleteSourceCount(source, attrRecommendationIds[i]);
  		if (delResult.status == 'failure') {
  			return this._getFailReturnObject(delResult.error.message, '');
  		}
  		
  		var delStateRe = this.deleteAttributeRecommendationState(entityMappingId, attrRecommendationIds[i]);
  		if (delStateRe.status == 'failure') {
  			return this._getFailReturnObject(delResult.error.message, '');
  		}
  	}
  	
  	// Filter out attribute recommendation to delete
  	var toSaveAttrIds = [];
  	var sourceGr = new GlideRecord(this.RECOMMENDATION_SOURCE_COUNT_TABLE);
  	sourceGr.addQuery(this.ATTRIBUTE_RECOMMENDATION, manualAttrRecommendationIds);
  	sourceGr.query();
  	while (sourceGr.next()) {
  		var attrId = sourceGr.getValue(this.ATTRIBUTE_RECOMMENDATION);
  		if (toSaveAttrIds.indexOf(attrId) < 0) {
  			toSaveAttrIds.push(attrId);
  		}
  	}
  	var toDeleteAttrIds = this._getUniqueValuesFromTwoArrays(manualAttrRecommendationIds, toSaveAttrIds);
  	
  	// Filter out class recommendation to delete
  	var toSaveClassIds = [];
  	for (var i = 0; i < toSaveAttrIds.length; i++) {
  		var toSaveClassId = manualAttrIdToClassId[toSaveAttrIds[i]];
  		if (toSaveClassIds.indexOf(toSaveClassId) < 0) {
  			toSaveClassIds.push(toSaveClassId);
  		}
  	}
  	var toDeleteClassIds = this._getUniqueValuesFromTwoArrays(manualClassRecommendationIds, toSaveClassIds);
  	
  	// Delete process
  	var gr = new GlideRecord(this.CLASS_RECOMMENDATION_TABLE);
  	gr.addQuery(this.SYS_ID, toDeleteClassIds);
  	gr.query();
  	while (gr.next()) {
  		gr.deleteRecord();
  	}
  	gr = new GlideRecord(this.ATTRIBUTE_RECOMMENDATION_TABLE);
  	gr.addQuery(this.SYS_ID, toDeleteAttrIds);
  	gr.query();
  	while (gr.next()) {
  		gr.deleteRecord();
  	}
  	return this._getSuccessReturnObject();
  },
  
  deleteAttributeRecommendation: function(source, conceptualClass, sourceField, targetField, targetClass, isLookup) {
  	var classMappingId = '';
  	var classMappingType = '';
  	var classGr = new GlideRecord(this.CLASS_RECOMMENDATION_TABLE);
  	classGr.addQuery(this.RECOMMENDATION_TABLE_CONCEPTUAL_CLASS, conceptualClass);
  	classGr.addQuery(this.RECOMMENDATION_TABLE_SOURCE_COLUMN_NAME, sourceField);
  	classGr.query();
  	if (classGr.next()) {
  		classMappingId = classGr.getValue(this.SYS_ID);
  		classMappingType = classGr.getValue(this.RECOMMENDATION_TABLE_TYPE);
  	} else {
  		return this._getTerminateReturnObject(gs.getMessage('Cannot find class recommendation'), '');
  	}

  	var attrMappingId = '';
  	var attrMappingType = '';
  	var attrGr = new GlideRecord(this.ATTRIBUTE_RECOMMENDATION_TABLE);
  	attrGr.addQuery(this.CLASS_RECOMMENDATION, classMappingId);
  	attrGr.addQuery(this.RECOMMENDATION_TABLE_TARGET_FIELD, targetField);
  	attrGr.addQuery(this.RECOMMENDATION_TABLE_TARGET_CLASS, targetClass);
  	attrGr.addQuery(this.RECOMMENDATION_TABLE_IS_LOOKUP, isLookup);
  	attrGr.query();
  	if (attrGr.next()) {
  		attrMappingId = attrGr.getValue(this.SYS_ID);
  		var deleteResult = this._deleteSourceCount(source, attrMappingId);
  		if (deleteResult.status == 'failure') {
  			return this._getTerminateReturnObject(deleteResult.error.message, '');
  		}
  		attrMappingType = attrGr.getValue(this.RECOMMENDATION_TABLE_TYPE);
  		if (attrMappingType != 'manual') {
  			return this._getTerminateReturnObject(gs.getMessage('Record type is not manual'), '');
  		}
  	} else {
  		return this._getTerminateReturnObject(gs.getMessage('Cannot find attribute recommendation'), '');
  	}
  	
  	var sourceGr = new GlideRecord(this.RECOMMENDATION_SOURCE_COUNT_TABLE);
  	sourceGr.addQuery(this.ATTRIBUTE_RECOMMENDATION, attrMappingId);
  	sourceGr.query();
  	if (sourceGr.next()) {
  		var currentSource = sourceGr.getValue(this.RECOMMENDATION_TABLE_SOURCE);
  		return this._getTerminateReturnObject(gs.getMessage('{0} is using this recommendation', currentSource), '');
  	}
  	if (!attrGr.deleteRecord()) {
  		return this._getTerminateReturnObject(gs.getMessage('Fail to delete attribute recommendation'), '');
  	}
  	// Delete orphan class recommendation
  	if (classMappingType == 'manual') {
  		var gr = new GlideRecord(this.ATTRIBUTE_RECOMMENDATION_TABLE);
  		gr.addQuery(this.CLASS_RECOMMENDATION, classMappingId);
  		gr.query();
  		if (!gr.hasNext()) {
  			classGr.deleteRecord();
  		}
  	}
  	return this._getSuccessReturnObject();
  },
  
  isNestedPayload: function(feedId) {
  	var isNestedPayload = false;
  	var templateStateGr = new GlideRecord(this.CMDB_INST_TEMPLATE_STATE);
  	templateStateGr.addQuery(this.APPLICATION_FEED_ID, feedId);
  	templateStateGr.query();
  	if (templateStateGr.next()) {
  		var dataInSingleColumn = templateStateGr.import_set_id.data_source.data_in_single_column.toString();
  		dataInSingleColumn = dataInSingleColumn === 'true' ? true : false;
  		isNestedPayload = dataInSingleColumn;
  	}
  	return isNestedPayload;
  },

  _deleteSourceCount: function(source, attrMappingId) {
  	var gr = new GlideRecord(this.RECOMMENDATION_SOURCE_COUNT_TABLE);
  	gr.addQuery(this.RECOMMENDATION_TABLE_SOURCE, source);
  	gr.addQuery(this.ATTRIBUTE_RECOMMENDATION, attrMappingId);
  	gr.query();
  	if (!gr.hasNext()) {
  		return this._getTerminateReturnObject(gs.getMessage('No source count matched'), '');
  	}
  	if (gr.next()) {
  		if (!gr.deleteRecord()) {
  			return this._getFailReturnObject(gs.getMessage('Fail to delete source count record'), '');
  		}
  	}
  	return this._getSuccessReturnObject();
  },
  
  _getUniqueValuesFromTwoArrays: function(array1, array2) {
  	var map = {};
  	var result = [];
  	for (var i = 0; i < array1.length; i++) {
  		if (map[array1[i]]) {
  			map[array1[i]] = map[array1[i]] + 1;
  		} else {
  			map[array1[i]] = 1;
  		}
  	}
  	for (var j = 0; j < array2.length; j++) {
  		if (map[array2[j]]) {
  			map[array2[j]] = map[array2[j]] + 1;
  		} else {
  			map[array2[j]] = 1;
  		}
  	}
  	var keys = Object.keys(map);
  	for (var k = 0; k < keys.length; k++) {
  		if (map[keys[k]] <= 1) {
  			result.push(keys[k]);
  		}
  	}
  	return result;
  },
  
  _getFailReturnObject: function(message, detail) {
  	return {
  		status: 'failure',
  		error: {
  			message: message,
  			detail: detail
  		}
  	};
  },
  
  _getSuccessReturnObject: function(data) {
  	return {
  		status: 'success',
  		data: data
  	};
  },
  
  _getTerminateReturnObject: function(message, detail) {
  	return {
  		status: 'terminated',
  		error: {
  			message: message,
  			detail: detail
  		}
  	};
  },

  type: 'CmdbRecommendations'
};

Sys ID

34b4769973133300202627b751ab9e4b

Offical Documentation

Official Docs: