business_rule_sysid_validate.js

//
// Intrux Business Rule Validator
// - checks if the referenced SysIDs used in the 'Filter Conditions' point to existing records
// - pure read only: does not alter anthing
//
// Copyright (c) 2025 Intrux GmbH
// All rights reserved.
//

if (['global', 'rhino.global'].indexOf(gs.getCurrentScopeName()) == -1) {
	gs.error('Script needs to be executed in global scope!');
}
else {
	gs.info('\n***\n***\n*** Intrux Business Rule Validator ***\n***\n***'
		+ '\n*** Please scroll down to the bottom of this page to see the results'
		+ '\n*** as there may be many non-hidable messages produced by the system.'
		+ '\n*** Those messages are produced by invalid encoded queries present in'
		+ '\n*** the Business Rule conditions - e.g. because of missing fields from plugins.'
		+ '\n***'
		+ '\n*** Hint: Scroll to the end of the page using End (Windows) or ⌘ + ↓ (MacOS)'
		+ '\n***'
		+ '\n***');
	var scriptGr = new GlideRecord('sys_script');
	scriptGr.addQuery('active', true);
	scriptGr.addNotNullQuery('collection');
	scriptGr.orderByDesc('sys_updated_on');
	//scriptGr.setLimit(100);
	scriptGr.query();
	var recordsChecked = 0;
	var sysIDsValidated = 0;
	var missingSysIDs = 0;
	var printDetails = false;
	var messages = [];
	while (scriptGr.next()) {
		var targetTable = scriptGr.getValue('collection');
		var recordChecked = false;
		[scriptGr.getValue('filter_condition'), scriptGr.getValue('template')].forEach(function (value) {
			if (!value) return;
			
			var result = checkCondition(targetTable, value);
			var message = '';
			var presentRecords = 0;
			for (var table in result.present) {
				presentRecords += Object.keys(result.present[table]).length;
			}

			if (presentRecords && printDetails) {
				message += '\n Validated ' + presentRecords + ' hard-coded records';
			}

			sysIDsValidated += presentRecords;

			var missing = [];
			for (var table in result.missing) {
				var ids = result.missing[table];
				if (ids.length) {
					missingSysIDs += ids.length;
					missing.push(table + ': ' + ids.join(','));
				}
			}

			if (missing.length) {
				message += '\n Found ' + missing.length + ' non-existing records:\n  ' + missing.join('\n  ');
			}

			if (message) {
				messages.push('\nBusiness Rule: ' + scriptGr.getDisplayValue() + message);
			}
			
			recordChecked = recordChecked || presentRecords || missing.length;
		});

		if (recordChecked) {
			recordsChecked++;
		}
	}

	messages.push(
		'\nSummary:\n ' + recordsChecked + ' Business Rules with hard-coded SysIDs checked\n ' + sysIDsValidated + ' hard-coded SysIDs\n ' + missingSysIDs + ' records referenced which do not exist on this instance'
	);
	gs.info('\n***\n***\n*** Intrux Business Rule Validator Results ***\n***'
		+ '\n***'
		+ '\n***'
		+ messages.join('\n'));
}

/**
 * Checks a given condition (encoded query
 * @param {string} table
 * @param {string} condition
 * @return {{present: Object.<string, string[]>, missing: Object.<string, string[]>}}
 */
function checkCondition(table, condition) {
	var pairs = {};
	getQueryStringTerms(table, condition).forEach(function (term) {
		if (term.endquery) return;

		var refTable = null;
		var refElem = null;
		var refValues = [];

		if (term.operator == '=' && GlideStringUtil.isEligibleSysID(term.value)) {
			refElem = new GlideRecord(table).getElement(term.field);
			refValues.push(term.value);				
		} else if (term.operator == 'IN' || term.operator == 'NOT IN') {
			refElem = new GlideRecord(table).getElement(term.field);
			refValues = term.value.split('.');
		} else if (term.operator == 'DYNAMIC') {
			refTable = 'sys_filter_option_dynamic';
			refValues.push(term.value);
		}

		if (refElem != null) {
			refTable = refElem.getReferenceTable();
		}

		if (refTable) {
			Array.prototype.push.apply(pairs[refTable] || (pairs[refTable] = []), refValues);
		}
	});

	var result = {
		missing: {},
		present: {}
	};

	for (var checkTable in pairs) {
		pairs[checkTable].forEach(function (sysId) {
			var anyGr = new GlideRecord(checkTable);
			anyGr.addQuery('sys_id', sysId);
			anyGr.setLimit(1);
			anyGr.setWorkflow(false);
			anyGr.query();
			var key = anyGr.hasNext() ? 'present' : 'missing';
			(result[key][checkTable] || (result[key][checkTable] = [])).push(sysId);
		});
	}

	return result;
}

/**
 * returns a encoded query in a processable form
 * e.g. "a=1^bIN2,3^ORc=4" -> [{ ..., field: 'a', operator: '=', value: '1' }, { ..., field: 'b', operator: 'IN', value: '2,3' }, ...]
 * @param {string} table
 * @param {string} query
 * @returns {{endquery: boolean, field: string, operator: string, value: string}[]}
 */
function getQueryStringTerms(table, query) {
	if (!gs.tableExists(table) || !(query || '').trim()) return [];
	var glideQuery = new GlideQueryString(table, query);
	glideQuery.deserialize();

	var terms = glideQuery.getTerms();
	var items = [];
	for (var i = 0, length = terms.size(); i < length; i++) {
		var term = terms.get(i);
		items.push({
			endquery: term.isEndQuery(),
			field: term.getTermField(),
			operator: term.getOperator(),
			value: term.getValue()
		});
	}

	return items;
}