Export a record attachment to a mid server's export folder

Awesome!! that was really helpful for me!
I used it as a base to adapt it to my requirement

Thanks for this code. I have a requirement where we have to create a CSV file and attach it with a change record. We need to export this file to MID server. Can you tell me how to achieve this one utilising the above script?

I would have to know more about your requirement. However if I am understanding, you could write a business rule based on the change being approved or in some state, checking for attachments to the change and then executing the above function to initiate the file transfer to a mid server.

Thanks.

Can we define a specific folder path that is present in MID server in the payload or the ecc_queue record?

Hi @johnstettin 

This is a bit new to me, I think I'm completely overlooking where I need to specify my attachment name, table, sys ID and MID...

Could you kindly give me a kick in the right direction please?

Cheers

Taylor

Hi TJ,

To answer your question I would need to know more about how you are trying to use it. Normally the code would be put in a script include or directly into from a script block. In the latter; you can paste the function into your script block (Server Side), and utilize the "usage" example above for the format (substituting your instance values for those in the example above).

Hi @johnstettin 

What is the default Midserver path that the file will be written to ?

I have given a specific export path but I still do not see any file written to the midserver.

I see an entry in ECC Queue, but the file is not being written into the Midserver.

Thank You.

Hi, I just set this up and it works.

To answer your query, I had to create an 'export' folder within the 'agent' folder. Once I did this the file was written to the 'export' folder as expected.

Geoff

This article works like a charm .

One struggle we had is to check in MID Server where the file is located.

The way to validate the successful File Transfer

>> Go to Export >> folder which you have specified while transferring the file in script.

Note: - We were able to transfer both .pdf and .txt file type

>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>

// Create ECC attachment record used to send the file to the mid server

    var attachment_name = "Complete.txt"; <replace with your file name>

    var mid_name = "MID_Server_SCG_BIC_DEV"; < replace with your MID server name>

    //var ecc_att = new GlideRecord('ecc_agent_attachment');

    //ecc_att.initialize();

    //ecc_att.name = 'Export Set Attachment';

    //ecc_att.short_description = 'exportToMid: ' + attachment_name;

    //ecc_att.insert();

    // Copy attachments (OOB Function copies all attachments)

    //GlideSysAttachment.copy(attached_table,attached_record,'ecc_agent_attachment',ecc_att.sys_id);

    // Get the SYS_ID of the exact attachment file to be exported and used in ECC Payload

    var at = new GlideRecord('sys_attachment');

    at.addQuery('table_name', 'ecc_agent_attachment');

    at.addQuery('table_sys_id', <sys_id_of_ecc_agent_attachment_record>);

    //at.addQuery('file_name', attachment_name);

    at.query();

    at.next();

    //gs.info('>>>> PRINT SYS ID>>'+at.getUniqueValue());

    var export_folder = 'vim/ivbarcode'; < replace with the file path that you wish to create>

    // Create XML for ECC Payload

    var xmlString = '<?xml version="1.0" encoding="UTF-8"?>' +

                    '<parameters>' +

                    '<parameter name=\"stream_relay_response_topic\" value=\"ExportSetResult\"/>' +

                    '<stream_relay_source attachment_sys_id=\"' + at.sys_id + '\" type=\"AttachmentSource\"/>' +

                    '<stream_relay_transform attachment.table_sys_id=\"' + "48279b7adb01a11050bea0bdd39619c3" + '\" order=\"0\" stream_relay_transfer_progress_interval=\"150\" type=\"AttachmentProgressTransformer\"/>' +

                    '<stream_relay_sink path="\/' + export_folder + '\/' + attachment_name + '" type=\"FileSink\"/>' +

                    '</parameters>';

    // Create ECC Record

    var eccQueue = new GlideRecord('ecc_queue');

    eccQueue.initialize();

    eccQueue.agent = 'mid.server.' + mid_name;

    eccQueue.topic = 'StreamPipeline';

    eccQueue.queue = 'output';

    eccQueue.state = 'ready';

    eccQueue.payload = xmlString;

    eccQueue.insert();

