How to get Sprint issue list by SQL

[Malik Chettih]

Hi,

I'm using actually Jira 5.1.6 and GreenHopper 6.0.5. My database is Postgresql.

For reporting purpose I need to list the issues that are planned in the current sprint and in the upcoming sprints.

Issues are in jiraissue table, project in project, version are in projectversion and the association between issues and projectversions is nodeassociation table.

But How can I link the issues with the sprints. I the sprints description in the tables AO_60DB71_SPRINT and AO_60DB71_SPRINTMARKER. I'm stuck the association between the two.

Any help will be greetly appreciated.

Regards,

[Nabil Sayegh · Accepted solution]

Here's some sqlserver code:

DECLARE @CUSTOM_FIELD_ID int = 13000;

WITH list (position, id, issue, "next") as (
	SELECT
		0 AS position,
		rang.ID,
		rang.ISSUE_ID,
		rang.NEXT_ID
	FROM
		AO_60DB71_ISSUERANKING rang 
		LEFT OUTER JOIN AO_60DB71_ISSUERANKING rang2 ON (rang2.CUSTOM_FIELD_ID = rang.CUSTOM_FIELD_ID and rang2.NEXT_ID = rang.ISSUE_ID) 
	WHERE
		rang.CUSTOM_FIELD_ID = @CUSTOM_FIELD_ID AND
		rang2.ID is null

	UNION ALL

	SELECT
		position + 1,
		rang.ID,
		rang.ISSUE_ID,
		rang.NEXT_ID
	FROM
		AO_60DB71_ISSUERANKING rang
		JOIN list ON (rang.ISSUE_ID = list."next")
	WHERE
		CUSTOM_FIELD_ID = @CUSTOM_FIELD_ID
) 
SELECT
	list.position,
	ji.pkey as "key",
	sm.MARKER_CONTEXT AS rapidView,
	sm.NAME AS sprint
FROM
	list
	LEFT OUTER JOIN jiraissue ji ON ji.id = list.ISSUE
	LEFT OUTER JOIN AO_60DB71_SPRINTMARKER sm ON sm.ID = -issue
ORDER BY
	position
OPTION (maxrecursion 0);

[Nabil Sayegh]

It looks like for the active Sprint there is no entry in SPRINTMARKER and one has to join customfieldvalue for the Sprint customfield and join the "stringvalue" to SPRINT.

[sclowes]

Hi Malik,

If you're not very familiar with the JIRA DB this will probably be quite challenging.

Sprints that are in progress can be found by looking at the 'Sprint' custom field in the issues (using the jiraissue, customfield, customfieldvalue and ao_60db71_sprint tables).

Future sprints do not own any issues, instead they are ranked in the linked list of ranked issues. You can find the ranking of items in ao_60db71_issueranking. Negative numbers in the issue_id field represent sprint footers. You can find the list of current sprint footers in ao_60db71_sprintmarker. You need to find all issues that match the filter for your board then check if they are lower in the ranking than the sprint marker you are looking for (and higher than the previous marker).

Thanks,
Shaun

[Matthew Zach]

Malik - I have this exact same need.

Shaun, I read through your comments and they make sense. I've walked through the data in my issueranking table and I thought I had the pattern you describe figured out, but ultimately I'm not able to query for the boundries. Once I can see a coorelation between the UI and the data I think I can figure it out, but not able to get my SQL to match the plan board yet. Could you throw us some basic SQL to get us headed in the right direction?

A more elegant solution to all of this would be for Atlassian to add export support to the plan mode.

[sclowes]

It's very painful to reconstruct the linked list of items using SQL because you need a recursive query (which is not supported in all DBs). Here's a sample one that will work in Postgres and will show the absolute rank value, the rank table row id, the issue id, next issue id and issue key in order of rank.

-- Get the linked list of items from the ranking table (recursive, only works in Postgres)
with recursive list (position, id, issue, next, key) as (
  select 0 as "position", rank."ID", rank."ISSUE_ID", rank."NEXT_ID", ji.pkey from "AO_60DB71_ISSUERANKING" rank 
   left outer join "AO_60DB71_ISSUERANKING" rank2 on rank2."CUSTOM_FIELD_ID" = rank."CUSTOM_FIELD_ID" and rank2."NEXT_ID" = rank."ISSUE_ID"  
   left outer join jiraissue ji on ji.id = rank."ISSUE_ID"
   where rank."CUSTOM_FIELD_ID" = 10410 and rank2."ID" is null
  union all
  select position + 1, "ID", "ISSUE_ID", "NEXT_ID", ji.pkey
   from "AO_60DB71_ISSUERANKING" 
   join list on "AO_60DB71_ISSUERANKING"."ISSUE_ID" = list.next 
   left outer join jiraissue ji on ji.id = "AO_60DB71_ISSUERANKING"."ISSUE_ID"
   where "CUSTOM_FIELD_ID" = 10410 ) 
