How to get last communication date from customers in an organization

[James K_]

Hello, my team has a need to record last communication - either the most recently opened ticket or most recently responded to ticket - per customer Organization.  I currently have a customization that updates "Last Customer Update" when a customer responds on a ticket.  Ideally, I will be able to query this information using a JQL query so that I can use the Google Sheets Jira integration.  I didn't see an existing report that seems to get the data I want.  Is there a way to get this information using JQL (either creating new automations or existing fields)?

Thanks!

[Esteban Diaz]

Hi @James K_ , 

Maybe you can create a Custom field to hold always the las comment...

And then an automation triggered on comments that makes a copy of the last comment into the Custom Field ... this way you´ll be able to query issues with JQL and get the last comment in a column that you can export to Google Sheets ...

To copy the last comment you can use an smart value like this one in your automation:

{{issue.comment.last.body}}

I hope this has been useful to you.
I will be waiting for your comments. 😉

Cheers!

Esteban Diaz
https://www.linkedin.com/in/estebandiaz/ 

[James K_]

Hello Esteban,

Thank you for the the suggestion.  I already have a field "Last Customer Comment Date" that is updated when a ticket is created and when it is responded to.

I don't necessarily need the actual comment but the date across our entire customer base in Jira Service desk.

Assume the following customers and 'Last Customer Comment Date" and Tickets #(Jira-##)

Company A -

   Jira 1 - 12/31/2022;    Jira 5 -9/4/2023

Company B -

   Jira 2 - 2/1/2023;        Jira 3 - 6/1/2023

Company C -

  Jira 4 - 7/1/2023;        Jira 6 - 9/12/2023

---

My understanding is that I can only query against tickets, and not against organizations.  Is it possible to query against our customer service desk project and only return the tickets with the most recent "Last Customer Comment Date".

Based on the above example, I essentially want a query that would return Jira-5, Jira-3, and Jira-6.

Thank you again for the suggestion and have a great day.

[Esteban Diaz]

Hi @James K_ , 

Unfortunately is not possible to get this result with a simple JQL ... as we have no clauses like "group by" or "distinct" used in SQL.

You will need to create a different JQL query for each  Company.

Or.. I can recommend you a great add on called EazyBI with which you can build this and many other even more complex reports.

This is the link for EazyBI: https://eazybi.com/ 

In this link you will find an EazyBI demo with preloaded data for JSM :  https://eazybi.com/accounts/1001/cubes/Issues

and another for Jira Software: https://eazybi.com/accounts/1000/cubes/Issues   

Cheers!

Esteban Diaz
https://www.linkedin.com/in/estebandiaz/ 

[Hannes Obweger - JXL for Jira]

Hi @James K_

if you're open to solutions from the Atlassian Marketplace, you may want to have a look at 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 advanced features, including support for (configurable) issue hierarchies, issue grouping by any issue field(s), sum-ups, or conditional formatting.

With these, you can build a view like this in just a couple of clicks:

(This will work perfectly fine with your Last Customer Update custom field - but JXL would also come with a so-called smart column for the date of last comment by customer, so you could probably also get rid of the automation.)

Any questions just let me know,

Best,

Hannes