Clean audit tables?

We have been using Sugar for more than 6 years. We have audit log records that go back to the start of our use of Sugar. The Archive Data tool does not have the ability to archive audit tables. What is the best practice to remove older data from these tables?

0 Francesca Shiekh 6 months ago

I can't think of anything out of the box. Of course you can always build a custom scheduler to delete older records.

Maybe Upsert has something to take care of this? Chris Raffle ?

FrancescaS
0 Chris Raffle 6 months ago in reply to Francesca Shiekh

We don't have a plugin to delete/archive audit data at this time

Chris

Co-Founder | SugarCRM's 2024 Global Outfitters Partner of the Year
0 Francesca Shiekh 6 months ago in reply to Chris Raffle

Yet.

0 Ben Hamilton 5 months ago

For Sugar Cloud, I've been asking support to run the following script, by making all intervals in MONTH units, I can then, depending on the clients desire, I do a search and replace on the number of months, i.e. 12 MONTH, or 36 MONTH...

Fullscreen

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
-- clean out (most but not all) audit tables in SugarCRM older than x months
-- Search and replace `12 MONTH` with timeframe you desire to keep
DELETE FROM accounts_audit
WHERE date_created < NOW() - INTERVAL 12 MONTH;
OPTIMIZE TABLE accounts_audit;
DELETE FROM audit_events
WHERE date_created < NOW() - INTERVAL 12 MONTH;
OPTIMIZE TABLE audit_events;
DELETE FROM bugs_audit
WHERE date_created < NOW() - INTERVAL 12 MONTH;
OPTIMIZE TABLE bugs_audit;
DELETE FROM business_centers_audit
WHERE date_created < NOW() - INTERVAL 12 MONTH;
OPTIMIZE TABLE business_centers_audit;
DELETE FROM calls_audit
WHERE date_created < NOW() - INTERVAL 12 MONTH;
XXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXX

-- clean out (most but not all) audit tables in SugarCRM older than x months
-- Search and replace `12 MONTH` with timeframe you desire to keep

DELETE FROM accounts_audit
WHERE date_created < NOW() - INTERVAL 12 MONTH;
OPTIMIZE TABLE accounts_audit;

DELETE FROM audit_events
WHERE date_created < NOW() - INTERVAL 12 MONTH;
OPTIMIZE TABLE audit_events;

DELETE FROM bugs_audit
WHERE date_created < NOW() - INTERVAL 12 MONTH;
OPTIMIZE TABLE bugs_audit;

DELETE FROM business_centers_audit
WHERE date_created < NOW() - INTERVAL 12 MONTH;
OPTIMIZE TABLE business_centers_audit;

DELETE FROM calls_audit
WHERE date_created < NOW() - INTERVAL 12 MONTH;
OPTIMIZE TABLE calls_audit;

DELETE FROM campaigns_audit
WHERE date_created < NOW() - INTERVAL 12 MONTH;
OPTIMIZE TABLE campaigns_audit;

DELETE FROM cases_audit
WHERE date_created < NOW() - INTERVAL 12 MONTH;
OPTIMIZE TABLE cases_audit;

DELETE FROM cj_forms_audit
WHERE date_created < NOW() - INTERVAL 12 MONTH;
OPTIMIZE TABLE cj_forms_audit;

DELETE FROM contacts_audit
WHERE date_created < NOW() - INTERVAL 12 MONTH;
OPTIMIZE TABLE contacts_audit;

DELETE FROM contracts_audit
WHERE date_created < NOW() - INTERVAL 12 MONTH;
OPTIMIZE TABLE contracts_audit;

DELETE FROM data_privacy_audit
WHERE date_created < NOW() - INTERVAL 12 MONTH;
OPTIMIZE TABLE data_privacy_audit;

DELETE FROM document_merges_audit
WHERE date_created < NOW() - INTERVAL 12 MONTH;
OPTIMIZE TABLE document_merges_audit;

DELETE FROM document_templates_audit
WHERE date_created < NOW() - INTERVAL 12 MONTH;
OPTIMIZE TABLE document_templates_audit;

DELETE FROM documents_audit
WHERE date_created < NOW() - INTERVAL 12 MONTH;
OPTIMIZE TABLE documents_audit;

DELETE FROM docusign_envelopes_audit
WHERE date_created < NOW() - INTERVAL 12 MONTH;
OPTIMIZE TABLE docusign_envelopes_audit;

DELETE FROM dri_workflows_audit
WHERE date_created < NOW() - INTERVAL 12 MONTH;
OPTIMIZE TABLE dri_workflows_audit;

DELETE FROM email_addresses_audit
WHERE date_created < NOW() - INTERVAL 12 MONTH;
OPTIMIZE TABLE email_addresses_audit;

DELETE FROM escalations_audit
WHERE date_created < NOW() - INTERVAL 12 MONTH;
OPTIMIZE TABLE escalations_audit;

DELETE FROM forecast_manager_worksheets_audit
WHERE date_created < NOW() - INTERVAL 12 MONTH;
OPTIMIZE TABLE forecast_manager_worksheets_audit;

DELETE FROM kbcontent_templates_audit
WHERE date_created < NOW() - INTERVAL 12 MONTH;
OPTIMIZE TABLE kbcontent_templates_audit;

DELETE FROM kbcontents_audit
WHERE date_created < NOW() - INTERVAL 12 MONTH;
OPTIMIZE TABLE kbcontents_audit;

