How to send attachment files to MID Server

Mussie
ServiceNow Employee
ServiceNow Employee

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.

find_real_file.png

Regards,

Mussie

1 ACCEPTED SOLUTION

Mussie
ServiceNow Employee
ServiceNow Employee

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:


find_real_file.png



Thanks,


Mussie


View solution in original post

11 REPLIES 11

naveenmenon
Giga Contributor

HI Mussie,



Not sure if you've tried something like this.



Exporting Data - ServiceNow Wiki (Calling URL Programatically)



Cheers,


Naveen



Calling URL Exports Programmatically

Mussie
ServiceNow Employee
ServiceNow Employee

Thanks for the reply Naveen, no I didn't. I haven't come across your suggested method so far and can't afford to spent anytime on it as I have to finish this in the next hour or two. But I have however created the script include which I found here, but I have no clue how to use this. Any ideas?


Please use below code to send file to mid server



Send an Attachment from Service Now and store it on FTP server via MID server | Gyan'o'Mania



Regards,


Sachin


Mussie
ServiceNow Employee
ServiceNow Employee

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:


find_real_file.png



Thanks,


Mussie