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

[John Corwith]

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.

[Payne · Accepted solution]

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

[John Corwith]

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

[Alexis Robert]

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

[John Corwith]

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.

[John Corwith]

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.

[John Corwith]

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.

[John Corwith]

I need something like:

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

but the above isn't the correct syntax

[Bill Sheboy]

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

[John Corwith]

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.

[John Corwith]

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

[Bill Sheboy]

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