JQL Query tickets that were left unopened for more than 30 days?

[JJ Legelis]

Hello,

I would like to query my project for incidents that took longer than "x days" to close. Let's say longer than 30 days.

I often move incidents back and forth between statuses such as "blocked", "escalated", or "in progress", before they finally end up resolved. Therefor, the CHANGED AFTER function is not super useful since any status changes occuring while the incident is still open would reset that counter.

I would like to do something such as, 

resolutiondate > "(openDate+30d)"

JQL doesn't seem to like logic within the date field comparison argument. I also don't seem to be able to create a variable that stores the date comparison value.

Any suggestions appreciated :)

[Walter Buggenhout · Accepted solution]

Hi @JJ Legelis and welcome to the Community!

Since you're talking about incidents, it would make a lot of sense if you managed those with Jira Service Management (JSM). JSM comes with SLA's, that have a timer running and that can quite easily be queried with specific functions related to those SLA's (breached(), everbreached(), withinCalendarHours(), ...).

If you are not using JSM, JQL in Jira does indeed not support comparison between 2 dates on an issue. There are marketplace apps out there that do support querying this way, like e.g Scriptrunner enhanced search or Appfire's JQL Extensions (which both have a dateCompare function).

Hope this helps!

[Valeriia_Havrylenko_SaaSJet]

Hi @JJ Legelis 
Welcome to the Community!

Thank you for your question! Querying for incidents that took longer than 30 days to close, considering the back-and-forth movement between statuses, can indeed be a challenge with standard JQL due to its limitations in date logic and unfortunately simple JQL for resolution time doesn't exist. 

I suggest you to try Time Metrics Tracker add-on, with which you can easily solve this problem. Here's how the add-on can help:

Measure Total Time Between Statuses

The add-on allows you to calculate the total time an issue spent in any statuses (e.g., "Open", "Blocked", "Escalated", "In Progress") and combines them to give you a precise duration until the issue was resolved. This is perfect for tracking total resolution time without being affected by intermediate status transitions.

Warning Time and Notifications

To avoid incidents staying unresolved for longer than desired:

• You can configure Warning and Critical time notifications in the add-on.
• Notifications can be sent to the assigned user or team, ensuring that no issue slips through the cracks.
• The warnings and alerts help your team be proactive, reducing delays and improving resolution times overall.

You can also book a live session or contact us at Support - we'll help you with add-on. 

Add-ons developed by my team.

Let me know if you'd like a walkthrough or more details! 😊

[Mehmet A _Bloompeak_]

Hi @JJ Legelis ,

In Jira, there are 3 status categories: New, In Progress and Done. Each status is mapped to one of these 3 status categories. If the statuses "Blocked", "In Progress" and "Escalated" are mapped to the "In Progress" category in your workflow, then the JQL below might work.

statusCategoryChangedDate <= -30d and statusCategory!=Done

On the other hand, 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.

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

If you have any questions, feel free to schedule a call with us.

Hope it helps.

[Madhu_RVS]

Hi @JJ Legelis 

Welcome to the community !!

As suggested by everyone, a mktplace app can help here . if you would be interested in the same, please try out an add-on we have developed for the same use case.

Time in Status Reports 

With the app you can easily filter issues for the time in status for a specific duration.

More details here

Disclaimer : I work for the vendor who built this app

[Gizem Gökçe _OBSS_]

Hello @JJ Legelis ,

Welcome to the community!

Jira's native JQL has limitations when it comes to calculating dynamic durations, such as determining if an incident took more than "x days" to close. However, if you're open to using a third-party app, I highly recommend Timepiece - Time in Status for Jira, developed by my team at OBSS.

With Timepiece, you can:

• Use the Duration Between Statuses report to calculate the time from "Created" to "Resolved," regardless of intermediate status changes.

• Easily filter and identify issues that took longer than a specified duration (e.g., 30 days) to resolve.

• Display this data in Jira dashboards or export it into various formats such as excel, csv for further analysis.

This approach provides the precision and flexibility you're looking for, without the constraints of native JQL. If you'd like to explore this solution further, feel free to check out Timepiece on the Atlassian Marketplace or schedule a demo with us for a walkthrough of the app's features.

Hope it helps,

Gizem

[Dan -minware-]

Disclaimer - marketplace partner

Hey @JJ Legelis if you end up going the marketplace route this is something that we support in minware. Below is a screenshot of our Bug SLA dashboard. Not shown are "longest open tickets" - super easy to throw a filter on >30 days and provide that list to you.

We offer a free trial if you want to check it out. Mention my response and I'm happy to extend that so you have plenty of time to explore.