It's not the same without you

Join the community to find out what other Atlassian users are discussing, debating and creating.

Atlassian Community Hero Image Collage

How to get raw statistics about reviews in Crucible ?

Yves Martin Jan 08, 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 Jan 09, 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 Oct 25, 2014

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

Yves Martin Oct 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 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.

Yves Martin Jan 09, 2014

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

Guru Prasad Oct 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
This widget could not be displayed.
This widget could not be displayed.
Community showcase
Published in Opsgenie

Handling Opsgenie Alerts

Hi everyone, welcome to the kb articles for Opsgenie FAQs. I'm one of the Technical Support Engineers for Opsgenie who will be providing weekly posts on FAQs from customers.   All alerts are ...

72 views 0 3
Read article

Community Events

Connect with like-minded Atlassian users at free events near you!

Find an event

Connect with like-minded Atlassian users at free events near you!

Unfortunately there are no Community Events near you at the moment.

Host an event

You're one step closer to meeting fellow Atlassian users at your local event. Learn more about Community Events

Events near you