Unzip a file using script

Mahesh Mani · ‎01-07-2020

Introduction

In one of the integration, the zip file was sent as a response. Due to the complexity of data I had to parse it manually and cannot use Data source. When I searched around, cannot find any solution. So I build the below logic to unzip the file using the mid server. Initially, I thought of sending the file to Lambda and unzipping. If I have to connect, I have to use the mid server. So I did the unzipping in the mid server and sending it back. There may be lot of other way to do this, please let me know in case of any better solution.

Implementation details

Please note, the whole process is done asynchronously. I break down into 2 step process because of the size of the data. You can change this to 1 step process and you can modify the code to get the file contents back instead of file names.

Round 1

Once zip file is received, it is attached to a record in table_x(here u_bd_transaction)
Script action will trigger Javascript probe to process the zip file. Attachment data sent as base64 encoded data.
Midserver script include(java based) will get the write the file to file system, unizp and write the content in file system. Javascript probe will return the list of file names and its path.

Round 2

Once the response from the midserver is received, business rule in the ECC queue to trigger separate calls to get the file contents.
Once file content is received, it can be attached to the same record in table_x.

Script action:

//script action. Event name: attachment.uploaded, condition: event.parm1 == 'u_bd_transaction'
var lib = new CMDBDiscoveryHelper();
lib.handleAttachmentsOnBDTransaction(event.parm2, event.instance);

Script include:

