Fisheye/Crucible List all users last log in date and time

Question

I know how to find how many licenses we have remaining for Fisheye / Crucible, however there is not a good way in the admin menu to find out when a user last logged into Fisheye / Crucible.

We use Crowd to manage the users access to the application, but I don't believe there is an easy way through Crowd to get a users last login through Fisheye / Crucible.

Is there a MSSQL script we can use to get a list of our FeCRU users, and their last login date and time?

This is helpful from a license management standpoint. Any help is appreciated.

Felipe Kraemer · Answer

Hi @Divya Yathagiri Venkata,

Can you please clarify which Fisheye / Crucible version you are currently running and what do you use instead of Crowd?

Do you manage users in Fisheye/Crucible itself, or do you retrieve users from either Jira or LDAP?

Divya Yathagiri Venkata · Answer

We don't use crowd, can you help me with the SQL query to get the list of users from db?

Felipe Kraemer · Answer

Hi @Joshua Romine,

It is not clear which version of Fisheye and Crowd you use, but I have just connected a Fisheye 4.5.0 instance to a Crowd 3.1.1 instance, synced users from Crowd and logged in as one of the users synced.

Whenever a user that originates from Crowd logs into Fisheye, Fisheye updates the user details. This means that the information you are looking for is stored at cwd_user.updated_date.

Hopefully this SQL query helps:

select 
   usr.id, 
   usr.user_name, 
   usr.display_name,
   usr.updated_date AS "last_auth"
from 
   cwd_user as usr, 
   cwd_directory as dir
where 
   usr.directory_id = dir.id
and 
   dir.directory_type='CROWD'
order by
   usr.updated_date DESC

Cheers,

Felipe Kraemer