query to find all issues in a status for > than x days

[Mark Stocker]

I need to setup a quick filter to show items that are in a certain status for > than 5 days. Cant figure it out so any help appreciated. I dont want to see anything not updated in the last 5 days it's that fact that it's been in a certain status for the last 5 days that I'm interested in.

[Vijay Khacharia]

Hi Mark,

I have an option.

status changed BEFORE -5d

It worked for me.

Vijay

[PhillipS]

Wouldn't the above filter show you everything that DID change status sometime before 5 days ago?  

The filter I'm using that I believe is working for this is:

!(status changed after -5d)

"Show me everything that has not changed status after 5 days ago" i.e. status has not been changed on the issue in the past 5 days.

Perhaps there is something I''m missing though...

[Arij]

The problem with this formula is that if the issue has been created with this status, it has not been touched since created, and no activity for more than X days; the issue has been in this status for more than X days and the status never CHANGED ... 

Has anyone found another way to check if an issue has been pending in the same status for more than X days, even if this is the very first status it was created with ?

Thanks !

[PhillipS]

Arij,

    My solution is working for me, even for the initial status.  Our Initial status is "Not Yet Started" and we are currently 6 days into a sprint (past the -5d in the filter).  There are 2 items in the sprint that have not been touched since they were created.  I did a quick test to see if they would show up:

filter = <team_board_filter_name> and sprint in openSprints() and sprint not in futureSprints() and status = "Not Yet Started" and !(status changed after -5d)

the in openSprints and not in futureSprints is the silly way in my filter I'm saying "only show me the active sprint" since there is no activeSprints() jql function.

This, as expected, showed me the 2 items my team has not started yet (on the 6th day of the sprint) as they've been in the "not yet started" status for longer than 5 days, are in an active sprint, and in my team's board (the initial filter).

[David Leal]

I was trying to follow your solution, but I don't get what do you mean by: <team_board_filter_name>. I am trying to put it into a JQL. I was assuming you mean the board ID for the active sprint, for example the value: 317 from the URL for the active board: rapidView=317, but it does not work. Thanks. David

[PhillipS]

Hi @David Leal ,

   I can report this filter is still working for me today, event though I implemented it almost a year ago. The only problem is that is also counts weekends, which is not ideal.

    By <team_board_filter_name>, I mean for you to replace this with you board's filter name (in quotes if there are spaces in the name).  Each board pulls in issues from jira based off of a saved filter, which has a name.  If you are an administrator of a particular board, you can find this name by going to the board, and then hitting the Board->Configure option at the top right of the screen.  In the "General" tab, which is on the left under "CONFIGURATION", you'll see a field "Saved Filter" field.  That's what you are looking for.  Let's say the saved filter was named:

Filter for Team A Board

The raw JQL would be:
filter = "Filter for Team A Board" and sprint in openSprints() and sprint not in futureSprints() and status = "Not Yet Started" and !(status changed after -5d)

I've actually found the clause "sprint not in futureSprints()" is not needed, because "sprint in openSprint()" only refers to ACTIVE sprints (not all active and future sprints).

As a quick tip, I actually use this filter in my card colours option in the board->configure area (another tab under "CONFIGURATION" like the general one we just explored above):

I changed "Colours based on" to be "Queries"

Color: Red

JQL: !(status changed after -5d) and status not in ("Not Yet Started", Finished, Canceled)

Since the board already uses the board filter, I don't need to include it again in this query as this is a sub-filter of that main board filter on tickets already in the board.  This allows me to see in a standup, while everyone is looking at the board, which tickets have been stuck in a non-todo or non-finished status for over 5 days (the statuses above are our todo and finished statuses).  It will turn the left side of the card red if it's been stuck in some sort of "in-progress" status for over 5 days. 

Hope this helps, let me know if you have any other questions.

[David Leal]

@PhillipS Card color is really a good idea!, I use it for the priority, but the priority is represented used some arrows icons too. My only comment is that in the way you describe your query, the order is relevant in order to have the information in different colors with the meaning you have.

For example if you want three colors for: more than 20d, more than 10, more than 5d. You need to put the queries in such order and not the other way around, so starting from red to the green (Jira by default puts the colors in the opposite way, so the way I did was to copy the RGB color code and change the order).

In order to achieve it, I have the following queries in the Card colors section of Board Settings:

!(status changed after -30d) AND status not in ("Build Complete", "DONE")
!(status changed after -20d) AND status not in ("Build Complete", "DONE")
!(status changed after -10d) AND status not in ("Build Complete", "DONE")

but it has to be in such order the query with higher duration at the top of the list and the query with the lower duration at the bottom. In this example the "Build Complete" is a final state for example for story and "Done" for Tasks.

Thanks, It is really a good tip

[David Leal]

@PhillipS by the way the query needs to be updated in order to consider the scenario were the issue was created and not modified the status (TO DO), since it was created. This was already mentioned by @Arij in a previous post. I am getting always two issues with such characteristics (initial status with no changed since then) regardless of the duration parameter. I was trying to add the following condition:

