Finding open issues with closed child tickets

[C-Ramon_Alicea]

I am trying to find all open issues within a certain state where they exceeded their due dates and that have all their child tickets closed. I have generated the following statement but keep getting results where a couple of the child tickets are still open:

project = PROJECT1 AND issuetype = "INTAKE Request" AND status = Initialization AND resolution = Unresolved AND issueFunction in linkedIssuesOf("status in (Closed, Completed, Cancelled)", "Dependency For", "relates to", "is related to") AND "Expected Completion Date" <= -1d

The goal is to identify open issues in the final status that have not been closed when all the child work (child tickets) are done.

Thank you in advance for any assistance.

[Laurie Sciutti · Accepted solution]

Hi @C-Ramon_Alicea and welcome to the Community!  Try the following:

project = PROJECT1 AND issuetype = "INTAKE Request" AND status = Initialization AND resolution = Unresolved AND NOT issueFunction in linkedIssuesOf("statuscategory in ('To Do', 'In Progress')", "Dependency For", "relates to", "is related to") AND "Expected Completion Date" <= -1d

One thing to note though:  if you are looking at child issues, I'm assuming you want to include a check of sub-tasks in your filter.  If so, AND if you use ScriptRunner, you would want to include the following as an OR statement:

issueFunction in parentsOf("statuscategory not in ('To Do', 'In Progress')")

[C-Ramon_Alicea]

Hi @Laurie Sciutti 

Thank you for your feedback. While I was able to get some response back from the statement, I am still receiving issues in the response that have either one (1) or two (2) linked tickets still opened. Additionally, to answer your previous question, I am using ScriptRunner.  A little additional information to my situation. The child tickets are listed in the Issue Links section has either relates to, is related to, or a very rare Dependency For or Depends On. These child tickets belong to other business units’ projects.

[Laurie Sciutti]

@C-Ramon_Alicea ~ that's helpful.  Of the ones that still appear in your query, are those "Depends On" linked type?  Those weren't in your provided example above.    If you adjusted your query to check for those?  If you did, do you see any similarities in the ones still showing that shouldn't?  Like, do they share they same link type?

[C-Ramon_Alicea]

@Laurie Sciutti 

I found the link type “Depends On” from your query and adjusted the query to include it. I believe that I implemented your feedback correctly:

project = PROJECT1 AND issuetype = "INTAKE Request" AND status = Initialization AND resolution = Unresolved AND NOT issueFunction in linkedIssuesOfAll("statuscategory in ('To Do', 'In Progress')", "Dependency For", "relates to", "is related to", "Depends On") AND "Expected Completion Date" <= -1d OR project = PROJECT1 AND issuetype = "INTAKE Request" AND status = Initialization AND resolution = Unresolved AND NOT issueFunction in parentsOf("statuscategory not in ('To Do', 'In Progress')") AND "Expected Completion Date" <= -1d

The ones that appeared that are outside the goal the link types vary.  

[Laurie Sciutti]

Did that provide the correct results, @C-Ramon_Alicea ?

[C-Ramon_Alicea]

@Laurie Sciutti 

It did not, it is still producing the same results where some of the linked tickets are still open. 

[Laurie Sciutti]

@C-Ramon_Alicea ~ try breaking down each piece of the query to zero in on the issue.  For instance, if you remove the link types and just run the first part of the statement as:

project = PROJECT1 AND issuetype = "INTAKE Request" AND status = Initialization AND resolution = Unresolved AND NOT issueFunction in linkedIssuesOfAll("statuscategory in ('To Do', 'In Progress')") AND "Expected Completion Date" <= -1d

This should return any unresolved Project1 Intake Requests with a status of Initialization that doesn't have any open linked issues (regardless of link type).

[C-Ramon_Alicea]

@Laurie Sciutti 

Thank you so much for your assistance, this resulted in what I was searching for. I truly appreciate you taking the time out of your day to help me.

[Laurie Sciutti]

Awesome!

[AndraskoP]

Hello @Laurie Sciutti ,

May I ask you one question.

I am trying to get similar result. 
I have one project XY where I want to find all issues that are in status "In Progress" but have related issues in Closed status. The issues are in relation "Relates to" and can be in different project.

Example: 
Issue XY-1 is in progress and it has issue AB-1 (from different project) linked as "Relates To" that is in status Closed.
Goal is to get all similar issues to XY-1 issue. 

This is the query I am trying to use:

project = XY AND status = "In Progress" AND NOT issueFunction in
linkedIssuesOf("statuscategory in ('To Do', 'In Progress')", "Relates to")

When I run this query I always run to a timeout. The estimated time for the query to finis is abour 20 min. 

Is there something I can do about it? 

Thank you very much!