How to search database for Audit Logs
プラットフォームについて: Server および Data Center のみ。この記事は、Server および Data Center プラットフォームのアトラシアン製品にのみ適用されます。
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.
*Fisheye および 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.
また、ファイルの直接変更によって行われるカスタマイズは、アップグレード プロセスには含まれない点にご注意ください。このような変更は、アップグレード後のインスタンスで手動で再適用する必要があります。
データベースの変更を行う場合は必ず事前にバックアップを取得してください。可能な場合は、まずステージング サーバーで SQL コマンドの変更、挿入、更新、または削除を行うようにします。
要約
SQL Queries to get details from Bamboo Audit Logs.
環境
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)
ソリューション
Here are some descriptions of some useful fields. Some fields will not be used depending on the type of Audit Log.
| フィールド | 値 |
|---|---|
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: