Find all System References to a Specific Record - ServiceNow Guru (2024)

Please note that depending on the record referenced, this script can end up doing a LOT of querying. Make sure you run this in your development or test system first, and be aware that the results may take a while to come back depending on the specific record.

‘Find Record References’ UI Action
Name: Find Record References
Table: Global
Order: 500
Action name:

Show insert/Show update: False/True
Form link: True
Client: True
Hint: Find and display all tables and records that reference this record
OnClick:

Condition:

Script:

//Client-side 'onclick' functionfunction confirmFindReferences() {if (confirm('Performing this action will query multiple tables and records and may take a long time to complete. Are you sure you want to continue?') == false) {return false; //Abort submission}//Call the UI Action and skip the 'onclick' functiongsftSubmit(null, g_form.getFormElement(), 'find_references'); //MUST call the 'Action name' set in this UI Action}//Code that runs without 'onclick'//Ensure call to server-side function with no browser errorsif (typeof window == 'undefined')findReferences();//Server-side functionfunction findReferences() {var msg = '<b>Matching tables and columns where this record is referenced (if any) are displayed below...</b>';var refTable = new TableUtils(current.getTableName()).getTables();gs.include("j2js");refTable = j2js(refTable).join();var refRecordID = current.sys_id;//Query dictionary table for reference, document_id, and condition fieldsvar dict = new GlideRecord('sys_dictionary');dict.addQuery('reference', 'IN', refTable).addOrCondition('internal_type', 'document_id').addOrCondition('internal_type', 'conditions');//Do not query audit and log fieldsdict.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.orderBy('name');dict.orderBy('element');dict.query();while (dict.next()) {var tblName = dict.name.toString();// Skip tables used for Table Rotationvar 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 recordsvar 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) {//Display table/column infomsg = msg + '<b>Table: </b><i>' + tblName + '</i>' + ' - <b>Column [Column type]: </b><i>' + dict.element + '</i> [' + dict.internal_type + ']' + ' --- ' + '<span style="color: #136fb0;">' + '<a href="' + dict.name + '_list.do?sysparm_query=' + dict.element + filterOperator + refRecordID + '" target="_blank" rel="noopener">' + rec.getRowCount() + recMessage + '</a></span>.' + '';}}}//Query for workflow variable valuestblName = '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) {//Display table/column infomsg = msg + '<b>Table: </b><i>' + tblName + '</i>' + ' - <b>Column [Column type]: </b><i>' + 'value' + '</i> [' + 'string' + ']' + ' --- ' + '<span style="color: #136fb0;">' + '<a href="' + tblName + '_list.do?sysparm_query=' + 'valueLIKE' + refRecordID + '" target="_blank" rel="noopener">' + vVal.getRowCount() + recMessage + '</a></span>' + '.' + '';}gs.addInfoMessage(msg);action.setRedirectURL(current);}

20 Comments

1. 

   Jim UominiJuly 29, 2013 at 11:28 am

   This is really nice Mark. Could be very helpful.

   I got a syntax error in the editor on the condition until I changed the quotes around admin to double quotes. Both should work, shouldn’t they?

   Thanks, Jim

   • 

     Mark StangerJuly 29, 2013 at 1:25 pm

     It’s probably just a copy/paste issue. Both will work just fine, but single quotes sometimes get messed up when you copy and paste from another website. If you manually re-type the single quotes back in they should work just fine as well.

     • 

       Jim UominiJuly 29, 2013 at 1:29 pm

       Yep, that was it. Thanks.

