Use of SCP or SFTP from mid-server to deliver reports...

[tonywelle]

I Researched, found and loved the SNCGuru Update Set for scheduled report extracts, Until I installed and understood it. Unfortunately, it only supports FTPS and not SFTP. I could not find where the package ftp4che supported any other protocols.

Any suggestions on how to use SCP or SFTP in similar functionality?

[john_andersen]

I would love to see it. Go ahead an paste it in this thread or send it via my contact form at:
http://www.john-james-andersen.com/contact-john

If you are OK with it, and after some testing, I could provide it to the author the Scheduled Data Extract to see if they want to include it in their update set.

[tonywelle]

var SNDataRetriever = Class.create();

SNDataRetriever.prototype = {
initialize: function() {
this.resLog = "FTP Log: \n";
this.log("Initializing SNDataRetriever");
this.MIDSERVER_FILE_PATH = "/opt/apps/snc/work/";
this.MIDSERVER_SUCCESS_FILE_PATH = "/opt/apps/snc/work/success/";
this.MIDSERVER_PRIVATE_KEY_PATH='/home/snc/.ssh/';
//this.MIDSERVER_FILE_NAME = "tmpFileToFTP_" + probe.getParameter('targetFileName') + ".txt";
this.MIDSERVER_FILE_NAME = probe.getParameter('targetFileName') + ".txt";
this.useProxy = this.getConfigParameter("mid.proxy.use_proxy");
if (this.useProxy){
this.proxyHost =this.getConfigParameter("mid.proxy.host");
this.proxyPort =this.getConfigParameter("mid.proxy.port");
this.proxyUser =this.getConfigParameter("mid.proxy.username");
this.proxyPass =this.getConfigParameter("mid.proxy.password");
}
this.user = this.getConfigParameter("mid.instance.username");
this.password = this.getConfigParameter("mid.instance.password");

var ftpCredArr = this.decryptCredentials(probe.getParameter('ftpCred'));
this.ftpUser = ftpCredArr[0];
this.ftpPass = ftpCredArr[1];

this.ftpTargetServer = probe.getParameter('ftpTargetServer');
this.ftpPort = (this.isANumber(probe.getParameter('ftpPort'))) ? (probe.getParameter('ftpPort')) : null;
this.stripHeader = probe.getParameter('stripHeader');
this.stripQuotes = probe.getParameter('stripQuotes');
//this.log('STRIP HEADER'+this.stripHeader);
//this.log('STRIP QUOTES'+this.stripQuotes);
this.targetPath = probe.getParameter('targetPath');
this.targetFileName = probe.getParameter('targetFileName');

this.reportURL = probe.getParameter('reportURL');
this.host = probe.getParameter('host');
this.transportMethod = probe.getParameter('transportMethod');
//this.ScpSourceDir = '/opt/apps/snc/queue/';

// this.log("user: " + this.user + "\npass: " + this.password + "\nproxyNeeded: " + this.proxyNeeded);
// this.log("\proxyUser : " + this.proxyUser + "\nproxyPass :" + this.proxyPass );
// this.log("\nproxyHost: " + this.proxyHost + "\nproxyPort:" + this.proxyPort + "\nreport: "+ this.reportURL+ "\nhost: "+ this.host);
// this.log("ftpUser: "+ this.ftpUser + "\nftpPass: " + this.ftpPass + "\nftpPort: " + this.ftpPort);
this.response = this.execute();

},

getConfigParameter: function(parm){
var m= Packages.com.service_now.mid.MIDServer.get();
var config = m.getConfig();
var res = config.getParameter(parm);
var res2 = config.getProperty(parm);
if (res){
return res;
}
else if (res2){
return res2;
}
else{
config = Packages.com.service_now.mid.services.Config.get();
return config.getProperty(parm);
}

},

decryptCredentials: function(data) {
var cred = new String(data);
var e = new Packages.com.glide.util.Encrypter();

var jsCred = cred + '';
var usernamePass = e.decrypt(jsCred);
var credArr = usernamePass.split(":", 2);
return credArr;
},

saveToFile: function(data, targetPath) {
var tmpLoc;
try{
if (this.transportMethod == "MID Server"){
tmpLoc = targetPath;
}
else{
tmpLoc = this.MIDSERVER_FILE_PATH + this.MIDSERVER_FILE_NAME;
}
var out = new Packages.java.io.PrintWriter(new Packages.java.io.FileWriter(tmpLoc));
out.print(data);
this.log("File saved to: " + tmpLoc);
}
catch(e){
this.log("Exception caught in SNDataRetriever->saveToFile: " + e.getMessage());
}
out.close();
},

execSSH: function(ssh,cmd){
session = ssh.openSessionChannel();

if ( session.executeCommand(cmd) ){
output = new Packages.com.sshtools.j2ssh.io.IOStreamConnector();
bos = new Packages.java.io.ByteArrayOutputStream();
output.connect(session.getInputStream(), bos );
ChannelState = new Packages.com.sshtools.j2ssh.connection.ChannelState();
session.getState().waitForState(ChannelState.CHANNEL_CLOSED);
theOutput = bos.toString();
//this.log('OUTPUT'+theOutput);
return theOutput;
}
},

copyToSFTP: function() {
this.log('Arriving to function copyToSFTP');
var tmpLoc;
var connectionType;
var connected = true;
var connectionLog = '';
var sftpSuccess=true;
if (this.transportMethod == "SFTP_RSA"){
try {
keyfile = this.MIDSERVER_PRIVATE_KEY_PATH+'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(this.ftpUser);
pk.setKey(sshfile.toPrivateKey(null));
}
catch(e){
connectionLog += "\nException Reading RSA Key: " + e;
sftpSuccess=false;
}

}
else if (this.transportMethod == "SFTP_DSA"){
try {
keyfile = this.MIDSERVER_PRIVATE_KEY_PATH+'id_dsa';
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(this.ftpUser);
pk.setKey(sshfile.toPrivateKey(null));
}
catch(e){
connectionLog += "\nException Reading DSA Key: " + e;
sftpSuccess=false;
}
}
else if (this.transportMethod == "SFTP_PWD"){
pk = new Packages.com.sshtools.j2ssh.authentication.PasswordAuthenticationClient();
pk.setUsername(this.ftpUser);
pk.setPassword(this.ftpPass);
}
var ssh = new Packages.com.sshtools.j2ssh.SshClient();
var ignoreHost = new Packages.com.sshtools.j2ssh.transport.IgnoreHostKeyVerification();
try {
ssh.connect(this.ftpTargetServer,ignoreHost);
}
catch(e){
connectionLog += "\nException Connecting to SFTP Host: " + e;
sftpSuccess=false;
}
authState = new Packages.com.sshtools.j2ssh.authentication.AuthenticationProtocolState();
if(ssh.authenticate(pk)==authState.COMPLETE){

this.log('SSH REMOTE AUTHENTICATE==TRUE');
//this.log(this.execSSH(ssh,'ls -lt'));
var sftp = ssh.openSftpClient();
try{
sftp.lcd(this.MIDSERVER_FILE_PATH);
}
catch(e){
this.log('Error in Sftp set remote directory: '+e);
sftpSuccess=false;
}
try{
sftp.cd(this.targetPath);
}
catch(e){
this.log('Error in Sftp set local directory: '+e);
sftpSuccess=false;
}
if(sftpSuccess==true){
try {
sftp.put(this.MIDSERVER_FILE_NAME);
}
catch(e) {
this.log('Error in Sftp put: '+e);
sftpSuccess=false;
}
}
if (sftpSuccess==true){
this.log('Successfully copied file: '+this.MIDSERVER_FILE_NAME+' to '+this.ftpTargetServer+':'+this.targetPath);

// File (or directory) to be moved
file = new Packages.java.io.File(this.MIDSERVER_FILE_PATH+this.MIDSERVER_FILE_NAME);

// Destination directory
dir = new Packages.java.io.File(this.MIDSERVER_SUCCESS_FILE_PATH);

// Move file to new directory
success = file.renameTo(new Packages.java.io.File(dir, file.getName()));
if (!success) {
this.log('File was not successfully moved!');
}
}
else{
this.log('Failed to copy file: '+this.MIDSERVER_FILE_NAME+' to '+this.ftpTargetServer+':'+this.targetPath);
}
//var fromFile = new Packages.org.ftp4che.util.ftpfile.FTPFile(this.MIDSERVER_FILE_PATH, this.MIDSERVER_FILE_NAME);
//var toFile = new Packages.org.ftp4che.util.ftpfile.FTPFile(this.targetPath, this.targetFileName);
}
else{
connectionLog += "\n**********FAILURE: Connection to SFTP server failed**************\n";
}

this.log(connectionLog);
},

copyToFTP: function() {
var tmpLoc;
var connectionType;
this.log('Arriving to function copyToFTP');
if (this.transportMethod == "FTPS (Auth SSL)"){
connectionType = "AUTH_SSL_FTP_CONNECTION";
if (!this.ftpPort) {this.ftpPort = 21;}
}
else if (this.transportMethod == "FTPS (Auth TLS)"){
connectionType = "AUTH_TLS_FTP_CONNECTION";
if (!this.ftpPort) {this.ftpPort = 21;}
}
else if (this.transportMethod == "FTPS (Implicit SSL)"){
connectionType = "IMPLICIT_SSL_FTP_CONNECTION";
if (!this.ftpPort) {this.ftpPort = 990;}
}
else if (this.transportMethod == "FTPS (Implicit TLS)"){
connectionType = "IMPLICIT_TLS_FTP_CONNECTION";
if (!this.ftpPort) {this.ftpPort = 990;}
}
else{
connectionType = "FTP_CONNECTION";
if (!this.ftpPort) {this.ftpPort = 21;}
}
this.log("ConnectionType: " + connectionType + " and port: " + this.ftpPort);

var pt = new Packages.java.util.Properties();
pt.setProperty("connection.host", this.ftpTargetServer);
pt.setProperty("connection.port", this.ftpPort);
pt.setProperty("user.login", this.ftpUser);
pt.setProperty("user.password", this.ftpPass);
pt.setProperty("connection.type", connectionType);
pt.setProperty("connection.timeout", "10000");
pt.setProperty("connection.passive", "true");

var connection = Packages.org.ftp4che.FTPConnectionFactory.getInstance(pt);

var fromFile = new Packages.org.ftp4che.util.ftpfile.FTPFile(this.MIDSERVER_FILE_PATH, this.MIDSERVER_FILE_NAME);
var toFile = new Packages.org.ftp4che.util.ftpfile.FTPFile(this.targetPath, this.targetFileName);

var connectionLog = "Connection Log:\n";
var connected = false;
try{
connection.connect();
this.log("Connecting to " + this.ftpTargetServer + " on port " + this.ftpPort);

connection.noOperation();
connected = true;
}
catch(e){
connectionLog += "\nException in block B: " + e;
connected = false;
}

if (connected == true){
try{
this.log("Connected...");
this.log("Uploading " + fromFile + " to " + toFile);
connection.uploadFile(fromFile, toFile);
this.log("File successfully uploaded\n\n");
connection.disconnect();
}
catch(e){
connectionLog += "\n**********FAILURE: Connection to FTP server failed**************\n";
connectionLog += "\nException in block C: \n" + e;
}
}
else{
connectionLog += "\n**********FAILURE: Connection to FTP server failed**************\n";
}

this.log(connectionLog);
},

getReport: function() {
this.log("Arriving to getReport function");
var client = new Packages.org.apache.commons.httpclient.HttpClient();
////Set Proxy if (if there is one)
if (this.useProxy && this.useProxy.toLowerCase() == "true"){
client.getHostConfiguration().setProxy(this.proxyHost, this.proxyPort);
if (this.proxyUser){
client.getState().setProxyCredentials(new Packages.org.apache.commons.httpclient.auth.AuthScope(this.proxyHost, this.proxyPort), new Packages.org.apache.commons.httpclient.UsernamePasswordCredentials(this.proxyUser, this.proxyPass));
}
}

client.getState().setCredentials (new Packages.org.apache.commons.httpclient.auth.AuthScope(null, 443, null), new Packages.org.apache.commons.httpclient.UsernamePasswordCredentials(this.user, this.password));

var method = new Packages.org.apache.commons.httpclient.methods.GetMethod(this.reportURL);
method.setDoAuthentication(true);
try{
var statusCode = client.executeMethod(method);

if (statusCode != Packages.org.apache.commons.httpclient.HttpStatus.SC_OK) {
this.log("Method failed: " + method.getStatusLine());
}

// Read the response body.
var responseBody = method.getResponseBodyAsString();
//this.log("responseBody: " +responseBody);

if (this.stripHeader == 'true'){
this.log('Removing Report Header');
var start = responseBody.indexOf('\n');
responseBody = responseBody.substr(start);
}
if (this.stripQuotes == 'true'){
this.log('Removing Quotations from Report Body');
responseBody = responseBody.replace(/['"]/g,'');
}
}
catch(e){
this.log("Exception caught in SNDataRetriever->getReport: " + e.getMessage());
}

method.releaseConnection();
this.log("Releasing Connection");
return responseBody;

},

getLog: function() {
return this.resLog;
},

log: function(data) {
ms.log(data);
this.resLog+="\n"+data;
},

isANumber: function(data) {
data = data + '';
return ((data - 0) == data && data.length > 0);
},


execute: function() {
this.log("Running SNDataRetriever execute");
var reportContents = this.getReport();
//this.log('TRANSFORT METHOD:'+ this.transportMethod+this.transportMethod.indexOf('SFTP'));
if (reportContents){
this.saveToFile(reportContents, this.targetPath+ this.targetFileName);
if (this.transportMethod.indexOf('SFTP') != -1 ){
this.copyToSFTP();
}
else if (this.transportMethod.indexOf('FTP') != -1 ){
this.copyToFTP();
}

}
else{
this.log("The report was empty?");
}
},

type: 'SNDataRetriever'
};

[tonywelle]

In addition to the SFTP protocol support I modified the script to:

Set variables for several directories on the (Linux) midserver to support a work directory followed by a success or failed sub-directory to move the files to after processing. It's easier to setup monitoring to alert me if a file shows up in the failed directory.

Additional parameter to strip the file header
Additional parameter to strip double quotes from the file content

Other personalizations...

Enjoy!

[Jacob_Andersen]

I'm actually the person that posted this on the SNC Guru website. I have been meaning to rewrite much of this solution and provide a v2 on the guru site. I would love to test the SFTP ability that you added and merge some of it into my next version. Let me know if that would be fine with you....

Thanks!

[Jacob_Andersen]

I'm actually the person that posted this on the SNC Guru website. I have been meaning to rewrite much of this solution and provide a v2 on the guru site. I would love to test the SFTP ability that you added and merge some of it into my next version. Let me know if that would be fine with you....

Thanks!