var CMDBDiscoveryHelper = Class.create();
CMDBDiscoveryHelper.prototype = Object.extendsObject(AbstractAjaxProcessor, {

    handleAttachmentsOnBDTransaction: function(table_sys_id, attachment_sys_id) {

        var gr = new GlideRecord('sys_attachment');

        if (gr.get(attachment_sys_id)) {

            if (gr.file_name.indexOf(".zip") > -1) {
                var stringUtil = new GlideStringUtil();
                var sa = new GlideSysAttachment();
                var data = stringUtil.base64Encode(sa.getBytes(gr));

                var jspr = new JavascriptProbe(this.getMidServer());
                jspr.setName('FileZipUtil'); //Any descriptive name will do
                jspr.addParameter("verbose", "true");
                jspr.addParameter("encoded_data", data);
                jspr.addParameter("file_name", gr.file_name);
                jspr.addParameter("request_type", "initiate_file_transfer");
                jspr.addParameter("bd_transaction_sys_id", table_sys_id);
                jspr.setJavascript('var connector = new FileZipUtil(); res = connector.execute();');
                jspr.addParameter("skip_sensor", true);
                jspr.create();

                var u_bd_transaction = this.getRecordForInsertorUpdate('u_bd_transaction', "sys_id=" + table_sys_id);
                u_bd_transaction.u_status = 'Unzip request submitted';
                u_bd_transaction.u_work_notes = "Unzip request submitted";
                this.handleInsertOrUpdate(u_bd_transaction);
            } else {
                gs.log('Triggering handleBDIntegration ' + attachment_sys_id, 'CMDBDiscoveryHelper');

                this.handleBDIntegration(attachment_sys_id, table_sys_id);

                var u_bd_transaction = this.getRecordForInsertorUpdate('u_bd_transaction', "sys_id=" + table_sys_id);
                u_bd_transaction.u_status = 'XML Processing Started';
                u_bd_transaction.u_work_notes = "Processing XML: " + gr.file_name;
                this.handleInsertOrUpdate(u_bd_transaction);
            }
        }
    },


    handleInboundXMLFile: function() {
        try {
            var xmlObj = this.getPayload(current.sys_id);

            var json_result = JSON.parse(xmlObj.results.result.output);

            //gs.log(json_result, "CMDBDiscoveryHelper");
            var u_bd_transaction = this.getRecordForInsertorUpdate('u_bd_transaction', "sys_id=" + json_result.bd_transaction_sys_id);
            for (var key in json_result.files) {
                var file_obj = json_result.files[key];
                gs.log(file_obj.file_name, "CMDBDiscoveryHelper");
                if (json_result.request_type == "initiate_file_transfer") {

                    var jspr = new JavascriptProbe(this.getMidServer());
                    jspr.setName('FileZipUtil'); //Any descriptive name will do
                    jspr.addParameter("verbose", "true");
                    jspr.addParameter("file_name", file_obj.file_name);
                    jspr.addParameter("requested_file_path", file_obj.file_path);
                    jspr.addParameter("request_type", "get_file_content");
                    jspr.addParameter("bd_transaction_sys_id", json_result.bd_transaction_sys_id);
                    jspr.setJavascript('var connector = new FileZipUtil(); res = connector.execute();');
                    jspr.addParameter("skip_sensor", true);
                    jspr.create();


                    u_bd_transaction.u_work_notes = "XML File: " + file_obj.file_name;
                    this.handleInsertOrUpdate(u_bd_transaction);
                } else if (json_result.request_type == "get_file_content") {

                    u_bd_transaction.u_work_notes = "Saving XML File: " + file_obj.file_name;
                    this.handleInsertOrUpdate(u_bd_transaction);
                    var sa = new GlideSysAttachment();
                    sa.write(u_bd_transaction, file_obj.file_name, 'text/xml', GlideStringUtil.base64DecodeAsBytes(file_obj.file_content));

                }
            }

            this.handleInsertOrUpdate(u_bd_transaction);
        } catch (ex) {
            this.handleExceptionMessage('Error occured', ex);
            //this.createOrUpdateIncidentRecord("Error in processing ZIP File", "Error in processing ZIP File", "CMDBDiscoveryHelper");
        }
    },

    getPayload: function(ecc_sys_id) {
        var xmlObj = {};
        var gr = new GlideRecord('ecc_queue');
        if (gr.get(ecc_sys_id)) {

            var payload = gr.payload + '';
            if (payload == "<see_attachment/>") {
                var sa = new GlideSysAttachment();
                payload = sa.get(gr, 'payload');
            }


        }

        return gs.xmlToJSON(payload);
    },




    getMidServer: function() {
        var gr = new GlideRecord('ecc_agent');

        if (gs.getProperty('instance_name') == "mycompanydev" || gs.getProperty('instance_name') == "mycompanyqa")
            gr.addEncodedQuery('host_name=hostname.compamyname.com');
        else
            gr.addEncodedQuery("u_functions=WINAD");

        gr.orderByDesc('status');
        gr.orderByDesc('stopped');
        gr.query();

        if (gr.next()) {
            return gr.name;
        }
        return '';
    },
	

	
	getRowCount: function(table_name, encoded_query) {
        var count = new GlideAggregate(table_name);
        count.addEncodedQuery(encoded_query);
        count.addAggregate('COUNT');
        count.query();
        var result = 0;
        if (count.next())
            result = count.getAggregate('COUNT');

        return result;
    },


    getRecordForInsertorUpdate: function(table_name, encoded_query) {

        var gr = new GlideRecord(table_name);
        gr.addEncodedQuery(encoded_query);
        gr.setLimit(1);
        gr.query();

        if (gr.next()) {
            return gr;
        } else {
            gr.newRecord(); // create a new record and populate it with default values
            return gr;
        }
    },

    handleInsertOrUpdate: function(gr) {

        if (gr.isNewRecord()) {
            gr.insert();
        } else {
            gr.update();
        }
    },

handleExceptionMessage: function(textMessage, exception) {
        gs.addInfoMessage(textMessage + exception);
        gs.error(textMessage + exception.stack, 'CMDBDiscoveryHelper');
    },

    type: 'CMDBDiscoveryHelper'
});

Midserver script include:

var FileZipUtil = Class.create();

