Find all references to an existing record in ServiceNow

findrecords.js

/* 
Borrowing from https://servicenowguru.com/system-definition/find-references-specific-record/
This background script finds all references to an existing record, e.g., a company, or group
and exports them to a CSV saved in the sys_data_source table.
*/

var table = 'core_company';
var rec_sys_id = '0e8b8e650a0a0b3b004f285ffbb1a4fc';

var references = findReferencesToCoreRecord(table, rec_sys_id);
// gs.info(references);

var csvData = jsonToCsv(references);
// gs.info(csvData);

// Finally, export data to a data source where we can retrieve
saveToDataSource(table, rec_sys_id, csvData);

function findReferencesToCoreRecord(table, sys_id) {
    var TableGR = new GlideRecord('sys_dictionary');
    TableGR.addQuery('internal_type', 'reference');
    TableGR.addQuery('reference', table);
    // Do not query audit and log fields. If any tables return an invalid table name, add them to this list.
    var excludedTables = ['var__m_', 'cmdb_ci_pcf_component', 'ecc_', 'ha_', 'syslog', 'sys_history', '_log', 'text_search', 'ts_', 'sys_watermark', 'sys_audit'];
    excludedTables.forEach(function (excludedTable) {
        TableGR.addQuery('name', 'DOES NOT CONTAIN', excludedTable);
    });
    TableGR.query();

    var resultArray = [];

    while (TableGR.next()) {
        var resultObj = {
            table: TableGR.getDisplayValue('name'),
            field: TableGR.getDisplayValue('element')
        };
        resultArray.push(resultObj);
    }

    // Loop through each result and query
    var referenceArray = [];
    resultArray.forEach(function (resultItem) {
        var rec = new GlideRecord(resultItem.table);
        rec.addQuery(resultItem.field, sys_id);
        rec.query();

        while (rec.next()) {
            //only return CMDB results that match the CI class to avoid duplicates
            if ((rec.sys_class_name == resultItem.table) || (!resultItem.table.startsWith('cmdb') ) ) {
            var resultObj = {
                table: resultItem.table,
                field: resultItem.field,
                display: rec.getDisplayValue(),
                sys_id: rec.getValue('sys_id')
            };
            referenceArray.push(resultObj);
            }
        }
    });

    return referenceArray;
}

function jsonToCsv(jsonData) {
    var csv = '';

    // Extract headers
    var headers = Object.keys(jsonData[0]);
    csv += headers.join(',') + '\n';

    // Extract data
    jsonData.forEach(function (dataItem) {
        var row = headers.map(function (header) {
            return dataItem[header];
        });
        csv += row.join(',') + '\n';
    });

    return csv;
}

function saveToDataSource(table, rec_sys_id, csvContent) {
    // Write CSV content to a data source
    var file = new GlideRecord('sys_data_source');
    file.initialize();
    file.file_name = table + '_export' + '.csv';
    file.import_set_table_name = 'u_' + table + 'reference_export';
    file.name = 'u_' + table + 'reference_export';
    file.type = 'File';
    file.format = 'CSV';
    file.insert();

    var attachmentGr = new GlideSysAttachment();
    var sysId = attachmentGr.write(file, table + '_' + rec_sys_id + '_references.csv', 'csv', csvContent);

    gs.info('Export to CSV completed. Results saved to sys_data_source table named ' + file.name);
}