How to find the last time a review was created inside each Crucible project from the database

お困りですか?

アトラシアン コミュニティをご利用ください。

コミュニティに質問

プラットフォームについて: Server と Data Center のみ - この記事は、サーバーおよびデータセンター プラットフォームのアトラシアン製品にのみ適用されます。

目的

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.

Those are just a few examples of use case scenarios.

ソリューション

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
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_KEYPROJECT_NAMELAST_REVIEW_CREATED_ON
CRDefault Project10-12-2020 08:15:45
PROJMy Project16-12-2020 10:43:08
TESTTesting Project24-11-2020 06:37:50
HELLOHello Project06-07-2019 07:21:35
SQLSQL Project17-03-2018 12:15:08
GITGit Project14-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 inside your Crucible projects.


説明How to find the last time a review was created inside each Crucible project from the database
製品Fisheye, Crucible
最終更新日: 2020 年 12 月 17 日

この内容はお役に立ちましたか?

はい
いいえ
この記事についてのフィードバックを送信する
Powered by Confluence and Scroll Viewport.