Get list of users sorted by last login or last activity date

[Jason Ashby]

I was hoping to see a "last login date" or similar in the list of users in the Fisheye admin panel, but I do not.

Is there a way to use the REST API to get such a a list? If not, what about a raw SQL query (I'm using postgres)?

I'm trying to delete old users from the system, i.e. former employees or employees that do not Fisheye often.

[Nick · Accepted solution]

If you have access to the postgress database in use by Crucible, the following:

select cru_user_name, to_timestamp( max(cru_last_viewed)/1000), count(cru_user_name) from cru_recently_visited group by cru_user_name order by max(cru_last_viewed) desc;

Should give you roughly what you need.

It wont give you logins per-se, however will let you know which users recently viewed an 'entity' in FishEye/Crucible. An entity is: a repository, a project, a review or a user page.

You could also consult the cru_login_cookie table to get the last time a login cookie was created for any given user.

[Jason Ashby]

Good advice, thanks. The postgres query gave me `ERROR: column "cru_recently_visited.cru_last_viewed" must appear in the GROUP BY clause or be used in an aggregate function`, so I removed the order by part and did the sorting in a script instead.

[Dotun O]

Hi,

I ran the query without the order part. Please could you interprete the results I have for me? What are the numerical figures and how do I use them to identify recent logins

1385416048262 | user1

1344284846104 | user2

[Nick]

Those numbers are Unix times: milliseconds since epoch.

[Caterina Curti]

Hi,

The information about the last login can be retrieved by following the steps / running the query on the following article:

- How to query for inactive/idle users in FishEye/ Crucible

This page covers both older and newer (4+) versions of Fisheye.

Cheers,

Caterina - Atlassian