Purpose

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 retrieve a list of active users who accessed Confluence during a certain time period
• 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:

  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:

  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:

  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:

  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;