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

[Rhys Hanrahan]

Hi all,

First off, many thanks to you Allen for your direction to the original
script, it helped me greatly in achieving my goal.  I've attached (I
hope correctly - post is over limit, will attach in following message)
my own version of the script, which I've added a few extra features to -
namely a CSV report which can be emailed, auto generation of dates and
support for a ticket custom field which contains customer names.

Why I'm writing here again is because I found a problem scenario in the
reporting.  Right now I've been pulling the queue ID from the ticket's
entry in the Ticket table.  That's fine in some cases, but for us, when
we're done with a ticket, it then goes (still open) into a pending
billable queue, it then moves about till it's done being processed by
accounting, and is resolved.

Essentially, this means that for a lot of the jobs we perform over the
previous month is sitting in one of the billable queues, meaning that
all these jobs show up as in those queues.  However, this is no good for
our reporting as it prevents any proper analysis based on time spent on
different areas.  Right now, we're having to go through and change the
queue to what we think it should be before we generate any reports from
the CSV file.

What I've decided to do is this: when the script goes to get the queue
ID in the stats hash it checks the Queue ID of that ticket's record, if
it matches one of the "billQueue"s (checked against an array in the
script) it then uses the SearchBuilder to pull a new set of
transactions.  Otherwise it simply pulls the Queue ID from the Ticket
table record.

These are any transactions where the Queue has been set for on a ticket
with that ticket's ID, it also checks to make sure that the OldValue in
the transaction is not that of a billable queue.  It sorts them by date
of transaction to pull the latest change found, then uses the OldValue
in that transaction. 

The idea being that one would have a ticket in a queue and possibly
change it many times, then when done put it in the billable queue, the
query will pick that last transaction where the queue was changed from
some non-billable queue to another queue, which according to our
protocol, will always be a billable queue.

If a transaction of that sort can't be found, it obviously just has to
fall back on the current queue - which would have to be a billable
queue.  The only way I can see this happening is if for whatever reason
someone creates a new ticket directly in a billable queue - it's
happened once already.  This sounds fine in theory to me, but it's not
working properly for whatever reason.

I did initially have it partly working, but the script would throw
critical errors when trying to call OldValue on what I assume was an
invalid transaction object, but did work part way through till it got to
one.  Before I put in the extra if (defined $nq) checks, the queue ID's
were being replaced properly, but now it seems as though it is _always_
falling back on the queue listed in the ticket table - which for these
tickets is a billable queue.

I have a feeling this is simply a case of knowing the correct sanity
check to use to prevent the use of invalid objects, but strangely, the
Count field on the transaction list seems to always return 1, but then
$nq seems to be always undefined.  What's more, I seem to get a slew of
"Use of uninitialised value" errors within the "CurrentUserCanSee" sub
in Transaction_Overlay.pm file.  I'm assuming this is a problem with my
code, not RT's - and I want to steer clear of hacking RT code to stop
errors.

I'm hoping that someone can point out what I need to be doing here,
though I probably won't get another chance to look at it myself to debug
it till the weekend, I'll gladly take the time to try something out or
provide more info if someone posts.  Any optimisations to the script are
also welcome, I know much of it can be improved, but I don't want to
take the time to re-think the solution as it's only run once a month, so
optimisation is not critical.

Since this message has turned out to be big, I've decided to attach the
snippet of the errors I'm getting instead of pasting them here.  The
pattern seems to be the same throughout: the warnings occur any time a
ticket is processed in which it needs to look up additional transactions
to find an old queue ID, additionally, the T COUNT always seems to be 1,
and the "Using" and "Current" queue debug entries always seem to be the
same.

Thanks all!

Ps.  If anyone needs me to paraphrase this message, perhaps to bullet
point form, I'm happy to.  But I always find that more detail is better
in technical situations.

-----Original Message-----
From: rt-devel-bounces at lists.bestpractical.com
[mailto:rt-devel-bounces at lists.bestpractical.com] On Behalf Of Rhys
Hanrahan
Sent: Saturday, 13 March 2010 4:58 PM
To: Allen; rt-devel at lists.bestpractical.com
Subject: Re: [Rt-devel] Pulling data on ticket transactions - what's
thebest method?

Looks very promising, thanks for this!  I'm hoping that RT's external
methods and properties won't change much in the future.

I'm also a little concerned about the prolonged execution time.  This
seems to take a couple of seconds to execute on my RT machine, compared
to the instantaneous page load (viewing via LAN) of the PHP script.  I'm
hoping that by adding extra limit()'s I can reduce the transactions
result set, and hence, execution time.

-----Original Message-----
From: Allen [mailto:allen+rtlist at crystalfontz.com]
Sent: Friday, 12 March 2010 8:51 AM
To: Rhys Hanrahan; rt-devel at lists.bestpractical.com
Subject: Re: [Rt-devel] Pulling data on ticket transactions - what's the
best method?

> I'm in the process of writing a small script to pull data on ticket
transactions in order to find the time worked

Have you looked at the timeworked.pl , Version 0.03  2009-08-21, by Fran
Fabrizio, UAB CIS, and Drew Barnes that reports minutes worked per
person per ticket in a given time period for one or more queues?

They released it on the mailing list last year. It uses standard DBI
searchbuilder querying to find the transactions. No hitting the database
directly.

A
_______________________________________________
List info:
http://lists.bestpractical.com/cgi-bin/mailman/listinfo/rt-devel
-------------- next part --------------
An embedded and charset-unspecified text was scrubbed...
Name: RT-Script-Errors.txt
URL: <http://lists.bestpractical.com/pipermail/rt-devel/attachments/20100409/904573f7/attachment.txt>