How to get download a file from FTP and attach to a record in ServiceNow?

[jamesmcwhinney]

Background

I am working on a solution to download a file (a word doc) from a remote FTP server and attach it to a record in ServiceNow.

After much searching I have concluded that the functionality to download from FTP does exist in the OOTB processor "sys_import" which is used by data sources of type FTP. Unfortunately while an FTP data source will connect to the an FTP server and download the file, it immediately tries to parse it into an import set, and there is no way to retrieve the downloaded file or do anything useful with it.

Furthermore, sys_import runs the underlying jar package ftp4che, however as of the Fuji release ServiceNow has locked down any way to access these packages directly.

I have been successful in building a javascript probe + custom mid server script to download the file via the mid server (You can still access the ftp4che jar package directly via the mid server). - Thanks to this post here to get that working.

Question:

I am now struggling with getting the file back into ServiceNow.  Either via the ECC queue or via the rest attachment API.

Does anyone have any insight they can share in terms of bringing a file from the Mid server back into ServiceNow?

[sachin_namjoshi]

Have you tried using the Scheduled File Import? 

https://www.servicenowguru.com/integration/scheduled-file-import-mid-server/

Regards,

Sachin

[jamesmcwhinney]

I saw that one too, unfortunately it looks like the download has been deleted.

[sachin_namjoshi]

Below solutions should be useful to you

https://hi.service-now.com/kb_view.do?sysparm_article=KB0817437

Regards,

Sachin

[jamesmcwhinney · Accepted solution]

I was able to create a solution for this based on information gained via two existing posts within the community forum:

• Get FTP File Listing via MID server
• Making a REST call from MID server

This took alot of hair pulling and digging through java source files etc but was able to make it work:

Mid Server Script Include:

var myFTPDownloader= Class.create();

