Push .csv file from mid server to an SFTP location

ark6 · ‎08-23-2017

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

},

};

ark6 · ‎08-25-2017

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

}

};

View solution in original post

Michael Ritchie · ‎08-23-2017

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

File Builder FTP

ark6 · ‎08-23-2017

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..

SudhirOjha · ‎08-24-2017

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

ark6 · ‎08-24-2017

What is that? Can you explain please?