Query for Last Comment

danielmaycock September 23, 2021

I'd like to be able to have a query (or quick query) that shows the last comment for all my project's stories.  Currently I have to open up each individual story & scroll down for this.  Having a query that shows this, lets me know quickly if its been updated or not.  

4 answers

2 accepted

2 votes
Answer accepted
Alex
Rising Star
Rising Star
Rising Stars are recognized for providing high-quality answers to other users. Rising Stars receive a certificate of achievement and are on the path to becoming Community Leaders.
September 23, 2021

Hi @danielmaycock welcome to the community!

 

Jira will not have a function that shows the last comment in JQL. But, I believe you can have similar results with this JQL

updated > startOfDay()

For the additional functions that work with date fields check out https://support.atlassian.com/jira-software-cloud/docs/advanced-search-reference-jql-functions/#Advancedsearchingfunctionsreference-endOfDayendOfDay-

 Also, I found an interesting post that explains how to display the last comment on a custom field using automation for Jira (https://community.atlassian.com/t5/Marketplace-Apps-Integrations/How-to-display-Last-comment-in-Filter-JQL-and-any-Add-on/ba-p/1489153).

You might be able to store the last comment's date in a date custom field by switching

{{issue.comments.last.body}}

to

{{issue.comments.last.created}}

then create a filter that sort by that date custom field

I hope it helps. If this answer helps solve the problem, please come back and mark this answer as solved to help other community members with the same challenge. If not, you are welcome to share your solution as well.

Cheers,

Alex

danielmaycock September 23, 2021

Hi Alex,

 

Thanks very much for your quick reply.

In your first suggestion: 

updated > startOfDay()

Does it require a numeric in between ()? What does the query mean? Is it anything that has been updated after "startOfDay"?  

danielmaycock September 24, 2021

Hi Alex, just wondering if you have a thought on this?  I can also put it out to the community?

Thanks,

Daniel

Alex
Rising Star
Rising Star
Rising Stars are recognized for providing high-quality answers to other users. Rising Stars receive a certificate of achievement and are on the path to becoming Community Leaders.
September 27, 2021

Hi @danielmaycock 

The function StartOfDay() performs searches based on the start of the current day. It lists issues that have been updated after the beginning of the day. 

There are many other time-related functions in JQL. You can have the complete specification of all JQL out-of-the-box Jira functions at this link Advanced search reference - JQL functions.

Cheers,

Alex

1 vote
Answer accepted
Daniel Turczanski - __JQL Search Extensions
Marketplace Partner
Marketplace Partners provide apps and integrations available on the Atlassian Marketplace that extend the power of Atlassian products.
October 14, 2021

You may be interested in our commentLastUpdatedOnDate JQL alias.

You can find issues that were recently commented on and order them by the comment date:

commentLastUpdatedOnDate < -2d order by commentLastUpdatedOnDate desc

 It's part of JQL Search Extensions.

Eric Lyke
I'm New Here
I'm New Here
Those new to the Atlassian Community have posted less than three times. Give them a warm welcome!
July 25, 2023

Hi Daniel,

This doesn't work on Jira Cloud. Any easy ways to get the same last comment using JQL on Jira Cloud?

Thanks,

Eric

1 vote
Myesha May 18, 2024
Howdy from Texas! My name is Myesha and I am excited to answer this question! I am a Kanban Lead for 4 Kanban teams and had this exact same challenge. My organization does not allow me to use any plugins or ScriptRunner so I had to find a solution with out of the box Jira. I was able to solve it by creating a custom date field, setting up an automation and then creating a JQL query based on the new custom field. 

Assignee Last Comment Automation Rule.png

To achieve the functionality of tracking when the last comment was made by an assignee on a JIRA ticket, you can follow these steps:


1. **Create a Custom Date Field:** Begin by creating a custom date field named something like "Assignee Last Comment Date." This field will store the date of the last comment made by the ticket assignee.


2. **Trigger:** Configure the automation to trigger whenever a new comment is added to an issue.

Rule is run when a new comment is added to an issue.png

3. **Add a smart values condition:** Add a condition to check if the `Assignee Name` equals the `Comment Author's Display Name`.

{{assignee.displayName}}
{{comment.author.displayName}}

Compare 2 values using smart values and regular expressions.png

4. **Edit Issue:** If the condition is true (meaning the assignee made the comment), update the "Assignee Last Comment Date" custom field with the current date.

Set the custom field to todays date.png
5. **Create a JQL Query for Reporting:**
   - Use a JQL query to identify tickets where the "Assignee Last Comment Date" is older than your team's agreed-upon update frequency (e.g., more than 1, 2, or 3 days). Here’s an example of such a query:
    
statusCategory = "In Progress" AND "Assignee Last Comment Date" <StartOfDay(-3)
    
This will show you all tickets in progress where the assignee has not commented in the last three days.

This setup will allow you to efficiently monitor and ensure that all tickets are updated as per team expectations without having to manually check each one.

NOTE!
Please note that while this automation rule will effectively track comments made by assignees from the moment it is set up, it does not retroactively apply to tickets with comments made before the rule was implemented. This means that any tickets that needed updates prior to the implementation will not be automatically flagged by this rule. For those, you might need to manually review or set up a temporary measure to ensure they meet the team's commenting expectations until they receive new comments under the new system. This automation is primarily beneficial on a go-forward basis, helping to maintain regular updates moving forward.

Hope this helps!
Nic Brough -Adaptavist-
Community Leader
Community Leader
Community Leaders are connectors, ambassadors, and mentors. On the online community, they serve as thought leaders, product experts, and moderators.
May 18, 2024

Please, don't use AI to answer questions - this "solution" is not correct, it does not address the problem.

Myesha May 18, 2024

Hi Nic Brough! Good day to you! Are you doing okay? I see you are saying that my solution is not correct and it does not address the problem. Can you explain why you feel like that? I am running this on multiple Jira projects and it is working perfectly. Also, I am not using AI to answer questions, I am using a solution that I designed myself after countless hours of searching and searching due to the fact that my organization does not allow me to use any plugins or add-ons or ScriptRunner. I had to come up with a solution using out-of-the-box Jira. I just want to help Scrum Masters and Kanban Leads like myself who are facing similar restrictions. I hope you have a really great day and I wish you the very best.

Nic Brough -Adaptavist-
Community Leader
Community Leader
Community Leaders are connectors, ambassadors, and mentors. On the online community, they serve as thought leaders, product experts, and moderators.
May 18, 2024

Please read the original question again, and compare what they are looking for with the output you get from your "solution".  They will not match.

Myesha May 18, 2024

Hi Nic! Good to hear from you again. Are you having a good day? I re-read the question and I can see your point. My response will allow someone to identify the last comment date on tickets but not the actual comment.

The first step in the automation, a trigger that runs when there is a comment will most definitely help.

They can skip the second step, which is to check to see if the commentor is the assginee (that is the extra part).

The 3rd step of capturing the comment date and populating a custom field, will allow them to run queries against that. I think that could indeed be helpful to this individual.

What I could have added, is a step to capture the text of the last comment. That is where I can see your point.

I see other "solutions" that will not match what they are looking for but I love the fact that we are all sharing and trying to help others. I thought that was what the community was all about.

Any specific reason why you addressed my post and not the others? I am new to commenting/replying in the community so I am hoping that a Community Leader like yourself would give a newbie some advice. My aim is to be a valuable asset.

Again, I hope you are doing well and I am so happy to be engaging with people here. I have been using Jira for over 10 years but never got involved with the community. Getting "scolded "on my first post is a little discouraging, but I will not give up as my only intent is to help. 

I am pretty confident that someone in the community will benefit from my "solution".

Happy Saturday to you!

Nic Brough -Adaptavist-
Community Leader
Community Leader
Community Leaders are connectors, ambassadors, and mentors. On the online community, they serve as thought leaders, product experts, and moderators.
May 18, 2024

I did not mean to scold you at all.  It was important for other readers to understand that your solution does not answer the question asked.

As an aside, to be helpful, it's better that you don't provide answers to three-year old questions that have already been answered and had the answers accepted.

Myesha May 18, 2024

I actually visited this post when I was looking for solutions to my problem so I figured other people, with the same challenge I was facing, could possibly land here too.

I have seen replies to older posts, including this one, so I did not realize that this was considered bad practice.

If you look immediately below this thread, and above it, there are replies from 2023.

They are from 2023 not 2024, I will give you that, but it is still past the date that answers were accepted. Also, my experience with Jira has shown me that Jira changes frequently so sometimes a solution that worked a year ago, might not work today OR there could be an easier way.

Any reason why my particular reply warranted a response?

Like Lukas Benes likes this
0 votes
Balaji June 8, 2023

Use this below, but I think you need ScriptRunner installed:

 

issueFunction in lastComment("by <add user name here>") ORDER BY updated DESC

Suggest an answer

Log in or Sign up to answer