2. 

   Joel MaxwellSeptember 16, 2013 at 2:26 pm

   I tried the script and it kept thinking that the “recMessage out of scope”. I removed these variables and the script ran. With that said, it appears as though it does find matches for a given incident in the Metrics table but doesn’t show records that the “Related Records” tab shows as this shows links for Affected CI’s, TimeWorked, SLA’s, and group approvals. So that said, it doesn’t appear like the script is taking relationships into consideration?

   See Also

   Omied Badr on LinkedIn: does anybody know where I can get a reference code for the servicenow next…GlideSystem - getMessage(String messageID, Object args) - Product Documentation: Washington DCSenior ServiceNow Consultant Resume Dallas, TX - Hire IT PeopleSolution Sales Executive - Employee Workflow

   • 

     Mark StangerSeptember 16, 2013 at 9:23 pm

     Looks like it wasn’t accounting for the table hierarchy in the case of extended tables. I’ve updated the script above. Give it a try and let me know how it goes.

     • 

       Joel MaxwellSeptember 17, 2013 at 9:16 am

       Mark,

       Great, thanks Mark! I was actually working with another Sys Admin here (Don McMullen) on this last night and we found an additional solution in that we added to the OrCondition for the Reference type. This allowed us to pinpoint what tables we were finding relationships to: “.addOrCondition(‘internal_type’,’reference’);”.

       Thanks again,
       Joel

3. 

   Earl LewisNovember 6, 2013 at 8:27 am

   Excellent! Thank you!

   Earl

   • 

     Mark StangerNovember 6, 2013 at 9:03 am

     No problem, I’m glad it helped!

4. 

   Dan SchaeferApril 10, 2015 at 8:27 am

   You saved me a boatload of time. Thanks!

5. 

   James FrickerNovember 26, 2015 at 11:42 pm

   Beware of tables with query business rules. These may impact the query count. You may end up with a getRowCount of zero even when a record with a reference to the target record does exist.
   Most query business rules include a condition of gs.isInteractive() or gs.getSession().isInteractive() and for this UI action that will be true, and so may affect the result set. You might not see something that is really there.

   • 

     James FrickerNovember 29, 2015 at 6:35 pm

     Obviously adding the line rec.setWorkflow(false); would help avoid query business rules.

6. 

   Markus SchärFebruary 29, 2016 at 7:55 am

   This is an excellent script and we are still using it frequently.

   In Fuji, one thing I noticed though is that addEncodedQuery in combination with addOrCondition is not working as expected anymore. The ^OR conditions seem to be ignored. One way to fix this issue is to modify line 24 (replace addEncodedQuery by addQuery):

   dict.addQuery(‘reference’, ‘IN’, refTable).addOrCondition(‘internal_type’, ‘document_id’).addOrCondition(‘internal_type’, ‘conditions’);

   // Test with Fuji Patch 10
   var dict = new GlideRecord(‘sys_dictionary’);
   dict.addEncodedQuery(‘referenceIN’ + ‘core_company’).addOrCondition(‘internal_type’, ‘document_id’).addOrCondition(‘internal_type’, ‘conditions’);
   gs.log(dict.getEncodedQuery()); // *** Script: referenceINcore_company

   var dict = new GlideRecord(‘sys_dictionary’);
   dict.addQuery(‘reference’, ‘IN’, ‘core_company’).addOrCondition(‘internal_type’, ‘document_id’).addOrCondition(‘internal_type’, ‘conditions’);
   gs.log(dict.getEncodedQuery()); // *** Script: referenceINcore_company^ORinternal_type=document_id^ORinternal_type=conditions

   • 

     Mark StangerFebruary 29, 2016 at 9:27 am

     See Also

     What is ServiceNow? A complete Guide on how it works? - MLE Systems

     Awesome, thanks for the update! I’ve adjusted the solution above with this fix.

7. 

   Peteris BMarch 21, 2017 at 2:17 am

   Not sure if this is related only to Helsinki release, but the “audit and log fields” exclusion list, in my case, had to be populated with “dl_matcher” and “grc_risk”.

   dict.addQuery(‘name’, ‘DOES NOT CONTAIN’, ‘dl_matcher’);
   dict.addQuery(‘name’, ‘DOES NOT CONTAIN’, ‘grc_risk’);

   This is in case for the find record reference script to work when attempting to execute the action on Users table.

   I believe its due to the base tables not being accessible for the admin user. The missing table error is also posted to ServiceNow logs.

   • 

     Mark StangerMarch 21, 2017 at 4:26 am

     Thanks for the heads up! I haven’t seen this before so I’m not sure it applies everywhere but this will be good to have here just in case someone else encounters the issue.

