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?

Parents

0 Ben Hamilton 7 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)

Reply

0 Ben Hamilton 7 months ago

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)

Children

No Data