Writing a query to return all my InProgress parents with all children Resolved

[Tim Bainbridge]

New Jira user coming from many years of ADO/VSTS query writing. Here is my scenario:

I want to have a query that will identify all my Epics (the Parent) that are still "unresolved" when all the linked User Story /Bug /Task / Subtask (the Children) are "resolved" or "wont' do".  Ideally the result set would return the Parent and all the Children...but I would settle for just a list of the Parents. 

[Trudy Claspill · Accepted solution]

@Tim Bainbridge 

You might want to consider using the Automation feature to automatically close the Epics when all child issues are done.

Here's an older article on that topic.

Disclaimer: I have not actually tried this, and I note that the article is from 2017 when Automation for JIRA was a separate app. The functionality has since been pulling into the JIRA product.

https://blog.codebarrel.io/automation-of-the-week-how-to-transition-an-epic-when-all-stories-are-done-64672c743c58

[Tim Bainbridge]

Thanks, I was reviewing automation samples when your note came in. I found similar that does exactly what I want with a modest refinement of the work item type and statuses!!

[Tim Bainbridge]

Automation Playground | Atlassian

[Bill Sheboy]

Hi @Tim Bainbridge  -- Welcome to the Atlassian Community!

Jira Cloud cannot do this out-of-the-box as the JQL is not a query language with JOINS. So...

How many times do you need to perform this query?

• Lots of times: probably need to buy a scripting add-on from the marketplace or build your own merge/join by pulling data with the REST API
• One time: export the data to a spreadsheet in chunks with JQL (epics, then stories, then sub-tasks) and join in the spreadsheet

Best regards,

Bill

[Tim Bainbridge]

Thanks Bill. Ideally I could do this daily and set a subscription so that the hygiene of my work items status was kept up. 

Thanks again, tim

[Bill Sheboy]

Gotcha; then you are going to need a marketplace add-on to help with that, such as ScriptRunner.  If you search in the community for example queries with ScriptRunner, or check their documentation examples, that may help you decide.

As a free work-around to try (if you do not have too many issues in your project), you may use automation rules and scheduled triggers to check with some JQL, and then drill into the child items with branches.  Please look here for examples that might give you ideas:

https://community.atlassian.com/t5/Automation-articles/Jira-Automation-all-the-resources-you-need-in-one-page/ba-p/1388465

https://www.atlassian.com/software/jira/automation-template-library#/label/all/1453