One of the great features of Platform Analytics is that it allows any user to create visualizations and dashboards. As users explore the tables in ServiceNow they start to ask questions about the myriad of columns in the task table and the tables extended from it. Unfortunately, I couldn’t find any resource which describes each of the columns in the task table so I wrote a script which updates a knowledge article with an html table of columns, data types and field hints. This script is scheduled to run nightly which gets any new columns or hints added during the day.
Here are the steps I took:
- Create a knowledge article for each table you want to have a data dictionary for (do this in production)
- Create the following system property:
- Name: knowledge.datadictionary.articles
- Type: string
- Value: name of the table : sys_id of the knowledge article you created for it in step 1
{"sn_hr_core_case_talent_management":"b50fcd7c97ffe250d8d4791ad053afa6",
"sn_hr_core_case_total_rewards":"c29f01bc97ffe250d8d4791ad053afc7",
"sn_hr_core_case_workforce_admin":"5cef49bc97ffe250d8d4791ad053af91",
"sn_hr_core_case_payroll":"5620513097332650d8d4791ad053af07",
"sn_hr_le_case":"2db0ddfc97ffe250d8d4791ad053afc4",
"sn_hr_core_case": "06e0917097332650d8d4791ad053af5a",
"sn_hr_core_position":"23315d3097332650d8d4791ad053af78",
"sn_hr_core_case_operations":"8e5899bc97332650d8d4791ad053afc3"}3. Create the following script include:
var KnowledgeDataDictionaryUtils = Class.create();
KnowledgeDataDictionaryUtils.prototype = {
initialize: function() {},
updateArticleForTable: function(tableName, articleSysId) {
//master method for updating data dictionary knowledge articles
//tableName = technical name of table you want to update the dictionary for
//articleSysId = sys_id of kb_knowledge record to be updated.
var tableGR = new GlideRecord("sys_db_object");
tableGR.addQuery('name', tableName);
tableGR.setLimit(1);
tableGR.query();
if (tableGR.next()) {
var tableNamesArr = [];
tableNamesArr.push(tableGR.getValue('name'));
//if the table is extended, go get the other tables it comes from
if (tableGR.getValue('super_class')) {
this.getExtendedTableName(tableGR.getValue('super_class'), tableNamesArr);
}
//create the html for the kb article
var html = '';
html += '<p><strong>' + this.getSpan() + tableGR.label.getDisplayValue() + ' (' + tableName + ')</strong></p>';
html += this.getTableCommentFromDictionary(tableGR.getValue('name')) || '';
html += '<p><em>Note that this article is updated automatically from the Update Data Dictionary Knowledge Articles scheduled job. Any manual changes will be overwritten.</em></p>';
html += this.getTableColumns(tableNamesArr);
html += '</span>';
this.updateArticle(articleSysId, html);
}
},
getExtendedTableName: function(tableSysId, tableNamesArr) {
//recurive query to get names of tables a given table is extended from
//tableSysId = sys_id (from sys_db_object) of table to check for extended tables
//tableNamesArr = array of table names to store the extended table names in
var extTableGR = new GlideRecord("sys_db_object");
extTableGR.get(tableSysId);
tableNamesArr.push(extTableGR.getValue('name'));
//recursively call the function to get further extended tables
if (extTableGR.getValue('super_class')) {
this.getExtendedTableName(extTableGR.getValue('super_class'), tableNamesArr);
}
return tableNamesArr;
},
getTableCommentFromDictionary: function(tableName) {
//gets the comments for a given table from the collection record
var dictGR = new GlideRecord('sys_dictionary');
dictGR.addQuery('internal_type', 'collection'); //collection
dictGR.addQuery('name', tableName);
dictGR.setLimit(1);
dictGR.query();
if (dictGR.next()) {
return dictGR.getDisplayValue('comments');
}
},
getTableColumns: function(tableNamesArr) {
//returns an html table of columns and hints for the tables in tableNamesArr array
var tableHtml = '<table style="border-collapse: collapse; border-width: 1px;" border="1 solid black" ><tr>';
tableHtml += '<th>' + this.getSpan() + 'Field Name</th>';
tableHtml += '<th>' + this.getSpan() + 'Field Type</th>';
tableHtml += '<th>' + this.getSpan() + 'Field Hint</th></tr>';
var dictGR = new GlideRecord('sys_dictionary');
dictGR.addEncodedQuery('internal_type!=collection'); //collection
dictGR.addQuery('name', 'IN', tableNamesArr);
dictGR.addActiveQuery();
dictGR.orderBy('column_label');
dictGR.query();
while (dictGR.next()) {
tableHtml += '<tr>';
tableHtml += '<td>' + this.getSpan() + dictGR.column_label.getDisplayValue() + '</td>';
tableHtml += '<td>' + this.getSpan() + dictGR.internal_type.getDisplayValue() + '</td>';
tableHtml += '<td>' + this.getSpan() + this.getFieldHint(dictGR.getValue('name'), dictGR.getValue('element')) + '</td>';
tableHtml += '</tr>';
}
tableHtml += '</table>';
return tableHtml;
},
getSpan: function() {
//used for formatting
return '<span style="font-family: tahoma, arial, helvetica, sans-serif; font-size: 14pt;">';
},
getFieldHint: function(tableName, columnName) {
//gets the field hint for a given column from sys_documentation
var docGR = new GlideRecord('sys_documentation');
docGR.addQuery('name', tableName);
docGR.addQuery('element', columnName);
docGR.addQuery('language', 'en');
docGR.setLimit(1);
docGR.query();
if (docGR.next()) {
return docGR.hint.getDisplayValue();
}
},
updateArticle: function(articleSysId, html) {
//Updates the text of a kb article
var kbGR = new GlideRecord('kb_knowledge');
kbGR.get(articleSysId);
kbGR.setValue('text', html);
kbGR.update();
},
type: 'KnowledgeDataDictionaryUtils'
};4. Create a scheduled script (sysauto_script)
//Updates knowledge articles containing list of fields and hints for each table named in the system property
var articlesObj = JSON.parse(gs.getProperty('knowledge.datadictionary.articles'));
for (var i in articlesObj){
new KnowledgeDataDictionaryUtils().updateArticleForTable(i,articlesObj[i]);
}
After execution, the knowledge articles you created in step 1 should look like the below
