It's not the same without you
Join the community to find out what other Atlassian users are discussing, debating and creating.
As I use Crucible without FishEye, project dashboards with statistics are not available.
I would like to report some basic statistics about reviews in Crucible, per project: number of reviews, first review creation date, latest review update date, number of users working on a project based on reviews and comments for instance
What is the best way to get it ? SQL query ?
Here are some of the queries I built, my instance is Crucible 2.7.13:
All projects statistics:
select p.cru_proj_key as projectkey, p.cru_name as projectname, count(r.cru_review_id) as '#reviews', count(c.cru_comment_id) as '#comments', FROM_UNIXTIME(greatest(coalesce(max(r.cru_create_date), 0), coalesce(max(c.cru_create_date), 0))/1000, '%Y-%m-%d') as latestactivitydate from cru_project p left join ( cru_review r left join ( cru_review_comment cr inner join cru_comment c on cr.cru_comment_id = c.cru_comment_id ) on cr.cru_review_id = r.cru_review_id ) on p.cru_project_id = r.cru_project group by p.cru_project_id order by latestactivitydate desc;
Users' activity (reviews and comments) on a single project (here ID 12 to replace by your own, thanks to query select cru_project_id, cru_name, cru_proj_key from cru_project;
):
select u.cru_user_name as username, count(r.cru_review_id) as '#reviews', FROM_UNIXTIME(min(r.cru_create_date)/1000, '%Y-%m-%d') as 'firstreviewdate', FROM_UNIXTIME(max(r.cru_create_date)/1000, '%Y-%m-%d') as 'lastreviewdate' from cru_review r, cru_user u where r.cru_creator = u.cru_user_id and r.cru_project = 12 group by u.cru_user_id order by lastreviewdate desc; select u.cru_user_name as username, count(c.cru_comment_id) as '#comments', FROM_UNIXTIME(min(c.cru_create_date)/1000, '%Y-%m-%d') as 'firstcommentdate', FROM_UNIXTIME(max(c.cru_create_date)/1000, '%Y-%m-%d') as 'lastcommentdate' from cru_review r, cru_comment c, cru_review_comment rc, cru_user u where c.cru_user_id = u.cru_user_id and r.cru_review_id = rc.cru_review_id and rc.cru_comment_id = c.cru_comment_id and r.cru_project = 12 group by u.cru_user_id order by lastcommentdate desc;
All users' activity on the system, to control your license usage for instance:
select u.cru_user_name as username, count(r.cru_review_id) as '#reviews', count(c.cru_comment_id) as '#comments', FROM_UNIXTIME(greatest(coalesce(max(r.cru_create_date), 0), coalesce(max(c.cru_create_date), 0))/1000, '%Y-%m-%d') as latestactivitydate from cru_user u left join cru_review r on u.cru_user_id = r.cru_review_id left join cru_comment c on c.cru_user_id = u.cru_user_id group by u.cru_user_name order by latestactivitydate desc;
Hope this helps
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
You can get project ID in database by querying {{select cru_project_id, cru_name, cru_proj_key from cru_project;}}
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
Take a look at the Crucible REST API - https://docs.atlassian.com/fisheye-crucible/latest/wadl/crucible.html
It has several endpoints that will get information about reviews. I didn't see anything to filter by project at first glance, so you may need to do that on your end when processing the data.
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
This approach requires you to have the JIRA administrative rights. The main aim of this article is to help you achieve an organized, easy-to-maintain workflows in your JIRA instance thereby, reducin...
Connect with like-minded Atlassian users at free events near you!
Find a groupConnect with like-minded Atlassian users at free events near you!
Unfortunately there are no AUG chapters near you at the moment.
Start an AUGYou're one step closer to meeting fellow Atlassian users at your local meet up. Learn more about AUGs
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.