Expire audit log items
Platform Notice: Data Center Only - This article only applies to Atlassian products on the Data Center platform.
Note that this KB was created for the Data Center version of the product. Data Center KBs for non-Data-Center-specific features may also work for Server versions of the product, however they have not been tested. Support for Server* products ended on February 15th 2024. If you are running a Server product, you can visit the Atlassian Server end of support announcement to review your migration options.
*Except Fisheye and Crucible
Summary
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.
Solution
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.
Things to be noted before you proceed
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
This can cause data loss/corruption if done incorrectly
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
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
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';of all check the counts on the tables you want:
1
2
3
4
5
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";1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
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.
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
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:
1
2
3
4
5
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";Was this helpful?