Contributed script: A time worked reporting tool

Fran_Fabrizio · August 21, 2009, 4:48pm

I’ve attached a script I’ve developed over the summer to help me produce
time worked reports for my staff. Its features are:

FEATURES

- Produces a simple text report of the number of minutes worked per

person per ticket in a given datetime range for all tickets that

experienced > 0 minutes worked in that time period

- Allows user to specify one or more queues to include in the ticket

search

- Assumes start and end datetimes entered in local time and adjusts for

GMT which is RT’s internal representation (but see --gmtime flag)

- Correctly calculates time one person puts onto another person’s ticket

- Correctly calculates TimeWorked when directly updated via the Basics or

Jumbo ticket update page as well as when added to a comment or reply

- Optional --debug mode for verbose output

- Basic --help text display

- Uses RT’s API (does not query database directly)

The script is pretty well documented so most questions should be
answered within. For usage, run “timeworked.pl --help”. There is one
line within the script, the “use lib” line, that may need to be edited
before it’ll work for you - I’ve noted this in the script documentation.

I’d certainly like to get feedback on the script, to see if this is
useful for others. It also could use more validation/testing - I’ve
been using and developing it for a while and I think it’s doing pretty
well now, but I only have so much data and so many scenarios to test
against in my own RT install. Also, in the interest of full disclosure,
I should point out that it’s my first RT API script. Comments and
suggestions very welcome.

Thanks,
Fran

Fran Fabrizio
Senior Systems Analyst
Department of Computer and Information Sciences
University of Alabama at Birmingham

205.934.0653

timeworked.pl (16.4 KB)

Drew_Barnes · August 21, 2009, 5:42pm

To second Fran, I have also been using this script in production for a while
now and have found it to be a good reporting tool for management.

We use it both on the command line, and with a wrapper to fit RT’s look and
feel. If needed, I would be happy to share that off list.On 8/21/09 12:48 PM, “Fran Fabrizio” fran@cis.uab.edu wrote:

I’ve attached a script I’ve developed over the summer to help me produce
time worked reports for my staff. Its features are:

FEATURES

- Produces a simple text report of the number of minutes worked per

person per ticket in a given datetime range for all tickets that

experienced > 0 minutes worked in that time period

- Allows user to specify one or more queues to include in the ticket

search

- Assumes start and end datetimes entered in local time and adjusts for

GMT which is RT’s internal representation (but see --gmtime flag)

- Correctly calculates time one person puts onto another person’s ticket

- Correctly calculates TimeWorked when directly updated via the Basics or

Jumbo ticket update page as well as when added to a comment or reply

- Optional --debug mode for verbose output

- Basic --help text display

- Uses RT’s API (does not query database directly)

The script is pretty well documented so most questions should be
answered within. For usage, run “timeworked.pl --help”. There is one
line within the script, the “use lib” line, that may need to be edited
before it’ll work for you - I’ve noted this in the script documentation.

I’d certainly like to get feedback on the script, to see if this is
useful for others. It also could use more validation/testing - I’ve
been using and developing it for a while and I think it’s doing pretty
well now, but I only have so much data and so many scenarios to test
against in my own RT install. Also, in the interest of full disclosure,
I should point out that it’s my first RT API script. Comments and
suggestions very welcome.

Thanks,
Fran

Drew Barnes
Applications Analyst
Network Resources Dept.
Raymond Walters College

Kenn_Crocker · August 21, 2009, 5:49pm

Drew, Fran,

Is there anything unique to this scrip per RT version? In otherwords,
will it work on 3.6.4 as well as 3.8.4?

Kenn
LBNLOn 8/21/2009 10:42 AM, Drew Barnes wrote:

To second Fran, I have also been using this script in production for a while
now and have found it to be a good reporting tool for management.

We use it both on the command line, and with a wrapper to fit RT’s look and
feel. If needed, I would be happy to share that off list.

On 8/21/09 12:48 PM, “Fran Fabrizio” fran@cis.uab.edu wrote:

I’ve attached a script I’ve developed over the summer to help me produce
time worked reports for my staff. Its features are:

FEATURES

- Produces a simple text report of the number of minutes worked per

person per ticket in a given datetime range for all tickets that

experienced > 0 minutes worked in that time period

- Allows user to specify one or more queues to include in the ticket

search

- Assumes start and end datetimes entered in local time and adjusts for

GMT which is RT’s internal representation (but see --gmtime flag)

- Correctly calculates time one person puts onto another person’s ticket

- Correctly calculates TimeWorked when directly updated via the Basics or

Jumbo ticket update page as well as when added to a comment or reply

- Optional --debug mode for verbose output

- Basic --help text display

- Uses RT’s API (does not query database directly)

