How to Determine Which Users Have Logged But Are Not A Member of a Group
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
Determine the default group name from your External User Directory connector.
select 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