How to associate Page Activity with Confluence Groups

Use Case

As a Confluence Administrator, I want to do a clean-up in the application and remove groups that I believe to be inactive from it. Before doing so, I would like to check whether the users inside these groups have recently contributed somehow in the sense of creating/modifying content or not.

How-To

There's no easy way to do that in the UI, unfortunately, however, achieving that through the database is pretty feasible.

1. The following query will bring all pages created by users inside a given group - Just replace confluence-users by the name of the group you want to query for - You can also replace the content type that's currently set to PAGE to something else, as BLOG for example, if you want to check for other content types:

   1 2 3 4 5 select * from content where creator in ( select user_key from user_mapping where username in ( select user_name from cwd_user where id in ( select child_user_id from cwd_membership where parent_id in ( select id from cwd_group where lower_group_name = 'confluence-users')))) and contenttype = 'PAGE';

2. The following query will bring all pages that have been recently modified by users inside a given group - Just replace confluence-users by the name of the group you want to query for - You can also replace the content type that's currently set to PAGE to something else, as BLOG for example, if you want to check for other content types:

   1 2 3 4 5 select * from content where lastmodifier in ( select user_key from user_mapping where username in ( select user_name from cwd_user where id in ( select child_user_id from cwd_membership where parent_id in ( select id from cwd_group where lower_group_name = 'confluence-users')))) and contenttype = 'PAGE';