Get all issue status transitions and their respective Issuenum

[Benjamin chee_guoee]

Hey community,

I refer to this amazing post 

https://confluence.atlassian.com/jirakb/how-to-list-all-the-issue-status-transitions-from-database-in-jira-747832179.html

It has helped me get the issue transitions i needed.

Now I want to get the issue number of these issue transitions.

Can someone tell me which table to join to and on what.

Thank you very much!

[Antoine Berry · Accepted solution]

Hi @Benjamin chee_guoee ,

Please try this : 

SELECT 
CONCAT(project.pkey, '-', jiraissue.issuenum) as issuenum,
changeitem.oldstring as oldstatus, 
changeitem.newstring as newstatus, 
changegroup.author as author, 
changegroup.created as created 
FROM changeitem 
JOIN changegroup ON changeitem.groupid=changegroup.id 
JOIN jiraissue ON jiraissue.ID = changegroup.issueid
JOIN project ON project.ID = jiraissue.PROJECT
WHERE changeitem.field='status'

Antoine 

[Antoine Berry]

Hi @Benjamin chee_guoee ,

Did that help ?

Antoine

[Benjamin chee_guoee]

Hi Antoine, yes it worked! Thank you! (:

[John Funk]

Hi Benjamin - Welcome to the Community!

If you are looking for the ID of the transition itself (and not really an issue id/key) then you can find that by editing the workflow, then click on Text to see the full listing of all transitions and find the appropriate ID for it. 

[Benjamin chee_guoee]

Hey John,

I will try this out! 

Thank you!