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