8. 

   Christian ProbstApril 19, 2017 at 7:29 am

   Hi,
   when running against an Incident record we got an error message “Rule entry under cmdb_ci_endpoint_storf identifier using non-existent field is ignored during identification!”. I don;t how that ci class is relevant for the incident so no issue at this point, just want to let you guys know :-)
   Cheers, Christian

   • 

     Mark StangerApril 19, 2017 at 7:43 am

     Thanks Christian, I haven’t heard of that happening before but I’ll keep an eye on it.

9. 

   Barry JonesMay 17, 2017 at 3:49 pm

   Super helpful.
   Nit pick, but add semi-colon to:
   Condition: gs.hasRole(‘admin’)
   and
   Client: true

   Great post.

10. 

    Chris BuiJuly 29, 2017 at 11:45 am

    Hi Mark!

    Just a heads up, Jakarta seems to handle line breaks in gs.addInfoMessage differently. I put a br tag at the end of the msg lines and it fixed the problem.

    Also, we rely a lot on this functionality, especially when it comes to assignment groups. If someone asks to rename a group we use this to see what catalog items, assignment rules, scripts, etc. that will be affected. Right or wrong, there’s a lot of .setDisplayValue() going on here and it needs to be maintained.

    To solve this, I created a function to check for named references if we’re checking a group. You can easily add/remove areas you need to check for named references. It’s pretty handy so I wanted to share. Added the following code before your gs.addInfoMessage(msg) at the end.

    //Query for name references if it’s a group
    if(current.getTableName() == ‘sys_user_group’){
    var nameMsg = ‘Matching SCRIPT fields (Assignment Rules, Record Producers, Client Scripts(client, catalog, wizard), and Workflows) where this record is referenced by NAME are displayed below…‘;

    nameMsg += findTableReferences(‘sysrule_assignment’, ‘script’, current.name);
    nameMsg += findTableReferences(‘sc_cat_item_producer’, ‘script’, current.name);
    nameMsg += findTableReferences(‘sys_script_client’, ‘script’, current.name);
    nameMsg += findTableReferences(‘catalog_script_client’, ‘script’, current.name);
    nameMsg += findTableReferences(‘expert_script_client’, ‘script’, current.name);
    nameMsg += findTableReferences(‘sys_script_include’, ‘script’, current.name);
    nameMsg += findTableReferences(‘u_workflow_script’, ‘val_value’, current.name);
    gs.addErrorMessage(nameMsg);
    }

    And here’s the function…

    function findTableReferences(tblName, column, name){
    var message = ”;
    var recMsg = ‘ records found’;
    var workflowURLQuery = ”;
    var prefix = ‘Table’;

    var gr = new GlideRecord(tblName);
    gr.addQuery(column, ‘CONTAINS’, name);

    //for the client script table, only return client scripts and not catalog or wizard
    if(tblName == ‘sys_script_client’)
    gr.addQuery(‘sys_class_name’, ‘sys_script_client’);

    if(tblName == ‘u_workflow_script’){
    gr.addQuery(‘wfa_workflow_version.published’, true);
    workflowURLQuery = ‘wfa_workflow_version.published=true^’;
    prefix = ‘Workflow Activity’;
    }
    gr.query();

    if(gr.getRowCount() == 1){
    recMsg = ‘ record found’;
    }

    if(gr.getRowCount() > 0){
    message = ‘–‘ + prefix + ‘: ‘ + tblName + ‘‘ + ‘ – Column [Column type]: ‘ + column + ‘ [‘ + ‘string’ + ‘]’ + ‘ — ‘ + ” + ‘‘ + gr.getRowCount() + recMsg + ‘‘ + ‘.’ + ”;
    }else{
    message = ‘–‘ + prefix + ‘: ‘ + tblName + ‘‘ + ‘ – Column: ‘ + column + ‘‘ + ‘ — ‘ + ‘NOTHING FOUND’;
    }

    return message;
    }

11. 

    Randall KingMay 12, 2023 at 5:44 pm

    This was very helpful!!!