Playing with Attachments in ServiceNow
- 1. Sys_attachment
- 2. Sys_attachment_docs
- “sys_attacment” has got ONE-TO-MANY relationship with “sys_attachment_docs” table
Attachment in ServiceNow - Overview:
a) Parent transactional record references a single record in "sys_attachment" Table
b) "sys_attachment" table has got sys ID of your Transaction record (i.e. sys_Id of parent Incident or Change or Service Request or anything which has got this attachment)
c) Field "Table sys id" contains the sys id of Parent Transaction Record.
d) "sys_attachment" table has got one-to-many relationship with "sys_attachment_docs" table.
e) Field "sys_attachment" on Table "sys_attachment_docs" holds sys_id of parent mapping record of table "sys_attachment".
f) There can be more than one records in "sys_attachment_docs" table which are basically 4K chunks of the gzip archive of actual attachment.
g) Each of these records contains a field "position". Position defines ordering, and simply is 0,1,2,3,.. for reconstructing.
Saving an Attachment to ServiceNow:
- Attachments are stored in sys_attachment_doc table in 4k chunks,
- Attachment information is stored in sys_attachment table.
- Sys_attachment table stores file's meta information including, file name, size & type.
- Sys_attachment_doc table stores files in the following way.
- ServiceNow receives an attachment
- We gzip it => split the gzip into 4k chunks => Execute a base64 encoding of the 4k chunks.
Retrieving an Attachment from ServiceNow:
To retrieve an attachment is as simple as finding the 4k chunks, base64 decoding, constructing the gzip archive and then extracting the file from the archive.
- The first step will be to locate the parent reference: the single record in sys_attachment
- Lookup sys_attachment table for
Table sys id = <Your Transactions Record sys ID>
- This will return the attachments in this transactional record. We can refine above query to get the single record we are after.
- Get sys ID of this record.
- Search sys_attachment_docs table with
Sys_attachment = <Sys ID of parent record in sys_attachment table, copied above>
- This will return all the records for this attachment.
- 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.
- Once we have the chunks,
- base64_decode each chunk
- It will build a gzip archive
- gunzip this archive
- and voila you have your attachment.