Find all System References to a Specific Record

//Find all System References to a Specific Record

Find all System References to a Specific Record

A

t Crossfuze, one of the areas we specialize in is helping struggling ServiceNow implementations get back on the right track. One type of issue that we encounter frequently is bad or redundant data that’s being used and needs to be deleted, de-activated, or cleaned up in some way. The best way to handle this issue is to keep it out of your system in the first place, but what do you do if it has been there for months or years and has been referenced in who knows how many places and ways? The options vary depending on the situation, but a common component of any potential solution is finding out just how much of a problem you’ve really got. How do you decide to replace or modify the bad data if you don’t even understand where or how that bad data is being used?

To help answer this question, we recently created a really useful admin utility to find all places where a record is referenced. In this article I’ll show you how you can set it up in your instance!

Find Record References UI Action

The script to produce the kind of data you need in this case could be run from a variety of places. We chose to make it a simple global UI action so that it would be easy to access and use on any record in the system. The UI action works by first querying the system dictionary for all reference, document_id, and condition fields that reference the table you initiate the action from. It filters out unnecessary system and log tables. Then it iterates through all of the remaining records, performing a table/sys_id query on each table where a match exists. The query results are then output to the browser in an information message.

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.

You can set the UI action up in your instance by creating a new UI action with the following settings. Once set up, you’ll have a ‘Find Record References’ link at the bottom of each form in your system.

‘Find Record References’ UI Action
Name: Find Record References
Table: Global
Order: 500
Action name: find_references
Show insert/Show update: False/True
Form link: True
Client: True
Hint: Find and display all tables and records that reference this record
OnClick: confirmFindReferences()
Condition: gs.hasRole('admin')
Script:

//Client-side 'onclick' function
function 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' function
    gsftSubmit(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 errors
if (typeof window == 'undefined')
    findReferences();

//Server-side function
function findReferences() {
    var msg = '<b>Matching tables and columns where this record is referenced (if any) are displayed below...</b><br/>';
    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 fields
    var 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 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.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) {
                //Display table/column info
                msg = 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>.<br/>' + '';
            }
        }
    }

    //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) {
        //Display table/column info
        msg = 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);
}
By | 2018-07-09T14:59:53+00:00 July 29th, 2013|Categories: System Definition|Tags: , |19 Comments

About the Author:

Mark has worked in the IT industry since 2002 and with ServiceNow since 2007. He is the founder and creator of SN | Guru and the co-founder of Crossfuze, one of the worlds leading ServiceNow consulting partners. Prior to co-founding Crossfuze, he worked for ServiceNow as a Senior Architect on the Professional Services team. He has personally led dozens of successful implementations encompassing every part of the ServiceNow platform. He is also responsible for designing and developing groundbreaking ServiceNow solutions and best practices in the form of various applications, turnkey solutions, and integrations during his tenure at ServiceNow, Crossfuze and, of course, SN | Guru. These solutions are used today by ServiceNow administrators and consultants alike in hundreds of ServiceNow instances around the world!

19 Comments

  1. Jim Uomini July 29, 2013 at 11:28 am - Reply

    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 Stanger July 29, 2013 at 1:25 pm - Reply

      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.

  2. Joel Maxwell September 16, 2013 at 2:26 pm - Reply

    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?

    • Mark Stanger September 16, 2013 at 9:23 pm - Reply

      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 Maxwell September 17, 2013 at 9:16 am - Reply

        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 Lewis November 6, 2013 at 8:27 am - Reply

    Excellent! Thank you!

    Earl

  4. Dan Schaefer April 10, 2015 at 8:27 am - Reply

    You saved me a boatload of time. Thanks!

  5. James Fricker November 26, 2015 at 11:42 pm - Reply

    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 Fricker November 29, 2015 at 6:35 pm - Reply

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

  6. Markus Schär February 29, 2016 at 7:55 am - Reply

    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 Stanger February 29, 2016 at 9:27 am - Reply

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

  7. Peteris B March 21, 2017 at 2:17 am - Reply

    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 Stanger March 21, 2017 at 4:26 am - Reply

      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 Probst April 19, 2017 at 7:29 am - Reply

    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 Stanger April 19, 2017 at 7:43 am - Reply

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

  9. Barry Jones May 17, 2017 at 3:49 pm - Reply

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

    Great post.

  10. Chris Bui July 29, 2017 at 11:45 am - Reply

    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;
    }

Leave A Comment