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

ark6 · ‎08-23-2018

What kind of authentication are you using? This script is for a public key authentication

sry · ‎09-05-2019

Hi ark, we were doing the same solution without a privatekey and it was working. Now they have added ciphers on SFTP folders. and our connection is not working.

Do you have any idea on how to set this logic if ciphers are set on SFTP? what should we have in servicenow to make sure exchanging keys works with SFTP folder? please help me if you ever worked on ciphers.

Thanks,

Sry

Xeretni · ‎11-23-2021

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

Blitz · ‎05-06-2025

Hi Ark, only post export set script and mid server script include is required to transfer the file from mid server to an sftp location?

Elias11 · ‎08-29-2018

Hi Ark

indeed it is not a public key authentication, I am using the Private authentication, is there any way to do that?

thanks

Elias