How do I find a set of users that are not part of one group
Platform Notice: Data Center Only - This article only applies to Atlassian products on the Data Center platform.
Note that this KB was created for the Data Center version of the product. Data Center KBs for non-Data-Center-specific features may also work for Server versions of the product, however they have not been tested. Support for Server* products ended on February 15th 2024. If you are running a Server product, you can visit the Atlassian Server end of support announcement to review your migration options.
*Except Fisheye and Crucible
Summary
You want to look up users in the database that do not belong to a certain group, but this cannot be done through the UI. For example, if you want to disable any users that do not belong to confluence-users and should not have access you can run the query below. To run this, just replace the <group_name> with the name of the group you want to exclude.
Solution
Connect to your database and run the following query:
1
2
3
4
5
SELECT *
FROM cwd_user
WHERE id NOT IN (SELECT m.child_user_id FROM cwd_membership AS m
JOIN cwd_group AS g ON m.parent_id = g.id
WHERE g.group_name = '<group_name>' AND m.child_user_id IS NOT NULL);Was this helpful?