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