myFTPDownloader.prototype = {
	initialize : function() {
		this.resLog = "FTP Log: \n";
		this.log("Initializing FTP Download and Attach Tool");
		
		var ftpCredArr = this._decryptCredentials(probe.getParameter('ftpCred'));
		this.ftpUser = ftpCredArr[0];
		this.ftpPass = ftpCredArr[1];
		this.ftpTargetServer = probe.getParameter('ftpTargetServer');
		this.ftpPort = (this._isANumber(probe.getParameter('ftpPort'))) ? (probe.getParameter('ftpPort')) : null;
		this.ftpTargetPath = probe.getParameter('ftpTargetPath');
		this.ftpTargetFile = probe.getParameter('ftpTargetFile');
		this.ftpTransportMethod = probe.getParameter('ftpTransportMethod');
		this.tempFilePath = probe.getParameter('tempFilePath');
		this.newFileName = probe.getParameter('newFileName');
		this.attachmentTable = probe.getParameter('attachmentTable');
		this.attachmentSysID = probe.getParameter('attachmentSysID');
		this.attachmentRestUser = probe.getParameter('attachmentRestUser');
		this.attachmentRestPassword = probe.getParameter('attachmentRestPassword');
		this.instanceName = probe.getParameter('instanceName');
		
   },
   _decryptCredentials: function(data) {
      var cred = new String(data);
      var e = new Packages.com.glide.util.Encrypter();
     
      var jsCred = cred + '';
      var usernamePass = e.decrypt(jsCred);
      var credArr = usernamePass.split(":", 2);
      return credArr;
   },
  
   getDirectoryListing: function() {
      var result = '';
      this.log("Running ftp4che getDirectoryListing command");
      var pt = new Packages.java.util.Properties();
      pt.setProperty("connection.host", this.ftpTargetServer);
      pt.setProperty("connection.port", this.ftpPort);
      pt.setProperty("user.login", this.ftpUser);
      pt.setProperty("user.password", this.ftpPass);
      pt.setProperty("connection.type", this.ftpTransportMethod);
      pt.setProperty("connection.timeout", '10000');
      pt.setProperty("connection.passive", 'true');
      var connection = Packages.org.ftp4che.FTPConnectionFactory.getInstance(pt);
      connection.connect();
      var myFiles = connection.getDirectoryListing(this.ftpTargetPath);
      var toArray = myFiles.toArray();
      var ret= toArray.join(',');
      ret = this.sysID+'|'+ret;
      this.log("results:" + ret);
	   return this.resLog;
     
     
   },
   downloadFile: function() {

	   var result = '';
	   this.log("Running ftp4che downloadFile command");
	   var pt = new Packages.java.util.Properties();
	   pt.setProperty("connection.host", this.ftpTargetServer);
	   pt.setProperty("connection.port", this.ftpPort);
	   pt.setProperty("user.login", this.ftpUser);
	   pt.setProperty("user.password", this.ftpPass);
	   pt.setProperty("connection.type", this.ftpTransportMethod);
	   pt.setProperty("connection.timeout", '10000');
	   pt.setProperty("connection.passive", 'true');
	   var connection = Packages.org.ftp4che.FTPConnectionFactory.getInstance(pt);
	   connection.connect();

	   var fromFile = new Packages.org.ftp4che.util.ftpfile.FTPFile(this.ftpTargetPath , this.ftpTargetFile);
	   var toFile = new Packages.org.ftp4che.util.ftpfile.FTPFile(this.tempFilePath + "\\", this.ftpTargetFile);
	   connection.downloadFile(fromFile, toFile);
	   
	   
	   
	   connection.disconnect(); 
	   this.log("FTP Download Completed OK!");
	   if(this.attachmentSysID != ""){
		 this._attachFile();   
	   }
	   
	   return this.resLog;
	   
     
   },	
   _attachFile: function(){
	   
	    this.log("Trying Upload to Attachment Rest API");
		
	    this.boundary = "===" + new Date().getTime() + "==="; 
		this.charset = "UTF-8";
		this.LINE_FEED = "\r\n";

		try{
			this.log("Getting Connection...");
			var url = "https://" + this.instanceName + ".service-now.com/api/now/attachment/file?table_name=" + this.attachmentTable + "&table_sys_id=" + this.attachmentSysID + "&file_name=" + this.newFileName;
			var conn = new Packages.java.net.URL(url).openConnection();
			var userCredentials = new Packages.java.lang.String(this.attachmentRestUser + ":" + this.attachmentRestPassword);
			var basicAuth = "Basic " + Packages.java.util.Base64.getEncoder().encodeToString(userCredentials.getBytes());
			conn.setRequestProperty("Authorization", basicAuth);
			conn.setDoOutput(true);
			conn.setRequestMethod("POST");
			conn.setUseCaches(false);
			conn.setRequestProperty("Content-Type", "multipart/form-data; boundary=" + this.boundary);
			conn.setRequestProperty("User-Agent", "MID Server POST");

			this.log("Getting File...");
			var outputStream = conn.getOutputStream();
			var writer = new Packages.java.io.PrintWriter(new Packages.java.io.OutputStreamWriter(outputStream, this.charset), true);
			var fieldName = "uploadFile";
			var uploadFile = new Packages.java.io.File(this.tempFilePath + "\\" + this.ftpTargetFile);
			var fileName = uploadFile.getName();
			
			this.log("Writing File Data...");
			var inputStream = new Packages.java.io.FileInputStream(uploadFile);
			var data = new Packages.java.lang.reflect.Array.newInstance(Packages.java.lang.Byte.TYPE, 4096);
			var bytesRead = 0;
			while ((bytesRead = inputStream.read(data)) != -1) {
				outputStream.write(data, 0, bytesRead);
				outputStream.flush();
			}
			inputStream.close();
			
			this.log("Completing Message...");
			writer.close();
			this.log("Response Code:" + conn.getResponseCode());
		  
		} catch (e) {
			this.log("JMM-Error!");
			this.log(e);

		}
		//return this.resLog;
   },
	
	
	
   getLog: function() {
      return this.resLog;
   },
  
   log: function(data) {
      ms.log(data);
      this.resLog+="\n"+data;
   },
  
   _isANumber: function(data) {
      data = data + '';
      return ((data - 0) == data && data.length > 0);
   },
  
  
   type : "myFTPDownloader"
};

Calling the Mid Server Script Include using a javascript probe:

CallCustomJSProbe();

function CallCustomJSProbe(){
  
   try{
	  
      var jspr = new JavascriptProbe('nameofyourmidserver');
      jspr.setName('pickanynameforyoureccmessage');
      jspr.setJavascript("var ftptool = new myFTPDownloader();res = ftptool.downloadFile();");
      jspr.addParameter("ftpCred","username:password");
      jspr.addParameter("ftpTargetServer","ftp.yattayatta.com");
      jspr.addParameter("ftpPort","21");
      jspr.addParameter("ftpTargetPath","your/remote/file/path");
      jspr.addParameter("ftpTransportMethod","FTP_CONNECTION");
      jspr.addParameter("ftpTargetFile","myfile.doc");
      jspr.addParameter("tempFilePath","C:\\localfilepathonmidserver");
      jspr.addParameter("newFileName","myfile.doc"); //In case you want to rename it in flight
      jspr.addParameter("attachmentTable","incident"); 
      jspr.addParameter("attachmentSysID","yourincidentrecordsysid"); 
      jspr.addParameter("attachmentRestUser","yourrestaccountname");  //Account must have write access to table that file is being attached to
      jspr.addParameter("attachmentRestPassword","yourrestaccountpassword"); 
      jspr.addParameter("instanceName","yourinstancename"); 
      jspr.create();
      gs.log('Completed: Check Mid Server log');
   }
   catch(e){
      gs.log('exception in calling mid server : '+e);
     
   }
}