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

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 Join to answer
Community showcase
Teodora [Botron]
Published Feb 15, 2018 in Marketplace Apps

Jira Inferno: The Nine Circles of Jira Administration Hell

If you spend enough time as a Jira admin - whether you are managing a single, mid-sized instance, a large enterprise one or juggling multiple instances at once - you will eventually find yourself in ...

1,080 views 6 19
Read article

Atlassian User Groups

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

Find a group

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

Find my local user group

Unfortunately there are no AUG chapters near you at the moment.

Start an AUG

You're one step closer to meeting fellow Atlassian users at your local meet up. Learn more about AUGs

Groups near you
Atlassian Team Tour

Join us on the Team Tour

We're bringing product updates and pro tips on teamwork to ten cities around the world.

Save your spot