Is there a way to export a list of tickets, between specific dates, that includes all transitions and dates of those transitions?

[Christopher Douglas]

I'm trying to automate some ticket metrics I'm collecting, but I would like raw data so I can perform my own analysis on it.

[Joe Pitt · Accepted solution]

You can't get the dates of transitions out of the box with the normal reports. Maybe with JQL, but I'm not sure.  I capture the date of milestone transitions and who performed them for just such reporting data and easy reference. You could add that for the future.

[Christopher Douglas]

Thanks Joe. I don't necessarily need it out of a report. If I need to do API calls or add something to an advanced search query I'm ready and willing to do that. When exporting from an advanced search it includes a bunch of things, like creation date and resolved, but it's missing things that happen between those two points.

I, unfortunately, want more than just milestones. I want workflow data. This is our tool for tracking tickets, and milestones aren't enough for true continuous improvement. I could start collecting data now, but that also means I can't do anything with that data for 2 or 3 months. this isn't the solution i'm looking for. there's years worth of data just sitting in JIRA i can't access. I'm sure there's a way to get to it, I just don't know what it is

[Joe Pitt]

I don't have much experience with JQL but if the data is available through it you may be able to get what you need. Search the forum and the web for JQL help. You can create the markers for every transition if that is what you need. I wouldn't put them on any of the screens and use a post function to set them. The trick is to copy the date/time from current AFTER the update command in the post function list.

[MattS]

You can use the REST API to get the issue history which includes the transitions. I suggest https://pythonhosted.org/jira/ and the following rough code

def fix_status(log, options, jira):
    '''
    Return non-zero on error
    '''
    
    jql = 'project = EXAMPLE and status = Closed and updated >= 2013-08-28'
    log.info("Searching with: %s" % (jql))
    
    start_idx = 0
    block_size = 200
    # Total issues changed so far
    total_issues = 0
    # Maximum number to be changed
    max_to_change = 3000
    while True and max_to_change > total_issues:
        issues = jira.search_issues(jql, startAt=start_idx, maxResults=block_size)
        if not len(issues):
            log.info("No more issues returned in search")
            break
        for issue in issues:
            log.info('%s processing' % (issue.key))
            res = set_status(log, options, jira, issue)
            if res == 0:
                continue
            if res == 2:
                log.warn("%s: problem updating status for " % issue.key)
                continue
            if res != 1:
                log.error("Incorrect return code")
                continue
            total_issues += 1
            if total_issues == max_to_change:
                break
        log.info("Total: %s issue(s) updated" % total_issues)
        start_idx += block_size
    log.info("Overall Total: %s issue(s)" % total_issues)
    return 0
    
def set_status(log, options, jira, issue):
    '''
    Return 0 for no update, 1 for update, 2 for error
    '''
    issue = jira.issue(issue.key, expand='changelog')
    changelog = issue.changelog
    # Note order change for performance
    foundA = False
    FoundB = False
    for history in reversed(changelog.histories):
        for item in history.items:
            debug = False
            if debug:
                log.info('Date %s' % (history.created))
                log.info('Field %s' % (item.field))
                log.info('fromString %s' % (item.fromString))
                log.info('from %s' % (getattr(item, 'from')))
                log.info('toString %s' % (item.toString))
                log.info('to %s' % (item.to))
            if item.field in ['status'] and item.fromString in ['Resolved'] and item.toString in ['Open']:
                foundA = True
                
            if item.field in ['Workflow'] and item.fromString in ['JIRA Default Workflow'] and item.toString in ['Example Workflow']:
                foundB = True
    if not foundA or not foundB:
        return 0
    if options.dryrun:
        log.info("DRYRUN: %s: setting status to Resolved" % (issue.key))
    else:
        try:
            log.info("%s: setting status to Resolved" % (issue.key))
            # Workflow properties or permissions schemes can give 
            log.info("%s: changing from Closed to Resolved" % issue.key)
            jira.transition_issue(issue.key, '191')
        except Exception,e:
            log.error("Unable to update status: %s" % e)
            return 2
    return 1

[Christopher Douglas]

Thanks Joe! I appreciate you taking the time. I think this will be useful going forward, but i dont' think I can get what i need via JQL. tried messing with that most of the evening but didnt' get anywhere really. something isn't clicking, or maybe its just not possible to export it via the issue navigator. I'll mess with it a bit more today, but i'm not hopeful.

I did put in a support ticket, and was pointed at this: https://marketplace.atlassian.com/plugins/com.deiser.jira.exporter/cloud/overview in addition to what you suggested. so going to try this plugin once my site-admin gets back or I get updated privileges to do it myself.

---------

Matt, that looks pretty awesome. I'll try that out if the plugin Atlassian suggested doesn't do what I need. I knew I could get it out of the API, just needed someone to point me in the right direction, thanks!

[Emre Toptancı _OBSS_]

For those interested in this, Time in Status app by OBSS produces reports that shows how much time each issues spent in each status, assignee or group. The app also includes reports that show first/last status transition dates for each status.

All report data can be exported as XLS, XLSX or CSV.