question Re: Push .csv file from mid server to an SFTP location in Developer forum

Push .csv file from mid server to an SFTP location

ark6 — Wed, 23 Aug 2017 19:59:28 GMT

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

};

Re: Push .csv file from mid server to an SFTP location

Michael Ritchie — Wed, 23 Aug 2017 20:14:38 GMT

Have you seen this on Share? It may help solve your issue:

File Builder FTP

Re: Push .csv file from mid server to an SFTP location

ark6 — Wed, 23 Aug 2017 21:07:27 GMT

Hi Michael,

Yes, I have seen this, but it also uses user id and password auth. not the public key authentication and also my code is a refrence from that one as well..

Re: Push .csv file from mid server to an SFTP location

SudhirOjha — Thu, 24 Aug 2017 18:54:46 GMT

one the file is on MID server, Why don't you use Linux scheduled task to move the file to sftp server.

Re: Push .csv file from mid server to an SFTP location

ark6 — Thu, 24 Aug 2017 19:11:36 GMT

What is that? Can you explain please?

Re: Push .csv file from mid server to an SFTP location

SudhirOjha — Thu, 24 Aug 2017 19:20:39 GMT

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

Re: Push .csv file from mid server to an SFTP location

ark6 — Thu, 24 Aug 2017 19:24:30 GMT

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

Re: Push .csv file from mid server to an SFTP location

SudhirOjha — Thu, 24 Aug 2017 19:26:35 GMT

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.

Re: Push .csv file from mid server to an SFTP location

ark6 — Thu, 24 Aug 2017 19:31:03 GMT

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

Re: Push .csv file from mid server to an SFTP location

ark6 — Fri, 25 Aug 2017 13:49:33 GMT

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

}

};

Re: Push .csv file from mid server to an SFTP location

Ankur Bawiskar — Fri, 10 Nov 2017 11:32:30 GMT

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

Re: Push .csv file from mid server to an SFTP location

ark6 — Mon, 13 Nov 2017 16:23:19 GMT

Hi Ankur,

Sorry for the delay in reply.

Yes, we did in the same way. We uploaded the key file in our mid server location , following with the creation of export sets and mid server script includes.

Re: Push .csv file from mid server to an SFTP location

Ankur Bawiskar — Mon, 13 Nov 2017 16:36:35 GMT

Hi Arka,

Thanks for the details. I have few questions:

1) was that a sftp server - I assume yes because it is port 22

2) was the key file a ppk file or with some other extension.

3) Did you load some jar file for sftp package call in java

Regards

Ankur

Re: Push .csv file from mid server to an SFTP location

ark6 — Mon, 13 Nov 2017 17:32:57 GMT

No we did it like below

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.

Once we did that, we haven't needed a .jar file to install on our end.

Re: Push .csv file from mid server to an SFTP location

Ankur Bawiskar — Tue, 14 Nov 2017 07:09:07 GMT

Hi Arka,

Got it. But in my case the sftp team has sent the key file which is a ppk file seems to be key file extension for putty application.

May be I can check whether I can use password to connect without key file.

Regards

Ankur

Re: Push .csv file from mid server to an SFTP location

ark6 — Wed, 15 Nov 2017 14:20:49 GMT

You could try with the username and password in FileZilla and check if you can access the SFTP location.

If you can, then I believe it becomes easier.

Re: Push .csv file from mid server to an SFTP location

Ankur Bawiskar — Mon, 29 Jan 2018 14:43:03 GMT

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

Re: Push .csv file from mid server to an SFTP location

ark6 — Wed, 31 Jan 2018 19:50:36 GMT

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-sockettimeoutexception-how-to-solve-soc…

Re: Push .csv file from mid server to an SFTP location

Ankur Bawiskar — Thu, 01 Feb 2018 04:59:15 GMT

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

Re: Push .csv file from mid server to an SFTP location

Elias11 — Fri, 10 Aug 2018 11:25:58 GMT

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