The script is pretty well documented so most questions should be
answered within. For usage, run “timeworked.pl --help”. There is one
line within the script, the “use lib” line, that may need to be edited
before it’ll work for you - I’ve noted this in the script documentation.

I’d certainly like to get feedback on the script, to see if this is
useful for others. It also could use more validation/testing - I’ve
been using and developing it for a while and I think it’s doing pretty
well now, but I only have so much data and so many scenarios to test
against in my own RT install. Also, in the interest of full disclosure,
I should point out that it’s my first RT API script. Comments and
suggestions very welcome.

Thanks,
Fran

Diona_Kidd1 · August 21, 2009, 5:54pm

Drew, I’m interested in viewing your work. Could you catch up with me off
list and share what you’ve done?

Thanks,

Diona

Allen · August 21, 2009, 6:24pm

I’ve attached a script I’ve developed over the summer to help me produce
time worked reports for my staff.

Thanks for this. It’s very nice.

It did put out a warning on my system, though:
[Fri Aug 21 18:30:08 2009] [warning]: Use of uninitialized value in
string eq at ./rt-timeworked.pl line 273. (./rt-timeworked.pl:273)

Looks like this line: if (($tr->TimeTaken != 0) || ($tr->Field eq
‘TimeWorked’)) {

Thank you!

A

Fran_Fabrizio · August 21, 2009, 7:28pm

Ken> I didn’t test on earlier versions, but I suspect that it’s pretty
safe throughout the 3.x series, because the only things it really
depends on are a few fields in the Transactions table that I think have
all been around a long time (OldValue, NewValue, TimeTaken, ObjectID,
etc…) and things from the Ticket object that have been around even
longer (OwnerID, etc…) Maybe one of the developers can comment about
how far back you’d have to go before one of those isn’t around.

Allen> I’ll work with you off-list to patch up the warning you are
seeing, thanks for reporting.

Drew> I’d like to see what you’ve done to integrate it into the RT
interface - can you share that off-list with me?

Thanks all,
Fran

Ken Crocker wrote:

Drew, Fran,

Is there anything unique to this scrip per RT version? In otherwords,
will it work on 3.6.4 as well as 3.8.4?

Kenn
LBNL

To second Fran, I have also been using this script in production for a while
now and have found it to be a good reporting tool for management.

We use it both on the command line, and with a wrapper to fit RT’s look and
feel. If needed, I would be happy to share that off list.

I’ve attached a script I’ve developed over the summer to help me produce
time worked reports for my staff. Its features are:

FEATURES

- Produces a simple text report of the number of minutes worked per

person per ticket in a given datetime range for all tickets that

experienced > 0 minutes worked in that time period

- Allows user to specify one or more queues to include in the ticket

search

- Assumes start and end datetimes entered in local time and adjusts for

GMT which is RT’s internal representation (but see --gmtime flag)

- Correctly calculates time one person puts onto another person’s ticket

- Correctly calculates TimeWorked when directly updated via the Basics or

Jumbo ticket update page as well as when added to a comment or reply

- Optional --debug mode for verbose output

- Basic --help text display

- Uses RT’s API (does not query database directly)

The script is pretty well documented so most questions should be
answered within. For usage, run “timeworked.pl --help”. There is one
line within the script, the “use lib” line, that may need to be edited
before it’ll work for you - I’ve noted this in the script documentation.

I’d certainly like to get feedback on the script, to see if this is
useful for others. It also could use more validation/testing - I’ve
been using and developing it for a while and I think it’s doing pretty
well now, but I only have so much data and so many scenarios to test
against in my own RT install. Also, in the interest of full disclosure,
I should point out that it’s my first RT API script. Comments and
suggestions very welcome.

Thanks,
Fran

Fran Fabrizio
Senior Systems Analyst
Department of Computer and Information Sciences
University of Alabama at Birmingham

205.934.0653

Allen_Lee · August 21, 2009, 11:33pm

$ perl -e ‘use lib “/usr/share/request-tracker3.8/lib”; use
RT::Transaction; print $RT::VERSION’
3.8.4

I think you are right that the warning is due to comparing string
against what is in the DB as NULL.

This fixed it for me:

Line 273:

if (($tr->TimeTaken != 0) || ($tr->Field eq ‘TimeWorked’)) {

if (($tr->TimeTaken != 0) || ($tr->Field && $tr->Field eq ‘TimeWorked’)) {

Thank you again!

Allen Lee
Crystalfontz America, Incorporated
12412 East Saltese Avenue
Spokane Valley, WA 99216-0357
Twitter @Crystalfontz allen@crystalfontz.com http://www.crystalfontz.com
voice (509) 892-1200 fax (509) 892-1203 US toll-free (888) 206-9720On Fri, Aug 21, 2009 at 1:09 PM, Fran Fabriziofran@cis.uab.edu wrote:

Ok so it is complaining about an uninitialized value in the eq comparison
$tr->Field eq “TimeWorked”. Some transactions have NULL in the Field field
in the database. The question is when there is NULL in the database, what
does RT::Transaction object return? Maybe it’s trying undef eq “TimeWorked”
and throwing the warning. The question is, why don’t I see those? What
version of RT.pm are you using?

What does:

perl -e ‘use lib “/usr/local/rt/lib”; use RT::Transaction; print
$RT::VERSION’

show? (Of course, updating the /usr/local/rt/lib path).

Maybe (off the wall, but…) an older version of the RT::Transaction
returns undef when NULL is read from the database, but the one I am using
(3.8.2) returns ‘’ or something?

Allen Lee wrote:

Hi,

The script still worked fine.

It looked like it was throwing that warning for every iteration of the
loop. Below, it finds 1 tickets with Time Worked filled in, but more
than 24 warnings are thrown:

rt-timeworked.pl --startdate 2009-08-15 --enddate “2009-08-22” --queues

testy
[Fri Aug 21 19:54:28 2009] [warning]: Use of uninitialized value in
string eq at /usr/sbin/rt-timeworked.pl line 273.
(/usr/sbin/rt-timeworked.pl:273)
[Fri Aug 21 19:54:28 2009] [warning]: Use of uninitialized value in
string eq at /usr/sbin/rt-timeworked.pl line 273.
(/usr/sbin/rt-timeworked.pl:273)
[Fri Aug 21 19:54:28 2009] [warning]: Use of uninitialized value in
string eq at /usr/sbin/rt-timeworked.pl line 273.
(/usr/sbin/rt-timeworked.pl:273)
[Fri Aug 21 19:54:28 2009] [warning]: Use of uninitialized value in
string eq at /usr/sbin/rt-timeworked.pl line 273.
(/usr/sbin/rt-timeworked.pl:273)
[Fri Aug 21 19:54:28 2009] [warning]: Use of uninitialized value in
string eq at /usr/sbin/rt-timeworked.pl line 273.
(/usr/sbin/rt-timeworked.pl:273)
[Fri Aug 21 19:54:28 2009] [warning]: Use of uninitialized value in
string eq at /usr/sbin/rt-timeworked.pl line 273.
(/usr/sbin/rt-timeworked.pl:273)
[Fri Aug 21 19:54:28 2009] [warning]: Use of uninitialized value in
string eq at /usr/sbin/rt-timeworked.pl line 273.
(/usr/sbin/rt-timeworked.pl:273)
[Fri Aug 21 19:54:28 2009] [warning]: Use of uninitialized value in
string eq at /usr/sbin/rt-timeworked.pl line 273.
(/usr/sbin/rt-timeworked.pl:273)
[Fri Aug 21 19:54:28 2009] [warning]: Use of uninitialized value in
string eq at /usr/sbin/rt-timeworked.pl line 273.
(/usr/sbin/rt-timeworked.pl:273)
[Fri Aug 21 19:54:28 2009] [warning]: Use of uninitialized value in
string eq at /usr/sbin/rt-timeworked.pl line 273.
(/usr/sbin/rt-timeworked.pl:273)
[Fri Aug 21 19:54:28 2009] [warning]: Use of uninitialized value in
string eq at /usr/sbin/rt-timeworked.pl line 273.
(/usr/sbin/rt-timeworked.pl:273)
[Fri Aug 21 19:54:28 2009] [warning]: Use of uninitialized value in
string eq at /usr/sbin/rt-timeworked.pl line 273.
(/usr/sbin/rt-timeworked.pl:273)
[Fri Aug 21 19:54:28 2009] [warning]: Use of uninitialized value in
string eq at /usr/sbin/rt-timeworked.pl line 273.
(/usr/sbin/rt-timeworked.pl:273)
[Fri Aug 21 19:54:28 2009] [warning]: Use of uninitialized value in
string eq at /usr/sbin/rt-timeworked.pl line 273.
(/usr/sbin/rt-timeworked.pl:273)
[Fri Aug 21 19:54:28 2009] [warning]: Use of uninitialized value in
string eq at /usr/sbin/rt-timeworked.pl line 273.
(/usr/sbin/rt-timeworked.pl:273)
[Fri Aug 21 19:54:28 2009] [warning]: Use of uninitialized value in
string eq at /usr/sbin/rt-timeworked.pl line 273.
(/usr/sbin/rt-timeworked.pl:273)
[Fri Aug 21 19:54:28 2009] [warning]: Use of uninitialized value in
string eq at /usr/sbin/rt-timeworked.pl line 273.
(/usr/sbin/rt-timeworked.pl:273)
[Fri Aug 21 19:54:28 2009] [warning]: Use of uninitialized value in
string eq at /usr/sbin/rt-timeworked.pl line 273.
(/usr/sbin/rt-timeworked.pl:273)
[Fri Aug 21 19:54:28 2009] [warning]: Use of uninitialized value in
string eq at /usr/sbin/rt-timeworked.pl line 273.
(/usr/sbin/rt-timeworked.pl:273)
[Fri Aug 21 19:54:28 2009] [warning]: Use of uninitialized value in
string eq at /usr/sbin/rt-timeworked.pl line 273.
(/usr/sbin/rt-timeworked.pl:273)
[Fri Aug 21 19:54:28 2009] [warning]: Use of uninitialized value in
string eq at /usr/sbin/rt-timeworked.pl line 273.
(/usr/sbin/rt-timeworked.pl:273)
[Fri Aug 21 19:54:28 2009] [warning]: Use of uninitialized value in
string eq at /usr/sbin/rt-timeworked.pl line 273.
(/usr/sbin/rt-timeworked.pl:273)
[Fri Aug 21 19:54:28 2009] [warning]: Use of uninitialized value in
string eq at /usr/sbin/rt-timeworked.pl line 273.
(/usr/sbin/rt-timeworked.pl:273)
[Fri Aug 21 19:54:28 2009] [warning]: Use of uninitialized value in
string eq at /usr/sbin/rt-timeworked.pl line 273.
(/usr/sbin/rt-timeworked.pl:273)

***** TIME WORKED REPORT FOR PERIOD 2009-08-15 TO 2009-08-22 *****

PERSON: allen
MINUTES HOURS TICKET
12 0.2 11261:
-------- -------- TOTALS
12 0.2

for that ticket found above (the only ticket in that “testy” queue),
these are the rows in the Transactions table for it:

mysql> select * from Transactions where ObjectId = 11261 and
ObjectType = ‘RT::Ticket’\G
*************************** 1. row ***************************
id: 137323
ObjectType: RT::Ticket
ObjectId: 11261
TimeTaken: 0
Type: Create
Field: NULL
OldValue: NULL
NewValue: NULL
ReferenceType: NULL
OldReference: NULL
NewReference: NULL
Data: NULL
Creator: 22
Created: 2009-08-21 19:53:48
*************************** 2. row ***************************
id: 137324
ObjectType: RT::Ticket
ObjectId: 11261
TimeTaken: 0
Type: EmailRecord
Field: NULL
OldValue: NULL
NewValue: NULL
ReferenceType: NULL
OldReference: NULL
NewReference: NULL
Data: rt-3.8.4-12525-1250884428-729.11261-3-0@crystalfontz.com
Creator: 1
Created: 2009-08-21 19:53:48
*************************** 3. row ***************************
id: 137325
ObjectType: RT::Ticket
ObjectId: 11261
TimeTaken: 0
Type: Set
Field: TimeWorked
OldValue: 0
NewValue: 12
ReferenceType: NULL
OldReference: NULL
NewReference: NULL
Data: NULL
Creator: 22
Created: 2009-08-21 19:54:06
3 rows in set (0.00 sec)

I put only 1 time entry for that ticket in the “Basics” screen.

Is this the info you are looking for?

Allen Lee
Crystalfontz America, Incorporated
12412 East Saltese Avenue
Spokane Valley, WA 99216-0357
Twitter @Crystalfontz allen@crystalfontz.com http://www.crystalfontz.com
voice (509) 892-1200 fax (509) 892-1203 US toll-free (888) 206-9720

On Fri, Aug 21, 2009 at 12:30 PM, Fran Fabrizio fran@cis.uab.edu wrote:

Did the script still work for you or did it produce garbage?

This is a bit of a weird line to throw that warning, because it might
mean that the Transaction object doesn’t have a TimeTaken field. What
version are you on, and can you poke your head into your database and see
what the schema looks like for the transaction table?

Thanks,
Fran

Allen wrote:

I’ve attached a script I’ve developed over the summer to help me
produce
time worked reports for my staff.

Thanks for this. It’s very nice.

It did put out a warning on my system, though:
[Fri Aug 21 18:30:08 2009] [warning]: Use of uninitialized value in
string eq at ./rt-timeworked.pl line 273. (./rt-timeworked.pl:273)

Looks like this line: if (($tr->TimeTaken != 0) || ($tr->Field eq
‘TimeWorked’)) {

Thank you!

A

–
Fran Fabrizio
Senior Systems Analyst
Department of Computer and Information Sciences
University of Alabama at Birmingham
http://www.cis.uab.edu/
205.934.0653