how can I create a jira report with the latest comments in each issue?

[Sagayaraj Arulanandam]

I'm trying to find an option using JDL to generate a report of comments (latest or by specific dates) for specific set of issues. However, I'm unable to find such an option within JQL. Can someone help? 

[Stephen Wright _Elabor8_]

Hi @Sagayaraj Arulanandam 

Just to confirm, you want to be able to search by last comment date, and view the last comment made?

If so, this isn't a native piece of metadata - and JQL needs this to filter by it.

You could create this though using a custom field and Automation?

---

This is how I'd do it:

• Create a custom field, either Date Picker or Date Time Picker
• Create an Automation Rule to publish the Last Comment Date and Last Comment details for future comments made
• Create a second Automation Rule to publish this data for existing issues

---

Create Field

You'll need to be a Product Admin for this one:

• Go to Settings (cog icon in top-right) > Issues
• Select Issues from the left-hand menu
• Create 2 new custom fields
  • Paragraph - this is for "Last Comment"
  • Date Time Picker - this is for "Last Comment Date"

---

Rule 1 - Future Issues

This rule will update these fields in future, when new comments are made:

• Trigger: Issue Commented
• Action: Edit Issue
  • Field = Last Comment
    • Value = {{issue.comment.last.body}}
  • Field = Last Comment Date
    • Value = {{now}}

---

Rule 2 - Existing Issues

This rule will look at all existing Issues and update these fields.

I'd consider limiting the JQL to relevant issues (eg. open issues, issues created in 2023, etc), just so you don't overuse your executions for the month.

The settings for Scheduled are also not relevant - you only need this rule once.

• Trigger: Scheduled
  
  • Run a JQL search = TRUE (checkbox)
  • JQL - "Last Comment Date" is EMPTY AND...<additional parameters here>
  • Only includes issues that have changed... = FALSE (checkbox)
• Action: Edit Issue
  
  • Field = Last Comment
    • Value = {{issue.comment.last.body}}
  • Field = Last Comment Date
    • Value = {{issue.comment.last.created}}

Once you have created and published this rule, press the blue Run rule button to run it immediately.

Once complete and you've checked the data is accurate, you can deactivate/delete the rule, as Rule 1 will hand all future comments.

---

A few extra notes...

• You can now refer to this data in a JQL search, and add it as a filter column
• Be mindful of your automation limits with this rule; check those out here
• Smart values are in use above; to see more smart values for comments, check out this page

Let us know if this helps!

Ste

[Sagayaraj Arulanandam]

thank you Ste. I'm not an expert on Jira - will work with my mates and see if this could help achieve our goals. 

[Aron Gombas _Midori_]

If you look for an easy solution, with the Better Excel Exporter app you can just use this export type:

...to produce an Excel report from the issue fields you selected plus the comments (who, when and text):

That's it!

(Discl. this is a paid and supported app developed by our team. Free for 10 users.)

[Hannes Obweger - JXL for Jira]

Hi @Sagayaraj Arulanandam

just to add to Stephen's answer: If you're open to solutions from the Atlassian Marketplace, this would be trivial 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 number of so-called smart columns that aren’t natively available, including the latest comment (along with many other comment-related columns). This is how it looks in action:

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

This all just works and doesn't need any scripting or Automation whatsoever.

Any questions just let me know,

Best,

Hannes

[Sagayaraj Arulanandam]

thank you Hannes - looks like it would meet our needs. I'll look into it and keep this forum updated.