need to delete files from sys_attachment table (sys attachement report)

Jayaprakash P
Tera Contributor

Hi,

can someone helps us with the script to delete record from sys attachment table only for the reports been sent.

if report has an attachment and that schedule report send via email. We are trying to see the possibility of deleting attachment record which is associated with email send.

 

Thanks & Regards,

Jayaprakash

8 REPLIES 8

@Jayaprakash P 

simply enhance it and add in scheduled job

remember email log table is flushed every 30 days or so and hence you won't know if the email was actually sent or not

So you cannot use that to find previous records

you can do this at max

1) query sys_attachment records with created before 2023 31st Dec with table as "sysauto_report"

2) iterate and delete the records

var gr = new GlideRecord("sys_attachment");
gr.addEncodedQuery("table_nameSTARTSWITHsysauto_report^sys_created_on<javascript&colon;gs.dateGenerate('2023-12-31','00:00:00')");
gr.query();
while (gr.next()) {
    var attachment = new GlideSysAttachment();
    var attachmentSysID = gr.getUniqueValue();
    attachment.deleteAttachment(attachmentSysID);
}

If my response helped please mark it correct and close the thread so that it benefits future readers.

Regards,
Ankur
Certified Technical Architect  ||  9x ServiceNow MVP  ||  ServiceNow Community Leader

@Jayaprakash P 

Would you mind closing your question by marking appropriate response as correct?

Members have invested their time and efforts in helping you.

If my response helped please mark it correct and close the thread so that it benefits future readers.

Regards,
Ankur
Certified Technical Architect  ||  9x ServiceNow MVP  ||  ServiceNow Community Leader

Jayaprakash P
Tera Contributor

above query will delete the data from sys attachment, but sys attachment holds meta data of the attachment and sys attachment doc is where the file 4k chunk will be stored. how we can delete the sys attachment doc table data which is related to the sys attachment we try to delete.

@Jayaprakash P 

if you delete attachment using GlideSysAttachment it should delete sys_attachment_doc meta data as well OOTB.

I believe I have answered your question with the relevant scripts and you can enhance it further as per your requirement and developer skills

If my response helped please mark it correct and close the thread so that it benefits future readers.

Regards,
Ankur
Certified Technical Architect  ||  9x ServiceNow MVP  ||  ServiceNow Community Leader