[Rt-devel] Pulling data on ticket transactions - what's the best method?

[Rhys Hanrahan]

Hi,

 

I'm in the process of writing a small script to pull data on ticket
transactions in order to find the time worked for each RT user, per
queue, per ticket, each week.  Once I have all this data I format it
into a CSV file and send out an email with the file attached.  Right now
the script is in PHP, though I could move to Perl if need be, and the
script is running locally on the machine which hosts RT and its
database.

 

Currently, I've got a fairly large SQL statement which filters all of
the ticket transactions for the given date range (amongst other things)
then pulls ticket, queue, user and custom field data from each of the
tables using LEFT JOIN clauses.  The query works great and I've been
able to pull and sort/group all the data how I need it for the CSV.
However, I've read in the mailing list while researching that the DB
schema is set to change significantly as of 4.0; obviously this was
always going to be an issue with any custom code, but I also saw the
REST protocol and I'm now wondering if there's a better method which is
also relatively simple to implement.

 

I had figured that having to learn all of RT's Perl functions would take
a fair bit of investigation and it would be much faster just to query
the DB directly, and it has been fast as I've only spent 2~ hours on it
so far.  I've looked at the REST API and haven't found any way to use it
for searching transactions, only tickets.  I've also seen an RT stats
add-on, but again this is custom code and I want to keep it all as stock
as I can, as I'm the only coder employed here.

 

I need to be able to first and foremost pull the time worked for each
user during the week, not the total time worked for each ticket they
own/request, the only way I can see doing this is querying the
transactions table in some fashion, because I need to know when a user
makes a comment on any ticket with worked time, or sets the worked time
on a ticket.  I'll keep working with the database directly for now, but
if anyone has any suggestions for a solution that I can "set and
forget", I'd love to hear it.

 

Hopefully my needs are clear, but if not, I'm happy to provide the
query, an example of the CSV report, or any other further information.

 

Thanks in advance.

-------------- next part --------------
An HTML attachment was scrubbed...
URL: http://lists.bestpractical.com/pipermail/rt-devel/attachments/20100311/121284fe/attachment.htm