Query JIRA server database

Question

Hello there!

I need to create a report to determine the issue types that are not currently in use in our JIRA server instance (8.13).
I have access to query the postgres database (psql 9.2.24, server 11.13), the problem is that I'm not very good with SQL.
I was able to retrieve the issue types that are not currently in use, but I need to retrieve more than that.
The ideal report would contain the following information:

Issue Type | Jira Project | Project lead | number of issues | date of last issue created

Could someone help me with the query to get this information? I understand that some of the columns may be difficult to get (like "date of last issue created" per issue type), so if you provide a query that gets me some of the columns but not all of them it would be super ok as well.

Thanks in advance!

Ignacio A · Answer

Hi all,

I'm still looking to get the data as I stated in my previous comments. In this quest I found something in this post.

The query returns project key, project lead, and last updated. I had to modify the query so it could work on my postgres version, here it is:

SELECT DISTINCT p.pkey,p.LEAD,MAX(i.UPDATED) as "Last Updated" FROM jiraissue i INNER JOIN project p ON p.ID = i.PROJECT GROUP BY p.pkey,p.LEAD ORDER BY MAX(i.UPDATED) ASC;

I hope this helps

Ignacio A · Answer

Thank you @Matt Doar , that was very helpful!

Would there be a way to get the projects in which they are being applied?

Thanks!

Nic Brough -Adaptavist- · Answer

I would very strongly recommend that you stop looking at the database.  It's not designed or intended for reporting and unless you have a good understanding of it, and decent SQL knowledge, you are quickly going to be building queries that are hideously complex, non-performant and probably just plain wrong.

I've recently run into a query that we had to tell the client "the best thing you can do is remove access to the database from this user" - it was over 3,000 characters long, included 40 joins, and was the reason they had performance issues with their Jira.  It was reading for a single issue, and giving the wrong answer.

You should use the REST API instead.