SQL for getting all issues related to a sprint.

[J C]

I can only find one table with sprint related data: AO_60DB71_SPRINT - Are there other tables that store additional sprint data such as which issues were completed or not completed in a sprint.  Basically want to get the data from the Sprint reports in a SQL query.

[Andy Heinzer]

There are other tables in the Jira database that store this sprint related information.  The AO table you referenced keeps track of the sprints, their names, and their start/stop times.  It doesn't keep track of which issues belong to which sprints.   That data is stored in the customfieldvalue table, but it's difficult to understand what you're looking at with that table alone.   I played around with this a bit and found an interesting way to gather this data between these tables in order to see which issues belong to which sprint.

select cfv.issue, cfv.stringvalue as SprintNumber, ao."NAME" as SprintName, cf.cfname, P.PKey || '-' || issue.IssueNum as IssueKey
from CustomFieldValue cfv 
join CustomField CF on CF.Id = CFV.CustomField
join jiraissue issue on issue.id = cfv.issue
join Project P on issue.Project = P.Id
join "AO_60DB71_SPRINT" ao on ao."ID" = Cast(cfv.stringvalue AS bigint) 
where cf.cfname='Sprint'

I was able to use this syntax in Postgres in order to see the issue key and sprint name among other values together in a single query.  The syntax might be different for other database types such as Oracle, Mysql, or MS SQL.

I hope this helps.

Andy

[J C]

Thanks Andrew!  Do you know if there's a table that indicates if the issue has been "Completed" or "Not Completed" in the actual sprint?  

[Andy Heinzer]

Hi James,

I wasn't sure of the answer here, so what I did was to quickly turn on SQL logging in Jira, and the load a sprint report to see what SQL queries are being generated.  I found a lot of SQL select queries being run during this short time, for example there are a number of individual queries to pull specific issue data relating to this report:

SELECT ID, pkey, issuenum, PROJECT, REPORTER, ASSIGNEE, CREATOR, issuetype, SUMMARY, DESCRIPTION, ENVIRONMENT, PRIORITY, RESOLUTION, issuestatus, CREATED, UPDATED, DUEDATE, RESOLUTIONDATE, VOTES, WATCHES, TIMEORIGINALESTIMATE, TIMEESTIMATE, TIMESPENT, WORKFLOW_ID, SECURITY, FIXFOR, COMPONENT 
FROM public.jiraissue WHERE issuenum='20' AND PROJECT='10000';

This is an example query that selected a single issue in this report that was completed.  I did note that this pulls a resolutiodate field.   I believe that the report is using this date field and then comparing it against the start/stop dates and times for the sprint. If this issue happens to have been resolved in that time it is shown as having been completed in the sprint.   Issues that were not completed during the sprint would either have a value outside this range or no value at all.

So perhaps you could tweak the first query a bit to first pull all the issues and then compare their resolution date against this sprint times.  The current limitation with doing so directly from SQL is that the AO table is storing the start/stop dates of the sprint in Unix Epoch time format, but the resolutiondate is stored in a yyyy-mm-dd hh:mm:ss format.   So there would likely need to be a SQL cast or convert made so that you can compare these values against each other to figure out if the resolution happened during the sprint or not.

[J C]

Thank you so much for the tips Andrew, I was unaware of SQL logging and can see it being very useful!  OK, so I was on the same page as you mentioned above, I ensured all my issues had the correct date resolutions and my sprints had the correct start/end dates. I also took the time to convert the dates to Epoch time before importing into the Sprint table and unfortunately I did not have any luck yesterday with that approach BUT then I just figured it out and it was a missing step on my end!  I forgot to map the 'Closed' status to the 'Done' column for it to show up in the sprint reports.  Now the completed and not completed issues are showing up correctly with the proper status/column mappings.

Thank you for confirming that there weren't any random tables that I was not factoring in.  Appreciate the response and tips!

[Adolfo Casari]

@J C 

I am looking for the same query, can you share it?

[Nic Brough -Adaptavist-]

A lot of the data like that is calculated when needed, or calculated and stored in the index. You'll need to replicate those calculations or extractions in order to work it out for your queries.

SQL is the single worst way you can possibly attempt to do Jira reporting.  Whatever you're trying to report on, consider using the API instead - what's wrong with the sprint reports?  Are there add-ons that could do what you need?  The REST API can usually give you a lot of calculated fields.  And there are generalised reporting tools like EasyBI that understand the database so your SQL doesn't have to.

[J C]

Thanks Nic, actually I'm not trying to generate reports using SQL.  What I'm trying to do is migrate a JIRA project from one instance to another, unfortunately I only have export/api access on the source instance and do not have access to the full backup of the system.  I have a proper import of the project from the other JIRA instance, but the only issue I have are the sprint reports.  I cannot get the issue to show up as an "Issue Completed" in the sprint reports... they actually do not show up at all, it only shows the issues not completed.  The issues are mapped correctly and it sees it as part of a sprint but just doesn't show up in the reports hence why I was trying to figure out how that query is generated so I can possibly get the sprint reports to work properly.  I tried re-indexing the project but the reports are still not right, hence why I thought the issue completed/not completed from the sprint reports may be stored in a table that I can't find.

[J C]

Actually I figured it out, it was a status mapping that was not in the done status, hence why my newly imported items were not showing up in the sprint reports.