Transition report on all issues

[Chenhao]

Hi Guys.

Is it possible to get a report from JIRA that will show me the issue number, name/description and the transition history of all issues for a given project.

For example, we have project A and we want report like this:

issue number name/description transition history

A-1 xxxxxxx xxxxxx
A-1 xxxxxxx xxxxxx
A-2 xxxxxxx xxxxxx
A-3 xxxxxxx xxxxxx
A-3 xxxxxxx xxxxxx
A-3 xxxxxxxxxxxxx

A SQL script from would be ideal.

Regards,
Chen

[Henning Tietgens · Accepted solution]

You can install Script Runner, and run the following Script to get you list.

import com.atlassian.jira.bc.JiraServiceContextImpl
import com.atlassian.jira.bc.issue.search.SearchService
import com.atlassian.jira.component.ComponentAccessor
import com.atlassian.jira.issue.Issue
import com.atlassian.jira.issue.changehistory.ChangeHistoryManager
import com.atlassian.jira.security.JiraAuthenticationContext
import com.atlassian.jira.web.bean.PagerFilter

jqlQuery = 'project = XXX'
result = ''

ChangeHistoryManager changeHistoryManager = ComponentAccessor.getChangeHistoryManager()
SearchService searchService = ComponentAccessor.getComponent(SearchService.class);
JiraAuthenticationContext jiraAuthenticationContext = ComponentAccessor.getJiraAuthenticationContext()

user = jiraAuthenticationContext.getLoggedInUser()
ctx = new JiraServiceContextImpl(user)
List<Issue> issues = null

SearchService.ParseResult parseResult =  searchService.parseQuery(user, jqlQuery);
if (parseResult.isValid()) {
    def searchResult = searchService.search(user, parseResult.getQuery(), PagerFilter.getUnlimitedFilter())
    issues = searchResult.issues
} else {
    result = "Invalid JQL: $jqlQuery"
}

issues.each{ Issue issue ->
    changeItems = changeHistoryManager.getAllChangeItems(issue)

    changeItems.findAll{it.getField()=='status'}.each{
        result += "${issue.key}: ${it.created} ${it.getFrom()} -> ${it.getTo()}\r\n"
    }
}
return result

Maybe you want to adapt the ouput.

Henning

[Chenhao]

Hi Henning,

Much appreciated for your help.

I was also able to extract information using SQL query.

select jji.pkey as JiraNumber,

jit.pname as IssueType,

jji.SUMMARY,

jci.OLDSTRING as OldStep,

jci.NEWSTRING as NewStep,

jcg.AUTHOR,

jcg.CREATED

from jiraschema.changeitem jci

join jiraschema.changegroup jcg on

jci.groupid = jcg.ID

join jiraschema.jiraissue jji on

jcg.issueid = jji.ID

join jiraschema.issuetype jit on

jji.issuetype = jit.id

where jci.FIELD = 'status'

and jji.pkey like '%LIFE%'

and jji.RESOLUTIONDATE > '2012-09-01'