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.

[vincentchinys]

Tested the method above, MS SQL is getting the binary rather than base64 string.

[josh_nerius]

Hi Vincent,

This isn't directly related to the problem at hand, but I noticed you're constructing your JDBC Probe XML manually. If you'd prefer, you can use the JDBCProbe Script Include (as long as this is active in your instance) and use the following methods to set up your manual SQL statement.

var j = new JDBCProbe("your-mid-server");

j.setDriver("your.driver.here");

j.setConnectionString("your:connection:string");

j.setFunction("");

j.addParameter("query", "Specific SQL");

j.addParameter("sql_statement", yourSqlVariable);

Your method will absolutely work, but you may find using the addParameter method a bit more manageable in the long run.

Cheers!

Josh

[vincentchinys]

Thanks a lot Josh,

Agree, it's a better idea to code it using the JDBCProbe script like what you showed.

regards

Vincent

[Inactive_Use603]

this might help to understand the approach of sending files via Web Service ServiceNow Attachment Files via Web Service - YouTube

[josh_nerius]

I know this post has been here for awhile, but I wanted to provide an update for future readers.

With the Geneva release, several new APIs were introduced that make attachment handling much easier.

These are:

• REST Attachment API. This API allows you to interact with ServiceNow attachments from external systems in a RESTful way, and provides easy attachment retrieval and creation.
• Enhancements to RESTMessageV2. New method saveResponseBodyAsAttachment allows you to directly save the response of a call to an attachment and setRequestBodyFromAttachment allows you so use an existing attachment in the instance as the request body of an outbound request to an external system.

These enhancements should make the tasks discussed in this thread much easier.