How-To: Restoring Deleted References to Deleted Records

[Brian Dailey1]

Following is a generalized script of one I initially developed to help a user with recovering the assignment group values after a restored deletion (How to revert the Assignment groups for legacy incidents? )

Restoring an item from Deleted Records does not restore values in the tables that originally referenced the deleted record (they would have been cleared by the deletion process since they were no longer valid).

However, the information is stored as flat string values in the Audit table and can be recovered and re-associated once the deleted records themselves have been restored.   So, the procedure is very simple:

1. Restore the deleted records using the Deleted Records module.
2. Run a server script (Fix, Background, etc.) that calls the function restoreReferences(tableName, fieldName) with your table and field names.

function restoreReferences(tableName, fieldName){

var brokenRecord = new GlideRecord(tableName);

brokenRecord.addNullQuery(fieldName);   // No use processing records where there is still an AG

brokenRecord.query();

while(brokenRecord.next()){

        var lastFieldValue = new GlideRecord('sys_audit');

        lastFieldValue.addQuery('documentkey', brokenRecord.sys_id);

        lastFieldValue.addQuery('fieldname', fieldName);

        lastFieldValue.orderByDesc('sys_created_on');

        lastFieldValue.query();

        if(lastFieldValue.next()){

                  brokenRecord.setValue(fieldName, lastFieldValue.newvalue);

                  brokenRecord.update();

        }      

}

}

[Brian Dailey1]

Hi Pallavi,

I have used the script successfully, as have others.  If the audit records are showing 'DELETED' as the values, maybe it's a setting somewhere or perhaps a version difference (this was originally done on Fuji).

I will try to find an answer for you over the next few days and maybe do some testing in Helsinki (now that I have a dev instance to do that with)

Thanks,

-Brian

[pvl]

Still waiting for reply

[Brian Dailey1]

Hi Pallavi,

Sorry for the delayed reply, this was something I wanted to test but didn't have time to look into with Knowledge and lots of other things going on lately.   At first, I was wondering if it had something to do with the Reference Cascade Rule setting for the field, but now I think not.

I think what you are talking about is just the audit entry from when the record (e.g., from [sys_user_group]) actually gets deleted, and this is normal for it to show 'DELETED'.   The record we are going to be using is the last previous entry for the referring record (e.g., [incident]) that includes a reference field pointing to this record (e.g., assignment_group).

Example:

• The last time the AG field gets set on our incident, we'll see this in the audit table (you can ignore the entries for reassignment_count):
  
  
  
  
• After you delete that particular Assignment Group from [sys_user_group], you will see the following:
  
• After you restore the deleted record for the Assignment Group using the Deleted Records module or some other method, this audit record created at 14:32:37 is the one our script will pickup and use to restore the broken references.

One interesting thing I found from testing is that references set to Cascade or Delete are actually better-off in terms of restoring the records.   For example, if you have the 'assignment_group' reference on Incident set to Cascade delete, then when you delete the Group it will automatically delete the referencing Incidents.   HOWEVER, because the incidents get deleted at the same time, their record ALSO gets stored as an XML payload in [sys_audit_delete] and include all of the reference field sys_id values they contained at that time.   This means that when you go to restore the Group record and then the Incident records, all the proper values will still be there without the added effort.   However this is not a recommendation to use Cascade/Delete, merely an observation.   There are other factors to be taken into account for each use case.

Hopefully that explains it well enough to clear up any confusion.   But if I've made a wrong assumption about what you're seeing, or if this still doesn't work for you, please let me know.

Thanks,

-Brian