Extract time since status change

[michael jones]

Hi

I would like to run some JQL to show how if an issue hasnt transitioned through a status after X. 

I have tried 

Key = X and status changed BEFORE 1w

This is not giving me consistant results. There is alot on here about using "updated" but this includes other thing other than status, I just need to know how long things haven't move in order to target those issues. 

Looking forward to hearing from you :)

[Andy Heinzer · Accepted solution]

The way JIRA is tracking this information leaves the possibility for your JQL query to have 3 possible states for issues:

1. Yes the issue matches this query and it is returned,
2. No they don't match and are not returned, or
3. NULL (meaning they have no value for status that has changed) which are also not returned here. 

This is because it's possible to have issues created in JIRA that have never had a status change since creation.  All issues need to have a status, but the way that JQL looks for this in the changed operator isn't smart enough to consider that never is before a specific date.

Since that is the case, your JQL query won't technically find those NULL issues since they do not have a status change since created.

But I think you can still find these, you just need to tweak your query a bit to be something like this:

(NOT status changed) OR status changed BEFORE "-7d"

This should show you all issues changed before 7 days ago, and all issues that have never had a change to their status.

[michael jones]

Hi Andy, 

Thank you for your response.

I am sorry just reading my question i didnt give you all of the information.

The JQL is returning information however it is also returning items that status' have changed within the last week as well as issues that haven't.... 

I am not sure if this is a bug or not.. 

E.g. i write the following JQL:-

filter = MAR and issuetype = bug and status not in (Resolved, open) and status changed AFTER "-2d"

This brings back 16 results. 

and then:-

filter = MAR and issuetype = bug and status not in (Resolved, open) and status changed BEFORE "-2d"

This brings back 120 results

The filter is just specifiying a section of a project. 

However issue"Y" is both result lists. This transitioned through its last status 1 day ago; therefore I would assume that this shouldnt be in one of the results list? 

[Andy Heinzer]

Ah, thanks for clarifying Michael.  The thing about that "status changed" is that Jira is not just keeping track of the most recent change to the status of issues.   The fact that the issue appears in both of your JQL queries is possible.  But that's because the issue could have had its status change both before 2 days ago, and again since the past 2 days.

You can probably confirm this if you look at the issue details view of the that issue "Y".  On that page if you click the History tab, you should be able to see at least one change to the status field on that issue before 2 days ago, and at least one change within the past 2 days.   In that regard Jira's search requests would still return that same issue in both queries.

So it depends on how you want to restrict the results, you could use the NOT operator to elimate results that might appear in one search or the other.   But if you don't want the results of the most recent changes to the status field also appearing the older change list, you will probably need to adjust that second query to look something like this:

filter = MAR and issuetype = bug and status not in (Resolved, open) and ((status changed BEFORE "-2d") OR (Not status changed)) AND NOT status changed after "-2d"

That should be able to provide you a list of all the issues that have not been changed in the last 2 days, including those that have been changed before that time, and those that have never had a change to status field.

Please let me know if this helps.

[michael jones]

thank you, 

That work great!