Identifying and deleting unwanted Opportunity Audit Log Entries caused by Issue 95283

I’d like to share some insights I've gained while helping customers navigate the impact of Issue 95283.

This issue was introduced in Sugar version 14.1.0 and resolved in 14.2.0. During this period (14.1.0), it unfortunately led to audited fields in the Opportunities module logging entries with a blank "before value" in the Audit Log, even when those fields were not changed during a record save.

To help identify which audit log entries are likely erroneous and provide a method for targeted deletion, I’ve developed a query-based approach.

Since this involves production data, and there’s always the risk of accidentally deleting legitimate entries, I make sure to fully inform customers and leave the decision in their hands.

For an example instance upgraded to 14.1.0 on August 21, 2024, here's a query that returns a count of audit log entries made after the 14.1.0 upgrade with blank "before_value" that occurred during record updates (not creation), grouped by field name:

SELECT oa.field_name a, COUNT(oa.id)
FROM opportunities_audit oa
JOIN opportunities o ON oa.parent_id = o.id
WHERE oa.date_created > '2024-08-21 00:00:00'
AND oa.before_value_string IS NULL
AND oa.date_created != o.date_entered
GROUP BY a;

When reviewing the results, you may notice certain required fields that likely weren't empty after the record’s creation. Examples could include account_id, lead_source, sales_stage, assigned_user_id, team_id, and team_set_id.

Once you’ve identified these field entries, the following query could be run (by support) to clean up entries for specific fields that likely weren’t empty during updates:

DELETE oa
FROM opportunities_audit oa
JOIN opportunities o ON oa.parent_id = o.id
WHERE oa.date_created > '2024-08-21 00:00:00'
AND oa.before_value_string IS NULL
AND oa.date_created != o.date_entered
AND oa.field_name IN ('account_id', 'lead_source', 'sales_stage', 'assigned_user_id', 'team_id', 'team_set_id');

Although this approach may not fully clean up all of the consequences and could potentially remove some legitimate entries, this approach hopefully provides customers with an option to more confidently address the affected Opportunity audit logs caused by this issue.