How to export Automation for Jira audit log from the database in Jira Data Center

Platform Notice: Data Center - This article applies to Atlassian products on the Data Center platform.

Note that this knowledge base article was created for the Data Center version of the product. Data Center knowledge base articles 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

At times, the audit log is needed for troubleshooting, but exporting it from the UI times out, or could take effort to export from several rules.

This article provides a query that brings the audit data, so the admins can tweak the filters as desired to have more flexibility.

Environment

Jira Data Center with Automation for Jira.

Solution

(info) These queries were made in PostgreSQL, so keep in mind that it might be needed to remove the double-quotes for the table and column names when running them in other DB engines.

Audit logs for rule executions

(warning) Make sure to add the rule name and desired dates in the WHERE clause!

SELECT ai."OBJECT_ITEM_NAME" as "Rule name",
	ai."OBJECT_ITEM_ID" as "Rule ID",
	ai."CATEGORY",
	au.lower_user_name as "Author username",
	ai."CREATED",
	ai."DURATION",
	ai."END_TO_END_DURATION",
	ai."START_TIME" as "Rule start",
	ai."END_TIME" as "Rule end",
	ai."EVENT_SOURCE",
	aiai."NAME" as "Associated item name",
	aiai."TYPE_NAME" as "Issue",
	aicg."COMPONENT_NAME_KEY",
	aici."MESSAGE",
	aicg."COMPONENT_ID",
	aicg."DURATION" as "Component duration",
	aicg."START_TIME" as "Component start time"
FROM "AO_589059_AUDIT_ITEM" ai
LEFT JOIN "AO_589059_AUDIT_ITEM_ASC_ITEM" aiai ON aiai."AUDIT_ITEM_ID"=ai."ID"
LEFT JOIN "AO_589059_AUDIT_ITEM_COMP_CGE" aicg ON aicg."AUDIT_ITEM_ID"=ai."ID"
LEFT JOIN "AO_589059_AUDIT_ITEM_CGE_ITEM" AICI ON aici."AUDIT_ITEM_ID"=ai."ID" AND aici."AUDIT_ITEM_COMPONENT_CHANGE_ID"=aicg."ID"
JOIN app_user au ON ai."AUTHOR_KEY"=au.user_key
WHERE ai."CATEGORY" != 'CONFIG_CHANGE'
	AND ai."OBJECT_ITEM_NAME" = '<RULE_NAME_HERE>'
	AND ai."CREATED" > '2025-01-29T00:00:00' AND ai."CREATED" < '2025-01-30T00:00:00'
ORDER BY ai."ID";

Audit log for rule changes

(warning) Make sure to add the rule name and desired dates in the WHERE clause!

SELECT ai."OBJECT_ITEM_NAME" as "Rule name",
	ai."CATEGORY",
	au.lower_user_name as "Author username",
	ai."CREATED",
	ai."DURATION",
	ai."END_TO_END_DURATION",
	ai."START_TIME" as "Rule start",
	ai."END_TIME" as "Rule end",
	ai."EVENT_SOURCE",
	aici."FIELD_NAME" as "Operation"
FROM "AO_589059_AUDIT_ITEM" ai
LEFT JOIN "AO_589059_AUDIT_ITEM_ASC_ITEM" aiai ON aiai."AUDIT_ITEM_ID"=ai."ID"
LEFT JOIN "AO_589059_AUDIT_ITEM_CGE_ITEM" aici ON aici."AUDIT_ITEM_ID"=ai."ID"
JOIN app_user au ON ai."AUTHOR_KEY"=au.user_key
WHERE ai."CATEGORY" = 'CONFIG_CHANGE'
 	AND ai."OBJECT_ITEM_NAME" = '<RULE_NAME_HERE>'
	AND ai."CREATED" > '2025-01-29T00:00:00' AND ai."CREATED" < '2025-01-30T00:00:00';



Last modified on Jan 29, 2025

Was this helpful?

Yes
No
Provide feedback about this article
Powered by Confluence and Scroll Viewport.