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.

[sherman_1206]

There is a content type field on the sys_attachment records that indicate the mimetype of the base64 you decode.  

Some examples:

Content type                 | File extension

application/pdf             | pdf

image/png                         | png

[vkachineni]

Chris,

Lets say we get 2 sys_attachment records ordered by position 0,1.

pos 0

<data>H4sIAAAAAAAAAA==</data>

pos 1

<data>81Qoz89LSS1SyExTKM5IVcjOyy/PK1Yoz0gsAfHVixVS8jPz0hWAwnpcAJx0yzktAAAA</data>

Then we decode the data in pos 0 (say dec0) and decode pos 1 (say dec1)

Then concatenate the dec0 + dec1

Then Build a Gzip file and then inflate it to get the attachment.

Please confirm the steps.

Thank you,

Vin Kachineni

[vincentchinys]

Anyone has idea how to retrieve the attachment in binary instead of base64 encoded string?

I need the binary values to be feed into a JDBC queue. Thanks.

[sherman_1206]

ServiceNow does not have an inherent way to generate binary.   The system stores attachments in base64.  

You could in theory pull the sys_attachment records for the attachment, concatenate all the base64 and then convert to binary and return the binary.   This could be achieved through a processor however I am not sure this itself will work with a JDBC connection.

[vincentchinys]

Thanks Chris, I am trying to the raw SQL queries for the JDBCProbe, but I have to build the ECC Queue XML record manually as below, this will somehow informing the MS SQL to explicitly cast the base64 string back to binary.

var sqlQuery = ""insert into tableX (attachment) values( cast(N'' as xml).value('xs:base64Binary('QVNERjEyMzQ=')', 'varbinary(MAX)'))"";

var xml = '<?xml version="1.0? encoding="UTF-8??>';

xml = '<parameters>';

xml += '<parameter name="jdbc_driver" value="'+driver+'"/>';

xml += '<parameter name="connection_string" value="'+connectionString+'"/>';

xml += '<parameter name="query" value="Specific SQL"/>';

xml += '<parameter name="sql_statement" value="'+sqlQuery+'"/>';

xml += '</parameters>';

I have proved the insert statement will work. The only problem is the input JDBCProbe will be expected to return an error saying no resultset return.