search for reporter update over 5 days ago

[Joshua Lawrence]

I am looking to find a way to search for tickets that are still open where the requester's last update was over 5 days ago.  When I do "updated <= 5d" it will list if they or I updated less than 5 days ago, I want to list if their last update was within 5 days

[Jack Brickey · Accepted solution]

Hi @Joshua Lawrence , native JQL cannot get you there. You might consider an app addon or use a custom date field and automation to update the field each time the customer reporter comments/updates. Then you can use JQL to interrogate the date.

[Joshua Lawrence]

Thanks, I'll have to look into that

[Thiago Wenceslau -Appfire-]

Hi  @Joshua Lawrence 

I’m Thiago, a support engineer at Appfire.

If you are considering third-party apps, there’s a workaround using Power Scripts SIL Manager.


Using the script below, first, you will perform a basic JQL search, then check if the Creator matches the lastUpdater property, thus returning the correct criteria.

// This script finds issues where the Creator was the last to update 
// AND that update happened more than 5 days ago.

string[] issueList;
date fiveDaysAgo = currentDate() - "5d";

// 1. Get candidate issues (Open tickets updated more than 5 days ago)
// This narrows the pool for better performance
string[] candidates = selectIssues("project = TST AND statusCategory != Done AND updated < -5d");

for(string k in candidates) {
// 2. Fetch the issue's Creator and Last Updater
string creatorUser = %k%.issueCreator;

// lastIssueChanges returns an array of changes; the first element is usually the most recent
JFieldChange[] changes = lastIssueChanges(k);
string lastUpdater = changes[0].user;

// 3. Compare: Was the creator the last person to touch it?
if(lastUpdater == creatorUser) {
issueList += k;
}
}

return issueList;

Additionally, you could integrate this script to a gadget or schedule a daily email containing the list of issues.

Please remember to change the JQL filter on line 9 to match your project settings.

Please contact our support if you have any other questions about this.

Best regards, Appfire support team.

[Marc -Devoteam-]

Hi @Joshua Lawrence 

Use updated >= -5d

[Joshua Lawrence]

Thank you but I am looking for a result when the reporter's last update is over 5 days old.  if I use "updated<=5d" returns if the user or I updated it.  I am looking for something like "reporter_last_update > 5d" and that will return a ticket if the user hasn't updated within the past  5 days, so I can find old/dead tickets

[Marc -Devoteam-]

Hi @Joshua Lawrence 

Why not set tickets waiting on a reporter in a specific state in the workflow or add a component or label

Then based on this information and JQL updated >= -5d, you should have the issues you want.