How to Determine Which Users Have Logged But Are Not A Member of a 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
Reasons Why You Would Do This
Confluence was set up with an external user directory that does not automatically place users in a given group. A number of people log in, but are confronted without having sufficient permissions to access Confluence. The external user directory is then set up to have users automatically placed in a permitted group. We now need to go back and find those users that have logged in, but are not a part of a permitted group and thus are denied access. This is best done through a database query
The Query
Determine the default group name from your External User Directory connector.
1 2 3 4 5 6 7 8 9 10select user_name from cwd_user where id not in ( select m.child_user_id from cwd_membership m left join cwd_group g on (m.parent_id = g.id) where g.group_name = '<group_name>') and id in ( select user_id from cwd_user_attribute where attribute_name = 'lastAuthenticated')We are looking for the cwd_user_attribute 'lastAuthenticated" because it is created only after the user has logged on.
The other fields are of dubious use in this case
"requiresPasswordChange"
"passwordLastChanged"
"lastAuthenticated"
"lastAuthenticated"
"invalidPasswordAttempts"
Was this helpful?