How to Create a Centralized Audit-log via SQL Query

Purpose

Currently, the Security and Encryption's audit logs can only be displayed per macro basis. There are no centralized audit logs yet, and the feature request can be viewed on the following link:

• https://ideas.atlassian.servicerocket.com/ideas/SEC-I-31

The audit logs are located in the AO_DCA036_AUDIT_LOG table, which shows the user-mapping id along with the page-id, rather than the page's title or username:

Answer

You can customize the SQL result to show the page's title and the username by running the following SQL query:

SELECT al.EVENT_TYPE, al.MODIFIED_AT, al.PAGE_ID, c.title, um.username FROM AO_DCA036_AUDIT_LOG al, user_mapping um, CONTENT c where al.MODIFIED_BY = um.user_key and c.CONTENTID = al.PAGE_ID;

Sample Result: