Jira Reporting - Custom report via REST API?

[Ricardo Gomes]

Hi,

I need to get a report from Jira either via REST API or SQL (or maybe some other method as long as I don't have to pay for another plugin) that shows me:

• For each ticket of a certain project (based on certain criteria)

• What are the linked tickets to each of the above (based on a certain type of linkage)

• and for each linked ticket that matches the criteria, I'd like to see: Sprint and Status fields from those tickets.

Is it possible to accomplish the above somehow?

Desperate to get a solution here so any hints would be greatly appreciated.

P.S. not a developer so I have some knowledge of REST and can do some programming with some help, so any further tech assistance here would be helpful. Ideally, I'd like to run a script that would generate a spreadsheet for me with the above data.

Thanks in advance!

Cheers!

[Michael Raj]

Hi @Ricardo Gomes ,

Your request sounds challenging especially when you have to learn all the stuff from the beginning.

It is possible and not that hard but it requires few different modules and more than one request to be send to REST API.

From my experience you have to answer yourself this basic question:

1. Do you have an access to Jira database to use SQL? It would be easier than via REST API?

Based on answer you can think about next steps.

Best

Michael

[Ricardo Gomes]

Thanks, @Michael Raj .

I've used REST to get and manipulate data from Jira before using different types of script languages but I'm not sure I'd have access to their SQL database.

How do I know that? Or request access?

Thank you!

[Michael Raj]

Hi @Ricardo Gomes ,

If you use REST API, let's stay with it. In the meantime you can request access to Jira SQL Server but you can do this exercise via REST API as well.

Based on your requirements I would follow these steps:

1. collect all issues from particular project (using JQL which you can validate using Jira Service Desk and then decode it into correct request -> https://www.w3schools.com/tags/ref_urlencode.ASP or just copy whole URL to notepad)

2. when collecting issues from step 1, check field "issuelinks" where information about links is stored

3. collect all issues' keys that match your criteria (your second bulletpoint)

4. collect sprint and status from those issues

Unless you can use special functions (example: https://community.atlassian.com/t5/Jira-questions/Get-list-of-linked-issues-how-to/qaq-p/95753) these steps may look different.

If you have thousands of issues above method may not be very efficient as you have to send request for each issue to collect sprint and status. Maybe you can use jql search using issuekey in ('issuekey-1','issuekey-2') to do it faster. But optimization is the next step.

Build a tool to collect basic data and then you can optimize.

Good luck!

Michael

[Michael Raj]

Hi @Ricardo Gomes ,

I've checked this solution: https://community.atlassian.com/t5/Jira-questions/jira-query-issues-with-linked-issues/qaq-p/426769 which is just JQL code: 

Project = XYZ and issueLinkType in (blocks, causes)

 And it works. So you can collect all issues at once and that should be even more efficient than steps proposed by me above.

Best

Michael

[Ricardo Gomes]

Thanks, Michael. Really appreciate your help here.

However, I'm not sure the JQL above helps me with my use case at all.

See, this is what I'm trying to accomplish -

Say, I have projects A and B. I want to query tickets from project A and for each one of those tickets I want to see the sprint and status from all the linked issues from project B based on a certain linkage.

Example of the final output for each ticket queried from project A:

==========================

Ticket-XYZ:

Linked issues (from project B):

- ticket-1  |  Sprint 1  | To Do

- ticket-2  |  Sprint 2  | in Progress

==========================

So I don't think there's a way to obtain this result by only using JQL. Does that make sense?

[Michael Raj]

Hi @Ricardo Gomes ,

I had to check this in UAT and I think I have a solution.

Yes, you can do this using JQL. In the following steps I will refer to your example.

1. Search for all issues from project XXX ("XXX" is easier to see in the code) and with certain linkage ("blocks" and "causes" for example). This can be achieved by JQL:

jql=project%20%3D%20%22XXX%22%20and%20%20issueLinkType%20in%20(blocks%2C%20causes)

 For better efficiency you can limit fields only to issuelinks with this code:

&fields=issuelinks

2. Now you have list of all issues you're interested in. In the response look carefully for inwardIssue (issue from your project XXX) and for outwardIssue (issue from different project, linked to inwardIssue). Store somewhere all the issuekeys you will find in the mentioned fields. You will need them in the next step.

3. Now everything depends on how many issues do you have. Jira API cannot process too many issues at once so you will have to paginate the results or send smaller requests.

Let's say you have 26 results (no pagination then). I would use search using JQL and function issuekey in (list,of,all,issuekeys) [XXX and YYY are project names]:

jql=issuekey%20in%20(XXX-28313%2CXXX-22975%2CYYY-22952%2CYYY-22956)

 Again it's a good practice to limit fields only to ones you're interested in. Unfortunately I don't use Sprint field, but for Status it's just:

&fields=status

I suggest you to find it in the API response for one example issue using Firefox (I'm using it for validation requests strings). I also suggest to build JQL in Jira Service Desk and then use it in the API.

When you collect all the data, it's just a matter of data processing to pick only particular parts.

Hope that will help you.

Best

Michael 

[Ricardo Gomes]

Thanks, again! I'll give it a try!