How to search database for Audit Logs
Platform notice: Server and Data Center only. This article only applies to Atlassian products on the Server and Data Center platforms.
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
The information in this page relates to customizations in Bamboo. Consequently, Atlassian Support cannot guarantee to provide any support for the steps described on this page as customizations are not covered under Atlassian Support Offerings. Please be aware that this material is provided for your information only and that you use it at your own risk.
Also, please be aware that customizations done by directly modifying files are not included in the upgrade process. These modifications will need to be reapplied manually on the upgraded instance.
Always back up your data before performing any modifications to the database. If possible, test any alter, insert, update, or delete SQL commands on a staging server first.
Summary
SQL Queries to get details from Bamboo Audit Logs.
Environment
Tested on Bamboo 6, 7, and 8 with PostgreSQL. The provided queries might be slightly different if run on a different database. The Bamboo Audit log is stored in the Database in the audit_log table. Here is the description of the table in Bamboo 7 in Postgres:
Table "public.audit_log"
Column | Type | Collation | Nullable | Default
----------------+-------------------------+-----------+----------+---------
audit_id | bigint | | not null |
child_id | character varying(255) | | |
entity_id | character varying(255) | | |
entity_type | character varying(255) | | |
field_header | character varying(255) | | |
field_type | character varying(255) | | |
msg | character varying(4000) | | |
msg_type | character varying(255) | | |
new_value | character varying(255) | | |
old_value | character varying(255) | | |
msg_time_stamp | bigint | | |
user_name | character varying(255) | | |
Indexes:
"audit_log_pkey" PRIMARY KEY, btree (audit_id)
"entity_time_idx" btree (entity_id, msg_time_stamp)
Solution
Here are some descriptions of some useful fields. Some fields will not be used depending on the type of Audit Log.
| Field | Value |
|---|---|
entity_type | This defines if this log was for a system setting, Null (for Global audit entries), DeploymentProject, Plan, or ImageConfig (for Elastic Images) |
field_header | A description of the field that is being changed |
| field_type | The type of field that is being changed. Some field types are Task, Trigger, ImageConfig, Variable, Repository, Stage, DeploymentProject |
msg | A description of the change that is taking place |
msg_type | Defines a CONFIG_CHANGE or a FIELD_CHANGE |
| new_value | The new value that caused the Log to be taken |
| old_value | The value before the change |
msg_time_stamp | The time when the event happened |
user_name | The user who performed the action |
Example SQL
Find all repository changes
SELECT * FROM AUDIT_LOG WHERE FIELD_TYPE = 'Repository'; Global system audit logs
SELECT * FROM AUDIT_LOG WHERE ENTITY_ID IS NULL AND ENTITY_TYPE IS NULL ORDER BY MSG_TIME_STAMP DESC;Elastic image config audit logs
Elastic image config audit logs are part of the Global system audit logs, but carry a different ENTITY_TYPE:
SELECT * FROM AUDIT_LOG WHERE ENTITY_ID IS NULL AND ENTITY_TYPE = 'ImageConfig' ORDER BY MSG_TIME_STAMP DESC;Build plan audit logs
SELECT * FROM AUDIT_LOG WHERE ENTITY_ID IS NOT NULL AND ENTITY_TYPE = 'Plan' ORDER BY MSG_TIME_STAMP DESC;You can also filter the Plan query by plan key like in the below example:
SELECT * FROM AUDIT_LOG WHERE ENTITY_ID = 'PLAN-KEY' ORDER BY MSG_TIME_STAMP DESC;Deployment project audit logs
SELECT * FROM AUDIT_LOG WHERE ENTITY_ID IS NOT NULL AND ENTITY_TYPE = 'DeploymentProject' ORDER BY MSG_TIME_STAMP DESC;Please note, MSG_TIME_STAMP used by the queries above is stored in epoch / UNIX format. If you want to convert it to standard date, you will have to use your database functions to achieve that. This is for PostgreSQL: