Another script question

Using my previous user timesheet script as my template I decided to rewrite a
script that is used to gather the total time spent on each customer over the
previous week. It is currently set up to query the database directly using DBI.
I’ve found that this creates an inaccurate tally probably because our query is
probably incorrect so I’m trying to set it up using the API as I did with the
user timesheet.

However, it doesn’t seem to be giving me the results I expected. It seems
fairly straight forward, but I can’t get it to do anything. I’m using the
following FromSQL query:
$tix->FromSQL(‘Queue = “CustomerCare” AND ((LastUpdated > "’ . $weekStart . ‘"
AND LastUpdated < "’ . $weekEnd . ‘") OR (LastUpdated = "’ . $weekStart . ‘" OR
LastUpdated = "’ . $weekEnd. ‘"))’);

This is actually the same query I used in my user script which is providing the
desired results so I know the query is correct.

This is the block of code I would expect to provide me with the data I’m trying
to manipulate:
while (my $ticket = $tix->Next) {
foreach my $day (@searchDate){
my $env = $ticket->FirstCustomFieldValue(‘Environment’);
$tickets{$env}++;
my $transactions = $ticket->Transactions;
while (my $transaction = $transactions->Next) {
next unless (($transaction->Created == $day) ||
($transaction->TimeTaken));
$envTime{$env} += $transaction->TimeTaken;
}
}
}

@searchDate is populated with 7 dates using the yyyy-mm-dd format found in the
database.

The script will then alter the times (in minutes) to be output in an hh:mm
format as both a total and, using the $tickets{$env} entry, a mean time. It
could very well be that I’m not getting any output because my manipulation block
is wrong. However, the quickness that the script completes its task and returns
a file to me leads me to believe that it isn’t even pulling the data I need. It
also stops outputting data to the file after printing out the first date.

Does the above block look right for what I’m attempting to do?

Mathew

I’ve attached the complete script.

Mathew

Mathew Snyder wrote:

weekly_new.pl (4.93 KB)

At Friday 4/13/2007 05:52 AM, Mathew Snyder wrote:

I’ve attached the complete script.

Mathew

Mathew,

I don’t have time to investigate the whole script, but I wondered if
you’ve looked at the perl debugger to step through the code. I always
find the tool invaluable when I have a script that’s not doing what I expect.

Steve

Nope, haven’t done that. But, I just picked up the Perl Debugger Pocket
Reference so I can try to figure it out :slight_smile:

Mathew

Stephen Turner wrote:

I, more-or-less, have this working. It runs and gathers my data and does all of
its wonderful calculations printing it out to the file I want it to. However, a
strange thing is occurring. When the script runs it keep track of how many
tickets there are for each environment for each day. I use this number to
figure out the rough average time spent on each environment during each day.

I’m finding though, that the script is “finding” more tickets than RT itself
does. For instance, suppose my script finds 3 tickets for Company A on
2007-04-12. It increments the hash: $ticCount{2007-04-12}{Company A} += 1;
Then prints it out at the end. However if I do a search in RT for tickets that
were last updated on that same date for that same environment, I can end up with
fewer tickets or, sometimes, more tickets. In fact, sometimes, they don’t end
up in the final list at all.

I’ve inserted a print statement that will output the date, environment and
ticket number so that I can grep it and find how many times an environment shows
up on a particular day. I just did this and found I already have one
environment that shows up but isn’t even in the final output.

Why might this be happening? Attached is my latest version.

Mathew

Stephen Turner wrote: