Export status change history from Jira (inc. From, To, Date/Time, User)

[Ivan Shtanichev]

We are Jira cloud user and have a requirement from one of our users to export status transition history in below format to perform further analysis/reporting using excel.

Columns:

• Issue Key
• Status Changed From
• Status Changed To
• Status Changed on Date/Time
• Status Changed by User

Please advise if this can be queried/exported directly somehow, or if there is an add-on for this purpose?

[Ivan Shtanichev · Accepted solution]

Since asking this question, Atlassian have developed a free addon Jira Cloud for Excel (official) which allows exporting of status transition history by selecting sub-fields under 'Status Transition' under 'Fields' tab of the app, as shown in below screenshot. This exports all transition history per my original requirements and I would say is my preferred approach now.

 

[Stephen Sifers]

Hello all,

To follow up on this post to ensure there is a viable solution to be used with exporting data from Jira into a table format, we want to provide a couple of options. The two primary options are between using the Google Sheets app for Jira cloud and secondly using the REST API for more complex scenarios.

Firstly, the Google sheets app for Jira is a marketplace app which brings in functionality to export from Jira cloud and import into Google Sheets. This should allow you to specify the fields you need and how you want to export formatted. Further information on this on this app may be found at Jira Cloud for Google Sheets (Official).

Secondly, the more complex solution which will benefit departments which would like to automate the pull of data. Multiple Business intelligence products do over integration with a REST API or direct integration with Jira cloud itself (You would have to see how your BI/BA products will be compatible with a REST API). Further information on this may be found at Jira Cloud Platform Developer | REST API. There are also a few endpoints I would suggest starting with which will get you the data you need:

• GET /rest/api/3/issue/{issueIdOrKey}
• GET /rest/api/3/issue/{issueIdOrKey}/changelog
• GET /rest/api/3/issue/{issueIdOrKey}/worklog
• GET /rest/api/3/auditing/record

I hope these options above are viable options to be used with Jira cloud for exporting data into a useable format for your teams. Any feedback you have on the above would be appreciated, and we want to ensure you’re enabled complete your goals.

Regards,
Stephen Sifers

[Thomas Deiler]

Dear @Ivan Shtanichev,

best you do this with the REST API. Should I help you further?

So long

Thomas

[Thomas Deiler]

Dear @Ivan Shtanichev,

Have first a look at this already accepted articles:

JQL Query Return The Issue History for a Given Issue

Is it possible to get the issue history using the REST API?

Is it possible to get the issue history using the REST API? (same title, different page)

Issue history through REST API

If this all seems to be too complicated, I can help you with implementing in PHP.

So long

Thomas

[Maali Albalt]

Hi @Thomas Deiler just wondering whether you have used Jira APIs to implemented a solution to extract status transition dates.Thanks. 

[Thomas Deiler]

Dear @Maali Albalt,

have a look at the upper articles with "history" in the summary. Everything you need to know about one status change can be found in the issue history.

So long

Thomas

[karim -Atlassway-]

Hi @Antonio Sun  as an alternative , you can try out Issue Change History for better reporting .

It allows you to thoroughly examine and export the entire history of an issue. This includes details such as transitions, comments, updated fields, work log edits, attachment changes, and various other activities.

Atlassway Team

[Tomasz Kustrzynski]

There's an add-on addressing specifically this - https://marketplace.atlassian.com/plugins/com.sngtec.jira.cloud.exporter/cloud/overview

With Advanced Export you can choose fields changes to which you want to export.

Example below shows export of changes to Status, Components, Story Point and few others, but you can export only Status changes.

I think the only missing thing is information on user who performed the change, but since I'm the developer of this add-on I could add this for you fairly easily.  

[Thomas Deiler]

Dear @Ivan Shtanichev,

the plugin @Tomasz Kustrzynski named, is commercial. If you need to save money, I help you with the API.

So long

Thomas

[Ivan Shtanichev]

Hi @Thomas Deiler,

API sounds like the best way forward for us, we only require the 4 fields provided in excel or csv extract. If you can assist in any way with how to extract this via API that would be most helpful.

Regards,

Ivan

[Tomasz Kustrzynski]

@Thomas Deiler Just my 5 cents: Getting these exports right (API auth, formatting special cases for excel, testing, maintenance etc.) is not trivial.

Building custom solution makes sense if it's very specialised and is going to be used by 1 person in the org (since they tend not to be very user friendly).

@Ivan Shtanichev :

- if you're after one off use case, than you could use plugin in trial period for $0

- if you're after longer term tool, than development, testing and maintenance of custom solution will likely be much more costly than add-on ($1 for 10 users doesn't exactly break the bank :) )

[Thomas Deiler]

@Tomasz Kustrzynski you are right with the cost saving. But sometimes small agencies don't have the budget.

[Tomasz Kustrzynski]

Of course. I just presented my logic and one of the reasons add-ons make sense:

- if you're a small agency than you probably value time and have lots of important stuff to do, and in that case cost is very low.

- If you're a bigger organisation, more people will likely use your tool, in which case it's good for it to have good UI and be supported. Hence cost is higher, as it would be if you wanted to develop it in house.

The above is true for popular functionalities. If you're after something special or sophisticated or secret you may not have choice but to develop it yourself.

[Andreas]

Have you tried one of these solutions?

https://confluence.atlassian.com/jira/tracking-the-time-taken-for-each-workflow-transition-126910724.html

Edit: Just realized that you're using JIRA Cloud... so options #2 and #3 are not suitable for you. Sry.