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] Spreadsheet Custom Fields not Aligned.
On Mon, Apr 19, 2010 at 05:03:10PM -0700, William Graboyes wrote:
We just upgraded our RT system to 3.8.7 from 3.8.4. Now when we export a
spreadsheet none of the custom fields are aligned with the proper field.
Which is confusing to say the least. I was wondering if there something
simple that we may have missed? Has anyone seen this before?
I vaguely remember something on the list about this recently in the list,
but searching for it has proved to be futile.
I ran into that very same problem yesterday, but luckily I did manage to
find a post that explains a workaround:
http://www.mail-archive.com/rt-users@lists.bestpractical.com/msg24057.html
Essentially you need to edit /opt/rt3/share/html/Search/Results.tsv line
110. Change it from
foreach my $name ( sort { $cf_name_to_pos{$a} = $cf_name_to_pos{$a} }
keys %cf_name_to_pos ) {
.. to ..
foreach my $name ( sort { $cf_name_to_pos{$a} = $cf_name_to_pos{$b} }
keys %cf_name_to_pos ) {
Works for us.
--
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
[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
