JQL "...AND NOT status CHANGED BEFORE -xd" Not Showing Expected Results

[Bob Bob]

I am attempting to return results for issues whose status have not changed for greater than 90 days. But each one I try includes issues within 90 days, instead of BEFORE 90 days. 

Searching around I keep finding advice to use JQL to the effect of: 

...AND NOT status CHANGED BEFORE -90d...

But the actual results are returning issues such as those created today and yesterday. They have not even existed for 90 days, why are they included in the results?

I've tried different combinations with CHANGED BEFORE and CHANGED AFTER, but it doesn't make sense. 

How do I use JQL to return results for issues that have not had a status changed for more than 90 days? What is "CHANGED BEFORE" and "CHANGED AFTER" really targeting when combined with -xd or xd?

My goal is to create an automation that can send notifications for issues that have not been updated (have not had a status change) for more than 90 days. But I can't seem to even target the correct issues to begin.

Any help is most appreciated.

[Habib__Plugio__]

Hi @Bob Bob 

status CHANGED BEFORE -90d means "the issue has at least one status change older than 90 days". Negating it gives you issues with no change events older than 90 days. Brand-new issues match that, because all of their history is recent. That's why they show up.

What you want is "no status change in the last 90 days, and the issue is old enough to qualify":

NOT status CHANGED AFTER -90d AND created <= -90d

• CHANGED AFTER -90d = at least one status change in the last 90 days. NOT of that = zero status changes in the last 90 days. This also covers issues that never changed status at all.
• created <= -90d filters out issues younger than 90 days.
• -90d is just a relative timestamp: "now minus 90 days". BEFORE/AFTER compare the change events against that point in time.

You'll probably also want AND statusCategory != Done to skip completed issues.

[Bob Bob]

Thank you for the quick response @Habib__Plugio__ !

The concept is still a bit difficult to wrap my head around. 
But I think I understand, at least, that we need to exclude those created in the same time frame cause technically an issue created today (and hasn't been updated) has not had a status change within the past 90 days.

I guess I was trying to use the JQL to essentially black out the last 90 days from consideration, only consider issues before 90 days ago. But that's not what BEFORE -90d will do? 

Why would it be AFTER and not BEFORE? 

If -90d is "now minus 90 days" (so May 27th as of writing), is "AFTER -90d" not "after May 27th" (as in May 27 through today)?

If I want to check for issues that have not had a status change 90 or more days ago, would "CHANGED BEFORE -90d" not target that as "every day on and prior to May 27th"?

I did try your suggestion, and it did exclude items created within 90 days as expected, but I want to make sure I am understanding the concept and what time span is actually being targeted here. 

Thank you for your help, though I am still a bit unclear, your response is a huge help!

[Habib__Plugio__]

You've got the windows right: AFTER -90d is May 27 through today, BEFORE -90d is everything up to May 27.

The missing piece is what CHANGED checks. It doesn't look at when the last change was — it asks "does at least one status change event exist in this window?"

So "status CHANGED BEFORE -90d" = "has at least one change older than May 27". With NOT in front, that becomes "has no change older than May 27" — and a brand-new issue satisfies that perfectly, since its whole history is recent. That's exactly why today's issues showed up.

"Hasn't changed in 90+ days" really means "nothing happened recently", so the window to test is the recent one: NOT status CHANGED AFTER -90d = zero status changes between May 27 and today. The created clause just excludes issues too new to have 90 days of history.

Hope this helps.

[Bob Bob]

AAaaahhhh, I see! Okay, I think it clicked. Thank you very much for taking the time to explain it in straightforward terms.