JQL to show tickets in the same status for more than x days

[Jake S]

I'm looking for some JQL to show open tickets in the same status for more than x days. Any help would be appreciated. 

[Trudy Claspill]

Hello @Jake S 

You can use the CHANGED operator for that. 

https://support.atlassian.com/jira-software-cloud/docs/jql-operators/#CHANGED

Example:

statusCategory="To Do" and not status CHANGED AFTER startOfDay(-3d)

This filter would get all issues with a gray color-coded Status (regardless of the exact name of the Status) that have not changed Status values for at least the past three days.

[Jake S]

Brilliant thank you Trudy.

I am wondering what "statusCategory="To Do"" adds to the condition when I am looking to see all open cards. I understand this part "and not status CHANGED AFTER startOfDay(-3d)"

what does this mean "all issues with a gray color-coded Status"?

Thank you :)

[Trudy Claspill]

Each individual Status value belongs to one of three Status Categories. The Status Categories set the color for the Status lozenge.

The Stays Categories and the corresponding lozenge colors are:

• To Do - gray
• In Progress - blue
• Done - green

In your environment  you could have a variety of individual Status vales the all indicate "work has not been started" like Open, To Do, Submitted, New, and so on. If you wanted to report on all the issues in any of these Statuses you could create a filter such as

status in (Open, "To Do", Submitted, New) and...

Or you can filter for issues in any Status that is part of the "To Do" Status Category:

starusCategory="To Do" and...

The benefit of using Status Category is that you don't have to know and explicitly list each Status value. As new Status values are added to your environment, use of starusCategory in your filters will automatically account for the new values.

[Jake S]

I am now wondering if stale tickets are more then just in the same status for X days, could it be there has been no change to the ticket at all for X day or no comments have been added to the ticket? What do you guys suggest?

If so what would be the 2 JQL statement to identify tickets that:

1. have not changed for X days?

2. have not had comments added for X days?

Thank you

[Trudy Claspill]

Any change to an issue will update the Updated date/time field. To find issues that have had no change whatsoever in the past X days you could use:

updated <= startOfDay(-Xd)

There is not a native function to let you filter based on the date of the last Comment. You might be able to find that capability in a third party app that extends JQL capabilities.

https://marketplace.atlassian.com/search?hosting=cloud&product=jira&query=jql

A work around would be to set up a custom date/time field to record the timestamp each time a new comment is added. You could use an Automation Rule to update that field, and write you JQL like the one above to filter on that field.

Note, though, that on the Free subscription you get only 100 automation rule executions per month. Running a rule every time a comment is added could easily push you to that limit and halt the running of any more rules until the month has ended.

https://www.atlassian.com/blog/announcements/cloud-automation-packaging-update

[Hannes Obweger - JXL for Jira]

Hi @Jake S

I trust that the CHANGED operator in JQL resolved your issue. Just for future reference, for more advanced use cases, it may be helpful to use one of the many Marketplace apps that operate in this space; these apps may provide more configuration and filtering options.

E.g., my team and I work on an app that I think you may like, JXL for Jira.

JXL is a full-fledged spreadsheet/table view for your issues that allows viewing, inline-editing, sorting, and filtering by all your issue fields, much like you’d do in e.g. Excel or Google Sheets. It also comes with a number of so-called history columns that aren’t natively available, including time since status changed, time in [status], time since last comment, 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 across JXL's advanced features, such as support for (configurable) issue hierarchies, issue grouping by any issue field(s), sum-ups, or conditional formatting.

As already mentioned, there are number of apps that can help with this. You may already know that you can trial any app for free for 1 month, and depending on the size of your size, it may be free forever. So if an app is an option for your, perhaps try a few and see which works best for you.

Any questions just let me know,

Best,

Hannes

[Gizem Gökçe _OBSS_]

Update on May 9th, 2024: Time in Status by OBSS was recently rebranded as Timepiece. It is still the same capable app with the same dedicated team behind it. The answer below mentions the app as "Time in Status" but you can find the app in the marketplace as "Timepiece (formerly Time in Status)". 

Hello @Jake S ,

Sometimes it can be difficult, confusing and time-consuming to find your way through JQL and create a solution on your own. For specific needs like yours, it is better to use out-of-the-box solutions. If you are open to using an application specifically tailored to your needs, Time in Status which is developed by my team at OBSS, is the tool I would highly recommend. It is available for both Jira Cloud, and Data Center. Our app is free for up to 10 users and I see that you are on a free plan. So you can use this app without any additional payment.

Time in Status mainly allows you to see how much time each issue spent on each status or each assignee. 

Status Duration report (please see the screenshot above) which shows how much time each issue spent on each status. You can combine the time for multiple statuses to get metrics like Issue Age, Cycle Time, Lead Time, Resolution Time etc. You can also apply filters to your reports from the Filters menu to see the open tickets with the same status for more than x days.

The app calculates its reports using already existing Jira issue histories so when you install the app, you don't need to add anything to your issue workflows and you can get reports on your past issues as well.

Time in Status reports can be accessed through its own reporting page, dashboard gadgets, and issue view screen tabs. All these options can provide both calculated data tables and charts. And the app has a REST API so you can get the reports from Jira UI or via REST. Also you can export the reports in to various formats easily. 

Visit Time in Status to explore how our JIRA add-on can revolutionize your metrics measurement process. If you wish, you can also schedule a live demo. We will provide a comprehensive overview of the application and address any inquiries you may have.

Hope it helps,

Gizem

[Amay Purohit_RVS]

Hi @Jake S 

For advanced reporting if you prefer to get this data easily using a third party app, you may want to have a look at the one we have developed.

Time in Status Reports

The time in status report shows the time spent by your issues in each status during the entire workflow and with few special filters in the app you can easily extract the issues which are in a particular status for more that n number of days.

The app has 20+ reports to track your bottleneck Issues & Processes.

More details here

Disclaimer : I work for the vendor who built this app

[Mehmet A _Bloompeak_]

Hi @Jake S

Unfortunately, it is not possible to extract Time in Status info with built-in JQL. If you prefer using a marketplace app, you can try Status Time Reports app developed by our team. It mainly provides reports and gadgets based on how much time passed in each status.

Here is the online demo link, you can see it in action and try without installing the app. For your case, you can have a look at Time in Status for Each Issue report.

For further details, you can have a look at Status Time Reports How to Videos.

• This app has a dynamic status grouping feature so that you can generate various valuable reports as time in status, time in assignee, status entry dates and status counts, cycle time and lead time, resolution time, average/sum reports by any field(e.g. average in progress time by project, average cycle time by issue creation month).

• You can search issues by Project, Issue Type, Status, Assignee, Issue Creation/Resolution Date(and any other Date field) and JQL Query.
• Status durations are calculated according to the working calendar you define. 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.
• You can set different duration formats.
• You can export reports in CSV file format and open them in MS Excel.
• You can also add this app as a gadget to your Jira dashboards and reach “Status Time” from Issue Detail page.
• You can enable/disable access to Status Time reports&gadgets and Issue Detail page per project, users, groups or project role.

If you are looking for a completely free solution, you can try the limited version Status Time Reports Free.

Hope it helps.

[Iryna Komarnitska_SaaSJet_]

Hi @Jake S ,

My colleague has created a useful collection of JQL queries for Time in Status reports. I hope you find it useful!