Database query to find the interconnected Jira projects

[kp]

Hello Team,

If anybody knows how to find how many projects are interconnected. For example, If an agile board is pulling tickets from 5 projects.

If any database query or any another solution 

[Daina Tupule eazyBI]

Jira does not store a reference to projects for boards in the database. It has a reference to filter ID and the referenced search requests contain JQL query as plain text. You can check the database if you can build queries based on this. 

You can consider using some reporting tool for analyzing data quality along with some KPI. eazyBI imports data and will create cross-references between any fields in Jira via activities in issues.

If the issue from a specific project was included in any sprint at any time we will import this information and you will be able to create a report using Sprint and Project dimensions and see Board - Project relationships via issue activities in sprints.

Daina / support@eazybi.com

[Nic Brough -Adaptavist-]

I would very strongly recommend that you stop looking at the database.  It is the single worst possible way to do any reporting on Jira.

Just to give you an idea of why, let's break down what you will need to think through:

>If anybody knows how to find how many projects are interconnected

This is not stored in the database, there is no project relationship.  You have to infer one from data based on a rule

>If an agile board is pulling tickets from 5 projects.

Ok, that's one rule, let's explore what you will need to do:

• Read the board settings for each board, and for each
• Extract the saved filter that defines what the board selects for
• These are stored in JQL, so you'll need to write a parser that can work out what projects it selects for, remembering that you'll also need to deal with the edge cases (I'm looking at a Jira board now that says just "issuetype = incident".  You would have to go read all the project settings to find out that incident is used in only 3 of the 1217 projects in this system)
• Most of this is also useless out of context -too, do your users all see the same thing?  Are projects really linked if Bob has access to project A but not B?

Please, use the REST API, or even just look at Jira itself.   (And question of what use this question actually is outside the context of a user)