How to retrieve all reviews and respective participants from a Crucible project directly in database

Still need help?

The Atlassian Community is here for you.

Ask the community

Purpose

For reporting purposes, Crucible administrators might want to retrieve all reviews and their respective participants from a Crucible project directly in database.

Solution

  • Run the following SQL query for PostgreSQL:

    SELECT
        (p.cru_proj_key || '-' || rid.cru_number) as review_key,
        u.cru_user_name AS review_participants
    FROM
        cru_revpermaid rid,
        cru_project p,
        cru_review r,
        cru_review_participant rp,
        cru_user u
    WHERE
        rid.cru_proj_key = p.cru_proj_key
    AND rid.cru_review_id = r.cru_review_id
    AND rp.cru_review_id = r.cru_review_id
    AND rp.cru_user = u.cru_user_id
  • Sample result, in which there are two projects named PROJ-A and PROJ-B:

    REVIEW_KEYREVIEW_PARTICIPANTS
    PROJ-A-1user1
    PROJ-A-1user2
    PROJ-B-1user1
    PROJ-B-2user3


Last modified on Feb 11, 2022

Was this helpful?

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