Find issues whose status has not change in 5days ago and longer

[SJ]

I need help understanding how the "NOT status changed" using the AFTER modifier works.

On any given day I want to know all open issues that have not had a status change since 4 days ago and before. 

For example: Friday I want to know all open issues that have not had a status change since Monday and before. I don't need issues that the status has not changed for Tuesday - Friday I assume they are being worked.  Anything before Monday and the assignee needs a nudge to report on why the delay to close..

Here's my JQL: project = HELP AND issuetype = "Help Services" AND status not in (Done, Cancelled) AND NOT status changed after -4d

The results I'm getting include issues from Tuesday - Friday

I've tried variations using AND NOT status changed after startOfDay(-4d, now())

AND NOT status changed after endOfDay(-4d, now())  with the same result

I need help getting the JQL to exclude the most recent 4 days

[Trudy Claspill · Accepted solution]

Hello @SJ 

Can you provide a sample scenario with sample issues and dates creation and dates of status changes to illustrate which issues you would want in your results and which issue you would not want in your results?

Example.

Issue-1 created Sunday

Issue-1 undergoes no statuses changes between Sunday and Friday.

Issue-2 created on Sunday.

Issue-2 status changes on Monday

Issue-2 status does not change again before query runs on Friday

Issue-3 created on Sunday.

Issue-3 status changes on Tuesday

Issue-3 status does not change again before query runs on Friday

Issue-4 created on Tuesday.

Issue-4 status changes on Wednesday

Issue-5 created 3 weeks ago.

Issue-5 status changed 2 weeks ago.

Issue-5 status does not change again before query runs on Friday.

Which of the above issues would you expect to get in your results?

[SJ]

@Trudy Claspill Thank you for taking a look at this.

Issues 1,2 and 5 should be in the result.

The challenging part is verifying each issues status change date. Right now I'm trolling through the list and looking at the history of each issue individually.

[Trudy Claspill]

So, based on your response I understand that you want to

1. - include issues created more than 4 days ago
2. - exclude issues created in the past 4 days (issue 4)
3. - exclude issues that have had a status change in the past 4 days, even if they had no status change before that (issue 3)
4. - include issues that had status changes more than 4 days ago (issue 5, issue 2)
5. - include issues that have had no status changes ever (issue 1)

To satisfy 1 and 2, use this criteria

created < -4d

To satisfy 3 use this criteria

not status changed after -4d

So your entire filter would be

project = HELP AND issuetype = "Help Services" AND status not in (Done, Cancelled) AND NOT status changed after -4d and created < -4d

If that doesn't satisfy your need please provide an example of an issue that got included in your results that you wanted to exclude, including the details of when it was created and its status change history.

[Luis Escobar]

@Trudy Claspill I have a similar situation, I need to get all the tickets that didn't have status change after 5 days of created, Could you help me to create a query for that? Thank you in advance

[Trudy Claspill]

@Luis Escobar 

Welcome to the Atlassian community!

If your problem is "similar" but not identical to the original post, please start a new Question in the community and provide all the details of your scenario. It would be very helpful if you provided example data also indicating which issues should be included and which should not.

[SJ]

@Trudy Claspill Thank you for the last filter.  That looks like it will work.

I've had to create a Time in Status calculated field for this project so I can verify the Status changed value without having to look at the history of every single issue for a status change date/time.

The documentation for the BEFORE and AFTER clauses is not very clear to me.  Then again I'm probably just unfamiliar or getting old.

[SJ]

Has anyone seed this before?
I ran this jql on 1/19/2023  and there are issues included where the created date should exclude the issue from the results as we know the status cannot change before the created date.

[Trudy Claspill]

Hello @SJ 

Your filter is asking for issues that have not had a status change in the past 7 days and has no criteria related to Created Date. Which issues do you think should be excluded and why?

[SJ]

@Trudy Claspill Thank you again.

I went with a variation of a previous suggestion of yours.

project = TCO AND issuetype = "Help Desk" AND status not in (Done, Cancelled) AND NOT status changed AFTER -7d AND created < startOfDay(-7d)

Is there an easy way to verify the status changed date without looking at each issues history?

[Trudy Claspill]

Are you trying to verify that the results you got were correct? What problem are you trying to solve with that last question?

[SJ]

Yes I am trying to verify the results are correct and having the date easily accessible or viewable will be useful to verify a scripted field we are working on as well.

[Trudy Claspill]

I'm not aware of a field that can be referenced natively for the date/time of the last status change for an issue. You could create a custom field and use an Automation rule to set it for each status transition.

There might be a third party app that does that inherently. I've not looked for that particular feature.

[Tarun Kumar]

Hi @Luis Escobar

For your scenario you can use the below JQL:

Project = ABC AND issuetype = XYZ AND status = "To Do"(The Initial status in the workflow) AND NOT status changed BEFORE -5d AND createdDate <= -5d