Come for the products,
stay for the community

The Atlassian Community can help you and your team get more value out of Atlassian products and practices.

Atlassian Community about banner
Community Members
Community Events
Community Groups

Listing users with last login time in Crowd

I'm trying to find user accounts that haven't been used in a long time. I found this KB article, which cointains an SQL query to list users with last login time in Crowd, I have just one problem with it: The numbers are odd.

According to Crowd we currently have 451 users (they come from an Active Directory and an OpenLDAP server).

Running the query as it is given in the KB article returns 49 users with a last login timestamp.

Running a simpler query to list all users (SELECT user_name FROM cwd_user) returns 125 users.

How can I interpret these numbers? Are 451 the users that could possibly log in because they are in an allowed group, but only 49 users actually have logged in?

The returned users are from both directory sources.

The Crowd instance is used by JIRA, Confluence, FishEye and Apache basic auth.

1 answer

1 accepted

1 vote
Answer accepted

Are any of your LDAP connector directories uncached? Crowd won't record information like last login time for uncached directories, because only cached users show up in cwd_user. That might explain why you have a lower-than-expected amount of users appearing with select user_name from cwd_user - but it sure doesn't explain why you'd see users from both directories showing up in that case (I assume you've verified they are from different directories by comparing the directory_id on the cwd_user rows), unless you have more directories defined.

(I've assumed your directories are LDAP connector directories, rather than delegated authentication directories.) 

Where are you seeing the 451 number?

Just in case there's any confusion about the 49 <-> 125 discrepency, the query given in the KB article won't show users who have never authenticated. You can view those users with a query like the following:

select cwd_user.directory_id, cwd_user.user_name from cwd_user where not exists (select id from cwd_user_attribute where cwd_user_attribute.user_id = and cwd_user_attribute.attribute_name = 'lastAuthenticated');

Thanks, this really helps. The directories are not cached, so that explains it. The query with uses which haven't authenticated helps too, the result is 76 users which is the exact difference between 125 and 49. The number 451 is shown in the license information.

Suggest an answer

Log in or Sign up to answer
Community showcase
Published in Apps & Integrations

📅 Partner Webinar & Events Opportunities: July 2022

Hi community 👋, Here is another monthly roundup of the partner-organized events in July 2022. If there is any event missing please let me know in the comments below 🙏 If you're looking for Atla...

27 views 0 2
Read article

Community Events

Connect with like-minded Atlassian users at free events near you!

Find an event

Connect with like-minded Atlassian users at free events near you!

Unfortunately there are no Community Events near you at the moment.

Host an event

You're one step closer to meeting fellow Atlassian users at your local event. Learn more about Community Events

Events near you