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-----------------------------------