select * from list;

Some sample data:

As described above, the negative numbered items in the linked list are the future sprint markers. You'd still need to filter down the results to only match the filter for the board you're interested in (and change the CUSTOM_FIELD_ID to match the id of the Rank field in your instance).

Agreed a link to issue navigator from plan mode is a better option here, we do plan to add that in the future.

Thanks,
Shaun

[Nabil Sayegh]

Hi Shaun,

do you have any idea, why there are some negative ids that don't have a corresponding sprintmarker?

rgds

[sclowes]

Hmmm... I'm not sure. Off the top of my head I'd guess these might be sprint markers from deleted boards.

Cheers,
Shaun

[Matthias Wuttke]

Hi!

This query works for me in JIRA 7.0.0. It also retrieves a second user field (story points):

SELECT c.STRINGVALUE AS SprintId, i.issuenum AS IssueNum, i.ID as IssueId, d.NUMBERVALUE as StoryPoints FROM customfieldvalue c, jiraissue i, customfieldvalue d where i.ID=c.ISSUE and c.CUSTOMFIELD = 10004 AND c.STRINGVALUE = 5 and d.CUSTOMFIELD = 10002 and d.ISSUE=i.ID;

You need to replace 10002 (StoryPointsFieldId), 10004 (SprintFieldId) and 5 (SprintId) with your IDs.

Hope this helps, Matthias

[Bernhard Grünewaldt]

I needed a more simple SELECT Statement to just get the Issues of a Sprint in MySQL and Jira Agile 6.3.0.2.

This is my solution:

Get all Sprint-Ids and Names:

select ID,NAME from AO_60DB71_SPRINT;

Now you get something like this:

+----+--------------------------------+
| ID | NAME                           |
+----+--------------------------------+
|  1 | TestSprint 1                   |
|  2 | TestSprint 2                   |
+----+--------------------------------+

Now pick a Sprint-ID and use it in the next statement:

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

Here is CUSTOMFIELD = 10300 the Sprint-Customfield. And STRINGVALUE = 2 the Sprint-ID.

you get something like this:

+----------+---------------+
| SprintId | Task          |
+----------+---------------+
| 2        | XCZ-1439      |
| 2        | XCZ-1792      |
| 2        | XCZ-1627      |
| 2        | XCZ-704       |
+----------+---------------+

For me this was important because in some circumstances a sprint had tasks from different projects and most users did not have permissions on these other projects and couldn't start sprints. That way I was able to really see ALL issues assigned to a specific sprint.

[Eric_Engberg]

I like the simplicity here.

[Ajay Verma]

Hi @Offboarded X5677 

This query only giving the current related issues of the sprint . could you please help me to find out the issues which was removed from sprint ?

Thanks

Ajay

[Robert Jober]

This code is to get the list of issues in a sprint for jira 6.3

DECLARE @SprintName nvarchar(255) = 'Sprint 1';

SELECT 
	SprintId	= CFV.STRINGVALUE, 
	IssueId		= JI.ID,
	IssueCode = P.pkey + '-' + CAST(JI.issuenum AS nvarchar(10)),
	NextIssueId = IR.NEXT_ID
FROM JiraStore.dbo.customfield CF WITH(NOLOCK) 
	INNER JOIN JiraStore.dbo.customfieldvalue CFV WITH(NOLOCK) ON CFV.CUSTOMFIELD = CF.ID
	INNER JOIN JiraStore.dbo.jiraissue JI WITH(NOLOCK) ON JI.ID = CFV.ISSUE
	INNER JOIN JiraStore.dbo.project P WITH(NOLOCK) ON P.ID = JI.PROJECT
	INNER JOIN JiraStore.dbo.AO_60DB71_SPRINT S WITH(NOLOCK) ON CAST(S.ID AS nvarchar(10)) = CFV.STRINGVALUE
		AND S.NAME = @SprintName
	INNER JOIN JiraStore.dbo.AO_60DB71_ISSUERANKING IR WITH(NOLOCK) ON IR.ISSUE_ID = JI.ID
WHERE CF.cfname = 'Sprint'
;

JiraStore.dbo. needs to be replaced with your database name in your SQL server instance.

