How to find the last time a review was created or updated inside each Crucible project from the database
Platform Notice: Data Center Only - This article only applies to Atlassian products on the Data Center platform.
Note that this KB was created for the Data Center version of the product. Data Center KBs for non-Data-Center-specific features may also work for Server versions of the product, however they have not been tested. Support for Server* products ended on February 15th 2024. If you are running a Server product, you can visit the Atlassian Server end of support announcement to review your migration options.
*Except Fisheye and Crucible
Summary
The steps outlined on this article are provided AS-IS. This means we've had reports of them working for some customers — under certain circumstances — yet are not officially supported, nor can we guarantee they'll work for your specific scenario.
You may follow through and validate them on your own non-prod environments prior to production or fall back to supported alternatives if they don't work out.
We also invite you to reach out to our Community for matters that fall beyond Atlassian's scope of support!
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.
PostgreSQL 9.6
1
2
3
4
5
6
7
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;The SELECT query was built for PostgreSQL 9.6. You may need to adjust it to work with other databases e.g. Oracle, MySQL and etc.
Results will look similar to the following:
PROJ_KEY | PROJECT_NAME | LAST_REVIEW_CREATED_ON |
|---|---|---|
CR | Default Project | 10-12-2020 08:15:45 |
PROJ | My Project | 16-12-2020 10:43:08 |
TEST | Testing Project | 24-11-2020 06:37:50 |
HELLO | Hello Project | 06-07-2019 07:21:35 |
SQL | SQL Project | 17-03-2018 12:15:08 |
GIT | Git Project | 14-03-2018 02:45:01 |
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.
Postgres
1
2
3
4
5
6
7
8
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:
PROJ_KEY | PROJECT_NAME | REVIEW_LAST_UPDATED_ON |
|---|---|---|
CR | Default Project | 10-12-2020 08:15:45 |
PROJ | My Project | 16-12-2020 10:43:08 |
TEST | Testing Project | 24-11-2020 06:37:50 |
HELLO | Hello Project | 06-07-2019 07:21:35 |
SQL | SQL Project | 17-03-2018 12:15:08 |
GIT | Git Project | 14-03-2018 02:45:01 |
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.
Was this helpful?