SQL query for Jira 7.1.1 to get all RESOLVED issues.

[Vishal Kulshreshtha]

Hello All,

I have Jira 7.1.1 on PostgreSQL 9.6, I want SQL query for Jira 7.1.1 to get all status=RESOLVED issues.

[Ismael Jimoh]

@Vishal Kulshreshtha 

May I ask why you want to grab this from your database considering it is very easy to achieve it directly from the Jira UI?

Also the definition of resolved in Jira means the resolution field is not empty hence in your case you would need to check a few tables for this which I don’t know of heart but can get for you in a frw minutes.

[Ismael Jimoh]

This gives you all resolved tickets going by the assumption that resolved tickets have the resolution field set.

SELECT id, summary, project, resolution FROM jiraissue WHERE resolution IS NOT NULL;

You can also replace the attributes I selected with a '*' which would pull all attributes instead and you can do the same for the status of the issue.

To know the name of the resolution or status corresponding to the id, you can pull this value from the resolution and issuestatus tables respectively.

Hope it helps.