[Sorin Sbarnea (Citrix)]

Here is an updated Postrgres query, one that does not require you to specify the Ranking field, assuming that you have only on ranking field in your jira.

with recursive list (position, id, issue, next, key) as (
  select 0 as "position", rank."ID", rank."ISSUE_ID", rank."NEXT_ID", ji.pkey from "AO_60DB71_ISSUERANKING" rank 
   left outer join "AO_60DB71_ISSUERANKING" rank2 on rank2."CUSTOM_FIELD_ID" = rank."CUSTOM_FIELD_ID" and rank2."NEXT_ID" = rank."ISSUE_ID" 
   left outer join jiraissue ji on ji.id = rank."ISSUE_ID"
   where rank."CUSTOM_FIELD_ID" = (select id from customfield where customfieldtypekey = 'com.pyxis.greenhopper.jira:gh-global-rank')
 and rank2."ID" is null
  union all
  select position + 1, "ID", "ISSUE_ID", "NEXT_ID", ji.pkey
   from "AO_60DB71_ISSUERANKING"
   join list on "AO_60DB71_ISSUERANKING"."ISSUE_ID" = list.next
   left outer join jiraissue ji on ji.id = "AO_60DB71_ISSUERANKING"."ISSUE_ID"
   where "CUSTOM_FIELD_ID" = (select id from customfield where customfieldtypekey = 'com.pyxis.greenhopper.jira:gh-global-rank')) 
select * from list;

[Drickus Annandale]

Hi 

I please have a follow up question. I need to link issue to Kanban Boards. The above code associates sprints to issues. Currently I cannot find a way to link Issues to Kanban boards in the database. 

My starting point to look was the Rapidview table but I cannot find tables that would join this table to the jiraissue table. 

Kind Regards, Drickus

[Nic Brough -Adaptavist-]

There is no link.

This is one of hundreds of reasons you should forget SQL when looking at Jira - it is the worst possible approach to doing anything in Jira.

To explain why there is no link between issue and board in the database, it is because you do not need one.  A board is a view of a set of issues, which is determined by the board's filters.  For a Kanban board, you need to combine the two filters and run the resultant query to get the list of issues currently on the board (the main filter says what issues to return in general and the secondary filter then excludes issues that are removed from the board because they are considered "done")

[Amit Kumar Sharma]

Hi,

How will I get Sprint and list of issue removed from sprint from JIRA database. My database is in postgres sql.

Thanks!

Amit

[CGM]

Hey,

we're facing the same problem. It woult be very nice,if anybody can translate this SQL-Statement to a mySQL-Statement, because mySQL doesn't support functions like "with" and my SQL-Skills are not that good, that I can do this by myself.

[Nabil Sayegh]

Can't you switch to a proper RDBMS?

[CGM]

no, we can't :-(

[Russell Wilson]

Any ideas on how to do this in oracle?

[Sarah Shonnard]

... and how about MYSQL? I have a need for the entire backlog list in rank order from a JIRA install with a MYSQL backend.

[XDarren Jones]

On Oracle, try:

WITH issueranking AS
(
SELECT	*
FROM	AO_60DB71_ISSUERANKING
WHERE	custom_field_id = your_value_here
),
ranked_issues AS
(
SELECT issueranking.custom_field_id,
	issueranking.id,
	issueranking.issue_id,
	issueranking.next_id,
	project.pkey || '-' || jiraissue.issuenum AS pkey,
	LEVEL
FROM	issueranking
LEFT OUTER JOIN jiraissue
ON	jiraissue.id = issueranking.issue_id
LEFT OUTER JOIN project
ON	project.id = jiraissue.project
START WITH issueranking.id =
	(
	SELECT	first_issue.id
	FROM	issueranking first_issue
	WHERE	NOT EXISTS
		(
		SELECT	NULL
		FROM	issueranking all_issues
		WHERE	all_issues.next_id = first_issue.issue_id
		)
	)
CONNECT BY PRIOR next_id = issue_id
ORDER BY LEVEL
)
SELECT	issue_id,
	pkey,
	ROWNUM -1 AS globalrank
FROM	ranked_issues
WHERE	issue_id > 0

I currently only seem to have one value for custom_field_id in my AO_60DB71_ISSUERANKING table, so I don't really need the first WITH clause. But better safe than sorry, I suppose!

[Malik Chettih]

Hi Guys,

Sorry for the depalyed replay... Thanks Shaun, for the reply. It helped me to create mu query.

Many thanks again.

Rgds,