Showing results for 
Search instead for 
Did you mean: 
Sign up Log in
Deleted user
0 / 0 points
badges earned

Your Points Tracker
  • Global
  • Feed

Badge for your thoughts?

You're enrolled in our new beta rewards program. Join our group to get the inside scoop and share your feedback.

Join group
Give the gift of kudos
You have 0 kudos available to give
Who do you want to recognize?
Why do you want to recognize them?
Great job appreciating your peers!
Check back soon to give more kudos.

Past Kudos Given
No kudos given
You haven't given any kudos yet. Share the love above and you'll see it here.

It's not the same without you

Join the community to find out what other Atlassian users are discussing, debating and creating.

Atlassian Community Hero Image Collage

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 Jira Service Management

JSM June Challenge #2: Share how your business teams became ITSM rockstars

For JSM June Challenge #2, share how your non-technical teams like HR, legal, marketing, finance, and beyond started using Jira Service Management! Tell us: Did they ask to start using it or...

213 views 6 7
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