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:

   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:

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