SQL statement to find linked issues with a ticket being closed and the linked ticket being open?

Question

Hello users,

I have been assigned a task to remove any issues that have been closed for more than 18 months. This I was able to complete with ease.

However, one of the criteria set is any closed ticket that is linked (the issuelinks table) to an open ticket must remain.. Is there a method out there that would allow me to find these linked closed tickets, so I can exclude them from removal?

Any help would be much appreciated.

Niall Rooney · Answer

Hi Pablo,

Thank you for your response.

I was able to generate a statement that got the results I was looking for.

Many Thanks again.

Contact Kintosoft · Answer

Using SQL to access to the Jira native database is discouraged by everybody.

However, the SQL+JQL app provides its own standard SQL language implementation to access to the Atlassian's public Java API.

In addition, it supports the Jira API for links, therefore it is possible to use SQL, JDBC, etc for what you are asking about.

DISCLAIMER: I work for the company behind the app.