How to find all subtasks under standard tickets with a status of closed

[Anqi Zhang]

Hi

Is there a way to search for subtasks under closed standard tickets not using Issuefunction? (as I've no access to ScriptRunner)

Many thanks!

[Bill Sheboy · Accepted solution]

Hi @Anqi Zhang -- Welcome to the Atlassian Community!

That is not possible with the out-of-the-box features of JQL, as it cannot do sub-queries or joins, like a SQL.

As you note no access to marketplace addons like ScriptRunner, a few possible work-arounds are:

1. export all of the issues, and perform the join in another tool, such as a spreadsheet
2. identify the closed standard tickets, export just their keys to a spreadsheet, convert that to a comma-separated values (CSV) list, and then use those to create a new JQL statement: parent IN (list of the parent keys)
3. if you are doing this for one project and expect fewer than 100 issues in the result, you could automate the approach #2 above using an Automation for Jira rule

Kind regards,
Bill

[Anqi Zhang]

Thanks Bill. It works, I use vlookup function in the spreadsheet, and find the status of parent ticket

[Pavel Junek]

Hi Anqi,

Welcome to Atlassian community!

You can try write simple JQL, for eg.

project = xyz AND issuetype = Sub-task AND status = Closed

Jira JQL Advanced searching 

Pavel

[Anqi Zhang]

Thank you Pavel! My previous ask was unclear, Specifically , I would like to export all subtasks that belong to closed main tickets.  In other words,  the status of parent task is closed, I'd like to get all subtasks under such parent tasks. Many thanks.

[Danut M _StonikByte_]

Hi @Anqi Zhang,

You could try using the Work Breakdown Structure (WBS) gadget offered by our Great Gadgets plugin for visualizing the sub-tasks.

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

All you have to do it to have a filter that returns the closed parent tasks and sub-tasks and to configure the gadget to use this filter. It will do the grouping for you and you will be able to visualize the subtasks of closed stories/tasks. The JQL of the filter can be like this:

((issueType in standardIssueTypes() and statusCategory = Done) OR issuetype in subTaskIssueTypes()) AND project in ("Project1", "Project2")

Please note that this app offers many other gadgets that you will 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