How to Search for Subtasks with a Parent Issue of Specified Status in JIRA

In JIRA Query Language (JQL), searching for subtasks whose parent issues have a specific status can be achieved through a step-by-step process. This guide will detail how to perform such a search using JQL and alternative methods if direct querying isn't possible.

Using JQL to Search for Subtasks with Specific Parent Status

JIRA Query Language (JQL) provides powerful tools for querying issues, including subtasks. To find all subtasks where the parent issue's status is set to a specific value, you can use the following query:

Direct Approach Using JQL

The following JQL query can be used to find all subtasks whose parent issues have a status set to a specific value, such as 'Open' or 'Done':

issuetype  Sub-task AND parent in issues in statuses having statusCategory  Done

This query targets subtasks where the parent's status is in a specific status category, such as 'Done'. Replace 'Done' with the desired status as necessary.

Manual Approach for Non-Supported JIRA Instances

If your JIRA instance does not support the specific JQL functions, you may need to take a more manual approach:

Step 1: Find Parent Issues with Specific Status

First, identify parent issues that have the desired status. You can achieve this with the following JQL query:

status  Open

Run this query to find all parent issues that are 'Open'. Note the issue keys from the results.

Step 2: Find Subtasks of Identified Parent Issues

Once you have the issue keys of the parent issues, you can query for subtasks that are linked to these parent issues. Use the following JQL query to find subtasks:

parent in KEY-1 KEY-2 KEY-3

Replace 'KEY-1, KEY-2, KEY-3' with the actual issue keys obtained from the previous step. This query will return all subtasks with those parent issue keys.

Alternative Method Using Plugins

If you perform such queries frequently, consider using plugins like ScriptRunner. These plugins extend JQL functionality and can simplify your search processes. They allow you to perform complex queries and save time when managing large JIRA instances.

Direct Database Access for on-Premises JIRA

If you are working with the on-premise version of JIRA and have full access to its database, you can perform a direct query to retrieve the necessary data. While this method is more advanced and requires database knowledge, it can be more efficient for large-scale operations.

Workaround for Missing Parent Link Field

In some versions of JIRA, particularly JIRA v7.4.4 on-premise, the Parent ID or Parent Link field may not be directly accessible via JQL. Instead, you can use a workaround involving two queries and a VLookup function in Excel or Google Sheets:

Step 1: Query All Open Issues

Run a query to find all open issues:

status  Open

Export the results to CSV. This will include an 'Issue ID' field that is not visible in the web view.

Step 2: Query All Sub-Tasks with Parent ID

Run a query to find all sub-tasks, noting the 'Parent ID' field in the results:

issuetype  sub-task

Export the results to CSV.

Step 3: VLookup to Join Issue ID to Parent ID

Use a database or VLookup function in Excel/Google Sheets to join the 'Issue ID' from the first export to the 'Parent ID' from the second export. This will allow you to match subtasks with their respective parent issues.

Conclusion

While JIRA's native capabilities may not always provide the exact functionality needed, with a bit of creativity, you can achieve your goals by leveraging JQL, manual queries, or even direct database access for on-premise instances. The key is to break down the problem into manageable steps and utilize the available tools and resources to your advantage.

For more detailed information and best practices, consider exploring Atlassian's official documentation and discussing with your JIRA administrator or team.