Purpose

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.
  
  

  select 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:

  select 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;