Is there a way to find the references for a record using BACKGROUND script?

[Suggy]

Is there a way to find the references for a record using BACKGROUND script?

Scenario - We would get request to delete a group/CI etc. Need to know where and all those records are referenced using a script.

PS -  I have found few posts regarding same topic, but that needs a customization like create a button, script include or app based etc. but I dont want such things as we are allowed to do so. Hence looking for a script to be executed in background script.

[Suggy]

Anyone?

[Bert_c1]

It can be done using a script you run in scripts background.  You need to start with the sys_id of the group record or cmdb_ci record. then query the sys_dictionary table for internal_type = 'reference' and 'reference' = "table name". Collect the table names found. then query those tables using the sys_id value of the group/CI record for the reference field value.

Too much script logic for me to show at the moment.

[Bert_c1]

@Suggy 

The following seems to work when I want to see which records reference the "Hardware" group.

// find all records with references to the Hardware group
var grpID = '287ee6fea9fe198100ada7950d0b1b73';
var grpName = 'Database';
// get list of fields that reference the sys_user_group table
var dict = new GlideRecord('sys_dictionary');
dict.addQuery('internal_type', 'reference');
dict.addQuery('reference', 'sys_user_group');
var tableList = [];
var fieldList = [];
dict.query();
gs.info("FindAllRecords: found " + dict.getRowCount() + " dictionary records.");
while (dict.next()) {
    tableList.push(dict.name.toString());
    fieldList.push(dict.element.toString());
//    gs.info("FindAllRecords: Table = " + dict.name + ", field = " + dict.element);
}

// Now query each table and field for those where the field contains the sys_id of the group table
var i = 0;
while (i < tableList.length) {
//	gs.info("FindAllRecords: checking table: " + tableList[i] + " and field: " + fieldList[i]);
	var queryTable = new GlideRecord(tableList[i]);
	queryTable.addQuery(fieldList[i], grpID);
	queryTable.query();
	while (queryTable.next()) {
		gs.info("FindAllTables: Table: " + tableList[i] + ", record: " + queryTable.sys_id + " references the group");
	}
	i++;
}

However, when I run it there a Application scope errors related to HR tables, and some Java errors. But you do get a list of tables and the record sys_id values that reference the Hardware group.  Ant reference fields in records in tables the script doesn't process with show "empty" for the group Hardware once it is deleted. You may be able to live with that.

[ralvarez]

@Suggy, yes, you can. You can likely take the script from one of the posts you’re referring to and adapt it to run in the background—i.e., without using ‘current’ or requiring confirmation.

Here you have an example adapted from Mark Stanger's script in snguru:

var refRecordID = "XXXXXXXXXXX"; // Replace with the actual Record ID
var table_name = "XXX";          // Replace with the actual table name

var msg = 'Matching tables and columns where this record is referenced (if any) are displayed below...\n';
var refTable = new TableUtils(table_name).getTables();
gs.include("j2js");
refTable = j2js(refTable).join();

var dict = new GlideRecord('sys_dictionary');
dict.addQuery('reference', 'IN', refTable).addOrCondition('internal_type', 'document_id').addOrCondition('internal_type', 'conditions');

// Exclude audit and log fields
dict.addQuery('name', 'DOES NOT CONTAIN', 'var__m_');
dict.addQuery('name', 'DOES NOT CONTAIN', 'ecc_');
dict.addQuery('name', 'DOES NOT CONTAIN', 'ha_');
dict.addQuery('name', 'DOES NOT CONTAIN', 'syslog');
dict.addQuery('name', 'DOES NOT CONTAIN', 'sys_history');
dict.addQuery('name', 'DOES NOT CONTAIN', '_log');
dict.addQuery('name', 'DOES NOT CONTAIN', 'text_search');
dict.addQuery('name', 'DOES NOT CONTAIN', 'ts_');
dict.addQuery('name', 'DOES NOT CONTAIN', 'sys_watermark');
dict.addQuery('name', 'DOES NOT CONTAIN', 'sys_audit');
dict.addQuery('name', 'NOT LIKE', 'v_%');
dict.orderBy('name');
dict.orderBy('element');
dict.query();

while (dict.next()) {
    var tblName = dict.name.toString();
    
    // Skip tables used for Table Rotation
    var gr = new GlideRecord("sys_table_rotation_schedule");
    gr.addQuery("name.name", '!=', tblName);
    gr.addQuery("table_name", tblName);
    gr.query();
    
    if (!gr.hasNext()) {
        var recMessage = ' records found';
        var filterOperator = '=';
        var refType = dict.internal_type;
        if (refType == 'glide_list' || refType == 'conditions') {
            filterOperator = 'LIKE';
        }

        // Query each table for matching records
        var rec = new GlideRecord(tblName);
        if (refType == 'glide_list' || refType == 'conditions') {
            rec.addQuery(dict.element, 'CONTAINS', refRecordID);
        } else {
            rec.addQuery(dict.element, refRecordID);
        }
        rec.query();

        if (rec.getRowCount() == 1) {
            recMessage = ' record found';
        }

        if (rec.getRowCount() > 0) {
            // Generate table/column link
            var link = tblName + '_list.do?sysparm_query=' + dict.element + filterOperator + refRecordID;
            msg += 'Table: ' + tblName + ' - Column: ' + dict.element + ' [' + dict.internal_type + '] --- ' + rec.getRowCount() + recMessage + '\n';
            msg += 'Link: ' + link + '\n\n';
        }
    }
}

// Query for workflow variable values
tblName = 'sys_variable_value';
var vVal = new GlideRecord(tblName);
vVal.addQuery('value', 'CONTAINS', refRecordID);
vVal.query();

if (vVal.getRowCount() == 1) {
    recMessage = ' record found';
}

if (vVal.getRowCount() > 0) {
    // Generate link for sys_variable_value
    var link = tblName + '_list.do?sysparm_query=valueLIKE' + refRecordID;
    msg += 'Table: ' + tblName + ' - Column: value [string] --- ' + vVal.getRowCount() + recMessage + '\n';
    msg += 'Link: ' + link + '\n';
}

gs.print(msg);