How to retrieve a list of reviews with more than one repository ?

[Abdu Elbakry]

I have access to our SQL Server DB and trying to retrieve a list of reviews with more than one repository.

For example 

Review Key - Number of repos used 
Review-332 - 1
Review-334 - 3
Review-333 - 1
Review-335 - 2

I tried this : https://confluence.atlassian.com/fishkb/how-to-retrieve-a-list-of-users-and-groups-granted-permissions-to-repositories-and-projects-from-the-database-976169896.html

but I am not looking for permissions , Just how to see which review is linked to which repos .

[pmaisenovich]

select nested2.cru_review_id, nested2.repos, '<fisheye_url>/cru/' + cru_revpermaid.cru_proj_key + '-' + CAST(cru_revpermaid.cru_number as varchar) from (
select nested1.cru_review_id, count(1) repos from (
select distinct cru_review.cru_review_id, cru_revision.cru_source_name
from <FishEyeDB>.dbo.cru_review
join <FishEyeDB>.dbo.cru_frx on cru_review.cru_review_id = cru_frx.cru_review_id
join <FishEyeDB>.dbo.cru_frx_revision on cru_frx.cru_frx_id = cru_frx_revision.cru_frx_id
join <FishEyeDB>.dbo.cru_revision on cru_frx_revision.cru_revision = cru_revision.cru_revision_id
where cru_review.cru_review_id < 150
) nested1
group by cru_review_id
) nested2
join <FishEyeDB>.dbo.cru_revpermaid on cru_revpermaid.cru_review_id = nested2.cru_review_id
where nested2.repos > 1

Thank you @Danila Sudyko ! I constructed the above query based on your recommendation, and it seems to do exactly what we are looking to do! 

[Danila Sudyko]

Hello colleague.

FishEye + Crucible has no explicit link between revision and repositories, but I have found a hack. The SQL query below gives you all review items. In cru_source_name attribute you will find something like this -> PATCH:49 or UPLOAD:49. The number 49 means an ID in tables cru_patch and cru_upload_item.

select cru_review.cru_review_id, cru_default_source, cru_source_name
from cru_review
join cru_frx on cru_review.cru_review_id = cru_frx.cru_review_id
join cru_frx_revision on cru_frx.cru_frx_id = cru_frx_revision.cru_frx_id
join cru_revision on cru_frx_revision.cru_revision = cru_revision.cru_revision_id
order by cru_review_id
limit 100

For plugin I wrote the functions below.