How to Determine Which Users Have Logged But Are Not A Member of a Group

Still need help?

The Atlassian Community is here for you.

Ask the community

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.

    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

    • "requiresPasswordChange"
    • "passwordLastChanged"
    • "lastAuthenticated"
    • "lastAuthenticated"
    • "invalidPasswordAttempts"
Last modified on Feb 26, 2016

Was this helpful?

Yes
No
Provide feedback about this article
Powered by Confluence and Scroll Viewport.