Extract time since status change

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 :)

1 answer

1 accepted

0 votes

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.

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? 

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.

thank you, 

That work great! 

Suggest an answer

Log in or Join to answer
Community showcase
Sarah Schuster
Posted Jan 29, 2018 in Jira

What are common themes you've seen across successful & failed Jira Software implementations?

Hey everyone! My name is Sarah Schuster, and I'm a Customer Success Manager in Atlassian specializing in Jira Software Cloud. Over the next few weeks I will be posting discussion topics (8 total) to ...

2,802 views 11 18
Join discussion

Atlassian User Groups

Connect with like-minded Atlassian users at free events near you!

Find a group

Connect with like-minded Atlassian users at free events near you!

Find my local user group

Unfortunately there are no AUG chapters near you at the moment.

Start an AUG

You're one step closer to meeting fellow Atlassian users at your local meet up. Learn more about AUGs

Groups near you
Atlassian Team Tour

Join us on the Team Tour

We're bringing product updates and pro tips on teamwork to ten cities around the world.

Save your spot