Find all references to an existing record in ServiceNow
SummaryPermalink
Sometimes you need to know “which records are pointing to this record that I am about to work with”. For example a core_company record that you want to consolidate or you want to know if any active records are pointing to an aged out group. The servicenowguru.com blog has a good interactive solution but I needed to generate a very large list.
SolutionPermalink
This background script will find all referencing records to a specified table/sys_id combination and save the results to a CSV in the sys_data_source table.
This file contains hidden or bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
| /* | |
| 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); | |
| } |
Sample result of all records referencing the core_company “Microsoft” record.
Leave a comment