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?

[Geri1]

I took all the suggestions in this thread and put them together into one script. @andydoyle I found the problem with the Data Policies table - the field name is model_table. Copy/paste the code below into a fix script, add the name of the table in the first line, save, and run it. It prints the output in a modal window - I copy it into excel to make it easier to read.  Not fancy, but it fills a need.

var SpecTable = 'put your table name here';

var gr = new GlideRecord('sys_script');

gr.addQuery('collection', SpecTable);

gr.query();

while(gr.next()){

   gs.print('Business Rule looking at ' + SpecTable + ': ' + gr.name + ' ' + gr.sys_id);

}

var gr2 = new GlideRecord('sys_script');

gr2.addQuery('script', 'CONTAINS',SpecTable);

gr2.query();

while(gr2.next()){

   gs.print('Business Rule Running code on ' + SpecTable + ': ' + gr2.name + ' ' + gr2.sys_id);

}

var gr3 = new GlideRecord('sys_script_client');

gr3.addQuery('script', 'CONTAINS', SpecTable);

gr3.query();

while(gr3.next()){

   gs.print('client script looking at ' + SpecTable + ': ' + gr3.name + ' ' + gr3.sys_id);

}

var gr4 = new GlideRecord('item_option_new');

gr4.addQuery('type' , 8);

gr4.addQuery('reference' , SpecTable);

gr4.query();

while(gr4.next()){

   gs.print('Reference (item_option_new) to ' + SpecTable + ': ' + gr4.name + ' ' + gr4.sys_id);

}

var gr5 = new GlideRecord('sys_script_include');

gr5.addQuery('script', 'CONTAINS', SpecTable);

gr5.query();

while(gr5.next()){

   gs.print('script include contains ' + SpecTable + ': ' + gr5.name + ' ' + gr5.sys_id);

}

var gr7 = new GlideRecord('sys_report');

gr7.addQuery('table', SpecTable);

gr7.query();

while(gr7.next()){

   gs.print('Report uses ' + SpecTable + ': ' + gr7.title + ' ' + gr7.sys_id);

}

var gr8 = new GlideRecord('sys_dictionary');

gr8.addQuery('internal_type', 'reference');

gr8.addQuery('reference', SpecTable);

gr8.query();

while(gr8.next()){

   gs.print('Field (dictionary) using ' + SpecTable + ': ' + gr8.column_label + ' ' + gr8.sys_id);

}

var gr9 = new GlideRecord('sysevent_email_action');

gr9.addQuery('conditionLIKE'+ SpecTable);

gr9.query();

while(gr9.next()){

   gs.print('Notification using ' + SpecTable + ': ' + gr9.getDisplayValue() + ' ' + gr9.sys_id);

}

var gr10 = new GlideRecord('sys_ui_action');

gr10.addQuery('conditionLIKE' + SpecTable);

gr10.query();

while(gr10.next()){

   gs.print('UI action with condition using ' + SpecTable + ': ' + gr10.getDisplayValue() + ' ' + gr10.sys_id);

}

var gr11 = new GlideRecord('sys_ui_action');

gr11.addQuery('scriptLIKE' + SpecTable);

gr11.query();

while(gr11.next()){

   gs.print('UI action with script using ' + SpecTable + ': ' + gr11.getDisplayValue() + ' ' + gr11.sys_id);

}

var gr12 = new GlideRecord('sys_ui_policy');

gr12.addQuery('table=' + SpecTable);

gr12.query();

while(gr12.next()){

   gs.print('UI policy on ' + SpecTable + ': ' + gr12.short_description + ' ' + gr12.sys_id);

}

var gr13 = new GlideRecord('sys_ui_policy');

gr13.addQuery('conditionsLIKE' + SpecTable + '^ORscript_trueLIKE' + SpecTable + '^ORscript_falseLIKE' + SpecTable);

gr13.query();

while(gr13.next()){

   gs.print('UI policy using ' + SpecTable + ': ' + gr13.getDisplayValue() + ' ' + gr13.sys_id);

}

var gr14 = new GlideRecord('sys_ui_policy_action');

gr14.addQuery('table=' + SpecTable);

gr14.query();

while(gr14.next()){

   gs.print('UI policy Action using ' + SpecTable + ': ' + gr14.getDisplayValue() + ' ' + gr14.sys_id);

}

var gr15 = new GlideRecord('sys_data_policy2');

gr15.addQuery('model_table=' + SpecTable);

gr15.query();

