How to find orphan records for the email logs

[nagarjunadI]

We have deleted 5 lack email log records from the instance. Now we need to determine if there are any orphan attachments related to those deleted email logs.

Do we have any approach ?

[glideFather]

ahoy @nagarjunadI,

if it was by mistake, you can try to restore it from Deleted records module:

[Chaitanya ILCR]

Hi @nagarjunadI ,

you can run the background script and find out 

(function() {
    var batchSize = 5000; // Limits processing chunk size to avoid memory overloads
    var totalOrphans = 0;
    var orphanIDs = [];

    var attachmentGR = new GlideRecord('sys_attachment');
    attachmentGR.addQuery('table_name', 'sys_email');
    attachmentGR.chooseWindow(0, batchSize); 
    attachmentGR.query();

    while (attachmentGR.next()) {
        var parentEmailID = attachmentGR.getValue('table_sys_id');
        
        // Check if the parent sys_email record still exists
        var emailGR = new GlideRecord('sys_email');
        if (!emailGR.get(parentEmailID)) {
            totalOrphans++;
            orphanIDs.push(attachmentGR.getUniqueValue());
            
            // Optional: Print details to logs for verification
            gs.info("Orphan Found: Attachment ID " + attachmentGR.getUniqueValue() + " for deleted Email ID: " + parentEmailID);
        }
    }

    gs.info("Execution complete. Total orphaned email attachments found in this batch: " + totalOrphans);
    // To delete them, you can loop through orphanIDs and run attachmentGR.deleteRecord()
})();

Regards,
Chaitanya