Script to build timeworked report based on CustomField

Hi everyone.

I am pretty new at writing scripts to use with RT. Recently I wrote a
script that I use to build a report on how much time we have spent
working for the different departments in the company I work for. The
departments are set through a RT::Ticket Custom Field.

This is my first RT script so, I’m sure that it needs a lot of
improvements. Feel free to post your thoughts and ideas. I hope that
other in the RT community will also find it useful.

My fire proof suit is on and I look forward to hearing your input.

Jason Fenner

------------------------BEGIN SCRIPT-------------------------

#!/usr/bin/perl

Author: Jason Fenner

Date: 04-05-2006

Email: jfenner@vitamix.com

License: GPL

Description: Creates a report of time worked based on a ticket

custom field named “Department”. Report contains

total time worked and percent of total time per

department.

Note: Please email me with any improvements.

Load all modules we are going to use.

use DBI;
use POSIX(strftime);
use FileHandle;
use strict;
use Date::Manip;
use Math::Round;

I use sendmail here. Adjust this to match your system.

my $sendmail = “/usr/sbin/sendmail -oi -t”;

Set your email settings, these settings are used to create the email

containing the report.
my $reply_to = “Reply-to: noreply@foo.bar.com”;
my $subject = “Subject: RT Department Reporting\n\n”;
my $to = “To: foo@bar.com\n”;
my $cc = “Cc: foo@bar.com,foo@bar.com\n”;
my $from = “From: rt-bot@foo.bar.com\n”;

Define the rest of the variables.

my $i;
my $today = strftime("%a %m/%d/%Y %H:%M:%S",localtime());
my $department;
my $TimeWorkedsql;
my $ttotal;

This line is here for if you want to use a manual start time.

#my $startdate = “2006-03-13 00:00:00”;

my $sdate = DateCalc(“today”,"- 1 month",\ my $err);
my $bdate = &UnixDate("$sdate","%Y:%m:%d");
my $parsedate = ParseDate("$bdate");
my $startdate = Date_ConvTZ($parsedate,"",“GMT”);
my $refdate = &UnixDate("$sdate","%a %m/%d/%Y %H:%M:%S");
undef $/;
my $percent;
my $prepercent;
my $percentTotal;
my $percentTotalf;

$= = 200000;

Define our report format

format STDOUT_TOP =

                         Department Time Worked Report
                                 Foo-Bar RT

                      Generated on: @<<<<<<<<<<<<<<<<<<<
                      $today

@<<<<<<<<<<<<<<<<<<<<<<<
$refdate,$today
Department Time Worked %
of Total
.

format STDOUT=
@<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<< @<<<<<<<<<< @<<%
$department,$TimeWorkedsql,$percent
.

format FOOTER=

Total number of departments worked for: @<<<<<< Total Percentage: @<<<%
$i,$percentTotalf
Total time spent for departments: @<<<<<<
$ttotal

.

Open cache file for writing

open(TEMP, “>/tmp/testtemp.text”) or die “Can’t open temp: $!”;

SQL Database Access time.

Note: You need to edit the line below here with your db’s username and

password.
my $dbh = DBI->connect(“dbi:mysql:rt3”,“dbuser”,“dbpassword”);
my $sth = $dbh->prepare_cached('select CustomFieldValues.Name,
sum(Transactions.TimeTaken)+sum(if(Transactions.Field =
“TimeWorked”,Transactions.NewValue,0)) AS TotalTime from Transactions
INNER JOIN CustomFieldValues ON CustomFieldValues.Name =
ObjectCustomFieldValues.Content INNER JOIN ObjectCustomFieldValues ON
ObjectCustomFieldValues.ObjectId = Transactions.ObjectId INNER JOIN
Tickets ON Tickets.id = Transactions.ObjectId where Transactions.Created

? AND ObjectCustomFieldValues.CustomField =2 GROUP BY
CustomFieldValues.Name;’) or die "Couldn’t prepare statement: " .
$dbh->errstr;

$sth->execute($startdate) or die "Couldn’t execute statement: " .
$sth->errstr;

TEMP->format_name(“STDOUT”);
TEMP->format_top_name(“STDOUT_TOP”);
TEMP->format_lines_per_page(20000);

#Read our results

my @datafirst;

Note: Below, I run the SQL query twice. I think this is not needed

and slows the script down quite a bit. If you figure out how not to run it

twice, please let me know.