!(status changed after -Xd) AND status NOT IN ("Build Complete", "DONE") 
    AND created <= -Xd

 where X is the duration in days you want to consider that will eliminate such issues, but it should not be like that in all cases, it is too restrictive, because you can have an  issue created before Jesus Christ, that just changed the status in the last 20 days. I guess it is more related with the way 'status changed after' clause works in such cases and a possible bug, you can check it here: JST-469820.

On summary the query is considering for issues that the initial status has not been changed that the issue exist since the beginning of the interval (-Xd), which is not true.

Any thoughts?

[David Leal]

Update: Based on the support received from JIRA in the ticket:  JST-469820.

Recap: We had (thanks to @PhillipS ) a query like the following one to assign colors to the board cards:

NOT status changed after -Xd AND status NOT in (DONE)

where X represents the number of days, therefore the interval: [-Xd, now()] and DONE can be replaced with all final status of your board.

but as @Arij  and myself identified that as part of the result we have also untouched issues (i.e. issue created and remained on the initial status since then) regardless of the X value (so they were included even the issue this not exist during the entire period)

Based on Jira's feedback it was proposed the following version:

NOT status changed after -Xd AND status NOT in (DONE) 
    AND NOT created > -Xd

the added clause looks like redundant, but I tested it for my project and it seems to provide the expected result.

[PhillipS]

Thanks @[deleted] .  Yes, in my original filter I want to omit my "todo" status completely from the equation (never show me a card color there).  We use the status "Not Yet Started", which you can see i omit from my query.  If you want to include it, additional logic you've added in your query seems like a suitable workaround.

[codingouch]

Hi guys,

I'm wondering if I can extract the time in a certain status. For example, I'd like to filter the bugs that took more than 20 days to change from "In Progress" to " Resolved".

However, the current way I saw is all about filter the status that's still not changed. Like "not status changed after -20d". The problem is that I want to filter the bugs that current status is changed to "Resolved".

I don't know if I'm clear enough to make you guys understand. Is there any JQL query for this?

Thanks!

[PhillipS]

@codingouch Jira filters are not the best for asking the type of questions you are asking, but there is a great plugin for this called "time in status":
https://marketplace.atlassian.com/apps/1211756/time-in-status?hosting=cloud&tab=overview

[Margaret Valenti]

Hi- i have a kanban board and I want to run a dashboard or gadget that shows the amount of time a ticket is staying in each status and also what tickets have changed status from one day to the next? i am a little bit challenged with queries etc- any help would be appreciated!

[Phillip Saindon]

Hi Margaret,

    As far as I know, unfortueately, there is no easy out of the box way to have a dashboard show how long a bunch of tickets were in each status.  Although you can look at each ticket, and down where the ticket usually shows comments you'll see tabs.  Click the "All" tab which will show you the entire history of that ticket, at the very very bottom you'll see all the transitions of status made, and the time it was in the original status for for before each transition.

    This can be horrible if you are looking to get this information across multiple tickets.  Luckily if you need to investigate multiple tickets, and are looking for even more functionality for time in statuses (and have a little bit of money to spend) take a look at this fairly cheap plugin that we use called "Time in Status"

https://marketplace.atlassian.com/apps/1211756/time-in-status?hosting=server&tab=overview

At the very basic level it allows you to see multiple tickets from a filter and customize which status you want to see the time for.   You can even import this to google sheets/excel via exporting a CSV in the plugin to be able to sum status times across tickets (for instance, how much time were tickets in some sort of "waiting" status for this project or any JQL query you want).

As far as for your second question not all fields are supported with the "was in" and "changed" clause in JQL, but status is supported.  Similar to the query we discuss above you are looking for the opposite; above we are looking for things that are "stuck" in a status for longer than we'd like, but you are asking for thing that changed status, let's say sometime in the past day.  

For that try:

status changed after -1d

[Pratik Rawlekar]

!(status changed AFTER -30d) does not make sense to me as Draft is my initial state.. If I write query like !(status changed AFTER -30d) AND status IN (Draft) it is giving me all tickets of status = Draft present in jira

Any changes required?

[PhillipS]

Hello @Pratik Rawlekar I ran a quick test in my system with the initial status we use.  when I write these 2 queries:
!(status changed AFTER -30d) and status = <initial_status_name>
and
status = <initial_status_name>

My counts are different as i would expect, as there are issues in the second query that have not been in the initial status for more than 30 days.

Can you confirm that you actually have tickets that have been sitting in the initial status (Draft) for you for more than 30 days?  If there aren't any, then I would expect the counts to be the same.

[Deleted user]

Hi all - I am looking for a query where I would like to list 

1) the number of P1's Fixed by a team PLUS (+) 2) the number of days took for an assignee to fix the P1. 

I am actually looking for a filter query then I can use the same query in the two-dimensional gadget to get the counts? 

[David Leal]

@[deleted] I would suggest you to post a new question. Regards. David

[Phillip Saindon]

@[deleted] you'll need to give a little more information on what fields you define P1s and how you define a team.