FileZipUtil.prototype = {

    File: Packages.java.io.File,
    FileInputStream: Packages.java.io.FileInputStream,
    FileOutputStream: Packages.java.io.FileOutputStream,
    ZipEntry: Packages.java.util.zip.ZipEntry,
    ZipInputStream: Packages.java.util.zip.ZipInputStream,
    JavaString: Packages.java.lang.String,
    Base64: Packages.org.apache.commons.codec.binary.Base64,
    FileUtils: Packages.org.apache.commons.io.FileUtils,

    initialize: function() {

        this.verbose = probe.getParameter("verbose");
        this.file_name = probe.getParameter("file_name");
        this.encoded_data = probe.getParameter("encoded_data");
        this.request_type = probe.getParameter("request_type");
        this.requested_file_path = probe.getParameter("requested_file_path");
        this.result = {
            messages: [],
            files: [],
            bd_transaction_sys_id: probe.getParameter("bd_transaction_sys_id"),
            request_type: this.request_type
        };

    },

    execute: function() {
        try {
            this.debug("here is the debug message " + this.request_type);

            if (this.request_type == "initiate_file_transfer") {
                var destDir = new this.File("." + this.File.separator + "BD_processor");
                if (!destDir.exists())
                    destDir.mkdir();

                var data = this.Base64.decodeBase64(this.encoded_data);

                var stream = new this.FileOutputStream(destDir.getCanonicalPath() + this.File.separator + this.file_name);
                stream.write(data);
                stream.close();

                var fileZip = destDir.getCanonicalPath() + this.File.separator + this.file_name;
                var empty_str = new Packages.java.lang.String("test");
                var buffer = empty_str.getBytes();
                var zis = new this.ZipInputStream(new this.FileInputStream(fileZip));
                var zipEntry = zis.getNextEntry();
                while (zipEntry != null) {
                    if (zipEntry.getName().indexOf(".xml") > -1) {
                        var newFile = this.newFile(destDir, zipEntry);
                        var fos = new this.FileOutputStream(newFile);
                        var len;
                        while ((len = zis.read(buffer)) > 0) {
                            fos.write(buffer, 0, len);
                        }
                        fos.close();

                        var zip_file = new this.File(destDir, zipEntry.getName());
                        //var fileContent = this.FileUtils.readFileToByteArray(zip_file);
                        //var encodedString = this.Base64.encodeBase64String(fileContent);
                        this.result.files.push({
                            file_name: zipEntry.getName(),
                            file_path: zip_file.getCanonicalPath()
                        });
                    }
                    zipEntry = zis.getNextEntry();
                }
                zis.closeEntry();
                zis.close();

                //var downloaded_zip = new this.File(destDir.getCanonicalPath() + this.File.separator + this.file_name);
                //downloaded_zip.delete();

            } else if (this.request_type == "get_file_content") {
                var zip_file = new this.File(this.requested_file_path);
                var fileContent = this.FileUtils.readFileToByteArray(zip_file);
                var encodedString = this.Base64.encodeBase64String(fileContent);
                this.result.files.push({
                    file_name: this.file_name,
                    file_content: encodedString
                });

                //zip_file.delete();
            }



        } catch (e) {
            this.debug(e.toString());
        }
        return JSON.stringify(this.result);
    },

    newFile: function(destinationDir, zipEntry) {
        var destFile = new this.File(destinationDir, zipEntry.getName());

        return destFile;
    },


    debug: function(m) {
        if (this.verbose == "true") {
            ms.log(m);
            this.result.messages.push(m);
        }
    },

    type: 'FileZipUtil'
};

Business rule:

//business rule: table: ecc_queue, When: asynch, condition: topic=JavascriptProbe^queue=input^name=FileZipUtil
var library = new CMDBDiscoveryHelper();
	library.handleInboundXMLFile();

kml1 · ‎06-25-2020

Mahesh,

Very nice write-up. I have a situation and not sure if the above situation will work. Can you advise? In my case, I am getting a twice zipped file from an API integration. I am checking why the source system has it zipped twice. I can use a data source to process the zip file but I need to extract it at least once to get to that zipped file. What would I need to adjust in the script above to handle that?

Mahesh Mani · ‎06-25-2020

Hi, you can change the trigger condition. In this example, it is triggered once a file attached(using event). In your case, you can have a scheduled job to check for files created in the last 15 mins and you can choose which file to process and then trigger the unzip function for that file.

kml1 · ‎06-25-2020

Hi Mahesh,

Thanks for the suggestion will check that...I still have a question on the code above. The file I am getting is zipped twice...so I just want it unzipped one level. Basically they put a zipped file into another zipped file and I need to at least unzip one level and then let the data source do the other unzip and transform. Think that will work?