while (@datafirst = $sth->fetchrow_array()) {
#print $datafirst[0] . “\n”;
#print $datafirst[1] . “\n”;
$TimeWorkedsql = $datafirst[1];
$ttotal = $ttotal + $TimeWorkedsql;
}

#print “Total was: $ttotal\n”;

$TimeWorkedsql = 0;
#$ttotal = 0;

$sth->execute($startdate) or die “Couldn’t execute statement: " .
$sth->errstr;
my @data;
while (@data = $sth->fetchrow_array()) {
$i++;
$department = $data[0];
$TimeWorkedsql = $data[1];
$prepercent = $TimeWorkedsql / $ttotal;
$prepercent = $prepercent * 100;
$percentTotal = $percentTotal + $prepercent;
$percent = (round($prepercent));
chomp;
#$ttotal = $ttotal + $TimeWorkedsql;
#$content = $content.”\t$i: $name\t$address\n";
write(TEMP);
}

$percentTotalf = (round($percentTotal));

TEMP->format_name(“FOOTER”);
write(TEMP);
close(TEMP);

$dbh->disconnect;

Suck our cache file in

open(TEMP, “/tmp/testtemp.text”) or die “Can’t open temp: $!”;
my $content = ;

Send the report via email

open(SENDMAIL, “|$sendmail”) or die “Cannot open $sendmail: $!”;
print SENDMAIL $from;
print SENDMAIL $to;
print SENDMAIL $cc;
print SENDMAIL $subject;
print SENDMAIL $content;
close(SENDMAIL);
close(TEMP);

#print $to;
#print “Message sent!\n”;

------------------------------------END
SCRIPT-----------------------------------

Jason Fenner wrote:

use POSIX(strftime);

Be careful! RT uses a default PERL package called CTime that includes
its own versions of strftime, localtime, etc.

In my own scrips, I found that even if I specified

use POSIX;

up front, RT would occasionally pull the functions from CTime instead of
POSIX, and produce garbage as a result. localtime() seems to work
differently between the two packages.

For safety, I suggest using explicit function calls like

POSIX::strftime(argument)

and

POSIX::localtime()

Rick R.

rickr.vcf (182 Bytes)

Jason Fenner wrote:

use POSIX(strftime);

Be careful! RT uses a default PERL package called CTime that includes
its own versions of strftime, localtime, etc.

Huh?

Hi everyone.

I am pretty new at writing scripts to use with RT. Recently I wrote a
script that I use to build a report on how much time we have spent
working for the different departments in the company I work for. The
departments are set through a RT::Ticket Custom Field.

This is my first RT script so, I’m sure that it needs a lot of
improvements. Feel free to post your thoughts and ideas. I hope that
other in the RT community will also find it useful.

My fire proof suit is on and I look forward to hearing your input.

Jason Fenner

If you don’t want to access the sql database directly, you may also use
some of the modules which come with RT. This could also be useful in case,
the DB structure is changed in the future.
You can find some example code in $rtroot/bin/.
The modules are also well documented.
eg:
cd $rtroot/lib/RT
perldoc Tickets_Overlay

hth,
andreas

Very good idea to incorporate in a future version. Excellent point in
regards to keeping version portability.

Thank You for your input.

Andreas Putzo wrote:>On Wednesday 05 April 2006 20:37, Jason Fenner wrote:

Hi everyone.

I am pretty new at writing scripts to use with RT. Recently I wrote a
script that I use to build a report on how much time we have spent
working for the different departments in the company I work for. The
departments are set through a RT::Ticket Custom Field.

This is my first RT script so, I’m sure that it needs a lot of
improvements. Feel free to post your thoughts and ideas. I hope that
other in the RT community will also find it useful.

My fire proof suit is on and I look forward to hearing your input.

Jason Fenner

If you don’t want to access the sql database directly, you may also use
some of the modules which come with RT. This could also be useful in case,
the DB structure is changed in the future.
You can find some example code in $rtroot/bin/.
The modules are also well documented.
eg:
cd $rtroot/lib/RT
perldoc Tickets_Overlay

hth,
andreas

Jesse Vincent wrote:> On Wed, Apr 05, 2006 at 03:32:12PM -0500, Rick Russell wrote:

Jason Fenner wrote:

use POSIX(strftime);
Be careful! RT uses a default PERL package called CTime that includes
its own versions of strftime, localtime, etc.
Huh?

Sorry for the “huh”-worthy comment :slight_smile:

