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.

[ssangelov]

Hello   sherman.1206 ,

Thank you for the good explanation .

I have one question about your post. So i get all records from the sys_attachment table and everything works perfect i receive all records of attachments and from the response i have sys_id for each attachment . After that i`m trying to invoke sys_attachment_doc with the same sys_id but the response is empty? In your post you wrote that sys_attachment   should be sys_attachment = "" , but what exactly mean this and do you have any idea , why my response is empty ?

Thanks in advance.

[adamjgreenberg]

ssangelov,

sys_attachment is where we store the document information like the name, size, compression, and pointer to the sys_attachment_doc table.

sys_id = unique ID of the individual document

sys_attachment_doc is where we store the chunks of the attachment

sys_id = unique ID of each sys_attachment_doc chunk

sys_attachment = unique ID to the sys_attachment table that defines the actual attachment.

So, you need to have a where clause that joins these two ID's to ensure the chunks from DOC belong to the actual attachment.

sys_attachment.sys_id=sys_attachment_doc.sys_attachment

[yehiel]

Hi,

Is there any other way to get attachment content without all these steps: Base 64 decoding, Unzip..

I am working on a integration between Salesforce and ServiceNow using Boomi. In Boomi I managed to combine all the chuncks in one document but when I try to decode or unzip I receive some errors.

Thank you.