Get code review coverage by using Crucible Database queries


as we well know, the built-in code review coverage capabilities of Crucible are somewhat limited. Our team needs to generate a code review coverage for an entire program (might be anywhere from 3 month to 1 year). We also would use this to find checkins / revisions with missing code reviews.

Currently I have created a tool using Excel and VB that does what we need but I would like to "migrate" this tool to something web-based. My idea would be to use database queries as follows:

  1. Get all checkins on a certain branch starting with Date XX-XX-XXXX
  2. For each checkin ensure that there is at least 1 Code Review
  3. For each Code Review, check who the participants are and it's status
  4. Finally, return a list with the following format:

Rev. # | Rev. Date | Changes in Rev. | Change Author | Code Review ID | CR Participants | Status / Participant

The Rev# will be the "unique" identifier in this case.

I looked in the available databases (we use PostgreSQL) but I could not find, for example, the Code Review ID (ex: CR-1234) in any of the tables. Is there anywhere a description of the tables that crucible uses and the data that each contains?

Is there any other way to generate flexible code review reports?




4 answers

1 accepted

Hi Alexander,
I know someone in my team (the person left in the meantime) tried your plugin maybe 1.5 - 2 years ago and for some reason they decided against it (seems it was using somehow a lot of resources and also broke some things in our setup - no idea what exactly). I could try to convince my manager to let me try it again.
Eitherway, I managed to get the coverage that we need by using a predefined filter, exporting to CSV, importing in Excel and then with the help of VBA and REST get all the information we need.
I also found out how to do it directly in the database. Here's an example of an SQL function I made to get the Code Review ID with a revision number as input (we use PostgreSQL and I created the function by using pgAdmin III):

CREATE OR REPLACE FUNCTION cr_for_rev(revision character varying)
  RETURNS integer AS
$BODY$declare cr int;
	SELECT min(cru_revpermaid.cru_number) INTO cr
	FROM cru_revpermaid
	JOIN cru_frx ON cru_frx.cru_review_id = cru_revpermaid.cru_review_id
	JOIN cru_frx_revision ON cru_frx_revision.cru_frx_id = cru_frx.cru_frx_id
	JOIN cru_revision ON cru_revision.cru_revision_id = cru_frx_revision.cru_revision
	WHERE cru_revision.cru_revision = revision;
  COST 100;
ALTER FUNCTION cr_for_rev(character varying)
  OWNER TO atlassian;
COMMENT ON FUNCTION cr_for_rev(character varying) IS 'Returns a Code Review ID for a given revision';

To use the function you can do something like this:

SELECT cr_for_rev('12345')

This then returns the Code Review ID as displayed in Crucible (not the internal ID).

The other information I need is relatively easy to get as they are directly available in different databases or require 1 or 2 JOINs.

Now I would only have to create an (web) interface for it and use my new queries to get the data I need smile


Yes, your function looks pretty reasonable to me. Of course you'll need to adjust it to handle multiple reviews of the same revision. Even more frustratingly, don't forget that it's possible for the author to include a revision in a review but actually remove most of the changes using the edit review contents feature. As for the Flyover plugin, earlier versions did have performance problems, but with Crucible >= 3.0 the current version is fairly quick.

This query links review even if the revision was never reviewed, but just when one of the files was modified later. Not good. Did not yet find how to filter those "base" revisions in sql.

Just a note: In the table "cru_review" I see a column called "cru_review_id" but the ID from there doesn't match the ID that I have for the review in FeCru. Ex: cru_review_id = 2725; Crucible ID = CR-2689. I would like to get the CR-2689 from somewhere in the database if possible.

Nevermind, found it. There is a second table, called "cru_revpermaid" which "translates" cru_review_id"s to CR-XXXX.

The biggest challenge is mapping from revision -> review. I used the Crucible API to solve this problem, and to be honest it was quite a bit of work. I've made it available on the marketplace as a plugin which does exactly what you're trying to achieve:

A new version will be available soon that includes a spreadsheet (.csv) report along the lines of what you describe.

Hi Alexander, I convinced my manager to let me try your plugin once again (we had actually bought it 2 years ago but never renewed since it didn't seem to work for us). I installed it in our FeCru server but I get a message that the plugin is incompatible. We're using version 3.3.1. I sent you an "Update request".

Are you running both Fisheye and Crucible? This is a known issue with Fisheye servers, and Atlassian have been slow at looking at the issue. You should be able to download and install the plugin anyway and it will work normally. I'll remind Atlassian of the issue.

Atlassian have raised the priority of this issue and a developer is looking at it at their end. You can follow along here: To install the plugin manually visit the Marketplace listing: From there you download the .jar file using the download link at the bottom ( ) and generate a license using the "Try it free" button at top. Within Crucible admin you can upload a plugin from a local .jar file, and then apply the license. I hope that's all clear, Alex

In case you're interested, a new version of Flyover Reports has just been released including the CSV format of the report.

Hi Alexander,

When would the cloud version of your plugin (Flyover Reports )  be available to download? we are moving to Jira cloud in a month and would be great to have this. currently we have built our own custom SSRS reports which was very painful as you mentioned above. would be great to install this plugin to be able to audit our code reviews easily and cleanly.



Hi Bita A,

Flyover Reports is plugin for Crucible, which only has a self-hosted install model, it is not available in the cloud. Because of this, there is no need for cloud version of the plugin.

I am not sure if JIRA cloud can connect with Crucible.


Suggest an answer

Log in or Join to answer
Community showcase
Bridget Sauer
Published Mar 09, 2018 in Jira Service Desk

E.L. Fridge's take on education, Jira Service Desk, and creative Jira use cases

...word of mouth, so by 2016, we were working with several other entities on campus to implement Jira Service Desk . The Atlassian motto of “for every team” has really come true for us in this case. We...

337 views 0 9
Read article

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
Atlassian Team Tour

Join us on the Team Tour

We're bringing product updates and pro tips on teamwork to ten cities around the world.

Save your spot