How to get raw statistics about reviews in Crucible ?

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 ?

2 answers

1 accepted

This widget could not be displayed.

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

 

 

 

 

what do you mean by project ID here? where I can get it from crucible UI ?

You can get project ID in database by querying {{select cru_project_id, cru_name, cru_proj_key from cru_project;}}

This widget could not be displayed.
Jeff Thomas Atlassian Team Jan 08, 2014

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.

Thanks. But it was more efficient for me to query database in fact...

what do you mean by project ID here? where I can get it from crucible UI ?

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 Wednesday in New to Jira

Are you planning to trial, or are currently trialling Jira Software? - We want to talk to you!

Hello! I'm Rayen, a product manager at Atlassian. My team and I are working hard to improve the trial experience for Jira Software Cloud. We are interested in   talking to 20 people planning t...

88 views 1 0
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