How to retrieve reviews and their respective states, commits, JIRA issue keys, number of files and comments directly from database

Still need help?

The Atlassian Community is here for you.

Ask the community


For reporting purposes, Crucible administrators might want to retrieve all reviews and their respective states, commits, JIRA issue keys, number of files being reviewed and number of comments from a Crucible project directly from database.


NOTE 1: This SQL query below was tested with Fisheye / Crucible 3.7.x. It may require some adjustments in other versions.

NOTE 2: This SQL query below is compatible with PostgreSQL. It may require some adjustments in order to run with other database types.

Run the following query:

        cru_project.cru_name as "Project Name",
        cru_revpermaid.cru_proj_key || '-' || cru_revpermaid.cru_number as "Review Key",
        cru_review.cru_state as "Review State",
        string_agg(distinct(cru_revision.cru_revision), ',') as "Commits",
        cru_review.cru_issue_key as "Review JIRA Key",
        (select count(*)
         from cru_frx where cru_frx.cru_review_id = cru_review.cru_review_id) as "Number of Files",
        (select count (*)
         from cru_comment
         where cru_comment.cru_review_id = cru_review.cru_review_id
                 and not cru_comment.cru_draft
                 and not cru_comment.cru_deleted) as "Number of Comments"
from cru_review
        join cru_revpermaid on cru_revpermaid.cru_review_id = cru_review.cru_review_id
        join cru_project on cru_project.cru_project_id = cru_review.cru_project
        left join cru_frx on cru_frx.cru_review_id = cru_review.cru_review_id
        left join cru_frx_revision on cru_frx_revision.cru_frx_id = cru_frx.cru_frx_id 
        left join cru_revision on cru_revision.cru_revision_id = cru_frx_revision.cru_revision 
where cru_review.cru_review_type = 1
        and (cru_frx_revision.cru_revision_order != 0 
                or cru_revision.cru_added 
                or cru_frx_revision.cru_frx_rev_id is null) -- review without any revisions
        --and cru_revpermaid.cru_proj_key = 'CR' -- use this to restrict by project key
        --and cru_revpermaid.cru_number = 9 -- use this to restrict by review number
group by cru_project.cru_name, 

Sample output:

Project Name     Review Key  Review State  Commits                                   Review JIRA Key  Number of Files  Number of Comments  
---------------  ----------  ------------  ----------------------------------------  ---------------  ---------------  ------------------  
Default Project  CR-1        Draft         (null)                                    (null)           0                0                   
Default Project  CR-2        Draft         73                                        (null)           1                0                   
Default Project  CR-3        Draft         (null)                                    (null)           0                0                   
Default Project  CR-4        Draft         72,73                                     ECS-5378         1                1                   
Default Project  CR-5        Draft         73                                        (null)           1                0                   
Default Project  CR-6        Draft         1:0:T                                     (null)           1                0                   
Default Project  CR-7        Draft         2:0:T                                     (null)           1                0                   
Default Project  CR-8        Draft         59,60                                     JRA-1            1                1                   
Default Project  CR-9        Draft         101,80                                    JRA-1            2                6                   
Default Project  CR-10       Closed        7b5ce3ee4fbd740b6fdc3e53791db2615ab5c690  (null)           1                0                   
Last modified on Jul 31, 2018

Was this helpful?

Provide feedback about this article
Powered by Confluence and Scroll Viewport.