Accessing Jira Audit Information through the Database

お困りですか?

アトラシアン コミュニティをご利用ください。

コミュニティに質問

要約

In certain cases, it may be necessary to access Jira's audit log through direct database queries. This article gives an overview of how to do so.

All content in this article was generated with a PostgreSQL database connect to Jira v6.3.10. Any queries may need to be modified to suit different database types and/or Jira versions.

Please note that the audit table has changed from Jira v8.8 and onwards. The audit table is now called AO_C77861_AUDIT_ENTITY.

What's in the audit logs?

Referencing the documentation on Auditing in JIRA, the following information is audited by Jira:

  • ユーザー管理
  • グループ管理
  • プロジェクトの変更
  • 権限の変更
  • ワークフローの変更
  • 通知スキームの変更
  • カスタムフィールドの変更
  • component changes
  • version changes

Viewing the audit log in Jira:


Where is this information stored in the database?

Jira stores its audit information in the audit_log table.

The following SQL query lists which events are individually recorded:

SELECT DISTINCT(summary) FROM audit_log;

出力:

ここをクリックして展開...
"Custom field created"
"Custom field updated"
"Field Configuration scheme added to project"
"Field Configuration scheme created"
"Field Configuration scheme updated"
"Global permission added"
"Group created"
"Permission scheme added to project"
"Permission scheme created"
"Permission scheme removed from project"
"Permission scheme updated"
"Project component created"
"Project created"
"Project roles changed"
"Project updated"
"Project version created"
"Project version released"
"User added to group"
"User created"
"Workflow created"
"Workflow scheme added to project"
"Workflow scheme created"
"Workflow scheme updated"
"Workflow updated"

These individual events are broken up into categories (the category column), which may be useful for a broader view of events:

ここをクリックして展開...
"user management"
"fields"
"group management"
"projects"
"workflows"
"permissions"

How do I query for the information I need?

The audit_log table contains several columns with information that varies depending on the audit event (summary) and may require exploration to understand which columns should be included in the query. Knowing the possible entries for the summary and category columns allows for the formation of exploratory queries:

SELECT * FROM audit_log WHERE category = 'categoryType' LIMIT 5;
SELECT * FROM audit_log WHERE summary = 'summaryType' LIMIT 5;
Click here for an explanation of these queries...

Below is a breakdown of the following example query given above:

SELECT * FROM audit_log WHERE summary = 'summaryType' LIMIT 5;
SQL説明
SELECTA request to the database to return data.
*

A 'wildcard' operator. In this case, return all available columns.

As seen in the example below, this can be replaced with multiple, comma-separated column names to return a subset of the available columns in a table.

FROM audit_logSpecifying the table from which the database should return results.
WHERE summary = 'summaryType'

Request data from only those rows where the 'summary' column is equal to 'summaryType'.

tip/resting Created with Sketch.

summaryType needs to be replaced with a possible value from the 'summary' column, but needs to be encapsulated in quotations.

LIMIT 5

Truncate the query results to 5 rows of data. This is helpful for preventing the execution of large queries.

This can be adjusted to any number of results or removed entirely to return all matching results.

;

Signals the end of the SQL query.


In reviewing the results of these queries, the SELECT statement can be modified to isolate the information needed.

ここをクリックして展開...

Goal: Output the create time and username for each project.

Referencing the list of possible entries in the summary column, we see that "Project created" will likely contain the information desired.

SELECT * FROM audit_log WHERE summary = 'Project created' LIMIT 1;

出力:

IDremote_addresscreatedauthor_key要約categoryobject_typeobject_idobject_nameobject_parent_idobject_parent_nameauthor_typeevent_source_namesearch_field
10015"0:0:0:0:0:0:0:1""2014-12-08 14:11:40.564-06""admin""Project created""projects""PROJECT""10000""asdf"

1
"admin 0:0:0:0:0:0:0:1 project created projects asd jira internal directory generated by service project lead"

Based on that output, the query needs to include object_name, created, and author_key.

SELECT object_name,created,author_key FROM audit_log WHERE summary = 'Project created';

出力サンプル

object_namecreatedauthor_key
asdf"2014-12-08 14:11:40.564-06""admin"

最終更新日 2021 年 4 月 14 日

この内容はお役に立ちましたか?

はい
いいえ
この記事についてのフィードバックを送信する
Powered by Confluence and Scroll Viewport.