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

[Emmanuella_Emmanuel]

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

[Jack Nolddor [Sweet Bananas] · Accepted solution]

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:

• epicsOf()
• hasSubtasks()
• issuesInEpics()
• linkedIssuesOf()
• parentsOf()
• portfolioChildOf()
• portfolioParentOf()
• subtasksOf()

References:

• Official documentation

Hope this helps you to create awesome queries <3

Kind regards

[Emmanuella_Emmanuel]

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]]

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

• JQL Booster Pack

• JQL Tricks Plugin

• JQL Search Extensions for Jira & reports

• ScriptRunner for Jira

• Search Linked Issues

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

[Emmanuella_Emmanuel]

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

[Jack Nolddor [Sweet Bananas]]

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

[Emmanuella_Emmanuel]

Hi Jack you've been so helpful.

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

[Jack Nolddor [Sweet Bananas]]

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

[Peter Vandenberghe · Accepted solution]

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;

[Ryan Shillington · Accepted solution]

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)

[Ibrahim Hussaini]

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]

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

[Ibrahim Hussaini]

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.

[Surya Narayanan]

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]

This is brilliant, and works perfectly. Thanks!

[Levy, Jessica]

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

[Kevin Dickson]

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

[Nic Brough -Adaptavist-]

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