Past issue status changes

[Vanessa Friedrich]

I would like to know how long a ticket was in a certain state in the past.

The tickets are all in status closed now and I would like to know how many of them took  longer than three weeks to be moved from open to "in progress" 

I was trying to use

fixVersion = x.x AND status was in (open) and status changed from "open" to "in progress" after -3w 

but with that query i get transition changes (time in source status) returned within seconds:

[Tuncay Senturk _Snapbytes_]

Hi @Vanessa Friedrich 

Welcome to the Community!

As already mentioned, Jira Cloud does not natively support this.

I'd also like to recommend a free app SnapMetrics – Real Time Analytics, (I am one of the people behind this app), it’s a free app that can do exactly what you're looking for.

- You can define a Time Between KPI metric to measure exactly how long it took for issues to move from “Open” to “In Progress” — not just how long they stayed in a status.
- Results are shown directly on the issue or in the KPI Report, where you can filter by fixVersion and easily identify tickets that took more than three weeks.
- There's also a Time in Status Report if you want to drill down further.

SnapMetrics not only show time in statuses, but also goes further by letting you define your own event-to-event (creation, resolution, status changes, comment made etc.) time spans, making it perfect for this kind of analysis.

Let me know if you have any questions.

[Daniel Franz - JXL]

Hi @Vanessa Friedrich

Welcome to the community!

As mentioned by others here, there is no native feature in Jira that enables you to measure time in status. Personally, I have found the automation-based approach useful.

If you're open to solutions from the Atlassian Marketplace, I'd like to recommend another app that my team and I are working on: JXL for Jira

JXL is a full-fledged spreadsheet/table view for your Jira data that allows viewing, inline-editing, copy-pasting, sorting, and filtering by all your work items' fields, much like you’d do in e.g. Excel, Google Sheets, Smartsheet, or Airtable. It also comes with a long list of smart and history columns that aren't natively available in Jira, including Time in [status], Time between [status] and [status], Time with [assignee], and many more.

This is how it looks in action:

As you can see above, you can easily sort and filter by your history columns, and also use them with other JXL features, such as conditional formatting (e.g. highlight long-running tickets in the table), custom work item hierarchy, grouping by all fields, field value sum-ups, etc.

Any questions just let me know.

Cheers,
Daniel

[Rahul_RVS]

Hi @Vanessa Friedrich 

Welcome to the community !!

As suggested by everyone a mktplace app can help to get this data. f you would be interested in one, you can try out

Time in Status Reports 

The app allows to view time in status of all the issues, with the ability to filter issues based on status duration criteria as shown below.

With this app you generate time in each workflow status for multiple issues with multiple filter and grouping options. The app has 20+ reports to meet a variety of use cases.

More details here.

Disclaimer : I am part of the app team for this add-on

[Shalini Pradhan]

Hello @Vanessa Friedrich ,

Good day! Welcome to Atlassian Community :)

We do not have a direct way of getting the data on how long a ticket was in a certain state in the past in Jira cloud

However, as a workaround:

• You can use Automation: How to calculate the time spent on each status and total time on the issue
• There's a free third-party app (quite a popular one) called Status Time Reports Free - Time in Status, which offers similar capabilities and might come in handy for reporting purposes.

Thank you!

[Usman]

Hello @Vanessa Friedrich 

Jira Cloud does not natively support reporting how long an issue was in a specific status at a previous point in time using only JQL. While JQL offers operators like status CHANGED FROM ... TO ..., it cannot calculate elapsed time between transitions or show how long it remained in a status in the past

You can see similar topics - https://community.atlassian.com/forums/Jira-questions/JQL-Query-to-fetch-time-taken-from-one-status-to-another/qaq-p/2937046

The query you attempted retrieves issues where the status moved from "open" to "in progress" after a certain point. However, this checks when the transition occurred, not how long the ticket remained in "open" before moving to "in progress." This is why you see transition durations of just seconds, reflecting only the timing of the transition event, not the duration in the "open" state

A few limitations with Jira Cloud JQL

• JQL cannot calculate the duration between two separate status transitions or find issues that stayed in a status longer than a specific period before transitioning.

• Built-in JQL filters only allow you to see when transitions occurred, not the elapsed time between them.

• Time-in-status metrics are not available retroactively for closed issues unless a third-party add-on or automation was in place beforehand

Workaround

You can use marketplace apps that analyze issue history and calculate time spent in each status. Popular options include:

• Time in Status for Jira Cloud

• Timepiece - Time in Status
  These tools process historical data and can report on issues that took longer than three weeks in "open" before moving to "in progress"

Jira API 

• Use the Jira REST API to export the issue history. (GET https://your-domain.atlassian.net/rest/api/3/issue/{issueIdOrKey}/changelog)

• Write a script to parse each issue’s history and calculate the interval between entering "open" and "in progress."

• Filter for tickets where this interval exceeds three weeks

Jira Automation for Future Issues

• Set up automation rules that record timestamps in custom fields when a ticket enters or exits statuses.

• Calculate and store the duration in "open" for future analysis

[Danut M _StonikByte_]

Hi @Vanessa Friedrich,

Welcome to the community! 

Unfortunately this is not something that can be done by using Jira's default tools. You'll need an app from Atlassian Marketplace to get this type of reports. 

If you want to try an app, this can be easily achieved by using the gadgets offered by our Great Gadgets app. 

By using the Time in Status gadget you can see the total or average time in every status of the workflow for the issues in a filter/JQL. 

On its Data tab, you can see the duration in every status for each issue.

Also, by using Control Chart gadget offered by our app, you can easily identify the issues that took longer than 3 weeks in Open state. All you have to do is to configure it to calculate cycle as time since the entrance in a "New" status category (or since issue creation) and the entrance in an "In-progress" status category, and set a threshold of 21 days. The issues that took longer (breaches) will be displayed in red-color, on top of the red threshold line

You can also use the Histogram Chart gadget configured the same, to see how many issues took longer than 21days.

On their Data tab these gadgets display also a report, showing in red-color the ones that took longer.  

If you have any questions about our gadgets, feel free to contact support@stonikbyte.com. 

Danut.