[rt-users] Reports based on duration between open and update/closed
jesper at catnet.dk
Mon Jun 28 09:58:50 EDT 2010
On Wed, Mar 24, 2010 at 02:43:36PM +0100, Jesper Henriksen wrote:
> Is there a way to get reports on how much time a ticket has been open?
> For example we would like to see the average time it takes from when a
> customer reports a problem till we have closed the ticket. Bear in mind
> that if the ticket is closed and then re-opened, the duration in which
> it was closed should not be counted.
In case someone else needs an answer to this question, here's some SQL I
came up with after lots of persistent poking around in the database :)
It's probably not perfect, but it's a good start!
If someone can explain how I can show these status timers on RT's search
result page, and perhaps even in graphs, I would be very grateful.
sum(TimeInThisState) as TotalTimeInThisState
tr1.ObjectId as TicketId,
IF(tr1.Type='Create','new',tr1.NewValue) as Status,
-- Two ways of having the timer show. Uncomment the one you want:
-- Option 1: NULL means "still in this state"
-- unix_timestamp(tr2.Created)-unix_timestamp(tr1.Created) as TimeInThisState
-- Option 2: The clock keeps counting
unix_timestamp(IF(tr2.Created IS NULL,NOW(),tr2.Created))-unix_timestamp(tr1.Created) as TimeInThisState
Tickets as ticket,
Transactions as tr1
left join Transactions as tr2 on
and case tr1.Type
when 'Create' then 'new'
and tr1.Type in ('Create','Status')
) as TicketStatusOverview
More information about the rt-users