How to Audit Crucible licenses

[Electrawn]

Environment:

10,000+ Enterprise Jira, Unlimited Fisheye but only 100 Crucible licenses.

Problem: Product doesn't manage licenses via LDAP and Crucible was autoadded to users that don't need the functionality from previous versions of Fisheye/Crucible.

[Electrawn]

Assuming Your Database is MySQL, run this query against your Crucible/Fisheye database:

select distinct cru_user.cru_user_name,cru_crucible_enabled,from_unixtime(cru_recently_visited.cru_last_viewed/1000) as last_view from cru_user JOIN cru_recently_visited on cru_user.cru_user_name = cru_recently_visited.cru_user_name where cru_crucible_enabled=1 and cru_last_viewed = (SELECT MAX(cru_last_viewed) from cru_recently_visited WHERE cru_user.cru_user_name = cru_recently_visited.cru_user_name) 
UNION
select distinct cru_user.cru_user_name,cru_crucible_enabled,from_unixtime(cru_recently_visited.cru_last_viewed/1000) as last_view from cru_user LEFT JOIN cru_recently_visited on cru_user.cru_user_name = cru_recently_visited.cru_user_name where cru_crucible_enabled=1 and cru_recently_visited.cru_last_viewed is null ORDER BY last_view

This query selects all the users from Crucible and their last login time. The union selects all the users who have crucible enabled but have never logged in. Finally, we sort by date.

You can export the query and have a system admin go in and manually delete users or enhance this to auto delete crucible licenses by setting cru_crucible_enabled to zero.

Figured this was worth sharing, enjoy.

[Nick]

Brand new in Crucible 3.3 is an Admin REST API for User and Group Management.

You could use the PUT method to toggle crucible access for each user: https://docs.atlassian.com/fisheye-crucible/latest/wadl/fecru.html#d2e187

[Sergey Svishchev]

But last login date isn't available over REST...