Search Exact Issues which are Staying in a Specific Status for 90 days?

[Digvijay Singh Gehlot]

Hi Community,

I want to search those exact issues which are landed to a specific status (Open) and staying are there for 90 days.

So that, I can send an email notifying to the Reporter, to move issue for further process.

I tried configuring Jira automation rule with:

Trigger: Scheduled

Condition: If Status equals - Open

And: Issue type - Story

Then: Send email - Reporter

In Scheduled component, I am using:

- Fixed rate of every 90 days

- Run a JQL: project = TEST AND issuetype = Story AND status = Open AND NOT status CHANGED AFTER -90d

Enabled "Only include that have changed since the last time this rule executed"

Enabled "Process all issues produced by this trigger in bulk"
However, while looking to my JQL, it is showing all the issue before 90 days along with the issue which actually 90 days old.

Need further guidance on how to make the correct JQL while searching for the exact issue which only 90 days old from the current date.
Any suggest on how to achieve this via Scriptrunner, JSM, Workflow Conditions/Post-functions?

Thanks

[Vishal Biyani]

@Digvijay Singh Gehlot 

Can you try with query

project = TEST and issuetype = Story AND status = Open AND status changed to Open before startOfDay(-90) AND NOT status changed after startOfDay(-90)

1. status = Open → Ensures the issue is currently in the "Open" status.
2. status changed to Open before startOfDay(-90) → Ensures the issue moved to "Open" at least 90 days ago
3. NOT status changed after startOfDay(-90) → Ensures no further status changes happened in the last 90 days.