Migrating into CSDM identifying table dependencies

[scott_lemm]

CSDM Migration CI's from Custom/Nonconforming Tables

What if you already have CSDM related data but not in the recommended tables. How do you migrate from a nonconforming use into the CSDM. That process takes several steps but the most important step is identifying any dependencies you may have created on your nonconforming table. 

Attached is a script to use in your environment and instructions on how to run/utilize it. The results of this script will identify referenced dependencies such as Reports, UI Scripts, Business Rules, and other references for a specified table (editable in the script).

The results of this script help identify the level-of-effort (LOE) for migrating nonconforming CI's into the proper table of the CSDM.

Good luck and enjoy.

[RobinR1]

I noticed the term 'fix script' in the documentation.   Can you confirm this script does not update any data or advise if it does?  Thanks!

[Ryan Lee2]

Hi Robin,

If you look in the details of the fix you'll see that its just running print statements.    The idea is to use the script to identify where you'll need to make modifications.     It doesn't change anything.

[andydoyle]

Found this very useful and added a few more tables. It throws an error saying there's no table column on the data policies table about 50% of the time for some reason, works the other 50%.

// User Definable Variables
var fromTable = 'cmdb_ci_service';
var toTable = 'cmdb_ci_service_auto';
var logPrefix = 'TABLE DEPENDANCIES';
// End of user definabel variables

// Check if the new table has all the columns the old tabls has.
var grFromTableColsArray = getCols(fromTable);
var grToTableColsArray = getCols(toTable);
for (var i = 0; i < grFromTableColsArray.length; i++){
	var arrayUtil = new ArrayUtil();
	if (!arrayUtil.contains(grToTableColsArray, grFromTableColsArray[i])){
		gs.info(logPrefix + ': ' + fromTable + '->' + toTable + ' - ' + toTable + ' is missing column ' + grFromTableColsArray[i]);
	}
}

// Check Dependencies
var grBrCondition = new GlideRecord('sys_script');
grBrCondition.addQuery('collection', fromTable);
grBrCondition.query();
while(grBrCondition.next()){
	gs.info(logPrefix + ': ' + fromTable + '->' + toTable + ' - ' + 'Business Rule looking at table: ' + grBrCondition.name + ' ' + grBrCondition.sys_id);
}

var grBrScript = new GlideRecord('sys_script');
grBrScript.addQuery('script', 'CONTAINS',fromTable);
grBrScript.query();
while(grBrScript.next()){
	gs.info(logPrefix + ': ' + fromTable + '->' + toTable + ' - ' + 'Business Rule Running code on table: ' + grBrScript.name + ' ' + grBrScript.sys_id);
}

var grClientScript = new GlideRecord('sys_script_client');
grClientScript.addQuery('script', 'CONTAINS', fromTable);
grClientScript.query();
while(grClientScript.next()){
	gs.info(logPrefix + ': ' + fromTable + '->' + toTable + ' - ' + 'client script looking at table: ' + grClientScript.name + ' ' + grClientScript.sys_id);
}

var grItemOption = new GlideRecord('item_option_new');
grItemOption.addQuery('type' , 8);
grItemOption.addQuery('reference' , fromTable);
grItemOption.query();
while(grItemOption.next()){
	gs.info(logPrefix + ': ' + fromTable + '->' + toTable + ' - ' + 'Item Option: ' + grItemOption.name + ' ' + grItemOption.sys_id);
}

var grScriptInclude = new GlideRecord('sys_script_include');
grScriptInclude.addQuery('script', 'CONTAINS', fromTable);
grScriptInclude.query();
while(grScriptInclude.next()){
	gs.info(logPrefix + ': ' + fromTable + '->' + toTable + ' - ' + 'script include: ' + grScriptInclude.name + ' ' + grScriptInclude.sys_id);
}

var grReport = new GlideRecord('sys_report');
grReport.addQuery('table', fromTable);
grReport.query();
while(grReport.next()){
	gs.info(logPrefix + ': ' + fromTable + '->' + toTable + ' - ' + 'Report: ' + grReport.title + ' ' + grReport.sys_id);
}

var grDictionary = new GlideRecord('sys_dictionary');
grDictionary.addQuery('internal_type', 'reference');
grDictionary.addQuery('reference', fromTable);
grDictionary.query();
while(grDictionary.next()){
	gs.info(logPrefix + ': ' + fromTable + '->' + toTable + ' - ' + 'Field using table: ' + grDictionary.name + ' / ' + grDictionary.column_label + ' ' + grDictionary.sys_id);
}

var grNotification = new GlideRecord('sysevent_email_action');
grNotification.addQuery('conditionLIKE' + fromTable);
grNotification.query();
while(grNotification.next()){
	gs.info(logPrefix + ': ' + fromTable + '->' + toTable + ' - ' + 'Notification using table: ' + grNotification.getDisplayValue() + ' ' + grNotification.sys_id);
}

var grUiActionOnTable = new GlideRecord('sys_ui_action');
grUiActionOnTable.addQuery('conditionLIKE' + fromTable);
grUiActionOnTable.query();
while(grUiActionOnTable.next()){
	gs.info(logPrefix + ': ' + fromTable + '->' + toTable + ' - ' + 'UI Action with condition using table: ' + grUiActionOnTable.getDisplayValue() + ' ' + grUiActionOnTable.sys_id);
}

var grUiActionUsingTable = new GlideRecord('sys_ui_action');
grUiActionUsingTable.addQuery('scriptLIKE' + fromTable);
grUiActionUsingTable.query();
while(grUiActionUsingTable.next()){
	gs.info(logPrefix + ': ' + fromTable + '->' + toTable + ' - ' + 'UI Action with script using table: ' + grUiActionUsingTable.getDisplayValue() + ' ' + grUiActionUsingTable.sys_id);
}