Mahesh Mani · ‎06-25-2020

Yes, that should work, some modification is needed. If you call the handleAttachmentsOnBDTransaction function, it will process and ECC response will be list of files in it. Now write a BR on ECC queue to read the content of the file(get_file_content), in your case, the response is another zip file. You can save the content and pass that to data source.

kml1 · ‎06-25-2020

Thanks Mahesh. I will get start on setting this up and let you know how it goes. Thanks for everything!

kml1 · ‎06-25-2020

Mahesh,

Getting an error on line 85 of MID server script:

Could not save record because of a compile error: JavaScript parse error at line (85) column (32) problem = missing name after . operator (<refname>; line 85). Doesn't seem to like:

zip_file.delete();

When I comment that out it saves. Thoughts?

Mahesh Mani · ‎06-25-2020

Yes, you can comment it out.

Singletary · ‎01-28-2021

I want to encourage that you continue your great work, have a nice day!

Upsers

nguyen van luon · ‎02-02-2021

I have requirement from customer as below:

1. Sent email from someone to Servicenow instance (mail of servicenow: abc@service-now.com), mail has attachment file has been encrypted and set password (zip file), files in encrypted file is one or some excel file.

2. Do decrypt that attachment file, after that import data of excel file to table of Servicenow to manage (myself table)

Before that, with simple requirement is attachment file is only excel file then I have done by create Import set table, Tranform map and move file from sys_attachment to Data source and execute Transform to Servicenow table.

But with this difficult requirement (encrypted attachment zip file) I do not now solution to do this.

I am thinking maybe I have to use Mid serve to save file to Mid server and decrypt, after that call FLow designer to get decrypted file and import to table. But due to have no much experience so that I could not clear what I have to do.

Could you have me detail solution or give me idea for this requirement.

Thank you so much!.

nguyen van luon · ‎02-04-2021

Dear Mahesh Mani

Now I have task is same what you posted.

I want to ask that I need to build one Mid server (vitual machine) for this solution?

Because I have tried to use you sample code and apply for my instance, but It was not successful, It could not run. So I am worry about need to build Mid server or not.

And further, Deail of my task is below that I have posted. If it is possible, could you confirm and give me you idea?

************

I have requirement from customer as below:

1. Sent email from someone to Servicenow instance (mail of servicenow: abc@service-now.com), mail has attachment file has been encrypted and set password (zip file), files in encrypted file is one or some excel file.

2. Do decrypt that attachment file, after that import data of excel file to table of Servicenow to manage (myself table)

Before that, with simple requirement is attachment file is only excel file then I have done by create Import set table, Tranform map and move file from sys_attachment to Data source and execute Transform to Servicenow table.

But with this difficult requirement (encrypted attachment zip file) I do not now solution to do this.

I am thinking maybe I have to use Mid serve to save file to Mid server and decrypt, after that call FLow designer to get decrypted file and import to table. But due to have no much experience so that I could not clear what I have to do.

Could you have me detail solution or give me idea for this requirement.

Thank you so much!.

Mahesh Mani · ‎02-04-2021

Hi Nguyen, yes you need midserver to process this. To decrypt, you may need to modify the java code in FileZipUtil midserver script include. I never tried to decrypt in java.

You can also try with python, you need to install python in the midserver and use command line execution. You need to write a small script to get the base64 format for file from command, write to location midserver folder, decrypt, then print decrypted file as base64. Then you can process the Ecc response to write it to attachments.

nikeshnikky · ‎07-29-2021

Hello @Mahesh Mani

Thanks for the write-up, I have a similar kind of scenario, where user attaches the zip file ( which may contain more than 20 csv(s) in it) we need to read the zip and attach the CSV's to corresponding records.

I tried your solution with mid server, the issue am facing here is as per the belwo screenshot, it is not picking the file names in the ecc q payload. Post this i dont think script is moving forward in my scenarip. Please help me understand what am missing here,

Thanks in advance

Nikesh

Mahesh Mani · ‎02-14-2023

I didn't see the edit button. So created github with updateset - https://github.com/maheshmani-me/SNFileUnzipUtil