Listing users with last login time in Crowd

[Gerald Schneider]

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.

[Caspar Krieger · Accepted solution]

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 = cwd_user.id and cwd_user_attribute.attribute_name = 'lastAuthenticated');

[Gerald Schneider]

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.