Create
cancel
Showing results for 
Search instead for 
Did you mean: 
Sign up Log in
Deleted user
0 / 0 points
Next:
badges earned

Your Points Tracker
Challenges
Leaderboard
  • Global
  • Feed

Badge for your thoughts?

You're enrolled in our new beta rewards program. Join our group to get the inside scoop and share your feedback.

Join group
Recognition
Give the gift of kudos
You have 0 kudos available to give
Who do you want to recognize?
Why do you want to recognize them?
Kudos
Great job appreciating your peers!
Check back soon to give more kudos.

Past Kudos Given
No kudos given
You haven't given any kudos yet. Share the love above and you'll see it here.

It's not the same without you

Join the community to find out what other Atlassian users are discussing, debating and creating.

Atlassian Community Hero Image Collage

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

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
John Funk Community Leader Apr 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 ()

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 Apr 12, 2021

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

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

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!

Perhaps start with 

AO_60DB71_SPRINT

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

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!

Suggest an answer

Log in or Sign up to answer
DEPLOYMENT TYPE
CLOUD
PRODUCT PLAN
STANDARD
PERMISSIONS LEVEL
Site Admin
TAGS
Community showcase
Published in Bitbucket Pipelines

Bitbucket Pipelines Runners is now in open beta

We are excited to announce the open beta program for self-hosted runners. Bitbucket Pipelines Runners is available to everyone. Please try it and let us know your feedback. If you have any issue...

284 views 6 5
Read article

Community Events

Connect with like-minded Atlassian users at free events near you!

Find an event

Connect with like-minded Atlassian users at free events near you!

Unfortunately there are no Community Events near you at the moment.

Host an event

You're one step closer to meeting fellow Atlassian users at your local event. Learn more about Community Events

Events near you