Expire audit log items
On large instances, lots of audit logs can build up over time impacting your Jira database performance and clogging up your disk space. This guide explains how you can regularly delete those old audit log items you no longer need.
Configuring audit log expiry
If you are running Automation for Jira 3.12+ then audit log expiry can now be configured by global administrators!
Simply head to the Global Configuration section, then enable audit log expiry by sliding the audit log expiry period to your preferred value.
Hit Save and you're done! Audit logs now expire on that schedule.
Warning
This operation can be expensive and cause a lot of extra database load if you have a lot of audit log items. Please ensure that you pick a suitable time and set maximum run time limits to ensure the expiry only happens in the least busy periods for your Jira instance (e.g. on weekends).
If you have a large backlog of audit items, you may want to delete them all in bulk first using the database queries below during a scheduled downtime, before configuring the automatic audit log expiry in global configuration.
Expiring items directly in the database
Warning
This is a destructive process and if done incorrectly, can cause data loss or corruption. It is recommended that a backup is done in case you need to roll back.
This procedure should only be necessary if you are on an old version of Automation for Jira, or have a lot of audit items that you want to delete in bulk during scheduled downtime.
You do this in 3 steps: Identify the tables, then check the number of items and lastly delete them. None of this information is cached so there is no need to restart Jira or the add-on at the end.
First of all check the counts on the tables you want:
SELECT count(*) FROM "AO_589059_AUDIT_ITEM";
SELECT count(*) FROM "AO_589059_AUDIT_ITEM_ASC_ITEM";
SELECT count(*) FROM "AO_589059_AUDIT_ITEM_CGE_ITEM";
SELECT count(*) FROM "AO_589059_AUDIT_ITEM_COMP_CGE";
SELECT count(*) FROM "AO_589059_AUDIT_ITEM_PROJECT";Next pick a date and see how many items will be deleted:
SELECT count(*)
FROM "AO_589059_AUDIT_ITEM_ASC_ITEM"
WHERE "AUDIT_ITEM_ID" IN (SELECT "ID"
FROM "AO_589059_AUDIT_ITEM"
WHERE "CREATED" < '2016-09-12 07:20:17.508');
SELECT count(*)
FROM "AO_589059_AUDIT_ITEM_CGE_ITEM"
WHERE "AUDIT_ITEM_ID" IN (SELECT "ID"
FROM "AO_589059_AUDIT_ITEM"
WHERE "CREATED" < '2016-09-12 07:20:17.508');
SELECT count(*)
FROM "AO_589059_AUDIT_ITEM_COMP_CGE"
WHERE "AUDIT_ITEM_ID" IN (SELECT "ID"
FROM "AO_589059_AUDIT_ITEM"
WHERE "CREATED" < '2016-09-12 07:20:17.508');
SELECT count(*)
FROM "AO_589059_AUDIT_ITEM_PROJECT"
WHERE "AUDIT_ITEM_ID" IN (SELECT "ID"
FROM "AO_589059_AUDIT_ITEM"
WHERE "CREATED" < '2016-09-12 07:20:17.508');
SELECT count(*)
FROM "AO_589059_AUDIT_ITEM"
WHERE "CREATED" < '2016-09-12 07:20:17.508';This would be a good time to do a backup.
Now delete the items.
DELETE FROM "AO_589059_AUDIT_ITEM_ASC_ITEM"
WHERE "AUDIT_ITEM_ID" IN (SELECT "ID"
FROM "AO_589059_AUDIT_ITEM"
WHERE "CREATED" < '2016-09-12 07:20:17.508');
DELETE FROM "AO_589059_AUDIT_ITEM_CGE_ITEM"
WHERE "AUDIT_ITEM_ID" IN (SELECT "ID"
FROM "AO_589059_AUDIT_ITEM"
WHERE "CREATED" < '2016-09-12 07:20:17.508');
DELETE FROM "AO_589059_AUDIT_ITEM_COMP_CGE"
WHERE "AUDIT_ITEM_ID" IN (SELECT "ID"
FROM "AO_589059_AUDIT_ITEM"
WHERE "CREATED" < '2016-09-12 07:20:17.508');
DELETE FROM "AO_589059_AUDIT_ITEM_PROJECT"
WHERE "AUDIT_ITEM_ID" IN (SELECT "ID"
FROM "AO_589059_AUDIT_ITEM"
WHERE "CREATED" < '2016-09-12 07:20:17.508');
DELETE FROM "AO_589059_AUDIT_ITEM"
WHERE "CREATED" < '2016-09-12 07:20:17.508';Verify the new counts on the tables:
SELECT count(*) FROM "AO_589059_AUDIT_ITEM";
SELECT count(*) FROM "AO_589059_AUDIT_ITEM_ASC_ITEM";
SELECT count(*) FROM "AO_589059_AUDIT_ITEM_CGE_ITEM";
SELECT count(*) FROM "AO_589059_AUDIT_ITEM_COMP_CGE";
SELECT count(*) FROM "AO_589059_AUDIT_ITEM_PROJECT";