Query to find jira issues in one project that are linked to issues on another project

Emmanuella_Emmanuel March 25, 2020

Trying to search issues (defects/stories) in one project board that are linked to other issues in another board

5 answers

3 accepted

1 vote
Answer accepted
Jack Nolddor [Sweet Bananas]
Marketplace Partner
Marketplace Partners provide apps and integrations available on the Atlassian Marketplace that extend the power of Atlassian products.
March 25, 2020

Hi Emmanuella_Emmanuel and Welcome,

Sadly you cannot achieve the desired search using standard features on Jira, you must go for a third-party app instead. Using i.e. JQL Booster Pack you can type the following:

 

Find issues in one project linked to another project ,  i.e.:

project = "My Project" AND issue IN linkedIssuesOf(' project = "Other Project" ')

Note that this is just an exaple, you must tune your query to fit your needs

Using this app you can also query other issues relations, check:

 

References:

 

Hope this helps you to create awesome queries <3

Kind regards

Emmanuella_Emmanuel March 25, 2020

So the queries you listed (including the example) can only be used with JQL booster pack?

is there an alternative/something similar I can use without JQL booster pack?

Jack Nolddor [Sweet Bananas]
Marketplace Partner
Marketplace Partners provide apps and integrations available on the Atlassian Marketplace that extend the power of Atlassian products.
March 25, 2020

Sure, there are a few alternatives, the most known are:

 

...but there are more on the Atlassian Marketplace.

Like # people like this
Emmanuella_Emmanuel March 25, 2020

The jira instance I use has scriptrunner. How would I do the search using this?

Jack Nolddor [Sweet Bananas]
Marketplace Partner
Marketplace Partners provide apps and integrations available on the Atlassian Marketplace that extend the power of Atlassian products.
March 25, 2020

Then, you can be able to use the following query:

 

project = "My Project" AND issueFunction IN linkedIssuesOf(' project = "Other Project" ')

 

using Advanced Search functionality.

 

Regards

Like # people like this
Emmanuella_Emmanuel March 25, 2020

Hi Jack you've been so helpful.

Does the same query work with powerscript or is it different?

Jack Nolddor [Sweet Bananas]
Marketplace Partner
Marketplace Partners provide apps and integrations available on the Atlassian Marketplace that extend the power of Atlassian products.
March 25, 2020

I'm sorry but I don't know what powerscript is... Could you further explain your question, please?

0 votes
Answer accepted
Peter Vandenberghe September 15, 2021

I recently used this query for this on a PostgreSQL database:

SELECT count(PJ1.pkey) as linksfound,
       PJ1.pkey as SrcProjName,
    PJ2.pkey as DestProjName
from issuelink ILINK
inner join jiraissue JI1 on ILINK.source = JI1.id
inner join jiraissue JI2 on ILINK.destination = JI2.id
inner join project PJ1 on JI1.project = PJ1.id
inner join project PJ2 on JI2.project = PJ2.id
where PJ1.pkey != PJ2.pkey
group by SrcProjName, DestProjName
order by SrcProjName;
0 votes
Answer accepted
Ryan Shillington January 25, 2021

I solved this problem without an extra plugin, but it's not a perfect solution.

I wrote a little program to create a list of the last ~150 issues in the project I was looking for in a  single sentence and then put it in a query like:

project = QI AND linkedIssue in (QES-300, QES-301, QES-302, QES-303, QES-304, QES-305, QES-306, QES-307, QES-308, QES-309, QES-310, QES-311, QES-312, QES-313, QES-314, QES-315, QES-316, QES-317, QES-318, QES-319, QES-320, QES-321, QES-322, QES-323, QES-324, QES-325, QES-326, QES-327, QES-328, QES-329, QES-330, QES-331, QES-332, QES-333, QES-334, QES-335, QES-336, QES-337, QES-338, QES-339, QES-340, QES-341, QES-342, QES-343, QES-344, QES-345, QES-346, QES-347, QES-348, QES-349, QES-350, QES-351, QES-352, QES-353, QES-354, QES-355, QES-356, QES-357, QES-358, QES-359, QES-360, QES-361, QES-362, QES-363, QES-364, QES-365, QES-366, QES-367, QES-368, QES-369, QES-370, QES-371, QES-372, QES-373, QES-374, QES-375, QES-376, QES-377, QES-378, QES-379, QES-380, QES-381, QES-382, QES-383, QES-384, QES-385, QES-386, QES-387, QES-388, QES-389, QES-390, QES-391, QES-392, QES-393, QES-394, QES-395, QES-396, QES-397, QES-398, QES-399, QES-400, QES-401, QES-402, QES-403, QES-404, QES-405, QES-406, QES-407, QES-408, QES-409, QES-410, QES-411, QES-412, QES-413, QES-414, QES-415, QES-416, QES-417, QES-418, QES-419, QES-420, QES-421, QES-422, QES-423, QES-424, QES-425, QES-426, QES-427, QES-428, QES-429, QES-430, QES-431, QES-432, QES-433, QES-434, QES-435, QES-436, QES-437, QES-438, QES-439, QES-440, QES-441, QES-442)
1 vote
Ibrahim Hussaini
I'm New Here
I'm New Here
Those new to the Atlassian Community have posted less than three times. Give them a warm welcome!
January 30, 2023

