Accessing JIRA Audit Information through the Database

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 connect to JIRA v6.3.10. Any queries may need to be modified to suit different database types and/or JIRA versions.

What's in the audit logs?

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

  • user management
  • group management
  • project changes
  • permission changes
  • workflow changes
  • notification scheme changes
  • custom field changes
  • 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;

Output:

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

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

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 * FROM audit_log WHERE summary = 'Project created' LIMIT 1;

Output:

idremote_addresscreatedauthor_keysummarycategoryobject_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 desk 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';

Sample output:

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

Last modified on Nov 12, 2018

Was this helpful?

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