How to Get a List of Users Who Have Modified Content Recently

Summary

Sometimes it is useful for an administrator to find which users have created or edited content recently or on a specific date. This KB illustrates how to query the database via SQL to retrieve this information quickly.

Related KBs:

• How to get a list of active users counting towards the Confluence license

• How to get a list of users with their last logon times

• How to identify inactive users in Confluence

• How to get a list of users/contributors that created page/blog From the database

Solution

Users who have created or edited content since a specific date

The example SQL below retrieves all users who have created or edited content since May 1, 2016. The date can be modified as needed. Note that different databases may have different formats for the date field as well, which may require further tweaks.

• On PostgreSQL:

  1 2 3 4 5 6 SELECT um.lower_username FROM user_mapping um JOIN CONTENT c ON um.user_key = c.lastmodifier WHERE c.lastmoddate >= '2016-05-01' GROUP BY um.lower_username;

• On MS SQL:

  1 2 3 4 5 6 SELECT um.lower_username FROM user_mapping um JOIN CONTENT c ON um.user_key = c.LASTMODIFIER WHERE c.LASTMODDATE >= '2016-05-01' GROUP BY um.lower_username;

• On Oracle:

  1 2 3 4 5 6 SELECT um.lower_username FROM user_mapping um JOIN CONTENT c ON um.user_key = c.lastmodifier WHERE c.lastmoddate >= '01-Jan-2016' GROUP BY um.lower_username;

The last modification/creation of pages, blog posts, or comments

The SQL query below will provide you a list of those users separated by spaces (space keys starting with ~ are referring to personal spaces).

• On PostgreSQL or MS SQL:

  1 2 3 4 5 6 7 8 9 SELECT s.SPACEKEY,s.SPACENAME,u.username, MAX(c.LASTMODDATE) AS lastabsolutemoddate FROM CONTENT c JOIN user_mapping u ON u.user_key=c.LASTMODIFIER JOIN SPACES s ON s.SPACEID = c.SPACEID WHERE c.CONTENTTYPE !='USERINFO' AND c.LASTMODIFIER IS NOT NULL AND c.SPACEID IS NOT NULL GROUP BY s.SPACEKEY,s.SPACENAME,u.username ORDER BY s.SPACEKEY,u.username;