Download attachments at once from sys_attachment table

[Johnny Chong1]

I was requested by my clients to provide an attachment archiving solution to download all incident attachments at once into their organisation's file repository.   These files should be easily searchable by their ticket number and stamped with the datetime when the file was attached to the incident. Also, the solution must be easily scalable to any tables and schedulable.

To ensure I have explored all the options and proposed the best-suited solution, I spent sometimes in finding them at Almighty ServiceNow Wiki.

What have I learned?

Thanks to the community, I gained lots of knowledge.   Although I only picked up the solution that suits my challenge, I am sharing them with you as their contributions might help you in different challenges:

1. Download all attachments (in a zip file) for individual task/record via UI Action. https://www.servicenowguru.com/scripting/download-attachments-zip-file/
2. Sending out ServiceNow attachment via SOAP. http://www.john-james-andersen.com/blog/service-now/sending-out-servicenow-attachments-via-soap.html
3. Develop a custom Script, Application or Services using your preferred programming language and extract the attachments via ServiceNow attachment API. https://docs.servicenow.com/integrate/inbound_rest/concept/c_AttachmentAPI.html
4. Download all attachments with ServiceNow MID Server using MID Server Scripts.

https://community.servicenow.com/thread/240782

https://community.servicenow.com/message/1111625#1111625

How are attachments stored?

With so many options I got and not knowing what was the best option that suits me, I decided to first explore how attachments are being stored in ServiceNow.   Here is what I found in https://community.servicenow.com/docs/DOC-6091.

In summary, there is one master entry in sys_attachment table describing the file name, file type, file size, table name, and table sys_id of the record such as "incident INC0009999".

Any attachment saved in ServiceNow is broken up into 4k chunks.   These chunks are then base64 encoded, producing 4k strings.   Those strings are then written out as sequential rows in sys_attachment_doc table.   These various entries in sys_attachment_doc then relate back to that entry in sys_attachment table.

My Journey

After understanding how attachments are being stored, I decided to explore option #4; MID Server scripts.   I eventually got there, and I would like to share with you the possible issues you may encounter if you decided to build the similar solution.   You may wonder why the total file count is short if you compared them with a number of records in sys_attachment table. Here are some possible issues that you need to handle when you build the solution:

1. Special characters in the file name, e.g. ":" & etc.   Create a test file at target file folder manually with the filename of all possible characters, particularly the characters of filename stored in ServiceNow. If it doesn't allow you to name it with that special character, you should handle it at the MID server script to replace it with space or any character you wanted.
2. Duplicate filenames.   If two and more files are downloaded into the same folder, it will override the first copy and therefore files are short.   You may prefix the file name to differentiate them, such as datetime and ticket number.
3. Length of the filename.   Ensure the target file folder where the files are downloaded supports the length of the filename that you are downloading.   Again, test it manually and handle it within your MID server script.
4. Slow execution with single MID server.   To scale up the MID Server, you can adjust the number of threads and java max memory at MID Server Config and Wrapper files respectively.   Also, consider MID Server Load balancing to execute your downloads through various MID servers.
5. Anti-Virus. To protect your organisation's environment, make sure you have antivirus installed on your MID server or file repository.   On the other hand, if you have anti-virus installed do expect the affected files not being downloaded and therefore your files will possibly be short.   Create reconciliation mechanism to help you easily identify files not being downloaded for whatever reason.

What I came out with?

I hope the information mentioned-above help you in solving your attachment-related challenge.

Through option #4, I developed an application in ServiceNow which I named it EDA (Extract, Decode and Archive). For details, please refer to https://www.tcloudconsulting.com.au/eda

In a nutshell, EDA uses the ServiceNow MID Server engine to extract the attachments from ServiceNow to file repository within your organisation network.   It can be extracted in any periodical folder (weekly or monthly) via predefined or scheduled EDA triggers.

If further information is required, feel free to drop me an email at johnny@tcloudconsulting.com.au

[Chuck Tomasi]

Have you looked at using the Attachment REST API to retrieve the attachments from a third party app?

Attachment API

[Marcin Kroszel]

Hi Chuck,

thanks for sharing this link. I saw some SN movies on how to use the API you mentioned.

I have a request from a customer to download all tickets and related attachments so they can upload that somewhere locally.  

Correct me if I am wrong but it is not possible to mass export all tickets and related attachments right?

[Pooja Mahajan]

Hi Marcin, did you get the answer for this requirement? We have the same ask from client.

[Rajesh Mudam]

Hi Marcin,

Did you get any solution for this, if you found any please post here.

Thanks