Accessing Jira Audit Information through the Database

Related content

  • No related content found

Still need help?

The Atlassian Community is here for you.

Ask the community

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.

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

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("ACTION") FROM public."AO_C77861_AUDIT_ENTITY"

Output:

Click here to expand... 
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:

Click here to expand... 
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:

SELECT * FROM public."AO_C77861_AUDIT_ENTITY" WHERE "ACTION"='Project created' LIMIT 5;
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:

SELECT * FROM public."AO_C77861_AUDIT_ENTITY" WHERE "CATEGORY"='projects' LIMIT 5;
SQLExplanation
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 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'.

tip/resting Created with Sketch.

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

Click here to expand...

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 "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.

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

Last modified on Jun 30, 2022

Was this helpful?

Yes
No
Provide feedback about this article

Related content

  • No related content found
Powered by Confluence and Scroll Viewport.