JIRA JQL - Find issues with more than X days in Status

Question

I want to show all issues where it has been in a current status for more than X days - is this possible?

We have this workflow: Registered => Analyze => Planned ... etc. The ticket can be in Registered for 3 weeks and it can be 3 weeks in Analyze without any problems.

Currently I am using this JQL to show tickets that have been more than 3 weeks in Analyze:

project = MyProject AND status = Analyze AND created <= -6w

This is wrong due to so many reasons and it does not look at the time in the current transition state - nor does it take in to account that it can be pushed back from Planned to Analyze and then allow a new 3 weeks analyze period.

Is the above possible to filter in JIRA? I don't have the possibility to use the JIRA REST interface - only the builtin JQL.

I am running with JIRA version 6.4.5.

Dietary

Answer 1

You should be able to get there using the JQL CHANGED operator. Its documentation is available here.

Your query would look something like this:

project = MyProject AND status = Analyze AND status CHANGED BEFORE -3w

Betulaceous

This is not valid only and only if you have the status =Analyze is not repetitive in your workflow. In other words, if you got any status change from "Analyze" to "Registered" and then back to "Analyze" during this period then the query above will give a wrong result – Fullrigged

@Fullrigged That can be overcome with JQL like this: "AND status WAS NOT IN Analyze" or "AND status WAS IN Registered" – Fenland

Answer 2

 project = MyProject AND status = Analyze and not status changed during (-xd,now()) 

Fullrigged

Answer 3

If you want to know for what day range the issue was lying in a status and when status are consecutive, for example a UX review will happen before QA starts working on it and I want to know the issues which are lying in UX review for more than10 days then my JQL can be

project = *your project* AND status changed to "Ux review" before startOfDay(-10)  AND status changed from "UX Review" to "Ready to test" after startOfDay()  

Thetos

Answer 4

With Script Runner plugin I'd create a new scripted field that would just return the number of days since the last status change, with a Number field template, and Number Range Searcher. The

def items = com.atlassian.jira.component.ComponentAccessor.changeHistoryManager.getAllChangeItems(issue).findAll{it.field=="status"}

will return ChangeHistoryItems for Status field. Take the last one and use its getCreated() to find Timestamp. If the list is empty, it means that the issue is in the first step of the workflow, use its issue.getCreated(). Test. Re-index. Search. Use.

Mumps

Answer 5

The currently accepted answer has some limitations as pointed out by user1017344, so I'm attempting a better answer:

show all issues where it has been in a current status for more than X days

I'll use 21 days to represent 3 weeks as in the question:

project = MyProject and status changed before -21d and not status changed after -21d

it can be 3 weeks in Analyze without any problems

Of course, you can narrow it down to specific status

project = MyProject and status changed before -21d and not status changed after -21d and status = "Analyze"

Pummel