Attachments in ServiceNow

[vab_13]

Playing with Attachments in ServiceNow

Attachment Tables:

1. 1. Sys_attachment
2. 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

1. and voila you have your attachment.

HTH

[reddypinninti]

Thank you for the information.

I have used the following query to decode the data(for each position) and saved it to a file. I am stuck right there. I am not sure how to build gzip Archive. Could you please let me know if there are any utilities to build the archive.

SELECT FROM_BASE64(DATA)

  FROM sys_attachment_doc

WHERE sys_attachment = '<sys_id of parent record in sys_attachment table>'

and position = 0;

[sardam]

Thats a great article. Thanks for sharing. You can include bit on how to transfer attachments from request to incident too. I recently worked on moving attachments from request to requested item.

-saroja

[vab_13]

Thanks Saroja, thanks for the Recommendation, I'll create a new Article on this.

[vab_13]

Hi Rajesh,

Didn't see this message yet! I'll need more details

I am having trouble in understanding your question. If you still need advise or assistance with this - lodge a ticket in Hi and we can route you towards the right direction.

Or else, update here and I'll try my best to assist

[jasonqbe]

Thanks

[saloniahuja]

Thanks for the details.

Do you have a recommendation on how we should bulk-import attachments from a legacy 3rd party tool to ServiceNow?

This might not be the right place to put this question so apologies in advance.

Thanks

Saloni

[Harsh Vardhan]

Hi Vab,

can we write it in scripted rest api service to get the file? if yes please help me how to do glide query on it?

i want to retrieve the attachment from service now and this blog helped me a lot.

Thanks,

Harshvardhan

[vab_13]

Hi Harsh,

Thanks. and yes, for sure.

Executing a glide query is going to be nested. I'll try to write something for you.

HTH

Vab

[Harsh Vardhan]

i stuck at last points.

• Once we have the chunks,
  • base64_decode each chunk
  • It will build a gzip archive
  • gunzip this archive

1. and voila you have your attachment.

var attach1= new GlideRecord('sys_attachment_doc');

attach1.addQuery('sys_attachment','690253b78c7a8a040f0fae26bb664f99');

attach1.orderBy('position');

attach1.query();

while(attach1.next())

{

gs.print(attach1.position);

}

how to proceed after getting the position? please help me

[veronica1]

Thanks!

[joannezou]

Hi, Saloni

I wonder if you have found the answer?

Thanks.

[souren0071]

Hi,

Could you please provide me the query or fuction to perform last to task

• It will build a gzip archive
• gunzip this archive

I was able to decode the Base64 in multiple chunk. 

var attach= new GlideRecord('sys_attachment_doc');
attach.addQuery('sys_attachment','<sys_id>');
attach.orderBy('position');
attach.query();
while(attach.next())
{
gs.log(GlideStringUtil.base64Decode(attach1.data));
}

Please provide the next steps..

Regards,

Souren

[Deepak107]

How can we print the attachment data and then insert into a table?

[Rj27]

Hi @vab.13   ,

Can you please help me understand if we can import attachments in bulk to local system using scripted rest api?

i found this link https://www.servicenowguru.com/scripting/download-attachments-zip-file/ but this is using processor. But since processor is deprecated and alternative is scripted rest api can we achieve this using latter?

[Rj27]

Hi @saloniahuja ,

By any chance did you find solution for bulk import?

Thanks in advance!

[Rj27]

Hi @souren007 ,

Did you find any solution to your question?

[jitendrakumarsa]

need more clarity

[Luis O]

Hi Vab,

Thanks for putting this together. We had replicated the attachment tables to our local data warehouse database.  Your explanation helped me to finally reconstruct the attachments. 

Thanks!

Luis  

[Subrat3]

it has to be array, in case we have multiple attachments
then how we write the script.
thanx
Subrat

[Ram_n Botelho]

Hi ! I am kind of lost on your explanation. I receive a Gzip compressed base64 string from a REST Message and want to transform it to an attachment to a record. Reading your text it seems like I can go to sys_attachment_docs and rebuilt what a receive, is that correct? The attachments that I am adding are broken at download.

[NPatel21]

Hi,

Sorry to jump on this thread

I was wondering if someone can help me.

We have contracts in this table - ast_contract_list.do

 I want to run a report to show me which contracts have an attachment and which don't so I know where its FALSE I can chase the relevant stakeholders.

@vab_13 

Thanks

Neena

[Eoghan Sinnott]

Hi @NPatel21 ,

The accepted solution in the thread below will work for you. You just need to change the example from the INC table to the ast_contract. I have tested in my PDI and it works. It will show you all contracts that HAVE an attachment, so you would then need to filter these out of all contracts, to show the ones that do not have an attachment.

https://www.servicenow.com/community/developer-forum/getting-a-list-of-attachments-in-a-report/m-p/2...

Thanks, 

Please consider marking my reply as Helpful and/or Accept Solution, where applicable. Thanks!