SN_move_data_between_instance.js

/*
https://www.servicenow.com/community/developer-articles/migrate-data-from-one-instance-to-another/ta-p/2303331
Migrate Data from one instance to another

https://www.linkedin.com/in/raymond-estevao-31722619/
Raymond Estevao

*/

var ThrottleLimit = 50; //2 sec

function pausecomp(ms)
{
	ms += new Date().getTime();
	while (new Date() < ms)
	{}
}


//manage offsets and limits to ensure no corruption of calls with rest api
for (var count = 0 ; count <= 300 ; count++)
{
	caller(100,100*count);
	pausecomp(500);
}



function caller(limit,offset)
{
	var restEndpoint = "https://XXXXX.service-now.com/api/now/table/incident?sysparm_query=&sysparm_display_value=false&sysparm_exclude_reference_link=true&sysparm_suppress_pagination_header=true&sysparm_limit=" + limit + "&sysparm_offset=" + offset;

	puller(restEndpoint);	
}

function puller(restEndpoint)
{
	// neets update to transform table to
	var oldTableName = restEndpoint.split("?")[0].split("/")[6];
	var instanceRestName = restEndpoint.split("?")[0].split("/")[2].split(".")[0];

	
	var r = new sn_ws.RESTMessageV2();
	r.setEndpoint(restEndpoint);
	r.setHttpMethod('GET');
	r.setRequestHeader('Content-Type', 'application/json');
	r.setAuthenticationProfile('basic', "c8ac221adb1209900034150505961988");  //sys_auth_profile_basic.do?sys_id=c8ac221adb1209900034150505961988  //!!!! DATE TIME OF AUTH USER MUST BEEN SET TO GMT
// refers to the account on the target instance 			

	try
	{
		var response = r.execute();	
	}
	catch(e)
	{
		gs.log("ERROR: Sending REST API " + e + "\n\n" + restEndpoint, "DATA_MIGRATION_LOG");
	}

	if (!response.haveError())
	{
		var parsed = JSON.parse(response.getBody());
		var results = parsed.result;

		//for (var x = 0; x < results.length ; x++ ) // loop over json results

		results.forEach(function(item) //loops all fields in results
		{
			//SET TABLE
			var gr = new GlideRecord(oldTableName);
			gr.initialize();

			for (var key in item)  //Default sets all the fields that exist in the table. Custom fields that dont match are SKIPED
			{	
				if(key == "sys_id") gr.setNewGuidValue(item.sys_id);
				if(key != "sys_tags" && key != "sys_class_name" )gr.setValue(key, item[key]); //if(key == "sys_created_on" || key == "sys_updated_on")//spent tooooo long on this. 
			}

			//TRANSFORM DATA HERE

			gr.setValue("short_description" , "MIGRATED_" + item.short_description);
			//gr.sys_scope = "";
			//gr.sys_package = "";


			gr.autoSysFields(false);
			gr.setWorkflow(false);
			var recordSys_id = gr.insert(); 
		
			if (recordSys_id)
			{
				pausecomp(ThrottleLimit); //Throttle Limit - wait
				// voice call logs
				getRestQuery(instanceRestName,"sys_journal_field?&sysparm_query=element_id%3D" + recordSys_id,"","0");
				getRestQuery(instanceRestName,"sys_history_set?&sysparm_query=id%3D" + recordSys_id,"","0");
				getRestQuery(instanceRestName,"sys_history_line?&sysparm_query=set.id%3D" + recordSys_id,"","0");
				getRestQuery(instanceRestName,"sys_audit?&sysparm_query=documentkey%3D" + recordSys_id,"","0");
				//pausecomp(ThrottleLimit);
				getRestQuery(instanceRestName,"question_answer?&sysparm_query=table_sys_id%3D" + recordSys_id,"","0");
				getRestQuery(instanceRestName,"sc_multi_row_question_answer?&sysparm_query=parent_id%3D" + recordSys_id,"","0");
				//pausecomp(ThrottleLimit);
				getRestQuery(instanceRestName,"sys_attachment?&sysparm_query=table_sys_id%3D" + recordSys_id,"","0");
				getRestQuery(instanceRestName,"sys_attachment_doc?&sysparm_query=sys_attachment.table_sys_id%3D" + recordSys_id,"","0");
				getRestQuery(instanceRestName,"sys_email?&sysparm_query=instance%3D" + recordSys_id,"","0");
				//pausecomp(ThrottleLimit);
				//DISABLE SLA in PROD - can be reclac //
				//getRestQuery(instanceRestName,"task_sla?&sysparm_query=task%3D" + recordSys_id,"","0");
				getRestQuery(instanceRestName,"sysapproval_approver?&sysparm_query=sysapproval%3D" + recordSys_id,"","0");	
			}
			else
			{
				gs.log("NOT INSERTED: " + oldTableName + "." + item.sys_id + " : " + gr.getLastErrorMessage(), "DATA_MIGRATION_LOG");
			}
		});

	}
	else
	{
		var responseBody = response.getBody();
		var message = responseBody + "\nStatus Code: " + response.getStatusCode() + "\nError Code: " + response.getErrorCode() + "\nError Message: " + response.getErrorMessage();
		gs.log("ERROR: responce REST API " + restEndpoint + "\n\n" + message, "DATA_MIGRATION_LOG");
	}
}


