[rt-users] Building report on tickets

[Anton Heuschen]

Hallo.

Im trying to build a report on total amount of tickets logged by users, time
taken to resolve etc. 

In short here is the basic categories I am looking at:

Category(or Queue then) , Time Logged (TL) , Time Accepted (TA) , Time
Completed (TC)

To get: 


1) Quantity of tickets per set period 

2) Average time to accept ticket

3) Avg time to complete a ticket


I can see in the Tickets table the "Created" date - this gives me TL

I can work out the Time Completed by checking on Status=Resolved and
checking that the Queue is what I want.


ALSO NOTE, as an example, tickets are logged here first with SupportLevel1,
SupportLevel1 might sometimes close a ticket, but in most cases it gets
send/transferred to SupportLevel2... The stats should be on tickets that
reached SupportLevel2..THUS

TIME ACCEPTED (TA) should be when SupportLevel2 took the ticket,
SupportLevel1 could of taken the ticket and added many comments .before it
was transferred to SupportLevel2...I cannot see in the tables how RT
determines when it was moved from one Queue to the next? I can determine
when SupportLEvel2 resolved a ticket by querying the Queue = SupportLevel2
and Status = Resolved. I am not sure what field to use to say that
SupportLevel2 started a ticket at this date and time?  There are fields like
Told, Starts, Started.. The closest seems to be the Told field, but it does
not seem to be linked/or have a queue id with it.so how one would determine
if the date indicated by Told was when SupportLevel2 took over the ticket
seems questionable.


Here is what would typically happen with a ticket:

1) Ticket logged by a SupportLevel1 user.
2) Another or same SupportLevel1 user might add various comments
3) Then it might get forwarded (queue changed) to SupportLevel2 (where it
will wait till SupportLevel2 accepts it)
4) SupportLevel2 will add comments, either same user who accepted it or
between different SupportLevel2 members
5) Finally a SupportLevel2 user will resolve the ticket

So with above in mind ..the stats for Time to Accept the ticket, should be
from date that SupportLevel1 moved/forwarded/changed the queue from
SupportLevel1 to SupportLevel2. i.e. when SupportLevel2 could actually take
the ticket. 

Where does RT keep its ticket details ?? I tried to look at most of the
tables and see none of the ticket description details, also how does it keep
track of a ticket history? The table Tickets, simply is what I would term "a
short summary of the ticket, it does not quite give all the replies and
"commentators" and ticket "owner" and respondents (like what you would see
if you click on a RT entry on RT itself?

I've been trying to build the above queries in the Query tool also but still
have questions relating to how to establish where one Queue user took over
from another queue..as described already in the points above, I hope this is
clear enough? 


Your help and assistance appreciated