How to get raw statistics about reviews in Crucible ?

[Yves Martin]

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 ?

[Yves Martin · Accepted solution]

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]

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

[Yves Martin]

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

[Jeff Thomas]

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]

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

[Guru Prasad]

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