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

This widget could not be displayed.
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.

This widget could not be displayed.

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%'

 

 

 

 

Can you help with add Sub-Tasks as well please at the moment it only show Story's and Bugs but we add Sub-Tasks under our Stories 

This widget could not be displayed.

Really Helpful

This widget could not be displayed.

 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 Sign up to answer
Atlassian Summit 2018

Meet the community IRL

Atlassian Summit is an excellent opportunity for in-person support, training, and networking.

Learn more
Community showcase
Posted yesterday in New to Jira

Are you planning to trial, or are currently trialling Jira Software? - We want to talk to you!

Hello! I'm Rayen, a product manager at Atlassian. My team and I are working hard to improve the trial experience for Jira Software Cloud. We are interested in   talking to 20 people planning t...

72 views 1 0
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