Re: [rt-users] Reports based on duration between open and update/closed
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. select TicketId, Subject, Status, sum(TimeInThisState) as TotalTimeInThisState from ( select tr1.ObjectId as TicketId, ticket.Subject, 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 from Tickets as ticket, Transactions as tr1 left join Transactions as tr2 on tr1.ObjectId=tr2.ObjectId and tr1.ObjectType=tr2.ObjectType and tr2.idtr1.id and case tr1.Type when 'Create' then 'new' else tr1.NewValue end=tr2.OldValue where tr1.ObjectType='RT::Ticket' and tr1.ObjectId=ticket.EffectiveId and tr1.Type in ('Create','Status') group by tr1.id order by tr1.id ) as TicketStatusOverview group by TicketId,Status order by TicketId,Status -- Jesper Henriksen Discover RT's hidden secrets with RT Essentials from O'Reilly Media. Buy a copy at http://rtbook.bestpractical.com
Re: [rt-users] Reports based on duration between open and update/closed
On Wed, Mar 24, 2010 at 09:58:37AM -0700, Allen wrote: 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: [..lots of SQL..] Thanks. But we were hoping that RT itself could show the report. I'm surprised that doesn't seem to be implemented. -- Jesper Henriksen jes...@catnet.dk Discover RT's hidden secrets with RT Essentials from O'Reilly Media. Buy a copy at http://rtbook.bestpractical.com
Re: [rt-users] Reports based on duration between open and update/closed
Am 24.03.2010 14:43, schrieb Jesper Henriksen: Hey all, 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. It would also be nice if we could see how much time has passed from when the requestor (customer) creates or updates a ticket till one of our support members responds to it. I've searched the list and google in every way I could imagine, but I just can't crack this nut. Can anyone help? I'm trying to setup RT for my new employer and these report features are critical to us. Hi Jesper, you can do this with an callback under local/html/Callbacks/MyCallback/Elements/RT__Ticket/ColumnMap/Once where you extend the ticket column_map. Attached is the callback I use, where I compare created to resolved in days. You can then add the new field 'DaysCreatedToResolved' to the ticket search result. If you also want to use it within the chart feature it is a little more difficult. You have to copy lib/RT/Report/Tickets.pm to local/lib/RT/Report/Tickets.pm and modify it. Attached an diff of my version BUT this only works for mysql. I also removed some lines from the diff because I have some other modifications in this file, so don't apply the diff, use it as an example. One limitation that I didn't fixed is, that the days (DaysCreatedToResolved) are not sorted in the chart. If anyone else have done something similar, I would appreciate feedback. Chris %ARGS $COLUMN_MAP = {} /%ARGS %INIT $COLUMN_MAP-{DaysCreatedToResolved} = { title = 'DaysCreatedToResolved', value = sub { my $Created = $_[0]-CreatedObj-SetToMidnight; my $Resolved = $_[0]-ResolvedObj-SetToMidnight; return '' unless ( $Resolved 0 ); return ' 1' if ( $Resolved == $Created ); return ($Resolved-$Created)/60/60/24; } } /%INIT--- /opt/rt3/lib/RT/Report/Tickets.pm 2009-11-18 10:49:45.0 +0100 +++ /opt/rt3/local/lib/RT/Report/Tickets.pm 2010-02-26 13:26:13.0 +0100 @@ -69,12 +69,13 @@ } push @fields, map {$_, $_} qw( +DaysCreatedToResolved ); my $queues = $args{'Queues'}; @@ -229,6 +233,9 @@ $self-{_sql_report_watcher_users_alias_$type} = $u_alias; } @args{qw(ALIAS FIELD)} = ($u_alias, $column); +} elsif ( $field =~ /DaysCreatedToResolved/ ) { +my $alias = $args{'ALIAS'} || 'main'; +$args{'FUNCTION'} = IF(DATEDIFF($alias.Resolved, $alias.Created) = 0, ' 1', IF($alias.Resolved $alias.Created, DATEDIFF($alias.Resolved, $alias.Created), NULL)); } return %args; } Discover RT's hidden secrets with RT Essentials from O'Reilly Media. Buy a copy at http://rtbook.bestpractical.com
[rt-users] Reports based on duration between open and update/closed
Hey all, 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. It would also be nice if we could see how much time has passed from when the requestor (customer) creates or updates a ticket till one of our support members responds to it. I've searched the list and google in every way I could imagine, but I just can't crack this nut. Can anyone help? I'm trying to setup RT for my new employer and these report features are critical to us. -- Jesper Henriksen jes...@catnet.dk Discover RT's hidden secrets with RT Essentials from O'Reilly Media. Buy a copy at http://rtbook.bestpractical.com
Re: [rt-users] Reports based on duration between open and update/closed
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 != '-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