Initial ticket response calculation

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

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
| rt-3.4.4-766-1161182700-1437.9784-3-0@foo.com | 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 forrest@itasoftware.com 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


The rt-users Archives

Community help: http://wiki.bestpractical.com
Commercial support: sales@bestpractical.com

Discover RT’s hidden secrets with RT Essentials from O’Reilly Media.
Buy a copy at http://rtbook.bestpractical.com