How to Retrieve Jira Board ID and Project ID in Enterprise Insights
Platform Notice: Cloud and Data Center - This article applies equally to both cloud and data center platforms.
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
This article provides guidance on how to retrieve comprehensive information regarding Jira projects, associated boards, and programs using Enterprise Insights. The solution involves executing an SQL query that consolidates data from multiple tables, offering a detailed view of each project's association with boards and programs. This approach helps in identifying projects not yet linked to boards or programs and examining the relationships between these entities.
Solution
To gather detailed information on Jira projects, associated boards, and programs, you can execute the following SQL query. This query retrieves data from three primary tables within the Enterprise Insights data warehouse: Jira Project, Jira Board, and Program. By using LEFT JOINs, the query ensures that all projects are included, even if there is no associated board or program, facilitating a thorough examination of project associations.
The SQL query is as follows:
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
SELECT
JP.[Project ID],
JP.[Jira Project ID],
JP.[Project Name],
JP.[Project Key],
JB.[Board ID],
JB.[Board Name
JAP.[Program ID],
JAP.[Program Name]
FROM
current_dw.[Jira Project] AS JP
LEFT JOIN
current_dw.[Jira Board] AS JB ON JP.[Project Key] = JB.[Project Key]
LEFT JOIN
current_dw.[Program] AS JAP ON JB.[Program ID] = JAP.[Program ID]
ORDER BY
JP.[Project Key];Once the query is executed, here is an example of the result it shows:
By running this query, you will receive a comprehensive dataset that links each Jira project to its respective board and program information. This can be particularly useful for auditing purposes or when trying to establish the current state of project associations within Jira Align.
Was this helpful?