During a data loss scenario, identifying the records that were deleted is a big task. The issue is a bigger problem when we/customer doesn't know how it happened and when it happened.
- Look through the logs manually.
- Seek the help of SRE/DEV to identify the deletion activities from BINLOG (time consuming).
This article describes how to find out the deleted records and the tables that are affected from the instance itself.
*** very important.
sys_audit record WILL BE created even-if the customer use setWorkflow(false)/ autoSysFields(false) and the DELETE happened via a script.
Records deleted with Table cleaner also DO NOT have the records created in sys_audit. Note: TableCleaner does not call
DBDelete.setWorkflow(). This means
DBDelete objects run with
workflow=false (false is the default value for a Java boolean), hence Delete business rules and workflows are not triggered.
|Note: A few tables that are not tracked in sys_audit are listed towards the end of this article. Please leave a comment when a new table is identified so that this article can be kept up to date.|
The advantage of this method is that we can get the details of the deleted records and extent of deletion from the affected instance itself even without the Source Of Truth instance being UP.
This method will work even if the user un-deleted all/partial records.
For every deletion activity, sys_audit will log a record against that sys_id with the table information even if the table is not AUDITED.
This record is saved with the following fields and values:
- Fieldname = DELETED
- Old Value = DELETED
- New Value = DELETED
- Table name = Table name affected
- Document Key = sys_id of the deleted record.
- User ==> Will be "system" if it is a cascade delete. (Do NOT use a filter on this field.)
To check the records deleted today:
The result set will be the sys_id in the Document Key and this information will help in recovering the records from Source of Truth instance.
|Note: If the user undeletes the record from Deleted Records module, a new entry is created in sys_audit with the table name sys_audit_delete and Field name DELETED. However, the original DELETION that happened will be in the sys_audit table, by which you can get the full list of Impacted sys_id's from the respective table, which will aid in data/reference restoration activities.|
|List of tables where the records are recorded in sys_audit||List of tables where the records are NOT recorded in sys_audit|
|task||u_* tables NOT exteded from task|
|sys_user_has_role||x_* tables NOT extended from task|
|x_* if extended from task||sys_email|
|u_*if extended from task||sys_metadata|