JQL filter advanced search

[Joseph Ssebagala]

I am trying to filter for and either tag or resolve tickets where the reporter/s have not responded or commented on the ticket/s for two weeks or more. 

assignee in (1999, 2000, 2001, 2002 2003) AND 
project = APPLES AND 
issuetype in standardIssueTypes() AND 
status in ("In Progress", "Waiting for customer", "Work in progres") order by created DESC 

Possible missing piece: 

reporter or assignee lastComment >= 14days 

[Heather Ronnebeck · Accepted solution]

Welcome to the community. You can give this JQL a try:

(assignee in (1999, 2000, 2001, 2002 2003) AND 
project = APPLES AND 
issuetype in standardIssueTypes() AND 
status in ("In Progress", "Waiting for customer", "Work in progress")
AND
(lastComment >= 14d)
order by created DESC

[Joseph Ssebagala]

Hi @Heather Ronnebeck,

Thank you for the quick response. I've tried your suggestion above but I'm running into an issue: 

Field 'lastComment' does not exist or you do not have permission to view it.

[Heather Ronnebeck]

Apologies. It is actually. 

lastCommentedDate >= -14d

So if you substitute out what I had there in the beginning with that line above it should work inside the brackets.  

[Joseph Ssebagala]

Unfortunately, I'm still getting the same error message. 

[Heather Ronnebeck]

(assignee in (1999, 2000, 2001, 2002 2003) AND 
project = APPLES AND 
issuetype in standardIssueTypes() AND 
status in ("In Progress", "Waiting for customer", "Work in progress")
AND
(lastCommentedDate >= -14d)
order by created DESC

So this is returning the error of not having that field existing... Hmm. 

Let's try this. 

(assignee in (1999, 2000, 2001, 2002, 2003) AND 
project = APPLES AND 
issuetype in standardIssueTypes() AND 
status in ("In Progress", "Waiting for customer", "Work in progress"))
AND
(lastCommentedDate <= -14d)
order by created DESC

I also saw a few other JQL errors in the one I originally posted. 

Do you have any plugins installed in your instance?

[Bill Sheboy]

Hi @Heather Ronnebeck 

I recall that lastCommentedDate comes from the ScriptRunner addon and is not a built-in thing for JQL.

Kind regards,
Bill

[Heather Ronnebeck]

Thank you @Bill Sheboy

I do sometimes forget that small detail sometimes. 

[Joseph Ssebagala]

Yeah, I did look at ScriptRunner, but we're not confident about adding plugins to our instance. We've run into issues in the past after installing a plugin. 

Nonetheless, thank you, @Heather Ronnebeck and @Bill Sheboy, for the assistance.

[Heather Ronnebeck]

Sorry for the delay but I do have one more idea.

It isn't the nicest but it is one I've used before I had Scriptrunner installed. 

Basically what you do is pull up a list of tickets updated older than 14 days then sift through them in "detailed view" checking to see when the last comment was made. It's a bit longer and more hassle but it works. 

The JQL for this would be:

(assignee in (1999, 2000, 2001, 2002, 2003) AND 
project = APPLES AND 
issuetype in standardIssueTypes() AND 
status in ("In Progress", "Waiting for customer", "Work in progress"))
AND
(updatedDate <= -14d)
order by created DESC

I know it isn't the nicest but I do recall having to do this myself a few million times during the dark ages of not having plugins like Scriptrunner installed in my environment. I do admit it is a game changer @Joseph Ssebagala 

Anyways, I hope this helps. 

[Joseph Ssebagala]

Spot on! The manual back and forth is precisely what my team and I are trying to overcome. 

We'll look into the idea of ScriptRunner and see how it goes. Unfortunately, we currently don't have it installed. 

Thanks again, @Heather Ronnebeck

[Heather Ronnebeck]

I will say it was a huge game changer for me once I was allowed to get it installed in one environment. I would invest in it again to get the advanced searching features alone.

If not that one I'd find another similar plugin if you don't like Scriptrunner as an option.

[Bill Sheboy]

Another idea, @Joseph Ssebagala 

If getting a JQL addon isn't possible, you can also try this with an Automation for Jira rule.  If you are interested in trying that we can start another thread in this post to keep the solution approaches separate for future readers.  And...you may take a look at this documentation to see if this is something you want to try: https://www.atlassian.com/software/jira/guides/expand-jira/automation  

[Joseph Ssebagala]

Thanks, @Bill Sheboy, 

We might try Automation in the future, but I'm afraid it's not an option for us at this time. We just wish we could achieve what we're looking for without any plugins. 

Also, I can confirm that ScriptRunner and Automation are both viable options for what we're looking to accomplish. Therefore the answers here are correct. 

We can close this issue.

[Bill Sheboy]

Sounds good, Joseph.  And FYI that Automation for Jira is included with Jira Cloud.  It is only a separate addon when using the Server/Data Center versions....for that there are free and paid versions.