When I attempted to use common time functions like localtime & strftime
in my custom scripts, I found that I was getting unpredictable results.
Careful debugging revealed the strftime was rarely barfing with an error
because the output of localtime was not what it expected.

I suspect that the problem was a conflict between CTime & POSIX modules:

http://search.cpan.org/~nwclark/perl-5.8.8/ext/POSIX/POSIX.pod

http://search.cpan.org/~muir/Time-modules-2003.1126/lib/Time/CTime.pm

which has a version of localtime that returns a different data structure
than POSIX localtime. Or maybe it was pulling the wrong version of
strftime instead.

Is RT dependent on CTime? I’m not really sure. The POSIX module is a
default for sure.

Either way, the problem went away when I referred to the functions
explicitly as POSIX::localtime & POSIX::strftime.

BTW, I read RT Essentials. I liked it, but I wish there was more
coverage of scripting and the various data structures in RT. Figuring
out what data structures are associated with tickets & transactions is
often an exercise in trial-and-error.

Rick R.

rickr.vcf (182 Bytes)

Did someone ever put that online?

Joe Szabo
CCF Netops
Rutgers University

Did someone ever put that online?

Joe Szabo
CCF Netops
Rutgers University

I have not yet. I realized that I had built all my rpms for x86_64 arch
and I was in the process of rebuilding them for i386 when I got
distracted. If you would like them, I’ll try to get them posted
somewhere in the next couple of days.

Alternatively, I could just send you the spec files and you could build
them too, although that is a little less fun/easy than just installing
an rpm.

I wouldn’t mind seeing the spec file. What version of rt is it for? You
didn’t get the “Can’t locate Apache2.pm in @INC” message in
/var/log/apache2/error_log? Does the spec file list the versions of all
the modules?

JoeOn Tue, 18 Apr 2006, Joshua Colson wrote:

On Apr 18, 2006, at 9:12 AM, joe wrote:

Did someone ever put that online?

Joe Szabo
CCF Netops
Rutgers University

I have not yet. I realized that I had built all my rpms for x86_64 arch
and I was in the process of rebuilding them for i386 when I got
distracted. If you would like them, I’ll try to get them posted
somewhere in the next couple of days.

Alternatively, I could just send you the spec files and you could build
them too, although that is a little less fun/easy than just installing
an rpm.

I wouldn’t mind seeing the spec file. What version of rt is it for? You
didn’t get the “Can’t locate Apache2.pm in @INC” message in
/var/log/apache2/error_log? Does the spec file list the versions of all
the modules?

Joe

It is a bunch of spec files. Actually, I don’t think that I built any
modules for Apache2. I’m running on a dev box with the standalone_httpd.
I’m attaching a tar of the spec files that I built for 3.6.0pre0.

Joshua Colson jcolson@voidgate.org

rt-3.6.0pre0-perl-deps-rpm-specs.tar.bz2 (11.8 KB)

I’m sorry. Is standalone_httpd a suse rpm? Or are you just saying you
compiled apache 1 on your own?

JoeOn Tue, 18 Apr 2006, Joshua Colson wrote:

It is a bunch of spec files. Actually, I don’t think that I built any
modules for Apache2. I’m running on a dev box with the standalone_httpd.
I’m attaching a tar of the spec files that I built for 3.6.0pre0.


Joshua Colson jcolson@voidgate.org

I’m sorry. Is standalone_httpd a suse rpm? Or are you just saying you
standalone_httpd is shipped with RT and used for development it could
serve only one person at a time.

compiled apache 1 on your own?

Joe

It is a bunch of spec files. Actually, I don’t think that I built any
modules for Apache2. I’m running on a dev box with the standalone_httpd.
I’m attaching a tar of the spec files that I built for 3.6.0pre0.


Joshua Colson jcolson@voidgate.org


http://lists.bestpractical.com/cgi-bin/mailman/listinfo/rt-users

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

We’re hiring! Come hack Perl for Best Practical: http://bestpractical.com/about/jobs.html

Best regards, Ruslan.

I’m sorry. Is standalone_httpd a suse rpm? Or are you just saying you
compiled apache 1 on your own?

Joe

standalone_httpd is a perl script distributed with RT that uses a perl
module to act as a simple HTTP server (just like apache, but extremely
simple). Using the standalone_httpd there is no need for apache,
although that is just for testing purposes and in a production
environment I would use apache.

Thanks.

Joshua Colson jcolson@voidgate.org