var grUiPolicyOnTable = new GlideRecord('sys_ui_policy');
grUiPolicyOnTable.addQuery('table=' + fromTable);
grUiPolicyOnTable.query();
while(grUiPolicyOnTable.next()){
	gs.info(logPrefix + ': ' + fromTable + '->' + toTable + ' - ' + 'UI Policy on this table: ' + grUiPolicyOnTable.short_description + ' ' + grUiPolicyOnTable.sys_id);
}

var grUiPolicyUsingTable = new GlideRecord('sys_ui_policy');
grUiPolicyUsingTable.addQuery('conditionsLIKE' + fromTable + '^ORscript_trueLIKE' + fromTable + '^ORscript_falseLIKE' + fromTable);
grUiPolicyUsingTable.query();
while(grUiPolicyUsingTable.next()){
	gs.info(logPrefix + ': ' + fromTable + '->' + toTable + ' - ' + 'UI Policy using this table: ' + grUiPolicyUsingTable.getDisplayValue() + ' ' + grUiPolicyUsingTable.sys_id);
}

var grUiPolicyAction = new GlideRecord('sys_ui_policy_action');
grUiPolicyAction.addQuery('table=' + fromTable);
grUiPolicyAction.query();
while(grUiPolicyAction.next()){
	gs.info(logPrefix + ': ' + fromTable + '->' + toTable + ' - ' + 'UI Policy Action using this table: ' + grUiPolicyAction.ui_policy.getDisplayValue() + ' ' + grUiPolicyAction.sys_id);
}

var grDataPolicyOnTable = new GlideRecord('sys_data_policy2');
grDataPolicyOnTable.addQuery('table=' + fromTable);
grDataPolicyOnTable.query();
while(grDataPolicyOnTable.next()){
	gs.info(logPrefix + ': ' + fromTable + '->' + toTable + ' - ' + 'Data Policy on this table: ' + grDataPolicyOnTable.getDisplayValue() + ' ' + grDataPolicyOnTable.sys_id);
}

var grDataPolicyUsingTable = new GlideRecord('sys_data_policy2');
grDataPolicyUsingTable.addQuery('conditionLIKE' + fromTable);
grDataPolicyUsingTable.query();
while(grDataPolicyUsingTable.next()){
	gs.info(logPrefix + ': ' + fromTable + '->' + toTable + ' - ' + 'Data Policy using this table: ' + grDataPolicyUsingTable.getDisplayValue() + ' ' + grDataPolicyUsingTable.sys_id);
}

var grDataPolicyRule = new GlideRecord('sys_data_policy_rule');
grDataPolicyRule.addQuery('table=' + fromTable);
grDataPolicyRule.query();
while(grDataPolicyRule.next()){
	gs.info(logPrefix + ': ' + fromTable + '->' + toTable + ' - ' + 'Data Policy Rule using this table: ' + grDataPolicyRule.getDisplayValue() + ' ' + grDataPolicyRule.sys_id);
}

var grCertificationSchedule = new GlideRecord('cert_schedule');
grCertificationSchedule.addQuery('table=' + fromTable);
grCertificationSchedule.query();
while(grCertificationSchedule.next()){
		gs.info(logPrefix + ': ' + fromTable + '->' + toTable + ' - ' + 'Data Certification Schedule using this table: ' + grCertificationSchedule.getDisplayValue() + ' ' + grCertificationSchedule.sys_id);
}

var grCertificationTemplate = new GlideRecord('cert_template');
grCertificationTemplate.addQuery('table=' + fromTable);
grCertificationTemplate.query();
while(grCertificationTemplate.next()){
		gs.info(logPrefix + ': ' + fromTable + '->' + toTable + ' - ' + 'Certification Template using this table: ' + grCertificationTemplate.getDisplayValue() + ' ' + grCertificationTemplate.sys_id);
}

function getCols(table){
	var gr = new GlideRecord(table);
	gr.query();
	gr.next();
	var fields = gr.getFields();
	var grUtil = new GlideRecordUtil();
	return grUtil.getFields(gr);
}

[bstahl]

Is this script also a Fix Script?

[Cironside]

It can be used as a fix script or just ran in a background script, it just logs info so it doesn't matter where you run it from as long as it's server side

[Mathew Hillyard]

Nice work. As always when trying to crawl through an instance there are places that both the inbuilt Code Search function in Studio and other utilities miss. It's also not only dependencies for moving data but for "in progress" records and related items and artefacts you may ultimately want to remove from the instance - and some of these can be high-impact if overlooked.

Some less commonly-referenced tables include:

• Script Action [sysevent_script_action] - field is [script]
• Data Source [sys_data_source] - field is [data_loader]
• Decision Input [sys_decision_input] - Type [internal_type] is reference and field is [reference]
• Decision Table [sys_decision] - field is [answer_table]
• Fix Script [sys_script_fix] - field is [script]
• Processor [sys_processor] - field is [script]
• Relationship [sys_relationship] - fields are [basic_apply_to] and [basic_apply_from]
• SLA Definition [contract_sla] - field is [collection]
• Task SLA [task_sla] - search for one of the SLA Definitions returned above in field [sla]
• Transform Script [sys_transform_script] - field is [script]
• Values [sys_variable_value] - table is [wf_activity] and field is [value]
• View Table [sys_db_view_table] - field is [table]

[chelsea-a]

Sorry but how do I interpret the output of this script?