Create an incident/notify when a file transfer fails from MID server to SFTP server

[vdsvds]

Hey folks, 

I have a requirement to create an incident whenever an error occurs while transferring a .csv file from MID server to an SFTP server which is in a different network. I am using Export sets functionality and MID server script include to transfer the file. 

I have taken this article as a reference material for the development :

https://www.servicenow.com/community/in-other-news/using-export-sets-for-sftp-file-transfer/ba-p/228...

I have tested the file transfer and it was successful, however I'm finding it hard to create incidents when I tried this method below. Please let me know If there's a way to create incidents. I also understand this transfer will be encrypted till it reaches the sftp server (while in transit). I'm attaching few blocks of code for you guys to understand:

fileTransfer: function() {
        // transferring the file to the sftp server
        var shortDescription = '';
        var description = '';

        try {
            var localFileName = this.MIDSERVER_FILE_PATH + '/' + this.MIDSERVER_FILE_NAME;
            var remoteFileName = this.targetPath + '/' + this.MIDSERVER_FILE_NAME;
            this.log("Copying from local file of MID Server: " + localFileName);
            this.sftpFile(this.targetServer, this.targetUsername, this.targetPassword, localFileName, remoteFileName);
        } catch (e) {
            this.log("Error in writing file to SFTP server: " + e);
            shortDescription = 'Error in writing file to SFTP server';
            description = 'An error occured while attempting to write the file to the SFTP server: ' + this.targetServer + ' with an error: \n\n' + e;
            this.createIncident(shortDescription, description);
            this.log("Created an Incident for the error encountered");
        }
    },


    sftpFile: function(hostName, userName, password, localFileName, remoteFileName) {
        var shortDescription = '';
        var description = '';


        this.log('sftpFile(): attempting to connect to ' + hostName);
        var ssh = new Packages.com.sshtools.j2ssh.SshClient();
        var ignoreHost = new Packages.com.sshtools.j2ssh.transport.IgnoreHostKeyVerification();
        if (!this.targetPort) {
            this.targetPort = 22;
        }
        this.log('sftpFile(): attempting to connect to ' + hostName + " on port " + this.targetPort);

        try {
            ssh.connect(hostName, this.targetPort, ignoreHost);
            this.log("Connected to the host successfully");
        } catch (e) {
            this.log('Connection failed to the host ' + e);
            shortDescription = 'Connection failed to the host';
            description = 'An error occured while attempting to connect to the server: ' + hostName + ' with an error: \n\n' + e;
            this.createIncident(shortDescription, description);
            this.log("Created an Incident for the error encountered");
        }

        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);
        try {
            if (ssh.authenticate(pwd) == new Packages.com.sshtools.j2ssh.authentication.AuthenticationProtocolState().COMPLETE) {
                try {
                    sftp = ssh.openSftpClient();
                    this.log('Connection Established to the client');
                } catch (e) {
                    this.log('Unable to connect to the client ' + e);
                    shortDescription = 'Unable to connect to the client';
                    description = 'An error occured while establishing a connection to the client with an error: \n\n' + e;
                    this.createIncident(shortDescription, description);
                    this.log("Created an Incident for the error encountered");
                }
                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);
                    shortDescription = 'File not found error';
                    description = 'An error occured while attempting to copy file from MID server: ' + localFileName + ' to target path of the SFTP server' + remoteFileName + ' with an error: \n\n' + e;
                    this.createIncident(shortDescription, description);
                    this.log("Created an Incident for the error encountered");
                }
                sftp.quit();

                try {
                    // kill connection
                    ssh.disconnect();
                } catch (e) {
                    this.log('Manual connection kill not successful with error: ' + e);
                }
            }
        } catch (e) {
            this.log('User authentication Failed ' + e);
            shortDescription = 'User uthentication Failed';
            description = 'An error occured while authenticating with the server: ' + hostName + ' with an error: \n\n' + e;
            this.createIncident(shortDescription, description);
            this.log("Created an Incident for the error encountered");

Please let me know your inputs,

Thank you,

Suhail

[Ratnakar7]

Hi @vdsvds ,

You can modify the sftpFile function as follows:

sftpFile: function(hostName, userName, password, localFileName, remoteFileName) {
    var shortDescription = '';
    var description = '';

    this.log('sftpFile(): attempting to connect to ' + hostName);
    var ssh = new Packages.com.sshtools.j2ssh.SshClient();
    var ignoreHost = new Packages.com.sshtools.j2ssh.transport.IgnoreHostKeyVerification();
    if (!this.targetPort) {
        this.targetPort = 22;
    }
    this.log('sftpFile(): attempting to connect to ' + hostName + " on port " + this.targetPort);

    try {
        ssh.connect(hostName, this.targetPort, ignoreHost);
        this.log("Connected to the host successfully");
    } catch (e) {
        this.log('Connection failed to the host ' + e);
        shortDescription = 'Connection failed to the host';
        description = 'An error occurred while attempting to connect to the server: ' + hostName + ' with an error: \n\n' + e;
        this.createIncident(shortDescription, description);
        this.log("Created an Incident for the error encountered");
        return; // return if connection fails to the host
    }

    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);
    try {
        if (ssh.authenticate(pwd) == new Packages.com.sshtools.j2ssh.authentication.AuthenticationProtocolState().COMPLETE) {
            try {
                sftp = ssh.openSftpClient();
                this.log('Connection Established to the client');
            } catch (e) {
                this.log('Unable to connect to the client ' + e);
                shortDescription = 'Unable to connect to the client';
                description = 'An error occurred while establishing a connection to the client with an error: \n\n' + e;
                this.createIncident(shortDescription, description);
                this.log("Created an Incident for the error encountered");
                return; // return if unable to connect to the client
            }
            try {
                sftp.put(localFileName, remoteFileName);
                this.log("File successfully copied to target 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);
                shortDescription = 'File not found error';
                description = 'An error occurred while attempting to copy file from MID server: ' + localFileName + ' to target path of the SFTP server ' + remoteFileName + ' with an error: \n\n' + e;
                this.createIncident(shortDescription, description);
                this.log("Created an Incident for the error encountered");
                return; // return if file not found or error occurs
            }
            sftp.quit();

            try {
                // kill connection
                ssh.disconnect();
            } catch (e) {
                this.log('Manual connection kill not successful with error: ' + e);
            }
        }
    } catch (e) {
        this.log('User authentication Failed ' + e);
        shortDescription = 'User authentication

If my response helps you to resolve the issue close the question by ✅Accepting solution and hit 👍thumb icon. From Correct answers others will get benefited in future.

Thanks,

Ratnakar

[vdsvds]

I'm sorry, I just checked back and found that incidents were being created but without any details except for the callers name, that is, the mid server user as the caller. When I checked the MID server agent logs It shows this error in the below attached screen shot

Is there a way to enable read and write ACL's for mid server user.

[Ratnakar7 · Accepted solution]

Hi @vdsvds ,

Yes, 

Here are the steps:

1. Navigate to "System Security" -> "Roles" in the ServiceNow instance.

2. Locate the "mid_server" role and click on it to open the record.

3. In the "Role" form, scroll down to the "Access Controls" related list.

4. In the "Access Controls" related list, click on the "New" button to create a new access control record.

5. In the "Access Control" form, enter the following values:

   • Operation: read
   • Type: record
   • Table: [table where the file transfer logs are stored]
   • Condition: [condition to limit access to specific records, if necessary]
   • Roles: mid_server

6. Click on "Submit" to save the new access control record.

Repeat steps 4-6 to create another access control record for the "write" operation if necessary.

If my response helps you to resolve the issue close the question by ✅Accepting solution and hit 👍thumb icon. From Correct answers others will get benefited in future.

Thanks,

Ratnakar

[vdsvds]

I tried your approach and it worked well, however 'Access Controls' related list would not always be available as it wasn't for me. I just went to system Security -> Access Controls (ACLS) and created read and write rules for incident table for mid_server user. I tried unit testing for all the error messages and it incidents got generated. This was a good break-through. Thank you for helping out.