How to query User Activity via SQL

要約

This query will allow you to pull the User Activity in Confluence as seen in the Activity tab of a user's profile.

ソリューション

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