How to get raw statistics about reviews in Crucible ?

Yves Martin
Rising Star
Rising Star
Rising Stars are recognized for providing high-quality answers to other users. Rising Stars receive a certificate of achievement and are on the path to becoming Community Leaders.
January 8, 2014

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

2 votes
Answer accepted
Yves Martin
Rising Star
Rising Star
Rising Stars are recognized for providing high-quality answers to other users. Rising Stars receive a certificate of achievement and are on the path to becoming Community Leaders.
January 9, 2014

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

 

 

 

 

Guru Prasad October 25, 2014

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

Yves Martin
Rising Star
Rising Star
Rising Stars are recognized for providing high-quality answers to other users. Rising Stars receive a certificate of achievement and are on the path to becoming Community Leaders.
October 26, 2014

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

0 votes
Jeff Thomas
Atlassian Team
Atlassian Team members are employees working across the company in a wide variety of roles.
January 8, 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.

Yves Martin
Rising Star
Rising Star
Rising Stars are recognized for providing high-quality answers to other users. Rising Stars receive a certificate of achievement and are on the path to becoming Community Leaders.
January 9, 2014

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

Guru Prasad October 25, 2014

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
TAGS
AUG Leaders

Atlassian Community Events