For others who stumble across this problem, Jira cloud still does not have a solution. I decided to use a method that requires no installation of programs or use of db's. I used a chrome developer tools one-liner of code.

1. run a query to get all tickets from your project with your desired linked issue type
e.g. `project = Project1 and issueLinkType="relates to"`
If you want tickets with any linked issue type, try
`project = Project1 AND issueLinkType is not EMPTY`

2. Click 'Export as' and choose 'HTML Report (all fields)'

3. Open the HTML file of the results in chrome (or firefox, etc)

4. Right click and select 'Inspect'

5. Select the 'Console' tab

6. Paste in the following query to hide all issues which dont have a linked issue with a project key of "ABC-"
```
document.querySelectorAll(".issuelinks").forEach(elem => {
    if (!elem.innerText.includes("ABC-")) {
        elem.parentNode.remove();
}});

```
7. You should now have an HTML page with only Project1 tickets that have linked issues from project with key ABC. You can save and share this HTML with others.

Note. If you want to keep tickets which have linked issues from Project ABC or XYZ, you can just add more conditions to the code
```
document.querySelectorAll(".issuelinks").forEach(elem => {
    if (!elem.innerText.includes("ABC-") and !elem.innerText.includes("XYZ-")) {
        elem.parentNode.remove();
}});

```

I know this doesn't provide the results within Jira, but its the best I could get for a no installation, free solution

Ryan Shillington February 16, 2023

Or, export the results to Excel and use it to search. That works pretty well too.

Ibrahim Hussaini
I'm New Here
I'm New Here
Those new to the Atlassian Community have posted less than three times. Give them a warm welcome!
February 21, 2023

This was a while back, but from memory it was difficult to create a repeatable solution for excel as the column numbers to examine could shrink or grow. You could probably convert the data into a table, and then filter based on the heading of the column and the value of the column. In any case, please share your method for future users. For me this was simpler as its just a paste of a one liner and you're instantly given a shareable result.

0 votes
Surya Narayanan
I'm New Here
I'm New Here
Those new to the Atlassian Community have posted less than three times. Give them a warm welcome!
April 26, 2023

Hi All,

 

I am new to JQL and i did the following to get the desired outcome:

1. First i created a filter query that contains all the tickets from the projects that are linked to the tickets in the project from which i am trying to filter out the linked tickets

for example: 

Query name: LinkedProjects

JQL: "Project in (Project1,Project2,Project3, Project4, Project5...)

2. Then i create another query that lists down the tickets that are connected to the tickets mentioned in the above filter query

for example:

Query name: LinkedTickets

JQL: "project = MyProject" and issueFunction in linkedIssuesOf("filter = 'LinkedProjects'")

 

This approach gave me all the tickets from the project MyProject that were linked to other projects specified in the filter query LinkedProjects.

 

Hope this helps.

Randy Showalter
I'm New Here
I'm New Here
Those new to the Atlassian Community have posted less than three times. Give them a warm welcome!
May 31, 2023

This is brilliant, and works perfectly. Thanks!

Levy, Jessica
I'm New Here
I'm New Here
Those new to the Atlassian Community have posted less than three times. Give them a warm welcome!
August 24, 2023

I seem to be having issues with issueFunction and linkedIssuesOf - do you know if there are any alternatives to get the same result?

Like natasha.roumanoff likes this
Kevin Dickson May 21, 2024

I think he has ScriptRunner installed.
issueFunction is not a default JQL function

Like Nic Brough -Adaptavist- likes this
Nic Brough -Adaptavist-
Community Leader
Community Leader
Community Leaders are connectors, ambassadors, and mentors. On the online community, they serve as thought leaders, product experts, and moderators.
May 23, 2024

@Kevin Dickson is right - issueFunction is a Scriptrunner search term.

Like Kevin Dickson likes this

Suggest an answer

Log in or Sign up to answer