JQL query to exclude parent if all sub-tasks are closed

Patrick Till

I am writing a filter to list parent issues if any of the sub-tasks are still active.
i.e. I want to exclude issues if there are no sub-tasks or if all the sub-tasks are already closed.

Please do not suggest a 3rd party app, I do not have a budget to purchase such.

Thank you for your advice.

 

Mark Segall

Hi @Patrick Till - Unfortunately, to achieve this purely with JQL you would need a marketplace app.  However, there is a workaround that you can employ leveraging some native automation:

You'll need some mechanism that will "tag" the parent issue when all sub-tasks are resolved.  This could be a custom field, component, label, etc.  For my example, I'm going to use a custom field of type label called "System Labels" which is hidden from the issue screens as I only need it for querying purposes. Now for the automation...

• TRIGGER: Field Changed (Resolution)
• CONDITION: Issue Type = Sub-Task
• IF CONDITION: Resolution is resolved
  • BRANCH (Parent)
    • CONDITION (Related Issues)
      • Sub-Tasks
      • All Match Specified JQL

      • resolved IS NOT EMPTY

    • ACTION: Edit Issue (System Labels)
      • Add: subtasks_resolved
• ELSE
  • BRANCH (Parent)
    • ACTION: Edit Issue (System Labels)
      • Remove: subtasks_resolved

Quick explanation on the rule:

1. Triggers when the resolution changes
2. Verifies the issue is a sub-task
3. If resolution was set then navigate to the parent and verify that all sub-tasks are now resolved and add the label subtasks_resolved to the System Labels field
4. If resolution was cleared then navigate to the parent and clear the value

You'd need to create another one-time rule that processes all current issues as well (modify the trigger query as needed to cover any custom issue types you may have).

• TRIGGER: Scheduled

  • Type IN (Story, Task, Bug) AND resolved IS NOT EMPTY

• CONDITION (Related Issues)
  • Sub-Tasks
  • All Match Specified JQL

  • resolved IS NOT EMPTY

• ACTION: Edit Issue (System Labels)
  • Add: subtasks_resolved

Once that's set, you would incorporate this into your query:

("System Labels" IS EMPTY OR "System Labels" NOT IN (subtasks_resolved)