How to find out what are the Jira projects whose respective issues have information in their Development Panel
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
In this article we will determine what are the Jira projects whose respective issues have information in their Development Panel.
Solution
Connect to the database used by Jira
Run the following SQL statement:
PostgreSQL query
Note: PostgreSQL does not like uppercased table names and column names, so these have to be enclosed with double quotes.
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16WITH applinks AS ( SELECT SUBSTR(a.property_key,16,36) AS appkey, b.propertyvalue AS appname FROM propertyentry a JOIN propertystring b ON a.id = b.id WHERE a.property_key LIKE 'applinks.admin%name' ) SELECT DISTINCT applinks.appname AS source_instance, proj.pkey AS project_key, proj.pname AS project_name FROM "AO_575BF5_DEV_SUMMARY" AS summary INNER JOIN jiraissue AS issue ON summary."ISSUE_ID" = issue.id INNER JOIN project AS proj ON issue.project = proj.id INNER JOIN applinks ON applinks.appkey = summary."PROVIDER_SOURCE_ID" ORDER BY applinks.appnameMySQL query
Note: this SQL query is compatible only with MySQL 8.0+ because the
WITHexpression only exists from this version onwards, according to the manual.1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16WITH applinks AS ( SELECT SUBSTR(a.property_key,16,36) AS appkey, b.propertyvalue AS appname FROM propertyentry a JOIN propertystring b ON a.id = b.id WHERE a.property_key LIKE 'applinks.admin%name' ) SELECT DISTINCT applinks.appname AS source_instance, proj.pkey AS project_key, proj.pname AS project_name FROM AO_575BF5_DEV_SUMMARY AS summary INNER JOIN jiraissue AS issue ON summary.ISSUE_ID = issue.id INNER JOIN project AS proj ON issue.project = proj.id INNER JOIN applinks ON applinks.appkey = summary.PROVIDER_SOURCE_ID ORDER BY applinks.appnameAlways back up your data before making any database modifications. If possible, test any alter, insert, update, or delete SQL commands on a staging server first.
Was this helpful?