Get issues by sprint from database?

I'm trying to get issues per sprint (past/present/future) from our MySQL db (I've linked it to SQL Server so that I can do recursive queries).

I've followed this thread as far as it can take me: https://answers.atlassian.com/questions/96142/how-to-get-sprint-issue-list-by-sql

It gets me a ranked list of all issues but only sprint markers for the current sprint.

From there I don't know where I can find the different boards in the db and markers within them to potentially find what sprint issues are sorted into.

Any ideas? It's got to be possible, right?

4 answers

1 accepted

1 votes
Richie Gee Atlassian Team Nov 04, 2013

For you question, it is actually answered on the last post of the link which is the information is actually stored on the active object table below:

select ID,NAME,RAPID_VIEW_ID from AO_60DB71_SPRINT;

As for the boards, it should be the rapid_view_id column at the above table

To determine the list of sprints on particular board you can use the query below:

select "ID","NAME" from "AO_60DB71_SPRINT" where "RAPID_VIEW_ID" = (select "ID" from "AO_60DB71_RAPIDVIEW" where "NAME" like '%boardname%');

Then it is rather easy fron here to determine which sprint you want to retrieve from the database which can be fetched from the query:

SELECT c.STRINGVALUE AS SprintId, i.pkey AS Task 
  FROM customfieldvalue AS c 
  LEFT OUTER JOIN jiraissue i ON (i.ID=c.ISSUE) 
  WHERE CUSTOMFIELD = 10300 AND STRINGVALUE = 2

Customfield ID is retrieved from the below query:

select * from customfield where cfname like '%Sprint%';

And the string value is the sprint value that you have sorted on the first or second query.

Hope this helps, cheers

Hi, I see the "RAPID_VIEW_ID" field is referenced in the SPRINT table but in JIRA Database that I am connected to, I dont have that field in SPRINT table. This just makes my job harder because I am struggling to join RAPID_VIEW and SPRINT table.

I thought I would add how I did:

This query give me the list of boards and the sprints for each one:

select RW.ID AS BOARD_ID,RW.NAME AS BOARD_NAME,SP.ID as SPRINTID,SP.NAME, SP.COMPLETE_DATE, SP.END_DATE, SP.START_DATE, SP.STARTED, SP.SEQUENCE 
from AO_60DB71_SPRINT SP INNER JOIN dbo.AO_60DB71_RAPIDVIEW RW ON SP.RAPID_VIEW_ID = RW.ID ORDER BY BOARD_ID

 

This adds the issues for each sprint in a board:

SELECT  RW.ID AS BOARD_ID,RW.NAME AS BOARD_NAME,SP.ID as SPRINTID,SP.NAME, 
SP.COMPLETE_DATE, SP.END_DATE, SP.START_DATE, SP.STARTED, SP.SEQUENCE,
CFV.ISSUE AS JIRAISSUE_ID FROM [Novedad].[dbo].[customfieldvalue] CFV
INNER  JOIN [Novedad].[dbo].[customfield] CF ON CF.ID = CFV.CUSTOMFIELD
INNER JOIN [Novedad].[dbo].[AO_60DB71_SPRINT] SP ON 
cast(SP.ID as varchar) = CFV.STRINGVALUE 
INNER  JOIN dbo.AO_60DB71_RAPIDVIEW RW ON SP.RAPID_VIEW_ID = RW.ID
WHERE  cfname like '%sprint%' ORDER BY BOARD_ID

 

if you want you can add the project table and jiraissue table to select one particular project and display the key of the issue as below.  

SELECT   PR.PKEY + '-'+  CAST(JI.ISSUENUM AS VARCHAR) AS ISSUEKEY, CFV.ID, SP.NAME 
FROM [Novedad].[dbo].[customfieldvalue] CFV
INNER JOIN [Novedad].[dbo].[customfield] CF ON CF.ID = CFV.CUSTOMFIELD 
inner join [Novedad].[dbo].jiraissue JI   on JI.ID = CFV.ISSUE 
INNER JOIN [Novedad].[dbo].project PR  ON JI.PROJECT = PR.ID
INNER JOIN [Novedad].[dbo].[AO_60DB71_SPRINT] SP ON  SP.ID = CFV.STRINGVALUE 
WHERE PR.PKEY = 'SD' AND cfname like '%sprint%'

 

 

 

 

Really Helpful

 Here is the code that works in jira 7 (ms sql )



SELECT b.NAME SprintBoardName, b.ID SprintBoardID, S.NAME SprintName, s.ID sprintID, p.pkey+'-'+cast(i.issuenum as NCHAR(10)) IssueKey, p.pkey,
s.STARTED,
CASE WHEN s.END_DATE is not null then 'Yes' ELSE 'No' END Completed

FROM customfieldvalue c, jiraissue i, project p, AO_60DB71_SPRINT s left join AO_60DB71_RAPIDVIEW b on b.ID=s.RAPID_VIEW_ID where i.project=p.id and i.ID=c.ISSUE
and CUSTOMFIELD = 10000 and s.ID=CAST(c.STRINGVALUE as int)

For full research of  usage of sprints by boards and projects

Suggest an answer

Log in or Join to answer
Community showcase
Sarah Schuster
Posted Jan 29, 2018 in Jira

What are common themes you've seen across successful & failed Jira Software implementations?

Hey everyone! My name is Sarah Schuster, and I'm a Customer Success Manager in Atlassian specializing in Jira Software Cloud. Over the next few weeks I will be posting discussion topics (8 total) to ...

2,755 views 11 18
Join discussion

Atlassian User Groups

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

Find a group

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

Find my local user group

Unfortunately there are no AUG chapters near you at the moment.

Start an AUG

You're one step closer to meeting fellow Atlassian users at your local meet up. Learn more about AUGs

Groups near you
Atlassian Team Tour

Join us on the Team Tour

We're bringing product updates and pro tips on teamwork to ten cities around the world.

Save your spot