> Is there a way to get reports on how much time a ticket has been open?
I know this is not the sanctioned way to do things because it is not using RT APIs, but it works for now: # How many days have tickets been open: SELECT id, EffectiveId, Created, Resolved, WEEK(Created) as week, Status, TO_DAYS(IF(Resolved != '0000-00-00 00:00:00' AND Resolved != '1970-01-01 00:00:00', DATE(Resolved), CURDATE())) - TO_DAYS(DATE(Created)) as days_open FROM Tickets WHERE Queue = 5 AND Type = 'ticket' AND ( Status IN ('open','stalled','new','autoclose') OR ( Status = 'resolved' AND Resolved > DATE_SUB(CURDATE(), INTERVAL 30 DAY) ) ) ORDER BY id # how many resolved per month $sql = "SELECT COUNT(id) as ct, LPAD(MONTH(Resolved), 2, '0') as mon, YEAR(Resolved) as yr FROM Tickets WHERE Queue = 5 AND Status = 'resolved' AND Resolved > DATE_SUB(CURDATE(), INTERVAL 180 DAY) AND Type = 'ticket' GROUP BY yr,mon"; # how many created per month $sql = "SELECT COUNT(id) as ct, LPAD(MONTH(Created), 2, '0') as mon, YEAR(Created) as yr FROM Tickets WHERE Queue = 5 AND Status NOT IN ('rejected', 'deleted') AND Created > DATE_SUB(CURDATE(), INTERVAL 180 DAY) AND Type = 'ticket' GROUP BY yr,mon"; # how many resolved per day, per queue $sql = "SELECT COUNT(T.id) as ct, Q.Name as QueueName, LPAD(DAYOFYEAR(T.Resolved), 3, '0') as dayresolved, YEAR(T.Resolved) as year FROM Tickets T LEFT JOIN Queues Q on Q.id = T.Queue WHERE T.Status = 'resolved' AND T.Resolved > DATE_SUB(CURDATE(), INTERVAL 30 DAY) AND T.Type = 'ticket' GROUP BY dayresolved, T.Queue"; # how many created per day, per queue $sql = "SELECT COUNT(T.id) as ct, Q.Name as QueueName, LPAD(DAYOFYEAR(T.Created), 3, '0') as daycreated, YEAR(T.Created) as year FROM Tickets T LEFT JOIN Queues Q on Q.id = T.Queue WHERE T.Created > DATE_SUB(CURDATE(), INTERVAL 30 DAY) AND T.Status NOT IN ('rejected', 'deleted') AND T.Type = 'ticket' GROUP BY daycreated, T.Queue"; # status for unresolved tickets $sql = "SELECT COUNT(T.id) as ct, T.Status FROM Tickets T LEFT JOIN Queues Q on Q.id = T.Queue WHERE Status NOT IN ('deleted', 'rejected', 'resolved') AND T.Type = 'ticket' GROUP BY T.Status"; Discover RT's hidden secrets with RT Essentials from O'Reilly Media. Buy a copy at http://rtbook.bestpractical.com