Purpose

I want to find out when was the last time that a review was created inside my Crucible projects. I need to do this because...

• I want to review recent activity in certain projects.
• I want to know how often people create new reviews in Crucible.
• I want to check if any reviews were creating in the last X number of days.
• I want to check when was the last time a review was updated on a Crucible Project.

Those are just a few examples of use case scenarios.

Solution

Find the recent review created date for each project

This information cannot be found from the Crucible UI, but can be found by querying the Crucible database.

The following SELECT will give you a list of Crucible projects containing the project key and name as well as the last time a review was created inside each one of them.

select distinct on (CP.CRU_PROJ_KEY) CP.CRU_PROJ_KEY PROJ_KEY,
       CP.CRU_NAME PROJECT_NAME,
       TO_CHAR(TO_TIMESTAMP(CR.CRU_CREATE_DATE / 1000), 'DD-MM-YYYY HH24:MI:SS') LAST_REVIEW_CREATED_ON
  from CRU_REVIEW CR
  join CRU_PROJECT CP
    on CR.CRU_PROJECT = CP.CRU_PROJECT_ID
 order by CP.CRU_PROJ_KEY, CR.CRU_CREATE_DATE DESC;

Results will look similar to the following:

Find the recent review updated date for each project

If you are looking to find out the most recent date when a review was updated on a project then the below query can be used.

SELECT DISTINCT ON (CP.CRU_PROJ_KEY) CP.CRU_PROJ_KEY PROJ_KEY,
       CP.CRU_NAME PROJECT_NAME,
       TO_CHAR(TO_TIMESTAMP(CSC.CRU_TIME_STAMP / 1000), 'DD-MM-YYYY HH24:MI:SS') REVIEW_LAST_UPDATED_ON
  FROM CRU_REVIEW CR
  JOIN CRU_PROJECT CP
    ON CR.CRU_PROJECT = CP.CRU_PROJECT_ID
  JOIN CRU_STATE_CHANGE CSC ON CR.CRU_REVIEW_ID = CSC.CRU_REVIEW_ID
 ORDER BY CP.CRU_PROJ_KEY, CSC.CRU_TIME_STAMP DESC;

Results will look similar to the following:

You can export it to a CSV file to sort through the results and see when was the last time reviews were created or updated inside your Crucible projects.