Create
cancel
Showing results for 
Search instead for 
Did you mean: 
Sign up Log in

Next challenges

Recent achievements

  • Global
  • Personal

Recognition

  • Give kudos
  • Received
  • Given

Leaderboard

  • Global

Trophy case

Kudos (beta program)

Kudos logo

You've been invited into the Kudos (beta program) private group. Chat with others in the program, or give feedback to Atlassian.

View group

It's not the same without you

Join the community to find out what other Atlassian users are discussing, debating and creating.

Atlassian Community Hero Image Collage

Link Jira Issue to Kanban boards via SQL Database

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

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.

Hi Nic, thank you for the response. 

Kind Regards

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

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
Community showcase
Posted in Jira Core

How to manage many similar workflows?

I have multiple projects that use variations of the same base workflow. The variations depend on the requirements of the project or issue type. The variations mostly come in the form of new statuses ...

4,013 views 11 5
Join discussion

Community Events

Connect with like-minded Atlassian users at free events near you!

Find an event

Connect with like-minded Atlassian users at free events near you!

Unfortunately there are no Community Events near you at the moment.

Host an event

You're one step closer to meeting fellow Atlassian users at your local event. Learn more about Community Events

Events near you