DELETE FROM leads_audit
WHERE date_created < NOW() - INTERVAL 12 MONTH;
OPTIMIZE TABLE leads_audit;

DELETE FROM messages_audit
WHERE date_created < NOW() - INTERVAL 12 MONTH;
OPTIMIZE TABLE messages_audit;

DELETE FROM manufacturers_audit
WHERE date_entered < NOW() - INTERVAL 12 MONTH; -- date_entered
OPTIMIZE TABLE manufacturers_audit;

DELETE FROM meetings_audit
WHERE date_created < NOW() - INTERVAL 12 MONTH;
OPTIMIZE TABLE meetings_audit;

DELETE FROM messages_audit
WHERE date_created < NOW() - INTERVAL 12 MONTH;
OPTIMIZE TABLE messages_audit;

DELETE FROM metrics_audit
WHERE date_created < NOW() - INTERVAL 12 MONTH;
OPTIMIZE TABLE metrics_audit;

DELETE FROM notes_audit
WHERE date_created < NOW() - INTERVAL 12 MONTH;
OPTIMIZE TABLE notes_audit;

DELETE FROM notifications_audit
WHERE date_created < NOW() - INTERVAL 12 MONTH;
OPTIMIZE TABLE notifications_audit;

DELETE FROM opportunities_audit
WHERE date_created < NOW() - INTERVAL 12 MONTH;
OPTIMIZE TABLE opportunities_audit;

DELETE FROM pmse_inbox
WHERE date_entered < NOW() - INTERVAL 12 MONTH; -- date_entered
OPTIMIZE TABLE pmse_inbox;

DELETE FROM product_templates_audit
WHERE date_created < NOW() - INTERVAL 12 MONTH;
OPTIMIZE TABLE product_templates_audit;

DELETE FROM products_audit
WHERE date_created < NOW() - INTERVAL 12 MONTH;
OPTIMIZE TABLE products_audit;

DELETE FROM project_task_audit
WHERE date_created < NOW() - INTERVAL 12 MONTH;
OPTIMIZE TABLE project_task_audit;

DELETE FROM prospects_audit
WHERE date_created < NOW() - INTERVAL 12 MONTH;
OPTIMIZE TABLE prospects_audit;

DELETE FROM purchased_line_items_audit
WHERE date_created < NOW() - INTERVAL 12 MONTH;
OPTIMIZE TABLE purchased_line_items_audit;

DELETE FROM quotas_audit
WHERE date_created < NOW() - INTERVAL 12 MONTH;
OPTIMIZE TABLE quotas_audit;

DELETE FROM quotes_audit
WHERE date_created < NOW() - INTERVAL 12 MONTH;
OPTIMIZE TABLE quotes_audit;

DELETE FROM report_schedules_audit
WHERE date_created < NOW() - INTERVAL 12 MONTH;
OPTIMIZE TABLE report_schedules_audit;

DELETE FROM revenue_line_items_audit
WHERE date_created < NOW() - INTERVAL 12 MONTH;
OPTIMIZE TABLE revenue_line_items_audit;

DELETE FROM shifts_audit
WHERE date_created < NOW() - INTERVAL 12 MONTH;
OPTIMIZE TABLE shifts_audit;

DELETE FROM tags_audit
WHERE date_created < NOW() - INTERVAL 12 MONTH;
OPTIMIZE TABLE tags_audit;

DELETE FROM tasks_audit
WHERE date_created < NOW() - INTERVAL 12 MONTH;
OPTIMIZE TABLE tasks_audit;

Ben Hamilton (more)

0 Ben Hamilton 5 months ago

However it would be FANTASTIC if the Archive Data tool did clean out audit tables as well

Ben Hamilton (more)
0 Luke Ridgway 5 months ago

I raised a case with support and they trimmed the pmse_bpm table for us (sell, cloud, 14.0 back then, now 14.2).
Everything older than a month.

That seemed to have had a great effect in reducing the DB storage by 3.7GB...!

I also noticed that when I view the Process List (/#pmse_Inbox/layout/casesList) it's lightning fast... previously I would open it, go and do something else for a bit, then come back to it. So this is a nice improvement too.

This was in reply to my question/case:

There is no way via the UI to delete data from a pmse_bpm table. This is on purpose as the data within those tables control moving processes in your instance and deleting something incorrectly can result in lots of problems. The size of your pmse_bpm_flow table is quite typical, but I'd be happy to trim it for you to reduce size if that's okay with you.
0 Frédéric Rinaldi 5 months ago in reply to Luke Ridgway

Hi Luke Ridgway ,

the problem with that kind of basic cleaning is that your BPM can't be defined to run only once. So if you delete the previous execution trace, it can be triggered again.

Same for audit ; it could be great to keep the last change of a field instead of cleaning the full table ; furthermore, with GDPR / Data Privacy, the audit seems to be used to provide the last user that set the field.

But of course, those tables (audit and pmse working tables) need to be cleaned for performance and DB sizing (even more on the cloud) and it would be great that this cleaning (as the core prune jobs) will be a part of the Sugar solution.

Fred
0 Luke Ridgway 5 months ago in reply to Frédéric Rinaldi

That's interesting Frédéric Rinaldi...
I was following the Support advice via the case too.
So yes: it would be great that this cleaning (as the core prune jobs) will be a part of the Sugar solution.