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

John Corwith April 7, 2021

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

2 votes
Answer accepted
Payne
Rising Star
Rising Star
Rising Stars are recognized for providing high-quality answers to other users. Rising Stars receive a certificate of achievement and are on the path to becoming Community Leaders.
April 7, 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

John Corwith April 7, 2021

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
Community Leader
Community Leaders are connectors, ambassadors, and mentors. On the online community, they serve as thought leaders, product experts, and moderators.
April 7, 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

John Corwith April 7, 2021

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 April 7, 2021

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 April 7, 2021

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 April 7, 2021

I need something like:

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

but the above isn't the correct syntax

Bill Sheboy
Rising Star
Rising Star
Rising Stars are recognized for providing high-quality answers to other users. Rising Stars receive a certificate of achievement and are on the path to becoming Community Leaders.
April 7, 2021

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 April 7, 2021

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 April 7, 2021

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
Rising Star
Rising Star
Rising Stars are recognized for providing high-quality answers to other users. Rising Stars receive a certificate of achievement and are on the path to becoming Community Leaders.
April 7, 2021

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