Expiring audit log items

Note: Server only

This guide applies to server customers only. In cloud we already expire audit log items.

On large instances lots of audit logs can build up over time and you may want to start deleting these to improve your Jira database performance and save disk space.

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: Global Configuration

Then enable audit log expiry, but sliding the audit log expiry period to your preferred value. Audit Expiry Config

Hit 'Save' and you're done! Audit logs will now be expired on the schedule you selected. Please note that depending on the number of audit items you have in your Jira instance, this operation can be expensive and cause a lot of extra database load. Please ensure that you pick times and maximum run time limits to ensure expiry only happens in the least busy periods for you 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 scheduled downtime, before configuring automatic audit log expiry in global configuration.

Expiring items directly in the database

WARNING

This is a destructive process and if done incorrectly, could 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.

We will do this in 3 steps. Identify the tables, check the number of items and then delete them.

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 we pick and 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';

Next we delete the items. This would be a good time to do a backup.

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";

None of this information is cached so there is no need to restart Jira or the add-on.