Find tickets that were in status In Progress for longer than 5 days

[markk1]

Hey guys, I am trying to get better Metrics on teams progress by identifying tickets that have stayed "In Progress" for longer than 5 days. I want to find all tickets that meet this criteria from the start of year, and not tickets that are currently in "In Progress" and have been so for 5 days.

I have the following query, but I think the problem lies with the WAS operator I'm using. I need to use was (or something similar) as I'm trying to find tickets that historically have been in "In Progress" for 5 days, not tickets that are currently in progress and have been for 5 days.

status was "In Progress" and NOT status CHANGED AFTER "-5d" and createdDate > startOfYear()

However as you can see in the below screenshot that this ticket is not picking up the correct criteria:

Also note that I cannot purchase the Time in Status plugin to achieve this functionality. However I can utilize Scriptrunner JQL functionality if needed.

[Olga Videc]

Hello @markk1  

Great you have ScriptRunner that is what you need.

Also, you need to have Jira 8

This is the feature you need

https://scriptrunner.adaptavist.com/latest/jira/script-fields/date-of-first-transition.html

This is how scripted fields are added 

https://scriptrunner.adaptavist.com/latest/jira/script-fields/built-in-script-fields.html

Basically, this are the steps

1. Go to Admin console (Cog top right corner)

2. Manage Apps

3: Find ScriptRunner

3.1 Script Fields

3.2 Create Script Field -> choose "Time of status change" this will let you create a custom field, name it as you like.

The field won't be visible in the issue but you can filter based on it.

Go to issue navigator  -> more -> find the filed(name of the field you previously created)

Find your's filed and it will let you choose between 

"Within  the last"

"More than"

"Between"

"In range"

for your case 

"Field name" <= -5d 

What PVS mentioned would work but only on assumption that nobody did nothing on the issue, when you edit, assign, log work or anything else on the issue it's considered updating

BR, Olga

[PVS]

Hi @markk1 

Using this JQL 

issuetype=In Progress And update < -5d

Status changed to "In Progress"before -5d And status ="In Progress"