Sorry, I accidently sent a private reply to Forrest. Doh!
Hi Forrest, Thanks for the response. Perhaps my version of RT doesn't have a similar Transactions table as yours? Just a random query .. mysql> select * from Transactions where id = 149232; +--------+----------+-----------+-------------+-------+----------+----------+--------------------------------------------------------------+---------+---------------------+------------+---------------+--------------+--------------+ | id | ObjectId | TimeTaken | Type | Field | OldValue | NewValue | Data | Creator | Created | ObjectType | ReferenceType | OldReference | NewReference | +--------+----------+-----------+-------------+-------+----------+----------+--------------------------------------------------------------+---------+---------------------+------------+---------------+--------------+--------------+ | 149232 | 9784 | 0 | EmailRecord | NULL | NULL | NULL | <[EMAIL PROTECTED]> | 1 | 2006-10-18 14:45:00 | RT::Ticket | NULL | NULL | NULL | +--------+----------+-----------+-------------+-------+----------+----------+--------------------------------------------------------------+---------+---------------------+------------+---------------+--------------+--------------+ Don't necessarily see a way to extract a field for first update to the ticket .. Thanks :-) On 1/8/07, Forrest Blount <[EMAIL PROTECTED]> wrote:
The Transactions table contains far more detail relating to what kind of action your users are taking to respond to the ticket. I'd poke around there and determine which actions qualify as responses and then create your averages from there-- each transaction has a timestamp, so no need to worry about creating a database schema-- you can create your avgs over any interval you desire by specifying it on the query level and restricting the Transactions query to the earliest timestamp that meets your parameters... You don't even have to reference the Tickets table as Transaction tracks Created actions for tickets as well... best luck, Forrest Steve Finkelstein wrote: > Hi all, > > I'm working on simple script to determine the average ticket response > time per queue in an RT ticket. I was just curious if anyone has > already done this task and has any suggestions. The best way I see > making this possible is by using the Tickets table in the database: > > mysql> describe Tickets; > +-----------------+--------------+------+-----+--------------+----------------+ > | Field | Type | Null | Key | Default | > Extra | > +-----------------+--------------+------+-----+--------------+----------------+ > > | id | int(11) | | PRI | NULL | > auto_increment | > | EffectiveId | int(11) | | MUL | 0 > | | > | Queue | int(11) | | MUL | 0 > | | > | Type | varchar(16) | YES | | NULL > | | > | IssueStatement | int(11) | | | 0 > | | > | Resolution | int(11) | | | 0 > | | > | Owner | int(11) | | MUL | 0 > | | > | Subject | varchar(200) | YES | | [no subject] > | | > | InitialPriority | int(11) | | | 0 > | | > | FinalPriority | int(11) | | | 0 > | | > | Priority | int(11) | | | 0 > | | > | TimeEstimated | int(11) | | | 0 > | | > | TimeWorked | int(11) | | | 0 > | | > | Status | varchar(10) | YES | | NULL > | | > | TimeLeft | int(11) | | | 0 > | | > | Told | datetime | YES | | NULL > | | > | Starts | datetime | YES | | NULL > | | > | Started | datetime | YES | | NULL > | | > | Due | datetime | YES | | NULL > | | > | Resolved | datetime | YES | | NULL > | | > | LastUpdatedBy | int(11) | | | 0 > | | > | LastUpdated | datetime | YES | | NULL > | | > | Creator | int(11) | | | 0 > | | > | Created | datetime | YES | | NULL > | | > | Disabled | smallint(6) | | | 0 > | | > +-----------------+--------------+------+-----+--------------+----------------+ > > One can essentially use the Created field and the LastUpdated and run > a script from cron which will subtract the two. Then I'd need a way to > hash the ticket IDs I've already populated because the problem here > is, LastUpdated can arbitrarily be modified anytime a ticket gets > modified. I'd probably create a seperate database/table schema for > that, but that's another subject. > > Anyway, just my two cents. Any comments/suggestions are appreciated! > > Thanks, > > Steve > ------------------------------------------------------------------------ > > _______________________________________________ > http://lists.bestpractical.com/cgi-bin/mailman/listinfo/rt-users > > Community help: http://wiki.bestpractical.com > Commercial support: [EMAIL PROTECTED] > > > Discover RT's hidden secrets with RT Essentials from O'Reilly Media. > Buy a copy at http://rtbook.bestpractical.com
_______________________________________________ http://lists.bestpractical.com/cgi-bin/mailman/listinfo/rt-users Community help: http://wiki.bestpractical.com Commercial support: [EMAIL PROTECTED] Discover RT's hidden secrets with RT Essentials from O'Reilly Media. Buy a copy at http://rtbook.bestpractical.com
