I recently had a requirement where SN would have to retrieve a pdf file located on an on-prem network share and attach it to a record. SN has a nice KB article that very honestly explains "You can export a file to a mid server, but there is no out of the box feature to import a file from the MID server" and then proceeds to lay out some workarounds. I ended up using workaround 3b which leverages the MID server command probe.
Here's the 30,000 foot view:
- Business Rule (script include) on table you want file attached to creates MID server command probe (ecc_queue record).
- MID server runs a powershell script which gets the file and returns it Base64 encoded.
- Business Rule (script include) on ecc_queue table parses response from Powershell script and attaches file to record in step 1.
I put the most of the pieces in their own scoped application
Business Rule to Create Probe
In my case, the originating table is a custom table for purchase order records. The below BR calls a script include which creates the probe. The BR runs async.
(function executeRule(current, previous /*null when async*/) {
var att = new x_xxxx_att.AttachmentXXUtils();
var filename = 'S' + current.orderno.getDisplayValue() + '.Pdf';
att.getFileBase64('\\\\SERVERNAME\\achpo\\supl\\',filename,current.getUniqueValue(),current.getTableName());
})(current, previous);Here is the full script include, the above BR calls the getFileBase64 function:
var AttachmentXXUtils = Class.create();
AttachmentXXUtils.prototype = {
initialize: function() {
},
getFileBase64: function(directory,filename,id,tb){
//directory = the directory the file is stored in
//filename = the name of the file (with extension)
//id = the sysId of the record the file will eventually be attached to
//tb = the tablename for the record the file will be attached to.
//Adds a record to the ecc_queue table which runs getFileBase64.ps1 on the MID server
//See this KB article https://support.servicenow.com/kb?id=kb_article_view&sysparm_article=KB0858504
//A business rule on the ecc_queue table picks up the output of the script and attached a file to the record.
var arg1 = directory; //the directory of the file
var arg2 = filename; //the filename
var arg3 = "id:" + id; //sys_id of the current record. This is picked up by the BR on the ecc_queue table to attach the pdf. It doesn't do anything in the powershell script.
var arg4 = "tb:" + tb; //table name. This is picked up by the BR on the ecc_queue table to attach the pdf. It doesn't do anything in the powershell script.
var ecc = new GlideRecord("ecc_queue");
ecc.initialize();//to create record
ecc.agent = "mid.server.MIDSERVERNAME";
ecc.topic = "Command";
var value = "powershell scripts\\PowerShell\\AttachmentXX\\getFileBase64.ps1 "+arg1+ " " +arg2 + " " +arg3 + " "+arg4;
ecc.payload = '<?xml version="1.0" encoding="UTF-8"?><parameters><parameter name="name" value="'+value+'"/><parameter name="skip_sensor" value="true"/></parameters>';
ecc.queue = "output";
ecc.state = "ready";
ecc.insert();
},
attachToRecord: function(payload){
//parse the payload
var xmlDoc = new XMLDocument2();
xmlDoc.parseXML(payload);
var resNode = xmlDoc.getNode("//result");
var name = resNode.getAttribute("command");
// get the sysID of the record the file will be attached to from the name
var startPositionId = name.indexOf('id:') + 3;
var sysId = name.substring(startPositionId, startPositionId + 32);
// get the table name of the record the file will be attached to from the name
var patternTb = /tb:[A-Za-z0-9_]+/;
var tb = patternTb.exec(name)[0].substr(3);
//get the file name from the name
var pattern = /[A-Za-z0-9]+\.Pdf/;
var fileName = pattern.exec(name)[0];
//get the gliderecord that the file will be attached to
var recGR = new GlideRecord(tb);
recGR.get(sysId);
if(recGR){
//get the Base64 encoding from the payload
var encStr = xmlDoc.getNodeText("//stdout");
//var encStr = gs.getXMLText(payload, "//stdout"); //doesn't work in private scope
//attach the file to the record
var attachment = new GlideSysAttachment(); //this API does not work in the Global scope
attachment.writeBase64(recGR,fileName, 'application/pdf', encStr);
}
},
type: 'AttachmentXXUtils'
};I pass the sysId of the original record as well as the table name to the powershell script so that these elements appear in the payload of the response. This allows me to parse the response and attach the file it contains to the right record. They don't do anything in the Powershell itself.
Powershell Script
#gets the Base64 encoding of a file
#usage: getFileBase64.ps1 \\SERVERNAME\ACHPO\PURCH\ P175897.Pdf
#first parameter is the path, second is the filename
$path=$args[0];
$filename=$args[1];
[convert]::ToBase64String((Get-Content -path "$path$filename" -Encoding byte));Business Rule to Process Response
This business rule is on the ecc_queue table. The Name filter is STARTSWITH "powershell scripts\PowerShell\AttachmentXX\getFileBase64.ps1"
The BR calls afunction in the AttachmentXXUtils script include which processes the payload.
(function executeRule(current, previous /*null when async*/) {
var att = new x_xxxx_att.AttachmentXXUtils();
att.attachToRecord(current.payload);
})(current, previous);Here is the function it is calling
attachToRecord: function(payload){
//parse the payload
var xmlDoc = new XMLDocument2();
xmlDoc.parseXML(payload);
var resNode = xmlDoc.getNode("//result");
var name = resNode.getAttribute("command");
// get the sysID of the record the file will be attached to from the name
var startPositionId = name.indexOf('id:') + 3;
var sysId = name.substring(startPositionId, startPositionId + 32);
// get the table name of the record the file will be attached to from the name
var patternTb = /tb:[A-Za-z0-9_]+/;
var tb = patternTb.exec(name)[0].substr(3);
//get the file name from the name
var pattern = /[A-Za-z0-9]+\.Pdf/;
var fileName = pattern.exec(name)[0];
//get the gliderecord that the file will be attached to
var recGR = new GlideRecord(tb);
recGR.get(sysId);
if(recGR){
//get the Base64 encoding from the payload
var encStr = xmlDoc.getNodeText("//stdout");
//var encStr = gs.getXMLText(payload, "//stdout"); //doesn't work in private scope
//attach the file to the record
var attachment = new GlideSysAttachment(); //this API does not work in the Global scope
attachment.writeBase64(recGR,fileName, 'application/pdf', encStr);
}
},Note that the GlideSysAttachment API wasn't working for me in the global scope, even though the documentation says it should.
Run It!
When the first business rule is triggered (on the originating table), an output record is created in the ecc_queue table:
A few seconds later, an input record is created on the same table with the response. This triggers the BR on the ecc_queue table which attaches the file to the originating record.
As I mentioned earlier, all of the parts except the first business rule (on the originating table) are in their own scoped application. When this requirement comes along again (we love pdfs in my organization!), I'll simply have to call the script include from whatever table / record I want to attach a file to and the AttachmentXX application will take care of the rest.
