How to make a JQL query to find all issues that have not been commented on in the last 24 hours?

[Phoenix O_Connell]

I need to make a JQL query that will find all issues that have not been commented on in the last 24 hours. I seem to have some issue with JQL not finding the function 'commented(after -24h)' and then adding in the "NOT" aspect. Any help is appreciated.

[Prosper Agwegiokhe · Accepted solution]

Hi @Phoenix O_Connell ,

I’m Prosper, a support engineer at Appfire, and I’m here to help you.

If you are open a plugging suggestion, you can try out our App JQL Search Extension for Jira.

You can use the query:

commentLastCreatedOnDate < -1d and commentLastCreatedOnDate > -2d
to find issues with no comments in the last 24 hours.

You can see more information about this query here, and please contact our support if you have any other questions about this query.

We’ll be happy to help you!

Best regards,
Prosper.

[Phoenix O_Connell]

I installed the app, however the query you provided returns no results and no errors. None of my issues have comments on them so it should pull up all issues but it returns nothing.

[Trudy Claspill]

I'm just guessing but I would suggest you try adding:

(commentLastCreatedOnDate < -1d and commentLastCreatedOnDate > -2d) or commentLastCreatedOnDate is empty

If the issues have never been commented on then there is no date to be used in a comparison. So, the get the issues that have some comments and were not commented on in the past 24 hours you would use the original query. To also get the issues that have never had any comments added I suspect you need the additions I suggested.

Note I have not tested that because I don't have that app on my Jira instance.

[Phoenix O_Connell]

This pulled up my issues. I added a comment today and will post again tomorrow whether the entire query works. Thank you.

[Phoenix O_Connell]

This query did the trick, thank you everyone!

[Trudy Claspill]

Hello @Phoenix O_Connell 

Welcome to the Atlassian community.

Does the JQL work if you do not add "NOT" to it?

"commented" does not appear to be a native JQL function based on the documentation found here:

https://support.atlassian.com/jira-software-cloud/docs/use-advanced-search-with-jira-query-language-jql/

Do you have a third party app that provides the "commented" JQL function? If so, which app is that?

[Phoenix O_Connell]

I do not have a third party app, I think I am just using the wrong thing to do this.

[Trudy Claspill]

Does the JQL work when you don't add "NOT" to it? Or does it not work then also?

What is the symptom of "not working"? Do you get an error message? If so, what is the error message?

Jira JQL does not natively have a function to get what you want through a filter. That would require a third party app. Are you able to get third party apps?

It might be possible to create a work around through an Automation Rule. I have not yet tried to work out the details. If if was possible to do, then you would have to email the results to the desired recipients.

[Hannes Obweger - JXL for Jira]

Hi @Phoenix O_Connell

welcome to the community!

If you're open to solutions from the Atlassian Marketplace, this would also be easy to do using the app that my team and I are working on, JXL for Jira.

JXL is a full-fledged spreadsheet/table view for your issues that allows viewing, inline-editing, sorting, and filtering by all your issue fields, much like you’d do in e.g. Excel or Google Sheets. It also comes with a long list of so-called smart columns that aren’t natively available, including the time since last comment.

This is how it looks in action:

As you can see above, you can easily sort and filter by the time since last comment, and also use it across JXL's advanced features, such as support for (configurable) issue hierarchies, issue grouping by any issue field(s), sum-ups, or conditional formatting.

Once you've narrowed down your list of issues, you can work on your issues directly in JXL, trigger various operations in Jira, or export your issues with just one click.

Any questions just let me know,

Best,

Hannes