Hi Guys,
Does anybody know how to send a file to MID Server via a script? The original requirement was to truncate the header row of the CSV file before the file is sent via an export set. We were able to remove the first row and have it attached either to the original ecc_agent_attachment or to a new record. Either way, the file is not being sent to MID Server. It seems that files will only be passed to MID server when they are triggered from an actual export set not when a record is updated/created in the export set table (ecc_agent_attachment) via a script. So, we are stuck in a situation where we have the file in a proper format the client wanted to have but we couldn't find a way of sending it to MID server.
Here is what the code looks like:
var data_array = [];
var table_sys_id = current.sys_id;
var attachmentGR = new GlideRecord("sys_attachment");
attachmentGR.addQuery('table_name', 'ecc_agent_attachment');
attachmentGR.addQuery('content_type', 'test/csv');
attachmentGR.addQuery('file_name', 'STARTSWITH', '1');
attachmentGR.addQuery('table_sys_id', table_sys_id);
attachmentGR.query();
if(attachmentGR.next()){
var stu = new GlideStringUtil();
var gsa = new GlideSysAttachment();
//Get the raw bytes in the file
var bytesInFile = gsa.getBytes(attachmentGR);
var dataAsString = Packages.java.lang.String(bytesInFile);
dataAsString = String(dataAsString);
data_array = dataAsString.split("\n");
var newArray =[];
for(var i = 1; i<data_array.length-1; i++) {
newArray.push(data_array[i]+ "\n" + "*");
}
var strArr = String(newArray);
gs.print("StringData1 - "+strArr );
strArr = strArr.replaceAll("*," , '').replace("*",'');
gs.print("StringData2 - "+strArr );
var stringUtil = new GlideStringUtil();
//var base64EncodeString = stringUtil.base64Encode(strArr);
//create a new record and attach the new file
var midServerAttachGR = new GlideRecord('ecc_agent_attachment');
midServerAttachGR.initialize();
midServerAttachGR.name = 'Export Set Attachment';
midServerAttachGR.short_description = 'Updated attachment';
midServerAttachGR.source = 'a6d9e79f4f0376007861a90f0310c772';
var new_table_sys_id = midServerAttachGR.insert();
var attachment = new Attachment();
var attachment_sys_id = attachment.write("ecc_agent_attachment", new_table_sys_id, "22222222.csv", "csv", strArr);
}
But even create a new ecc_agent_attachment record doesn't trigger the file to be sent to the MID server.
Regards,
Mussie
I was able to resolve this by leveraging the MID Server Script Files module.
Here is the steps I followed:
Go to MID Server —> MID Server Script Files
Create New
Give it any name you like and click save
Then on my BR code, once I have removed the header row stripped from the attachment, I directly write the contents of the attachment on the script. This will automatically send the file to MID server, however the files will be stored on agent/scripts.
Here is what the BR code looks like:
Table - ecc_agent_attachment
When - async
Runs - on Insert
condition - current.source == gs.getProperty('pp_exportset_sysid')
(function executeRule(current, previous /*null when async*/) {
gs.sleep(10000); //wait for the related attachment to be created
var data_array = [];
var table_sys_id = current.sys_id;
//get the details of the attachment created via the export set
var attachmentGR = new GlideRecord("sys_attachment");
attachmentGR.addQuery('table_name', 'ecc_agent_attachment');
attachmentGR.addQuery('content_type', 'test/csv');
attachmentGR.addQuery('file_name', 'parking_permit_raw.csv');
attachmentGR.addQuery('table_sys_id', table_sys_id);
attachmentGR.query();
if(attachmentGR.next()){
gs.log('file name is ' + attachmentGR.file_name);
//if available, strip off the header row from the csv file
var stu = new GlideStringUtil();
var gsa = new GlideSysAttachment();
//Get the raw bytes in the file
var bytesInFile = gsa.getBytes(attachmentGR);
var dataAsString = Packages.java.lang.String(bytesInFile);
dataAsString = String(dataAsString);
data_array = dataAsString.split("\n");
var newArray =[];
for(var i = 1; i<data_array.length-1; i++) {
newArray.push(data_array[i]+ "\n" + "*");
}
var strArr = String(newArray); //strArr contains the string format of the csv without the header row
gs.print("StringData1 - "+strArr );
strArr = strArr.replaceAll("*," , '').replace("*",'');
gs.print("StringData2 - "+strArr );
var stringUtil = new GlideStringUtil();
var base64EncodeString = stringUtil.base64Encode(strArr);
//file name needs to have a time stamp in a format of 1-YYYYMMDD-hhmmss.csv
var gdt = new GlideDateTime();
gdt.setValue(gs.nowDateTime());
gdt.getLocalDate();
var execDateTime = gdt.toString();
execDateTime = execDateTime.replace('-', '');
execDateTime = execDateTime.replace(':', '');
execDateTime = execDateTime.replace(' ', '');
var execDate = execDateTime.substring(0,7);
var execTime = execDateTime.substring(8);
var fileName = '1-'+ execDate + '-' + execTime +'.csv';
var record_sys_id = gs.getProperty('pp_mid_server_script_file_sysid');
//update the corresponding MID Server script file with the content of the stripped attachment
var midServerAttachGR = new GlideRecord('ecc_agent_script_file');
if (midServerAttachGR.get(record_sys_id)){
midServerAttachGR.script = strArr;
midServerAttachGR.name = fileName;
midServerAttachGR.update();
}
}
})(current, previous);
Here is what the MID Server script file looks like:
Thanks,
Mussie
