Create
cancel
Showing results for 
Search instead for 
Did you mean: 
Sign up Log in

Next challenges

Recent achievements

  • Global
  • Personal

Recognition

  • Give kudos
  • Received
  • Given

Leaderboard

  • Global

Trophy case

Kudos (beta program)

Kudos logo

You've been invited into the Kudos (beta program) private group. Chat with others in the program, or give feedback to Atlassian.

View group

It's not the same without you

Join the community to find out what other Atlassian users are discussing, debating and creating.

Atlassian Community Hero Image Collage

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

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.

3 answers

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.

Screen Shot 2020-12-11 at 10.08.41.png

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.

Screen Shot 2020-12-11 at 10.14.15.png

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.

tisCloud_FirstTransitionToStatusDate_Report.pngTimeline.png

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

0 votes

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).

Suggest an answer

Log in or Sign up to answer
TAGS

Community Events

Connect with like-minded Atlassian users at free events near you!

Find an event

Connect with like-minded Atlassian users at free events near you!

Unfortunately there are no Community Events near you at the moment.

Host an event

You're one step closer to meeting fellow Atlassian users at your local event. Learn more about Community Events

Events near you