How to create a Trac report containing all tickets needing an answer?

Question

We are using Trac to handle support for our clients. I wonder if there is a way to create a report to help us identify the tickets unanswered. That is the ones where the last comment is not from the owner.

Any idea how to do that ?

score 0 · Answer 1 · answered Oct 17 '13 at 10:16

0

It won't be too difficult with a little SQL knowledge. Take a look at Trac's Database Schema:

enter image description here

You get the owner field from the ticket table and join with the ticket-change table by ticket.id and ticket-change.ticket to get the comment information.

I'm not sure this is the best approach however. You should just set all answered tickets to closed and then use the report for showing any open tickets. Or modified the workflow to better suit the process by adding a unanswered stage.

answered Oct 17 '13 at 10:16

Chris Seymour

83,387
30
160
202

I agree with the SQL statement recommendation. Why would one close all commented but not yet resolved tickets? Certainly not for report optimization. Switching ticket status with each possible ping-pong of client-support-communication would be too noisy as well, other conflicts like custom ticket status change restrictions aside. – hasienda Oct 17 '13 at 23:02
@hasienda the OP states they want `report to help us identify the tickets unanswered` this should be solved with the workflow. If the ticket is at stage `unanswered` reporting them all is trivial and won't return false positives. If a ticket is `answered` it should be `closed`. Hacking a report based on who left the last comment doesn't sound right *IMO*. – Chris Seymour Oct 18 '13 at 08:28
The preferred solution might be chosen by personal taste too. Better have a carefully crafted report than modifying workflow. While reports can be changed over and over again, workflow actions are recorded in ticket history for all times. The OP seems to have found a solution along these lines too. It highly depends on local policy, if an answer is already a solution. If so, how would you deal with support asking for more information in such a case? – hasienda Oct 27 '13 at 08:09

score 0 · Accepted Answer · answered Oct 21 '13 at 13:58

Thanks for giving me the direction. I have started from an existing report and have managed to do what I want, changing it to this SQL query :

SELECT DISTINCT p.value AS __color__, id AS ticket, 
   summary, component, milestone, t.type AS type, status, resolution, 
   t.time AS created, changetime AS modified, reporter AS _reporter
  FROM ticket t
  LEFT JOIN enum p ON p.name = t.priority AND p.type = 'priority'
  LEFT JOIN ticket_change tc ON id = tc.ticket
  WHERE owner = $USER AND status <> 'closed' AND t.type = 'support' AND 
    (owner != (SELECT author FROM ticket_change WHERE time = 
    (SELECT MAX(tc.time) FROM ticket_change tc WHERE tc.ticket = id)))
  ORDER BY CAST(p.value AS integer), tc.time, milestone, t.type

If that can help someone...

How to create a Trac report containing all tickets needing an answer?

2 Answers2