Link Jira Issue to Kanban boards via SQL Database

Drickus Annandale May 31, 2018

Hi

We are currently building a Dimensional Reporting Model from the Jira database to do extensive Power BI Analysis\Trending and Tracking on the Jira data and are building directly from the jira database schema.  

There is a need to view tasks per Kanban board and the challenge is to find a proper link from the ‘Kanban Board’  to its relevant project and jira issues in the database.

The table in question is the AO_60DB71_RAPIDVIEW table.

The below query returns all the sprint boards to the jiraissue table and works perfectly but the same logic does not apply for Kanban boards even though the Kanban board descriptions does appear in the A0…..RAPIDVIEW table.

Could you please be of assistance to help us identify the proper joins in the database to join the Kanban board to it’s project and issues.  

The below code links jira issues to the relevant sprints. 

 Select

       [spr].[NAME]                                                                                      As [SprintName],

       Case When spr.CLOSED = 1 Then 'Yes' Else 'No' End                          As [SprintClosed],

       [customf].ISSUE                                                                                As [IssueID],

       [spr].ID                                                                                             As [SprintID],

       [rv].[Name]                                                                                     As [BoardName],

       Convert(Date,DateAdd(Second,Convert(Bigint,Convert(bigint,Left([START_DATE],10))) , Cast('1970-01-01 00:00:00' As DateTime)))  As [SprintStartDate],

       Convert(Date,DateAdd(Second,Convert(Bigint,Convert(bigint,Left(END_DATE,10))) , Cast('1970-01-01 00:00:00' As DateTime)))        As [SprintEndDate]

 

From [jira].[customfieldvalue] AS [customf]

 Inner Join [jira].[AO_60DB71_SPRINT] As [spr]

On

Convert(varchar,[spr].[ID]) = [customf].STRINGVALUE

 

Left Outer Join [jira].[AO_60DB71_RAPIDVIEW] As [rv]

on

[rv].[ID] = [spr].[RAPID_VIEW_ID]

 Where [customfield] = (Select [ID] From [jira].[customfield] Where cfname = 'Sprint')

2 answers

1 vote
Nic Brough -Adaptavist-
Community Leader
Community Leader
Community Leaders are connectors, ambassadors, and mentors. On the online community, they serve as thought leaders, product experts, and moderators.
June 3, 2018

The relationship between a board and an issue is defined by the JQL used on the board and then filtered by the settings for the board (if you're interested in columns, swimlanes, quick filters or the Kanban board's "definition of done")

Your database query is, quite simply put, going to be horrid to construct and valid only once per board - you will need to build a new query for every single board, and adjust them as users configure their boards or create new ones.  You'll have to replicate the JQL in SQL (they are very different things) and then apply board settings too.

I would strongly suggest that you do NOT try this with SQL.

Drickus Annandale June 3, 2018

Hi Nic, thank you for the response. 

Kind Regards

0 votes
Drickus Annandale June 8, 2018

Hi Nic

I please have a follow up question. I realize that the Kaban to Issue link can only be retrieved by using the Rest API. 

Is there any way to link the Project to the Boards using T-SQL. 

The tables in questions is dbo.AO_60DB71_RAPIDVIEW, dbo.project maybe the [dbo].[searchrequest] table.

Thank you in advance, 

Drickus

Nic Brough -Adaptavist-
Community Leader
Community Leader
Community Leaders are connectors, ambassadors, and mentors. On the online community, they serve as thought leaders, product experts, and moderators.
July 6, 2018

Not directly, because you will have to read and parse every single board query to see if it might include the project you are looking for.

Seriously, don't try it with SQL, it's the worst possible way to get information out of Jira.

Suggest an answer

Log in or Sign up to answer
TAGS
AUG Leaders

Atlassian Community Events