How to Audit Crucible licenses

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.

2 answers

This widget could not be displayed.

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.


This widget could not be displayed.
Nick Pellow Atlassian Team Feb 12, 2014

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

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

Suggest an answer

Log in or Sign up to answer
Atlassian Summit 2018

Meet the community IRL

Atlassian Summit is an excellent opportunity for in-person support, training, and networking.

Learn more
Community showcase
Posted an hour ago in Statuspage

What are your best incident management tips and stories? #HugOps

 👋Community members! Downtime happens. And great incident response takes a village. Teams like Support, Dev, SRE, Ops, IT, and Marketing have to come together to resolve the problem while keep...

9 views 0 2
Join discussion

Atlassian User Groups

Connect with like-minded Atlassian users at free events near you!

Find a group

Connect with like-minded Atlassian users at free events near you!

Find my local user group

Unfortunately there are no AUG chapters near you at the moment.

Start an AUG

You're one step closer to meeting fellow Atlassian users at your local meet up. Learn more about AUGs

Groups near you