Automatically Delete HR Case Attachments 10 Days After Closed Complete Status

[adityaashok]

Hi Team,

I have a requirement for HR Cases (sn_hr_core_case) where attachments need to be automatically removed after a retention period.

Requirement

When an HR Case reaches "Closed Complete" status:

1. Attachments should remain available for 10 calendar days after the case is closed.
2. On the 11th day, all attachments associated with the HR Case should be automatically deleted.
3. The HR Case record itself must remain unchanged.
4. Deletion activity should be logged for audit purposes.
5. The process should run automatically without manual intervention.
6. Any errors during attachment deletion should be captured in system logs and ideally reported to administrators.
   
   

Current Approach

I am attempting to use a Scheduled Script Execution to identify HR Cases that have been in the Closed Complete state for more than 10 days and then delete the associated records from the sys_attachment table.

However, I am looking for guidance on:

• Whether a Scheduled Script Execution is the best practice for this use case.
• Whether there is a more efficient approach using Scheduled Jobs
• Best practices for logging and auditing attachment deletions.
• Any considerations specific to HRSD regarding attachment retention and deletion.
• For more information I shared the scheduled job script.
  gs.info('HR Attachment Deletion Job Started');

  var cutoffDate = new GlideDateTime();

  cutoffDate.addSecondsUTC(-600); // 10 minute

  var hrCase = new GlideRecord('sn_hr_core_case');

  hrCase.addQuery('state', '3');

  hrCase.addQuery('closed_at', '<=', cutoffDate);

  hrCase.query();

  gs.info('Cases Found = ' + hrCase.getRowCount());

  while (hrCase.next()) {

      gs.info('Case Found = ' + hrCase.number);

      var attachGR = new GlideRecord('sys_attachment');

      attachGR.addQuery('table_name', 'sn_hr_core_case');

      attachGR.addQuery('table_sys_id', hrCase.getUniqueValue());

      attachGR.query();

      gs.info('Attachments Found = ' + attachGR.getRowCount());

      while (attachGR.next()) {

          gs.info('Attachment Found = ' + attachGR.file_name);

           new GlideSysAttachment().deleteAttachment(attachGR.getUniqueValue());

      }

  }

Has anyone implemented a similar requirement for HR Cases? Any recommendations, sample scripts, or best practices would be greatly appreciated.

Thanks in advance!

[sachinchaudhary]

@adityaashok 

Your approach to using a Scheduled Script Execution is appropriate for this requirement. I would make one change to the attachment deletion logic.

Keep your existing query for HR Cases that are Closed Complete for more than 10 days but use GlideSysAttachment to delete the related attachments instead of directly deleting records from sys_attachment.

var cutoffDate = new GlideDateTime();

cutoffDate.addDaysUTC(-10);

var hrCase = new GlideRecord('sn_hr_core_case');

hrCase.addQuery('state', '3'); // Closed Complete

hrCase.addQuery('closed_at', '<=', cutoffDate);

hrCase.query();

var attachment = new GlideSysAttachment();

while (hrCase.next()) {

    var attachments = attachment.getAttachments(

        'sn_hr_core_case',

        hrCase.getUniqueValue()

    );

    while (attachments.next()) {

        attachment.deleteAttachment(attachments.getUniqueValue());

    }

}

First run the query without the delete operation to verify that only the intended HR Cases are returned, then enable the deletion after testing in a sub-production instance. Also confirm any applicable HR/legal retention requirements before enabling this in production.

https://www.servicenow.com/docs/r/yokohama/api-reference/server-api-reference/GlideSysAttachmentGlob...