Create
cancel
Showing results for 
Search instead for 
Did you mean: 
Sign up Log in

Next challenges

Recent achievements

  • Global
  • Personal

Recognition

  • Give kudos
  • Received
  • Given

Leaderboard

  • Global

Trophy case

Kudos (beta program)

Kudos logo

You've been invited into the Kudos (beta program) private group. Chat with others in the program, or give feedback to Atlassian.

View group

It's not the same without you

Join the community to find out what other Atlassian users are discussing, debating and creating.

Atlassian Community Hero Image Collage

Need help with a query to determine issues assigned to me for more than ~100 days

I don't have access to create/deploy a SLA.

I have looked at the gadgets and nothing seems to suit. 

Issues can be created days or months before being assigned to me so I can not use the creation date.

Issues aren't set to a resolution until 2 states beyond my responsibility so I can not use anything related to the resolution date.

I need a query that can pull issues that were either assigned more than ~100 days ago or that changed out of the initial state ~100 days ago.

Basically I have 6 states (with 2 additional states outside my scope.) Newly created issues are in state 1. Either the assigner or assignee moves it to state 2 within a day or two of the issue being assigned. I need to determine when issues have spent more than 100 days in states 2-6 (total) or, from either when the state changed from 1 to 2, exited 1 or entered 2.

I have looked at changed to, before, during,... now() but can't wrap my head around formulating a query and to top it off, access to my system is down right now, I can't experiment so I though I would ask the experts.

2 answers

1 accepted

1 vote
Answer accepted
Payne Community Leader Apr 07, 2021

Your summary of "Need help with a query to determine issues assigned to me for more than ~100 days" is a bit different than what you further describe, but if that truly will provide the issues you seek, try this:

assignee = currentUser() and assignee changed to currentUser() before -100d

You are correct, I went off the rails with my example. I didn't know about using assignee as a state change and most of the examples I found were related to status. When I did come across using assignee, I came up with the following:

assignee in (user1, user2,...) AND assignee changed BEFORE -100d

which is also slightly different than the question I asked but is what I need for our team.

Your solution truly answered what I asked.

I may have to find a solution for the state changes, since we sometimes change assignees and what we are truly trying to track is issues my team is responsible fore that have gone over 100 days. I'm not sure why Bill S's solution above didn't seem to work. I will go back and test it a little more

0 votes
Alexis Robert Community Leader Apr 07, 2021

Hi @John Corwith ,

 

I'm not sure if I understand correctly your need, but for issues that were not updated after 100 days you can use a JQL like this : 

updated <= -100d 

And then if needed, add more criteria like "status = state 1" or "assignee = john".

If I missed something, please explain a bit more about your use case ! 

 

Let me know if this helps, 

 

--Alexis

Wouldn't what you propose require an issue go untouched for 100 days? This is not what we need.

I 'm not sure what I can add to my description to make it more clear. These issues are being worked, a number of state changes, comments, attachments... but I need to know when we've spent more than 100 days working the issue. Basically I need a SLA for the overall states in my scope, but I'm not allowed to create/deploy a SLA.

Would this work?

AND not status changed to "7" before "-100d"

I'm not sure what this keys off of, status changes or creation. Status changes I hope.

Basically I think I want issues that haven't seen a status change in 100 days.

No, the above isn't right. I need issues that haven't had a status change to status 7 in 100 days from the first status change.

I need something like:

AND status in (2,3,4,5,6) after 100d

but the above isn't the correct syntax

Hi @John Corwith 

Have you tried something like this:

project = myProject
AND status IN (2,3,4,5,6)
AND NOT status CHANGED AFTER -100d

Best regards,

Bill

Either this didn't work or I can't figure out how to test it correctly. Used assignee instead, but I may have to come back to this if we change assignees and still want to know when issues go over 100 days from when assigned.

status in (2,3,4,5,6) AND status changed To 2 BEFORE -100d

status in (2,3,4,5,6) AND status changed From 1 BEFORE -100d

both seem to work and the latter keeps from resetting the timer if for some reason the issue goes back to 2 (can't go back to 1.)

Hi John,

I am glad those worked, and that Payne's answer helped you with the assignee part of your question.

The thing to note with the CHANGED operator is that it does what you ask.  :^)  So, it can help to make a quick state diagram (and/or review your workflow transitions) to confirm that the query matches the question you want to answer.

Best regards,

Bill

Suggest an answer

Log in or Sign up to answer
TAGS

Community Events

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

Find an event

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

Unfortunately there are no Community Events near you at the moment.

Host an event

You're one step closer to meeting fellow Atlassian users at your local event. Learn more about Community Events

Events near you