How to fetch list of epics based on child issue status

[Sree]

I need help fetching a list of epics. The scenario is that epics and stories share the same workflow. Based on the Story movement, we have an automated Epic movement. Let's say there are 4 child stories under my Epic, automation will scan all the stories and pick the least status for my Epic, like if 3 stories are "Done" and 1 story is in "In Progress", Epic will be in "In Progress".

But in some cases, even when all stories are "Done", epics will still remain in "In Progress", automations will not be able to move Epics due to some conditions configured. So for these use cases, we want to fetch the list of epics that are not in sync with their child stories flow.

Any possible way to achieve this?

Thanks!

[Hannes Obweger - JXL for Jira · Accepted solution]

Hi @Sree,

Unfortunately, this is trickier than one might think; as a hierarchical query, it would really require some kind of join or subquery, which isn't available in plain Jira/JQL.

A few directions forward:

• If it's a one-off thing, you could first query the relevant epics, and then use the keys of these epics in a second query, in an "parent in (KEY-1, KEY-2, ...)" clause.

If you want to run your search dynamically, without manually stitching two queries together, you'll need extra tooling:

• You might be able to use Jira Automation to "propagate" epic information down to the epic's children, and then use the respective field(s) on the children to include them into your filter. I understand that you already have Jira Automation in place, so I assume that this isn't viable to you for some reason.
• There's different apps from the Atlassian Marketplace that can help with that. First, there's a number of apps that extend JQL by additional functions, including hierarchy-related functions. JQL Search Extensions has been mentioned in another comment, but there are others.
• Alternatively, you could try one of the more hierarchy-focused apps from the Marketplace. These apps typically have their own ways of figuring out parent/child relationships between issues, and provide more powerful ways of searching through issue hierarchies.

Hope this helps,

Best,

Hannes

[Charlotte Santos -Appfire- · Accepted solution]

Hi @Sree 

I’m Charlotte, a support engineer at Appfire and I’m here to help you.

Unfortunately, using vanilla JQL, you’ll not be able to do it dynamically.

In the app where my team works, JQL Search Extensions for Jira, you can use this query to find Epics that are in progress, but their children are done:

issue in epicsOfChildrenInQuery("type=Story AND status=done") AND status != Done

Please contact our support if you have any other questions about this query.

We’ll be happy to help you!

[Sree]

Thank you, It helped : )

[Srinivaas]

why focusing on epics external Marketplace apps. why Atlassian is not creating these. why we are unable to find there

[Antuan Sammak]

Hi @Sree 

you would need to create an automation rule with scheduled trigger and jql that returns all tickets in your project, of course you can limit this jql to a certain level based on the coverage you need for your rule.

then open a branch in the rule with type Parent

then add a condition with a type of "related issues" and there select parent and pick condition (all match specified jql) and jql to be status in ({{statuses you have that represents Done}})

then another condition step with type issue fields condition, pick the field status and the condition "one of" then add the statuses like To Do / In Progress

last add an action to log the {{parent.key}} to send some notification, depends on your requirements.