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
what do you mean by project ID here? where I can get it from crucible UI ?
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.
Thanks. But it was more efficient for me to query database in fact...
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
what do you mean by project ID here? where I can get it from crucible UI ?
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.