>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>

Hi @johnstettin ,

Thank you for the great article, but could you tell me where to write this script?

I checked the scheduled data export table, but did not understand where to define the script.

Best Regards,

Aki

@Aki18 depends on the use case. For example I added the script to a flow designer action script step; since my use case was to transfer an attachment as part of a flow. What is your use case?

@Geoff_T ,

Thank you for your reply. My use case is like export record attachments to MID server on a daily basis and also on-demand.

One of my idea is to use Background script, but do you have any other recommendations?

@Aki18 you could use flow designer that could be set up to execute on schedule or on demand. Create a custom action with a script step to host the sample script from this article. That would be a good approach. Give it a shot and if you are having real difficulties post a new question on the forum and I can help.

I've managed to get this working using the following solution in Flow Designer

Build the attachment in an action

Inputs and map those into the same inputs on the script step

(function execute(inputs, outputs) {
  
var attach = new GlideSysAttachment();

//set up inputs
var rec = new GlideRecord(inputs.tableName);
rec.get(inputs.recordSysID);
  
var fileName = inputs.fileName;
var contentType = inputs.contentType;
var content = inputs.content;

var agr = attach.write(rec, fileName, contentType, content);
outputs.attachment = agr;
})(inputs, outputs);

Outputs > again map that from script step

Send CSV to MID

Subflow that takes the file name from the above as an input. Look up an attachment record that matches the attachment created.

Script step to send CSV to MID server

(function execute(inputs, outputs) {
// ... code ...
  
var gr = GlideRecord('sys_attachment');
gr.addQuery('sys_id', inputs.csv); //sysid of attachment
gr.query();
if (gr.next())
{
var sa = new GlideSysAttachment();
var binData = sa.getBytes(gr);
var encData =GlideStringUtil.base64Encode(binData);
var jspr = new JavascriptProbe('MIDSERVERNAME');
jspr.setName('ADGroupRequest'); // This can be any name 
jspr.setJavascript("var ddr = new ADAttachment(); res= ddr.execute();");
jspr.addParameter("verbose","true");
jspr.addParameter("filepath", 'C://ServiceNow//MID Server DEV//agent//csvs'); 
jspr.addParameter("filename",gr.file_name);
jspr.addParameter("encodedData",encData);
jspr.create();
}
})(inputs, outputs);

Hello @johnstettin 

Your script looks great to me. I am trying to see if I can implement your code for my business requirements. I try to script export the records with an attachment to xml. I'm thinking to myself how to extract the attachment out of xml? Also, the business wants to put those files into a network-shared folder. I like your idea to put those files on mid-server first. I would love to have your input on this or anyone else who commented below. Thanks 

Hello @johnstettin 

My csv file is not transfered to the mid server and the topic ExportSetResult stays in ready state and never change, can you or anyone help me know how to manage this pleaase ? (i used the exacte script in a scheduled job script and un click execute now to test immediatly)

thank you in advance !

@SnowDevOps 
The attachments should be Base64 encoded and could be parsed and re-assembled. You should be able to extract the attachment from the xml and then send it to a network share via something like powershell.  Or it might be even easier to have a flow execute on the mid server that could accomplish the same thing (depending on your SN FD licensing).

@zineberrada 
Its a bit difficult to debug without knowing more but, it sounds like the mid server isn't picking up the ECC record. Double check that your specifying a validated and correct mid server. 

Hello @johnstettin,

Thank you so much for your reply, i really appreciate it, actually i wasn't checking the correct path because initialy i was defining a custom path here : 

xml += '<stream_relay_sink path="/FTP/BSS/' + filename + '" type="FileSink"/>';

However, I found that the default path is actually /agent/export. I discovered this by running the following Linux command : 

find / -name *Extracted*

Additionally,  the ExportSetResult topic always stays in the "ready" state,  due to the following parameter:  

xml += '<parameter name="skip_sensor" value="true"/>';