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

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.


0 vote
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
How to earn badges on the Atlassian Community

How to earn badges on the Atlassian Community

Badges are a great way to show off community activity, whether you’re a newbie or a Champion.

Learn more
Community showcase
Published Thursday in Marketplace Apps

Tips on how to choose the best estimation method for your planning

Planning and grooming sessions all come with their own sets of rules. Team members meet to estimate stories or other work items, all according to an agreed-upon process. And with every session comes ...

76 views 0 11
Read article

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