Push .csv file from mid server to an SFTP location

[ark6]

Hi,

I have came across a requirement where I need to post a .csv file to my mid server and push to an SFTP location.

Please note that my mid server is hosted in Linux and my SFTP location uses a public key authentication method .

For that I am using the export set, and try to call a script include through a javascript probe, but that doesnot seem to be working. Could any one please help?

johnandersen ctomasi please help me with this.

My post export script:

var jprobe = new JavascriptProbe('mymid');

              jprobe.setName("Sftp File Transfer"); //any name can be given

              jprobe.setJavascript("var req = new SftpFile();req.FileTransfer();");

              jprobe.addParameter("targetPath",'test/test');

              jprobe.addParameter("keypath", '/home/myfolder/.ssh/');

              jprobe.addParameter("fileName", "ays_aptio_incident_data.csv");

              jprobe.addParameter("midServerTempPath", "/apps/opt/application/servicenow/midserver/agent/");

              jprobe.addParameter("deleteAfterUpload", true);

              jprobe.create();

My script include:

var SftpFile = Class.create();

SftpFile.prototype = {

  initialize: function() {

              this.Properties                     = Packages.java.util.Properties;

              this.StringUtil                     = Packages.com.glide.util.StringUtil;

              this.BufferedWriter             = Packages.java.io.BufferedWriter;

              this.File                                 = Packages.java.io.File;

              this.FileWriter                     = Packages.java.io.FileWriter;

              this.Encrypter                       = new Packages.com.glide.util.Encrypter();

              this.FileOutputStream         = Packages.java.io.FileOutputStream;

              this.FileInputStream           = Packages.java.io.FileInputStream;

              this.BufferedReader             = Packages.java.io.BufferedReader;

              this.InputStreamReader       = Packages.java.io.InputStreamReader;

              this.OutputStraem                 = Packages.java.io.OutputStream;

              this.BufferedOutputStream = Packages.java.io.BufferedOutputStream;

              this.Thread                             = Packages.java.lang.Thread;

      this.targetServer = 'my-sftp-server';

      this.targetPort =22 ;

      this.targetUsername = 'abc';

    // this.targetPassword = 'testpassword';

      this.deleteAfterUpload= probe.getParameter("deleteAfterUpload");

      this.targetPath = probe.getParameter("targetPath");

      this.MIDSERVER_FILE_PATH = probe.getParameter("midServerTempPath");

      this.MIDSERVER_FILE_NAME = probe.getParameter("fileName");

this.MIDSERVER_PRIVATE_KEY_PATH = probe.getParameter("keypath");

  },

FileTransfer: function() {

                      try {

                              var localFileName = this.MIDSERVER_FILE_PATH + "/" + this.MIDSERVER_FILE_NAME;

                              this.log("Copying from local file of MID Server: " + localFileName);

gs.log("Copying from local file of MID Server: " + localFileName);

                              this.sftpFile(this.targetServer, this.targetUsername, this.MIDSERVER_PRIVATE_KEY_PATH, localFileName, this.targetPath + this.MIDSERVER_FILE_NAME);

                      } catch (e) {

                              this.log("Error in writing file to SFTP server: " + e);

gs.log("Error in writing file to SFTP server: " + e);

                      }

      },

sftpFile : function(hostName, userName, password, localFileName, remoteFileName) {

              this.log('sftpFile(): attempting to connect to ' + hostName);

gs.log('sftpFile(): attempting to connect to ' + hostName);

              //var ignoreHost = new Packages.com.sshtools.j2ssh.transport.IgnoreHostKeyVerification();

keyfile = password+'id_rsa';

      sshfile = new Packages.com.sshtools.j2ssh.transport.publickey.SshPrivateKeyFile.parse(new Packages.java.io.File(keyfile));

//this.log('key format'+sshfile.getFormat());

//this.log('key value'+sshfile.toString());

pk = new Packages.com.sshtools.j2ssh.authentication.PublicKeyAuthenticationClient();

//this.log('PUBLIC KEY METHOD'+pk.getMethodName());

pk.setUsername(userName);

pk.setKey(sshfile.toPrivateKey(null));

var ssh = new Packages.com.sshtools.j2ssh.SshClient();

              if (!this.targetPort){

                      this.targetPort = 22;

              }

              this.log('sftpFile(): attempting to connect to ' + hostName + " on port " + this.targetPort);

              ssh.connect(hostName, this.targetPort, ignoreHost);

          // var pwd = new Packages.com.sshtools.j2ssh.authentication.PasswordAuthenticationClient();

              //var authPassword = new Packages.com.glide.util.Encrypter().decrypt(password);

          // pwd.setUsername(userName);

          //   pwd.setPassword(authPassword);

              // Get full path of filename

              this.log('sftpFile(): attempting to copy ' + localFileName + ' to ' + remoteFileName);

              //if(ssh.authenticate(pwd) == new Packages.com.sshtools.j2ssh.authentication.AuthenticationProtocolState().COMPLETE) {

                      sftp = ssh.openSftpClient();

                      try {

                            sftp.put(localFileName, remoteFileName);

                              this.log("File successfully copied to targert path\n\n");

                              if (this.deleteAfterUpload == "true") {

                                      this.log("deleteAfterUpload -> " + this.deleteAfterUpload + ", deleting local file...");

                                      new this.File(localFileName)["delete"]();

                              }

                      }

                                                                                              catch(e) {

                              this.log('FILE NOT FOUND ' + remoteFileName + ' or error: ' + e);

                      }

                      sftp.quit();

                      try{

                              // kill connection

                              ssh.disconnect();

                      }

                      catch(e){

                              this.log('Manual connection kill not successful with error: ' + e);

                      }

          //   }

      },

      log: function(data) {

              ms.log(data);

      },

};

[Gowri S]

Hi Ark,

I am getting error as parse is not a function, could you please help on it.

Error in writing file to SFTP server: TypeError: [JavaPackage com.sshtools.J2ssh.transport.publickey.SshPrivateKeyFile.parse] is not a function.

[Hitoshi Ozawa]

Which code are you running?

com.sshtools.J2ssh.transport.publickey.SshPrivateKeyFile.parse is only included in the code in the question and not included in the chosen answer code.

[Gowri S]

Hi Hozawa,

When we tried using username and password, it is working fine, but we need to use privatekey, so tried with that and it got error out.

I was referring the below code which Ark has shared. But getting error as "Error in writing file to SFTP server: TypeError: [JavaPackage com.sshtools.J2ssh.transport.publickey.SshPrivateKeyFile.parse] is not a function."

Mid Script Include:

var SFTP = Class.create();

SFTP.prototype = {

initialize: function() {

this.File             = Packages.java.io.File;

this.J2ssh           = Packages.com.sshtools.j2ssh;

this.targetHost = probe.getParameter('host');

this.targetPort = probe.getParameter('port');

this.targetUser = probe.getParameter('user');

this.privateKey = probe.getParameter('privatekey');

this.targetFile = probe.getParameter('target') + probe.getParameter('filename');

this.sourceFile = probe.getParameter('source') + probe.getParameter('filename');

this.deleteFile = probe.getParameter('deletefile');

},

fileTransfer: function() {

try {

this._sftp();

if (this.deleteFile) {

this._deleteFile();

}

} catch (ex) {

ms.log('SFTP Error: ' + ex);

}

},

_authenticate: function(ssh) {

privateKeyFile = new this.J2ssh.transport.publickey.SshPrivateKeyFile.parse(new this.File(this.privateKey));

publicKeyAuth = new this.J2ssh.authentication.PublicKeyAuthenticationClient();

publicKeyAuth.setUsername(this.targetUser);

publicKeyAuth.setKey(privateKeyFile.toPrivateKey(''));

var result = ssh.authenticate(publicKeyAuth);

if (result != new this.J2ssh.authentication.AuthenticationProtocolState().COMPLETE) {

throw 'SFTP Authenticate Failed: ' + this.targetUser + '@' + this.targetHost + ':' + this.targetPort;

}

},

_deleteFile: function() {

try {

new this.File(this.sourceFile)['delete']();

} catch (ex) {

throw 'SFTP Delete: ' + ex;

}

},

_sftp : function() {

ms.log('SFTP Connect: ' + this.targetUser + '@' + this.targetHost + ':' + this.targetPort);

var ssh = new this.J2ssh.SshClient();

try {

ssh.connect(this.targetHost, this.targetPort, new this.J2ssh.transport.IgnoreHostKeyVerification());

this._authenticate(ssh);

this._transfer(ssh);

} catch (ex) {

throw 'SFTP Error: ' + ex;

} finally {

ssh.disconnect();

}

},

_transfer: function(ssh) {

try {

sftp = ssh.openSftpClient();

sftp.put(this.sourceFile, this.targetFile);

ms.log('SFTP Transfer: ' + this.sourceFile + ' copied to ' + this.targetHost + ':' + this.targetPort);

} catch (ex) {

throw 'SFTP Transfer: ' + ex;

} finally {

sftp.quit();

}

}

};

[Xeretni]

Hi, have you been able to fix this? 

[Xeretni]

Hi Ark - I'm trying this code and I'm getting below error:

WARNING *** WARNING *** MID Script Include 'SFTP' does not exist.  Marking it to prevent future queries.
Worker-Standard:JavascriptProbe-871870b41b848118bb3143fccc4bcb37 WARNING *** WARNING *** org.mozilla.javascript.EcmaError: "SFTP" is not defined. Caused by error in JavaScript probe 'SFTP File Transfer' at line 1

And also, may I know what should I put here? Is it the public key or is it something else? 

this.privateKey = probe.getParameter('privatekey');

I'm new to ServiceNow and still trying to learn. I would greatly appreciate your help and guidance.

Look forward to hearing from you soon!

Cheers,

Sheryn