Need query to find epics where all the children are in a status of "To Do"

[Cash Coyne]

I'm able to find Epics where there are children that are In Progress with this ScriptRunner query:

issueFunction in epicsOf("project in (PrjA,PrjB) AND issuetype in standardissuetypes() and status not in ('To Do',Done)") and project in (PrjA,PrjB)

Now I want to find find epics where all the children are in a status of "To Do", and no other status

Any suggestions on that?

[Cash Coyne · Accepted solution]

Thanks to all for the JQL and ScriptRunner queries but unfortunately none of them gave me what I was looking for.  

I ended up creating a scheduled rule in Jira Automation and ran it once:

Using this JQL for the Schedule: project = RFP and issuetype = epic order by issuekey

Then; Create Variable: CurEpicKey = {{issue.key}}

And: Lookup Issues for this JQL: issuetype in standardissuetypes() and "Epic Link" = {{CurEpicKey}}

And: Create Variable: TotChildren = {{lookupIssues.size}}

And: Lookup Issues: issuetype in standardissuetypes() and "Epic Link" = {{CurEpicKey}} and status = "To Do"

And: Create Variable: TotToDo = {{lookupIssues.size}}

Then If: {{TotChildren}} does not equal 0

Then If: {{TotChildren}} equals {{TotToDo}}

Then add value to the audit log: {{CurEpicKey}},

After I run it, I can grab the string of keys from the audit log and throw it in a Jira Query.

I only need to use this periodically for cleanup of orphaned ideas/wants/thought, but it will save me a ton of time when I do

[PD Sheehan]

That use case is a little tricky. There isn't a good built-in way to check that ALL issues in the epic match your criteria.

But I think you can do it with a creative combination (warning, this query can be very slow)

issueFunction in epicsOf("status = 'To Do'") and NOT(issueFunction in epicsOf("status != 'To Do'"))

You might want to add some project-level criteria in each of the sub-query.

What this is doing is getting all epics that meet these 2 rules:

1. must have at least 1 linked issue with status='To Do'
2. must NOT have any linked issue with status OTHER than 'To Do'

[Laurie Sciutti]

Ah.  yes...this is definitely more thorough than my answer (I missed the all the children are in a status of "To Do").

[Danut M _StonikByte_]

Hi @Cash Coyne,

Another option to get the same info, is to use the Work Breakdown Structure (WBS) gadget offered by our Great Gadgets app.

This gadget takes the issues from a filter and displays them in a tree structure, by their hierarchy, in form of Epics > Stories, Tasks > Sub-tasks.

To get the epics with all their children in status "To Do", you could create a filter that includes at least these two conditions:

(issueType = Epic OR statusCategory = "To Do")

Then, configure the gadget to use this filter and it will do the grouping for you. This way you will be able to visualize the epics with all their child issues in "To Do" status category.

Please note that this app offers many other gadgets that you might find useful. Just have a look over the articles from our blog to make an idea. It can be a great asset for your team and company.

I hope this helps. 

Thank you,

Danut Manda 

[Laurie Sciutti]

Hi @Cash Coyne ~ with ScriptRunner, you can use the following:

project in (PrjA,PrjB) AND issuetype in standardissuetypes() and issueFunction in epicsOf("status = 'To Do' ")

[Cinthia Catalani]

Hello Cash!

Did you try something like this? 

project in (PrjA,PrjB) and issuetype in standardIssueTypes() and status = "To Do" and "Epic Link" is not EMPTY

Cheers :)