Reports based on duration between open and update/closed

RT Users
1

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 jesper@catnet.dk

2

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”;

3

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

Once (427 Bytes)

Tickets.pm.diff (760 Bytes)

4

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 jesper@catnet.dk

5

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? […] Bear in mind that if the ticket is closed and then
re-opened, the duration in which it was closed should not be
counted.

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.

$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;
}
}

Thanks. Now, perl isn’t my strong point. But if I read that snip of code
correctly, it doesn’t take into account that the ticket may have been
closed for a while and then re-opened. The duration in which the ticket
was closed should not be counted in.

Jesper Henriksen jesper@catnet.dk

6

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 :slight_smile:
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.id>tr1.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