How to export Automation for Jira audit log from the database in Jira Data Center
プラットフォームについて: Data Center - この記事は、Data Center プラットフォームのアトラシアン製品に適用されます。
このナレッジベース記事は製品の Data Center バージョン用に作成されています。Data Center 固有ではない機能の Data Center ナレッジベースは、製品のサーバー バージョンでも動作する可能性はありますが、テストは行われていません。サーバー*製品のサポートは 2024 年 2 月 15 日に終了しました。サーバー製品を利用している場合は、アトラシアンのサーバー製品のサポート終了のお知らせページにて移行オプションをご確認ください。
*Fisheye および Crucible は除く
要約
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.
環境
Jira Data Center with Automation for Jira.
ソリューション
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
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
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';