How to retrieve an attachment from service now via SOAP Web Service

[sherman_1206]

So after trial and error and some help from service now we are now able to successfully retrieve attachments from service now via SOAP Web Service. The basic idea is the following: attachments are stored in sys_attachment_doc table in 4k chunks, the attachment information is stored in sys_attachment table. The sys_attachment table stores file's meta information including, file name, file size, file type.

The sys_attachment_doc table stores files in the following way. When service now receives an attachment, they take it gzip it, split the gzip into 4k chunks and then do a base64 encoding of the 4k chunks. So to retrieve is as simple as finding the 4k chunks, base64 decoding, constructing the gzip archive and then extracting the file from the archive.

The trick is finding the attachment chunks. This is simple for example to find an attachment of an incident, we can look in sys_attachment table for table = "incident", table_sys_id = "". This type of query would return an attachment record, we can take the sys_id field of this record and then search sys_attachment_doc table for all records containing sys_attachment = "". This will return 1+ records and each of these records contains a field "position". Position defines ordering, and simply is 0,1,2,3,.. for reconstructing. So during your query in sys_attachment_doc table you can add "__order_by"=>"position" to retrieve the records in order.

And as mentioned before once we have the chunks, we base64_decode each chunk, then build a gzip archive and then inflate the archive and voila you have your attachment.

-Cheers

(Thanks to service now, and the wiki for finding a solution to this problem.)

Currently we have an enhancement request for an easier way to make one soap call and retrieve a file. I will update if this is implemented.

[viral1]

Hello

Does this technique possible with REST Table API ?

Thanks

[jmurtha]

I was able to create a processor that takes table_sys_id, table_name and attachment_sys_id as parameters and returns a base64 encoded byte stream.

Processor code:
var StringUtil = Packages.com.glide.util.StringUtil;
var tableSysId = g_request.getParameter("tableSysId");
var tableName = g_request.getParameter("tableName");
var attachmentSysId = g_request.getParameter("attachmentSysId");

var gr = new GlideRecord('sys_attachment');
if (gr.canRead()) {
gr.addQuery('table_sys_id', tableSysId);
gr.addQuery('table_name', tableName);
gr.addQuery('sys_id', attachmentSysId);
gr.query();

if (gr.next()){
var sa = new Packages.com.glide.ui.SysAttachment();
var binData = sa.getBytes(gr);
var encData = StringUtil.base64Encode(binData);
g_processor.writeOutput(gr.content_type, encData);
} else {
g_processor.writeOutput('text/plain', 'attachment not found for tableSysId=' + tableSysId + ' tableName=' + tableName + ' attachmentSysId=' + attachmentSysId);
}
} else {
g_processor.writeOutput('text/plain', 'insufficient rights!');
}

Java test program code:
public class App {

public static void main(String[] args) {
StringBuilder sb = new StringBuilder();
ByteArrayOutputStream bos;
String tableSysId = "599560f7b8147400aee325aae407bacb";
String tableName = "incident";
String attachmentSysId = "172ac6d8b8e47400aee325aae407ba47";

HttpClient client = new HttpClient();
sb.append("https://").append("wgudev.service-now.com").append("/WguDownloadAttachment.do")
.append("?tableSysId=").append(tableSysId)
.append("&tableName=").append(tableName)
.append("&attachmentSysId=").append(attachmentSysId);
System.out.println("web service url: " + sb.toString());
HttpMethod method = new GetMethod(sb.toString());
client.getState().setCredentials(AuthScope.ANY, new UsernamePasswordCredentials("user", "password"));
method.setDoAuthentication(true);
try {
// make http request
if (client.executeMethod(method) != HttpStatus.SC_OK) {
System.out.println("Http Get of PDF failed: " + method.getStatusLine());
return;
}

// check http response header mime type to know how to process resposne
if (method.getResponseHeader("Content-Type").getValue().equalsIgnoreCase("image/png")) {
System.out.println("Processing response content type of image/png...");

// process http response as a byte stream
InputStream in = method.getResponseBodyAsStream();
bos = new ByteArrayOutputStream();
int bytesRead;
int totalBytes = 0;
byte[] buffer = new byte[1024];
while ((bytesRead = in.read(buffer)) != -1) {
bos.write(buffer, 0, bytesRead);
totalBytes += bytesRead;
}
in.close();
System.out.println("base64 encoded bytes: " + totalBytes);

// decode response bytes
byte[] decodedData = Base64.decodeBase64(bos.toByteArray());
System.out.println("decoded bytes: " + decodedData.length);

// persist bytes as png file
try {
OutputStream output = null;
try {
output = new BufferedOutputStream(new FileOutputStream("download.png"));
output.write(decodedData);
} finally {
output.flush();
output.close();
}
} catch (IOException ex) {
System.out.println(ex.getMessage());
}
} else {
// process http response as error message
System.out.println("Processing response content type: " + method.getResponseHeader("Content-Type").getValue());
System.out.println("Error message: " + method.getResponseBodyAsString());
}

} catch (IOException ex) {
System.out.println(ex);
} catch (HTTPException ex) {
System.out.println(ex);
} finally {
method.releaseConnection();
}
}
}

[nidhi_gupta]

Hi Jmurtha,

I have tried this code in processor under type: script but it gives me error stated that "JavaScript parse error at line (25) column (9) problem = missing ; before statement". I believe might be i am trying to run a java code under script type, that is the problem.

Could you please help me or suggest any alternative.

Regards, Nidhi

[sherman_1206]

I believe this is because in recent version of ServiceNow some of the underlying java packages have been restricted and are no longer exposed.

[avid]

This is great!