function getRestQuery(instanceName,tableQueryStr,returnlimit,queryoffset)
{
	var instanceAPI = "https://" + instanceName + ".service-now.com/api/now/table/";
	var reqParams = "&sysparm_display_value=false&sysparm_suppress_pagination_header=true&sysparm_exclude_reference_link=true";
	var limit = "&sysparm_limit=" + returnlimit;
	var offSet = "&sysparm_offset=" + queryoffset;
	var endpointURL = instanceAPI + tableQueryStr + reqParams + limit + offSet;
	//gs.log(uniqueTimeFunction() + ": " + endpointURL, "DATA_MIGRATION_LOG");
	moveRecordFromSnowtoSnow(endpointURL);	
}


/*
creates exact replica of record from one isntance to this.
enhanced to cater for table name changes
give it endpoint. it pulls all records.
*/
function moveRecordFromSnowtoSnow(endpoint)
{
	var tableName = endpoint.split("?")[0].split("/")[6];

	var r = new sn_ws.RESTMessageV2();
	r.setEndpoint(endpoint);
	r.setHttpMethod('GET');
	r.setRequestHeader('Content-Type', 'application/json');
	r.setAuthenticationProfile('basic', "c8ac221adb1209900034150505961988");  //sys_auth_profile_basic.do?sys_id=dfa9cebcdb5e8150344b9517f39619d6

	try
	{
		var response = r.execute();	
	}
	catch(e)
	{
		gs.log("ERROR: Sending REST API " + e + "\n\n" + restEndpoint, "DATA_MIGRATION_LOG");
	}


	if (!response.haveError())
	{
		var parsed = JSON.parse(response.getBody());
		var results = parsed.result; 

		results.forEach(function(item)
		{
			var gr = new GlideRecord(tableName);
			gr.initialize();
			for (var key in item) 
			{
				if(key == "sys_id")
				{
					gr.setNewGuidValue(item[key]); //sets sys_id
				}
				else if(key != "sys_tags") // fields to ignore
				{
					//if (['name','table','tablename','parent_table_name','target_table','source_table'].indexOf(key) != -1)  //if the field name is one of the following
					//{
					//	if(item[key] == oldTableName) // if the value is the old table name
					//	{
					//		gr.setValue(key, newTableName);  // set it to the new table name
					//	}
					//	else
					//	{
					//		gr.setValue(key, item[key]); // my mistake - set the value normally
					//	}
					//}
					//else
					//{
						gr.setValue(key, item[key]);
					//}
				}
			}
			gr.autoSysFields(false);
			gr.setWorkflow(false);

			if (gr.insert())
			{
				//record inserted
			}
			else
			{
				gs.log("NOT INSERTED: " + tableName + "." + item.sys_id + " : " + gr.getLastErrorMessage(), "DATA_MIGRATION_LOG");
			}
		});
	}
}