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

      },

};

[Ankur Bawiskar]

Hi Arka,

As per my earlier comment to this post I informed that the third party has given me the key file of putty.

I tried to connect and upload file to sftp with username, password, port(22) but getting SocketTimeOutException.

Not sure what is this for. the team informed that the folder was not present in the sftp server

The sftp server is in client internal network and the mid server is not in client network. the client network team has opened up the sftp port so that mid server can communicate over sftp port.

Do you think I should try using the key file as per the script mentioned by you by placing the key file in mid server location.

Can you provide some thoughts on this?

We have created public keys for our mid servers with .pub extension and sent it to the SFTP team to add that on their end to establish a trust between the two systems.

What exactly it means and how it has to be done?

We did not generate any key file and send to third party team yet.

Regards

Ankur

[ark6]

Hi Ankur,

Sorry for the delay in reply. Not very much active in community now a days

If you have the keypairs, I believe that is sufficient to setup the trust between two systems. A userid/pass is not required in this case.

Your keypairs also need to be installed in the third party to setup a trust between two systems(public keys).

You may need to increase the timeout seconds to get rid of the socket Timeout exception. Below are few examples where they got rid of it by increasing the timeout seconds.

Getting java.net.SocketTimeoutException: Connection timed out in android - Stack Overflow

https://examples.javacodegeeks.com/core-java/net/sockettimeoutexception/java-net-sockettimeoutexcept...

[Ankur Bawiskar]

Hi Arka,

Thanks for your reply.

We don't have any key pairs generated by us. There is a key file which they have shared and they have shared details of sftp as well such port=22, hostname, password.

Till now I have not placed the key file in that mid server location and have not used code similar to yours to pick up that key file.

Format of key file they have shared:

File name is : abc.ppk (seems to be extension for putty file)

PuTTY-User-Key-File-2: ssh-rsa

Encryption: none

Comment: imported-openssh-key

Public-Lines: 6

// 6 lines of combination of numbers, chars,   special characters

Private-Lines: 14

// 14 lines of combination of numbers, chars,   special characters

Private-MAC:

Should I check with them that the key file shared does it include the mid server and sftp trust in it or it is some file which is generic

I increased the timeout from 10sec to 40 sec but still the same error. It says java.net.SocketTimeoutException: Read timed out.

The client network team has informed that they have opened the mid server IP incoming connection so that file can be sent over to sftp server.

Regards

Ankur

[ark6 · Accepted solution]

Hello,

I have managed to fix this. I had used gs.log multiple times as a result it was giving issue and also the package used for authentication was not correct. Here's the updated one. Please note, the fields marked with * in the code needs to be changed as per your config of the mid or SFTP

javascript probe:

var jprobe = new JavascriptProbe('*mid_server_name*'));

jprobe.setName('SFTP File Transfer'); //any name can be given

jprobe.setJavascript('new SFTP().fileTransfer();');

jprobe.addParameter('host', '*target_host_name'));

jprobe.addParameter('port', '*port_name');

jprobe.addParameter('user', '*user_id');

jprobe.addParameter('privatekey', '*private_key_path');

jprobe.addParameter('source', '*source_file_path');

jprobe.addParameter('target', '');

jprobe.addParameter('filename', '*file_name');

jprobe.addParameter('deletefile', true);

jprobe.create();

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

}

}

};

[Elias11]

Hi Ark

I have tried your script and it is only working by creating a file into my midserver but not more, it is not copiying the file into the sftp Server.

When I run the Schedule Export I am getting in the log NULL Pointer Exception.

what I am doing wrong?

Thanks

Elias