Accessing Jira Audit Information through the Database
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
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 connected to Jira v8.20.2. Any queries may need to be modified to suit different database types and/or Jira versions.
Please note that the audit table has been moved to AO_C77861_AUDIT_ENTITY from Jira v8.8 and onward. Prior to Jira 8.8, the audit table was audit_log.
Also, do note, from Jira 8.8.0, audit log entries can be exported to a CSV file. Refer to Auditing in Jira for more information.
Solution
What's in the audit logs?
Referencing the documentation on Auditing in JIRA, the following information is audited by Jira:
Board created
Custom field created
Custom field updated
Filter created
Global permission added
Group created
Issue deleted
Issue type created
New resolution created
Permission scheme added to project
Permission scheme created
Permission scheme removed from project
Permission scheme updated
Project created
Project roles changed
Project version created
Project version released
User added to group
User created
Workflow created
Workflow scheme added to project
Workflow scheme created
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:
1
SELECT distinct("ACTION") FROM public."AO_C77861_AUDIT_ENTITY"Output:
Audit Log configuration updated
Audit Log search performed
Board created
Custom field created
Custom field updated
Filter created
Global permission added
Group created
Issue deleted
Issue type created
License updated
New license added
New resolution created
Permission scheme added to project
Permission scheme created
Permission scheme removed from project
Permission scheme updated
Project created
Project roles changed
Project version created
Project version released
User added to group
User created
Workflow created
Workflow scheme added to project
Workflow scheme created
These individual events are broken up into categories (the CATEGORY column), which may be useful for a broader view of events:
ACTION, CATEGORY
("Audit Log configuration updated",Auditing)
("Audit Log search performed",Auditing)
("Board created",boards)
("Custom field created",fields)
("Custom field updated",fields)
("Filter created",filters)
("Global permission added",permissions)
("Group created","group management")
("Issue deleted",issue)
("Issue type created","issue types")
("License updated",system)
("New license added",system)
("New resolution created",workflows)
("Permission scheme added to project",permissions)
("Permission scheme created",permissions)
("Permission scheme removed from project",permissions)
("Permission scheme updated",permissions)
("Project created",projects)
("Project roles changed",projects)
("Project version created",projects)
("Project version released",projects)
("User added to group","group management")
("User created","user management")
("Workflow created",workflows)
("Workflow scheme added to project",workflows)
("Workflow scheme created",workflows)
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 (ACTION) and may require exploration to understand which columns should be included in the query. Knowing the possible entries for the ACTION and CATEGORY columns allows for the formation of exploratory queries:
1
SELECT * FROM public."AO_C77861_AUDIT_ENTITY" WHERE "ACTION"='Project created' LIMIT 5;1
SELECT * FROM public."AO_C77861_AUDIT_ENTITY" WHERE "CATEGORY"='projects' LIMIT 5;Click here for an explanation of these queries...
Below is a breakdown of the following example query given above:
1
SELECT * FROM public."AO_C77861_AUDIT_ENTITY" WHERE "CATEGORY"='projects' LIMIT 5;SQL | Explanation |
|---|---|
SELECT | A 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 public."AO_C77861_AUDIT_ENTITY" | Specifying the table from which the database should return results. |
WHERE "CATEGORY"='projects' | Request data from only those rows where the 'CATEGORY' column is equal to 'projects'. Note: The specified value needs to be a possible value from the 'CATEGORY' column, and 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.
Example
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.
1
SELECT "ACTION","ACTION_T_KEY","AREA","CATEGORY","CHANGE_VALUES","USER_NAME" FROM public."AO_C77861_AUDIT_ENTITY" WHERE "ACTION"='Project created' LIMIT 5;Output:
ACTION | ACTION_T_KEY | AREA | CATEGORY | CHANGE_VALUES | USER_NAME |
Project created | jira.auditing.project.created | LOCAL_CONFIG_AND_ADMINISTRATION | projects | [{"i18nKey":"common.words.name","key":"Name","from":null,"to":"Test Scrum 1"},{"i18nKey":"common.words.key","key":"Key","from":null,"to":"TS"},{"i18nKey":"common.concepts.description","key":"Description","from":null,"to":""},{"i18nKey":"common.concepts.projectlead","key":"Project Lead","from":null,"to":"admin"},{"i18nKey":"admin.projects.default.assignee","key":"Default Assignee","from":null,"to":"Unassigned"}] | admin |
Based on that output, the query needs to include object_name, created, and author_key.
1
SELECT "CHANGE_VALUES", "ENTITY_TIMESTAMP", "USER_NAME" FROM public."AO_C77861_AUDIT_ENTITY" WHERE "CATEGORY"='projects';Sample output:
CHANGE_VALUES | ENTITY_TIMESTAMP | USER_NAME |
[{"i18nKey":"common.words.name","key":"Name","from":null,"to":"Test Scrum 1"},{"i18nKey":"common.words.key","key":"Key","from":null,"to":"TS"},{"i18nKey":"common.concepts.description","key":"Description","from":null,"to":""},{"i18nKey":"common.concepts.projectlead","key":"Project Lead","from":null,"to":"admin"},{"i18nKey":"admin.projects.default.assignee","key":"Default Assignee","from":null,"to":"Unassigned"}] | 1644249758595 | admin |
[{"i18nKey":"admin.common.words.users","key":"Users","from":null,"to":"JIRAUSER10000"}] | 1644249759051 | admin |
[{"i18nKey":"common.words.name","key":"Name","from":null,"to":"Version 1.0"},{"i18nKey":"version.releasedate","key":"Release date","from":null,"to":"2022-01-30"}] | 1644249759097 | admin |
[] | 1644249759128 | admin |
[{"i18nKey":"common.words.name","key":"Name","from":null,"to":"Version 2.0"},{"i18nKey":"version.releasedate","key":"Release date","from":null,"to":"2022-02-13"}] | 1644249759151 | admin |
[{"i18nKey":"common.words.name","key":"Name","from":null,"to":"Version 3.0"}] | 1644249759160 | admin |
Was this helpful?