Jira database - How to filter out tickets in current sprint?

jfreidkes April 12, 2021

Hey all!

I'm using Jira data replicated into my warehouse, and I'm wondering how could I manage to filter the data in the issues table to only get the issues in the currently active sprint. It's required for some reporting, and I'd like to make it dynamically to always target current sprint, and drop the tickets either in the backlog or past sprints.

 

Thanks a lot!

3 answers

0 votes
Matt Doar
Community Leader
Community Leader
Community Leaders are connectors, ambassadors, and mentors. On the online community, they serve as thought leaders, product experts, and moderators.
April 12, 2021

This involves some reverse engineering to understand the Jira Software AO tables. I think I would start by using REST at 

GET /rest/agile/1.0/board/{boardId}/sprint

which returns a list of sprints, find the ID of the active sprint and then use

GET /rest/agile/1.0/sprint/{sprintId}/issue

to get the issues. Once that was working I would jump into the source code for the REST API for Jira Software and see what tables are being used.

Alternatively poke around a test instance (not warehouse) and the AO_60DB71 tables. Then take that info to the warehouse tables.

Fiddly!

Matt Doar
Community Leader
Community Leader
Community Leaders are connectors, ambassadors, and mentors. On the online community, they serve as thought leaders, product experts, and moderators.
April 12, 2021

Perhaps start with 

AO_60DB71_SPRINT

but I'm not sure in the "started" field is the same as "active"

jfreidkes April 12, 2021

Thanks for the reply Matt!

I'll try some of what you mentioned, but we're replicating the tables via Stitch, so there's not much I can do on the replication side. That's why I was facing a SQL approach, with the data already loaded.

 

Thanks again!

0 votes
Gonchik Tsymzhitov
Community Leader
Community Leader
Community Leaders are connectors, ambassadors, and mentors. On the online community, they serve as thought leaders, product experts, and moderators.
April 12, 2021
jfreidkes April 12, 2021

Thanks for the reply!

Yes, I've already checked those, but I have no table named ao_60db71_sprint, so that's why I'm asking.

I thought I could get it by combining issues table and one I found with the sprints data (including a status column), but not working:

SELECT * FROM jira.issues
WHERE id IN(
SELECT _sdc_source_key_id FROM jira.issues__fields__customfield_10007
WHERE boardid = 207 --specific board I'd like to pull data from
AND state = 'active');
0 votes
John Funk
Community Leader
Community Leader
Community Leaders are connectors, ambassadors, and mentors. On the online community, they serve as thought leaders, product experts, and moderators.
April 12, 2021

Hi @jfreidkes  - Welcome to the Atlassian Community!

If you are filtering within JQL it would look like:

project = ABC and sprint in openSprints ()

jfreidkes April 12, 2021

Thanks for that, but I'm using SQL in my tables replicated from Jira database, so no JQL I could apply.

John Funk
Community Leader
Community Leader
Community Leaders are connectors, ambassadors, and mentors. On the online community, they serve as thought leaders, product experts, and moderators.
April 12, 2021

Was afraid of that. Not sure how you are denoting current sprints when you replicate the data. 

Suggest an answer

Log in or Sign up to answer
DEPLOYMENT TYPE
CLOUD
PRODUCT PLAN
STANDARD
PERMISSIONS LEVEL
Site Admin
TAGS
AUG Leaders

Atlassian Community Events