while(gr15.next()){

   gs.print('Data Policy on ' + SpecTable + ': ' + gr15.getDisplayValue() + ' ' + gr15.sys_id);

}

var gr16 = new GlideRecord('sys_data_policy2');

gr16.addQuery('conditionsLIKE' + SpecTable);

gr16.query();

while(gr16.next()){

   gs.print('Data Policy using ' + SpecTable + ': ' + gr16.getDisplayValue() + ' ' + gr16.sys_id);

}

var gr17 = new GlideRecord('sys_data_policy_rule');

gr17.addQuery('table=' + SpecTable);

gr17.query();

while(gr17.next()){

   gs.print('Data Policy Rule using ' + SpecTable + ': ' + gr17.getDisplayValue() + ' ' + gr17.sys_id);

}

var gr18 = new GlideRecord('cert_schedule');

gr18.addQuery('table=' + SpecTable);

gr18.query();

while(gr18.next()){

   gs.print('Data Certification Schedule using ' + SpecTable + ': ' + gr18.getDisplayValue() + ' ' + gr18.sys_id);

}

var gr19 = new GlideRecord('cert_template');

gr19.addQuery('table=' + SpecTable);

gr19.query();

while(gr19.next()){

   gs.print('Certification Template using ' + SpecTable + ': ' + gr19.getDisplayValue() + ' ' + gr19.sys_id);

}

var gr20 = new GlideRecord('sysevent_script_action');

gr20.addQuery('script', 'CONTAINS', SpecTable);

gr20.query();

while(gr20.next()){

   gs.print('Script action contains ' + SpecTable + ': ' + gr20.getDisplayValue() + ' ' + gr20.sys_id);

}

var gr21 = new GlideRecord('sys_data_source');

gr21.addQuery('data_loader', SpecTable);

gr21.query();

while(gr21.next()){

   gs.print('Data Source is ' + SpecTable + ': ' + gr21.getDisplayValue() + ' ' + gr21.sys_id);

}

var gr22 = new GlideRecord('sys_decision_input');

gr22.addQuery('internal_type', 'reference');

gr22.addQuery('reference', SpecTable);

gr22.query();

while(gr22.next()){

   gs.print('Decision input is ' + SpecTable + ': ' + gr22.getDisplayValue() + ' ' + gr22.sys_id);

}

var gr23 = new GlideRecord('sys_decision');

gr23.addQuery('answer_table', SpecTable);

gr23.query();

while(gr23.next()){

   gs.print('Decision table answer table is ' + SpecTable + ': ' + gr23.getDisplayValue() + ' ' + gr23.sys_id);

}

var gr24 = new GlideRecord('sys_script_fix');

gr24.addQuery('script', 'CONTAINS', SpecTable);

gr24.query();

while(gr24.next()){

   gs.print('Fix script contains ' + SpecTable + ': ' + gr24.getDisplayValue() + ' ' + gr24.sys_id);

}

var gr25 = new GlideRecord('sys_processor');

gr25.addQuery('script', 'CONTAINS', SpecTable);

gr25.query();

while(gr25.next()){

   gs.print('Processor script contains ' + SpecTable + ': ' + gr25.getDisplayValue() + ' ' + gr25.sys_id);

}

var gr26 = new GlideRecord('sys_relationship');

gr26.addQuery('basic_apply_toLIKE' + SpecTable + '^ORbasic_query_fromLIKE' + SpecTable);

gr26.query();

while(gr26.next()){

   gs.print('Relationship using ' + SpecTable + ': ' + gr26.getDisplayValue() + ' ' + gr26.sys_id);

}

var gr27 = new GlideRecord('contract_sla');

gr27.addQuery('collection', 'CONTAINS', SpecTable);

gr27.query();

while(gr27.next()){

   gs.print('SLA collection contains ' + SpecTable + ': ' + gr27.getDisplayValue() + ' ' + gr27.sys_id);

}

var gr28 = new GlideRecord('sys_transform_script');

gr28.addQuery('script', 'CONTAINS', SpecTable);

gr28.query();

while(gr28.next()){

   gs.print('Transform script contains ' + SpecTable + ': ' + gr28.getDisplayValue() + ' ' + gr28.sys_id);

}

var gr29 = new GlideRecord('wf_activity_variable');

gr29.addQuery('choice_table', SpecTable);

gr29.query();

while(gr29.next()){

   gs.print('Activity Variable using ' + SpecTable + ': ' + gr29.label + ' ' + gr29.sys_id);

}