Calculating the number of emails/replies/correspondences per ticket

RT Users
1

Hi,

I’d like to find out how to add my own calculation to charts, as opposed to the limited Ticket Count and various time calculations available for charts.

I’d like to add a calculation that calculates the number of emails/correspondences between an agent and a customer. I’ve tried looking through “lib/RT/Report/Tickets.pm” and “share/html/Search/Chart.html”, but there’s a lot of things I don’t understand in there.

Now, realistically I may not have to go through the effort of adding a calculation for charts.

My end result is a table that has the columns: TicketId, NumberOfEmails (and I’ll probably add some other things like Subject and whatnot). However, I’d prefer not to have a Custom Field for NumberOfEmails, reason being I’m not sure if there’s a way to calculate the number of emails for all my past tickets and set all of them to their correct value apart from manually counting and inputting it. Instead, I was considering doing Callbacks or something to count the number of Correspondences in the Transactions table from the database and appending a column to a table.

If I instead added a chart calculation, the end result would be more flexible - at the cost of a (possibly?)difficult implementation.

New Chart

Group tickets by Ticket/Id. (individual tickets, so basically no grouping; this might get out of hand for large numbers of tickets though; this is something else I’d like to know how to do)

Calculate values of Email count.

Generate bar chart or table

Now that I think about it though, I might just be able to add a column called Number of Emails to search results and get that sorted out.

Sorry my thoughts are kind of all over the place right now.

In summary, envisioned end result:

  • A table/search result list (doesn’t necessarily have to be a chart) that can be added to a dashboard that has, at minimum, the columns: TicketId and NumberOfEmails

  • NumberOfEmails is preferably not a Custom Field

  • The column NumberOfEmails must be able to find the number of emails between an agent and customer of all tickets that are currently in my database (I’d imagine I have to use SQL for this somewhere to count Correspondences for specific TicketId’s)

I’d like to know what the best thing to do would be and how to start working on a solution.

I hope I’ve explained my problem well enough.

I can clarify further if you’d like.

Thank you,

Anthony

2

Hi,

I’d like to find out how to add my own calculation to charts, as opposed to the limited Ticket Count and various time calculations available for charts.
I’d like to add a calculation that calculates the number of emails/correspondences between an agent and a customer. I’ve tried looking through “lib/RT/Report/Tickets.pm” and “share/html/Search/Chart.html”, but there’s a lot of things I don’t understand in there.

Now, realistically I may not have to go through the effort of adding a calculation for charts.
My end result is a table that has the columns: TicketId, NumberOfEmails (and I’ll probably add some other things like Subject and whatnot). However, I’d prefer not to have a Custom Field for NumberOfEmails, reason being I’m not sure if there’s a way to calculate the number of emails for all my past tickets and set all of them to their correct value apart from manually counting and inputting it. Instead, I was considering doing Callbacks or something to count the number of Correspondences in the Transactions table from the database and appending a column to a table.

We have two CFs that are populated - ClientReplies, StaffReplies and can be used in the charts. I’ve done this twice before with two different approaches.

The first approach worked like this:
A crontab would use an action which would do this for each ticket. It would cycle through all the create and correspond transactions and attribute them to either staff or clients then update the CF when it was done. This meant that every single create,correspond transaction was processed for every ticket every time it ran. It was a wasteful way to do it.

The second approach worked like this:
Two conditions were created - One that detected ‘staff’ correspondence and another to detect ‘client’ (non-staff) correspondence. Depending on which condition was true an action to increment the appropriate CF (read the current CF value, add 1, delete the old CF value, record the new CF value). The net effect was that the CFs were updated only when it was necessary. This is a much more efficient way to do it.

Landon Stewart
Lead Analyst - Abuse and Security Management
INTERNAP ®
:e-mail: lstewart@internap.commailto:lstewart@internap.com
:earth_africa: www.internap.comhttp://www.internap.com

3

Hi Landon,

Thanks for the reply.
In your implementation, what kind of charts can you make with those CFs?

We classify our tickets by subject matter so the chart I’m seeing is basically grouped by three CFs. It basically shows that X staff replies are made for every Y client replies broken down by each classification. It’s not perfect but I haven’t looked into using the calculation part of the graphing yet for CFs.

I have implemented a “Number of Emails” CF before with a similar implementation as your second approach, but when charting I can only use it for grouping. So the table would look something like:
Number of Emails | Ticket count
0 53
1 89
2 25
Further, wouldn’t all tickets that already exist in my database have “(no value)” for the new CF? My understanding is that the new CF would only “kick in” for new tickets.
I might re-consider implementing a CF again, but something similar to your first approach. The second approach is definitely more efficient, but my supervisor has asked that I implement it in a way that it is drawing from the data in the database rather than incrementing a counter, which could end up being less reliable.

I think I would consider doing some creative joins and subqueries with the mysql tables to get this information instead but it will get complicated really quickly. I should have mentioned that after I used the second approach I described I had to update all the tickets with the first approach in order to get everything caught up but I only have to do it once after the second approach was finished and working.

Landon Stewart
Lead Analyst - Abuse and Security Management
INTERNAP ®
:e-mail: lstewart@internap.commailto:lstewart@internap.com
:earth_africa: www.internap.comhttp://www.internap.com