How to query User Activity via SQL
Platform Notice: Data Center Only - This article only applies to Atlassian products on the Data Center platform.
Note that this KB was created for the Data Center version of the product. Data Center KBs for non-Data-Center-specific features may also work for Server versions of the product, however they have not been tested. 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 query will allow you to pull the User Activity in Confluence as seen in the Activity tab of a user's profile.
Solution
You can run the below query against the database. Replace both instances of
<username>in the below query with your user's exact username.1 2 3select contenttype,title,creationdate,lastmoddate,pageid,spaceid from content where creator = (select user_key from user_mapping where username = '<username>') or lastmodifier = (select user_key from user_mapping where username = '<username>');or to obtain this data for all users along with creation date, you can use something like:
1 2 3 4 5 6select c.contenttype, c.title, c.creationdate, c.lastmoddate, c.pageid, c.spaceid, u.username as "last change made by" from content c JOIN user_mapping u ON c.creator = u.user_key where creator IN (select user_key from user_mapping) or lastmodifier IN (select user_key from user_mapping) group by c.contenttype, c.title, c.creationdate, c.lastmoddate, c.pageid, c.spaceid, u.username order by c.lastmoddate DESC;
Was this helpful?