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);

      },

};

[SudhirOjha]

I have windows server as MID server and i have created a task scheduler to move the files to sFTP location.

IN task scheduler the batch file is called to deploy the files on to sFTP server using winscp tool.

for more details how to move file in windows

How to automate SFTP file transfers in Microsoft Windows | ITworld

There is a way in LINUX server also to move the files to sFTP using command. using PUT command

SFTP Using Command Line(UNIX, Mac OS X) | IT Services | USC

[ark6]

Yes, thats fine, but for us we want to maintain it within servicenow. So trying to find a way how to do it through a probe

[SudhirOjha]

ok --thats your choice... but we don't want to share the credentials outside our organization network--so we preferred that way to move data on sFTP.

[ark6]

I might have got it to work.. I will do a test and if it works I will update the thread thanks.

[Ankur Bawiskar]

Hi Arka,

I have similar requirement sending csv file to sftp server.

The third party have given following details:

1) ftp host name

2) ftp port 22

3) ftp username

4) ftp password

4) key file with '.ppk' file extension.

Is the following approach correct:

1) load the ppk file to mid server location

2) follow your steps and mid server script and check whether the upload works

Are you using some external jar file which you have uploaded to mid server?

Regards

Ankur