nav[aria-label="Primary Navigation"] { padding: 0; & ul { list-style: none; width: 100%; display: flex; flex-direction: row; justify-content: start; align-items: start; gap: 30px; padding: 0; & li { margin: 0; } & ul li { list-style: none; } } }

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

jfreidkes

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!

Accepted answers

All comments

John Funk

Hi @jfreidkes - Welcome to the Atlassian Community!

If you are filtering within JQL it would look like:

project = ABC and sprint in openSprints ()

jfreidkes

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

John Funk

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

Gonchik Tsymzhitov

I hope you checkout those thread

https://community.atlassian.com/t5/Jira-Software-questions/How-to-get-Sprint-issue-list-by-SQL/qaq-p/320487

https://community.atlassian.com/t5/Jira-questions/Get-issues-by-sprint-from-database/qaq-p/422957

jfreidkes

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');

Matt Doar_tzgiej

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_tzgiej

Perhaps start with

AO_60DB71_SPRINT

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

jfreidkes

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!

Welcome!

It looks like you're new here. Sign in or register to get started.

Quick Links

Find more posts tagged with

jira

cloud

jira-software

jira-software-cloud

jira-cloud