Migrating attachments from MicroFocus Service Manager

[petercawdron]

On a recent engagement, we had to migrate attachments from MicroFocus Service Manager (previously HP SM). The organisation wanted to tidy up their records before migrating, so they downloaded them into Excel, tweaked them, and we then uploaded into ServiceNow, but obviously, that didn't include attachments. 

We had a couple of cracks at attachments and found that the timeout tended to blow out (even with small files coming from Service Manager) so we had to use the executeAsync option from ServiceNow's REST web service API. 

var loopThruTable = 'ast_contract';   //ServiceNow table (our target already contains migrated records but is missing attachments)
var loopThruField = 'vendor_contract';//ServiceNow field that contains a common identifier with system being migrated

var hpsm     = 'hpsm-server-name'; //PROD
var hpsmUser = 'hpsm-admin-ser';
var hpsmPwd  = 'hpsm-password';
var hpsmPort = ':13083';
var hpsmTable = 'contracts';//equivilent table in Service Manager

var getAllRecords, getAllResponse, allResponseBody;

///////////////////////////////////////////////////////////////////////////
//Loop through ALL records (to get their unique identifers)
try{
	var endPoint = 'http://'+ hpsm + hpsmPort +'/SM/9/rest/'+ hpsmTable +'/';

	getAllRecords = new sn_ws.RESTMessageV2();
	getAllRecords.setMIDServer("our_mid_server");
	getAllRecords.setEndpoint(endPoint);
	getAllRecords.setHttpMethod('GET');
	getAllRecords.setBasicAuth(hpsmUser,hpsmPwd);
	getAllRecords.setRequestHeader("Accept","application/json");
	getAllResponse = getAllRecords.executeAsync();
	getAllResponse.waitForResponse(120);

} catch(ex) {
	gs.info('Attachment Error: '+ JSON.stringify(ex,null,3));
} finally {
	allResponseBody = getAllResponse.haveError() ? getAllResponse.getErrorMessage() : JSON.parse(getAllResponse.getBody());
	status = getAllResponse.getStatusCode();
}

allResponseBody.content.forEach(function(record){

	///////////////////////////////////////////////////////////////////////////
	//Loop through EACH record in detail (using the unique identifier) and get attachments
	var getEachRecords, getEachResponse, eachResponseBody;
	try{
		var endPoint = 'http://'+ hpsm + hpsmPort +'/SM/9/rest/'+ hpsmTable +'/' + record.contract.ContractId + '/attachments';

		getEachRecords = new sn_ws.RESTMessageV2();
		getEachRecords.setMIDServer("our_mid_server");
		getEachRecords.setEndpoint(endPoint);
		getEachRecords.setHttpMethod('GET');
		getEachRecords.setBasicAuth(hpsmUser,hpsmPwd);
		getEachRecords.setRequestHeader("Accept","application/json");
		getEachResponse = getEachRecords.executeAsync();
		getEachResponse.waitForResponse(120);

	} catch(ex) {
		gs.info('Attachment Error: '+ JSON.stringify(ex,null,3));
	} finally {
		eachResponseBody = getEachResponse.haveError() ? getEachResponse.getErrorMessage() : JSON.parse(getEachResponse.getBody());
		status = getEachResponse.getStatusCode();
	}

	//We're only interested in previously migrated records (not all the original records) so if there's not an equivilent record we'll ignore these attachments
	var snowRecord = new GlideRecord(loopThruTable);
	if(snowRecord.get(loopThruField,record.contract.ContractId)){

		var snowAttachments = new GlideRecord('sys_attachment');
		snowAttachments.addEncodedQuery('table_name=ast_contract^table_sys_id='+snowRecord.sys_id);
		snowAttachments.query();

		var remoteAttachmentCount = eachResponseBody.content.length === undefined ? 0 : eachResponseBody.content.length;

		//If the attachment count doesn't equal, figure out what's missing and bring over the missing files
		if(remoteAttachmentCount != snowAttachments.getRowCount()){

			//Get a list of the current attachments for comparison
			var currentAttachments = '';
			while(snowAttachments.next()){
				currentAttachments += snowAttachments.file_name.toString();
			}

			//Loop through each record and transfer the attachments one at a time
			eachResponseBody.content.forEach(function(entry){
				if(currentAttachments.indexOf(entry.attachment.name)==-1){

					gs.info('Attachment: ' + record.contract.ContractId + ' : '+ entry.attachment['name'] );

					var thisAttachment = entry.attachment.href.split(':');
					var targetEndPoint = 'http://'+ hpsm + hpsmPort +'/SM/9/rest/'+ hpsmTable +'/'+ record.contract.ContractId +'/attachments/' + thisAttachment[1];

					var getAttach  = new sn_ws.RESTMessageV2();
					getAttach.setMIDServer("our_mid_server");
					getAttach.setHttpMethod('GET');
					getAttach.setEndpoint(targetEndPoint);
					getAttach.setBasicAuth(hpsmUser,hpsmPwd);

					//Attach to this ServiceNow contract record
					getAttach.saveResponseBodyAsAttachment(loopThruTable, snowRecord.sys_id.toString(), entry.attachment.name.replace(/\s/g,'+'));

					var attachResponse = getAttach.execute();
					httpResponseStatus = attachResponse.getStatusCode();
					gs.sleep(1000);
				}
			});
		}
	}
});

The only other issue was that there were duplicate attachments in Service Manager, so I wrote a clean-up script to remove any migrated duplicates from ServiceNow.

var eachMigratedRecord = new GlideRecord('ast_contract');
eachMigratedRecord.query();
 
while(eachMigratedRecord.next()){

  var findDuplicates = new GlideAggregate('sys_attachment');
  findDuplicates.addEncodedQuery('table_sys_id=' + eachMigratedRecord.sys_id);
  findDuplicates.addAggregate('COUNT', 'file_name');
  findDuplicates.addAggregate('COUNT', 'size_bytes');
  findDuplicates.addHaving('COUNT', 'file_name', '>', '1');
  findDuplicates.addHaving('COUNT', 'size_bytes', '>', '1');
  findDuplicates.query(); 

  while(findDuplicates.next()){
     gs.info(findDuplicates.sys_id + ' ' + findDuplicates.file_name);
     var deleteOne = new GlideRecord('sys_attachment');
     deleteOne.setLimit(1);
     deleteOne.addEncodedQuery('table_sys_id='+eachMigratedRecord.sys_id+'^file_name='+findDuplicates.file_name);
     deleteOne.query();

     if(deleteOne.next()){
        deleteOne.deleteRecord();  
     } 
  }
}