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!
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.
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.
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:
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);
}
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
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.
Yep, that was it. Thanks.
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?
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.
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
Excellent! Thank you!
Earl
No problem, I’m glad it helped!
You saved me a boatload of time. Thanks!
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.
Obviously adding the line rec.setWorkflow(false); would help avoid query business rules.
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
Awesome, thanks for the update! I’ve adjusted the solution above with this fix.
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.
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.
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
Thanks Christian, I haven’t heard of that happening before but I’ll keep an eye on it.
Super helpful.
Nit pick, but add semi-colon to:
Condition: gs.hasRole(‘admin’)
and
Client: true
Great post.
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;
}