How to write a JQL to see the history (dates) of all the status changes for all the tickets ?

[Arunn Subbaraman Thyagarajan]

I am trying to get the history (date) information and the corresponding status information for all the status changes that happened in all the tickets in a project.

I came across answers like this

project = "Project Name" AND status changed FROM "Status 1" TO "Status 2" DURING ('yyyy/MM/dd', 'yyyy/MM/dd')

in the Atlassian community. But in my case I am not looking for specific status changes, I am interested to see all the status changes that ever happened in the tickets along with what the change was.

Any help is appreciated. Thank you.

[Iago Docando]

Keep in mind that when you run a JQL in Jira you only get a LIST OF ISSUES that match your criteria.

Of course you can configure what colums you want to show but If you want to check any information that is not a column you'll need to make use of the API or some other 3rd party solution (they'll check the API for you and present the results in a more convenient way for you).

[Emre Toptancı _OBSS_]

Hello @Arunn Subbaraman Thyagarajan ,

As far as I understand, you want to see a list of issues with workflow status names as columns and the dates of entry to those statuses as values. Jira keeps these dates in its issue histories. You can see them in the History tab of each issue. I am afraid Jira does not provide this information as a report, out-of-the-box. 

You have a few options but first, two things to note here:

First, an issue might visit the same status more than once so those dates will be different. Second, I don't know what you will use these dates for but for some cases, using the status entry date makes sense, for other cases using status exit date makes more sense.

As a first option, you can create "custom data fields" for each status date that you are interested in, modify your workflows and use the built-in "Update Issue Custom Field" post function to copy the current date to appropriate date field. By modifying all workflow transitions, you can have status dates in different fields. It will also be possible to use those fields in JQL. This will get you a solution but you need to implement this in every transition and actually it is hard to maintain.

The alternative is doing your own custom development to get the raw data either from REST API or DB tables and prepare your own reports. 

Finally, if you are interested in a ready built solution, our team at OBSS built Time in Status app for this exact need. It is available for Jira Server, Cloud and Data Center.

The app contains 4 different date reports that show   first/last   entry/exit   dates for all statuses.

Time in Status also gives you reports that show how much time each issue spent on each status or assigned to each assignee. You can calculate averages and sums of those durations grouped by issue fields you select. (For example see the average InProgress time per project and per issuetype). You can also combine statuses into consolidated columns to see metrics like Cycle Time or Lead Time.

Using Time in Status you can:

• See how much time each issue spent on each status, assignee, user group and also see dates of status transitions.
• Calculate averages and sums of those durations grouped by issue fields you select. (For example see average InProgress time per project and per issuetype.)
• Define your custom calendars for more accurate duration reporting.
• Export your data as XLS, XLSX or CSV.
• Access data via REST API.
• See Time in Status data and charts for each issue as a tab on issue view screen.
• (Dashboard gadget for cloud is released, server&DC is on the way)

https://marketplace.atlassian.com/1211756

EmreT

[Bloompeak Support]

Hi @Arunn Subbaraman Thyagarajan , welcome to the community!

Unfortunately, it is not possible with JQL out-of-the-box. But the raw data is available in history of each issue. You can get issue history data using Jira Rest Api for each issue, you get a response similar to the below. Then using coding, you need to parse json, extract the status change date and generate the report.

Or, you can try marketplace apps. We built Status Time app for this exact need. It provides reports on how much time spent in each status as well as status entry dates and status transition count. It also has CSV export feature.

Once you enter your working calendar into the app, it takes your working schedule into account too. That is, "In Progress" time of an issue opened on Friday at 5 PM and closed on Monday at 9 AM, will be a few hours rather than 3 days. It has various other reports like assignee time, status entry dates, average/sum reports(eg. average in progress time per project).

Here is the online demo link, you can see it in action and try. Hope it helps.

[Agustín Nin]

Juan, how are you? Could you resolve to get the status history of an issue X at the end? I'm with the same problem, can you send me a example to method rest api to get histories.
Thank you