How to fetch advanced Audit information in Confluence from Database
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
Summary
This document will help you find the advanced audit information through the Database queries.
Environment
Confluence 7.5 and above
Solution
Auditing in Confluence was enhanced from Confluence 7.5 and above version to provide advanced level audit information to the users. To know more about the new features, please check the below document.
- Auditing in Confluence
- With enhanced auditing, Coverage level areas were introduced in Confluence. Coverage levels reflect the number and frequency of events that are logged. Some coverage levels are only available with a Data Center license.
- Prior to 7.5, Audit information was stored in AUDIT RECORD table. Now the data is stored in AO_C77861_AUDIT_ENTITY table. If you are using Confluence 7.4 or below, please refer the below document
AO_C77861_AUDIT_ENTITY Table structure
Let's see the explanation of the major columns present in the table to help us understand the log information better.
| Column | Meaning | Example |
| ACTION | What action was performed | Space permission removed |
| ACTION_T_KEY | Audit Key of the action | audit.logging.summary.space.permission.removed |
| AREA | Coverage Area | PERMISSIONS |
| ATTRIBUTES | Attributes of the action performed | Blank Value (It can have some value for different actions) |
| CATEGORY | Category of the Audit action performed | Permissions |
| CATEGORY_T_KEY | Category key | audit.logging.category.permissions |
| CHANGE_VALUES | What values were modified | [{"i18nKey":"Group","key":"Group","from":"confluence-administrators","to":null},{"i18nKey":"Type","key":"Type","from":"SETPAGEPERMISSIONS","to":null}] |
| ENTITY_TIMESTAMP | Timestamp | 1.6287E+12 |
| ID | Unique ID | 106 |
| LEVEL | Coverage Level(can be Base, Advanced Or Full) | BASE |
| METHOD | What method was used for the action | Browser |
| NODE | From which node was action performed (DC feature) | NA |
| PRIMARY_RESOURCE_ID | ID of the content on which action was performed(Can be Page or Space or any other resource) | 98306 |
| PRIMARY_RESOURCE_TYPE | Resource Type | Space |
| SOURCE | Information of the Source | 0:0:0:0:0:0:0:1 |
| SYSTEM_INFO | System Information | http://localhost:27113/c7113 |
| USER_ID | User ID(User key) | 4028818a7b360cbb017b360d473e0000 |
| USER_NAME | Username of the user who performed the action | admin |
| USER_TYPE | Type of the user(Can be User,System etc.) | user |
Sample Queries
We can get the advanced audit information using the SQL queries utilizing proper combination of the above Columns. Before running any query to pull out the information, make sure that the Coverage level is set properly. e.g. If you want to pull out the audit information about the Application Link creation which falls into Advanced Coverage level, you will have to set the Coverage level to Advanced through the UI. Otherwise the information will not be stored in the DB. To know more on this, please refer the below document.
These queries are written and tested in PostgreSQL. You can modify it as per your DB
Example 1
If we want the audit information related to Application Link, we can run the below query to find that.
Select "ACTION","ACTION_T_KEY","AREA","CHANGE_VALUES","USER_NAME"
from public."AO_C77861_AUDIT_ENTITY" where "LEVEL"='ADVANCED' and "ACTION"='Application link created'Result
| ACTION | ACTION_T_KEY | AREA | CHANGE_VALUES | USER_NAME |
| Application link created | audit.logging.summary.applink.added | GLOBAL_CONFIG_AND_ADMINISTRATION | [{"i18nKey":"audit.logging.changed.value.applink.type","key":"Type","from":null,"to":"Jira"},{"i18nKey":"audit.logging.changed.value.applink.name","key":"Name","from":null,"to":"JIRA"},{"i18nKey":"audit.logging.changed.value.applink.url","key":"Display URL","from":null,"to":"http://localhost:8870/j870"},{"i18nKey":"audit.logging.changed.value.applink.primary","key":"Primary","from":null,"to":"true"}] | admin |
Example 2
If we want the audit information related to User directory Creation, the below query will help.
Select "ACTION","ACTION_T_KEY","AREA","CHANGE_VALUES","USER_NAME"
from public."AO_C77861_AUDIT_ENTITY" where "ACTION"='User directory created'Result
| ACTION | ACTION_T_KEY | AREA | CHANGE_VALUES | USER_NAME |
| User directory created | audit.logging.summary.directory.added | USER_MANAGEMENT | [{"i18nKey":"ID","key":"ID","from":null,"to":"327681"},{"i18nKey":"Name","key":"Name","from":null,"to":"Confluence Internal Directory"},{"i18nKey":"Active","key":"Active","from":null,"to":"Yes"},{"i18nKey":"Encryption type","key":"Encryption type","from":null,"to":"atlassian-security"},{"i18nKey":"Description","key":"Description","from":null,"to":"Confluence default internal directory"},{"i18nKey":"Type","key":"Type","from":null,"to":"INTERNAL"},{"i18nKey":"Allowed operation","key":"Allowed operation","from":null,"to":"CREATE_USER"},{"i18nKey":"Allowed operation","key":"Allowed operation","from":null,"to":"DELETE_GROUP"},{"i18nKey":"Allowed operation","key":"Allowed operation","from":null,"to":"UPDATE_USER"},{"i18nKey":"Allowed operation","key":"Allowed operation","from":null,"to":"UPDATE_GROUP"},{"i18nKey":"Allowed operation","key":"Allowed operation","from":null,"to":"UPDATE_ROLE_ATTRIBUTE"},{"i18nKey":"Allowed operation","key":"Allowed operation","from":null,"to":"DELETE_ROLE"},{"i18nKey":"Allowed operation","key":"Allowed operation","from":null,"to":"UPDATE_ROLE"},{"i18nKey":"Allowed operation","key":"Allowed operation","from":null,"to":"DELETE_USER"},{"i18nKey":"Allowed operation","key":"Allowed operation","from":null,"to":"CREATE_GROUP"},{"i18nKey":"Allowed operation","key":"Allowed operation","from":null,"to":"CREATE_ROLE"},{"i18nKey":"Allowed operation","key":"Allowed operation","from":null,"to":"UPDATE_GROUP_ATTRIBUTE"},{"i18nKey":"Allowed operation","key":"Allowed operation","from":null,"to":"UPDATE_USER_ATTRIBUTE"}] | Anonymous |