Problems with script

RT Users
1

I’m getting all kinds of errors:

DBD::mysql::st execute failed: Incorrect information in file:
‘./rt3_devel/Users.frm’ at
/usr/lib/perl5/vendor_perl/5.8.8/DBIx/SearchBuilder/Handle.pm line 505.
RT::Handle=HASH(0x99e5a80) couldn’t execute the query ‘SELECT * FROM Users
WHERE Name = ?’ at /usr/lib/perl5/vendor_perl/5.8.8/DBIx/SearchBuilder/Handle.pm
line 518
DBIx::SearchBuilder::Handle::SimpleQuery(‘RT::Handle=HASH(0x99e5a80)’,
‘SELECT * FROM Users WHERE Name = ?’, ‘RT_System’) called at
/usr/lib/perl5/vendor_perl/5.8.8/DBIx/SearchBuilder/Record.pm line 1208

DBIx::SearchBuilder::Record::_LoadFromSQL(‘RT::CurrentUser=HASH(0x99be9ec)’,
‘SELECT * FROM Users WHERE Name = ?’, ‘RT_System’) called at
/usr/lib/perl5/vendor_perl/5.8.8/DBIx/SearchBuilder/Record.pm line 1127

DBIx::SearchBuilder::Record::LoadByCols(‘RT::CurrentUser=HASH(0x99be9ec)’,
‘Name’, ‘RT_System’) called at
/usr/lib/perl5/vendor_perl/5.8.8/DBIx/SearchBuilder/Record/Cachable.pm line 119

DBIx::SearchBuilder::Record::Cachable::LoadByCols(‘RT::CurrentUser=HASH(0x99be9ec)’,
‘Name’, ‘RT_System’) called at /usr/local/rt-3.6.3/lib/RT/Record.pm line 395
RT::Record::LoadByCols(‘RT::CurrentUser=HASH(0x99be9ec)’, ‘Name’,
‘RT_System’) called at
/usr/lib/perl5/vendor_perl/5.8.8/DBIx/SearchBuilder/Record.pm line 1072

DBIx::SearchBuilder::Record::LoadByCol(‘RT::CurrentUser=HASH(0x99be9ec)’,
‘Name’, ‘RT_System’) called at /usr/local/rt-3.6.3/lib/RT/CurrentUser.pm line 253
RT::CurrentUser::LoadByName(‘RT::CurrentUser=HASH(0x99be9ec)’,
‘RT_System’) called at /usr/local/rt-3.6.3/lib/RT.pm line 181
RT::Init() called at ./user_timesheet.pl line 13
DBD::mysql::st execute failed: Incorrect information in file:
‘./rt3_devel/Users.frm’ at
/usr/lib/perl5/vendor_perl/5.8.8/DBIx/SearchBuilder/Handle.pm line 505.
RT::Handle=HASH(0x99e5a80) couldn’t execute the query ‘SELECT * FROM Users
WHERE Name = ?’ at /usr/lib/perl5/vendor_perl/5.8.8/DBIx/SearchBuilder/Handle.pm
line 518
DBIx::SearchBuilder::Handle::SimpleQuery(‘RT::Handle=HASH(0x99e5a80)’,
‘SELECT * FROM Users WHERE Name = ?’, ‘Nobody’) called at
/usr/lib/perl5/vendor_perl/5.8.8/DBIx/SearchBuilder/Record.pm line 1208

DBIx::SearchBuilder::Record::_LoadFromSQL(‘RT::CurrentUser=HASH(0x99ecf20)’,
‘SELECT * FROM Users WHERE Name = ?’, ‘Nobody’) called at
/usr/lib/perl5/vendor_perl/5.8.8/DBIx/SearchBuilder/Record.pm line 1127

DBIx::SearchBuilder::Record::LoadByCols(‘RT::CurrentUser=HASH(0x99ecf20)’,
‘Name’, ‘Nobody’) called at
/usr/lib/perl5/vendor_perl/5.8.8/DBIx/SearchBuilder/Record/Cachable.pm line 119

DBIx::SearchBuilder::Record::Cachable::LoadByCols(‘RT::CurrentUser=HASH(0x99ecf20)’,
‘Name’, ‘Nobody’) called at /usr/local/rt-3.6.3/lib/RT/Record.pm line 395
RT::Record::LoadByCols(‘RT::CurrentUser=HASH(0x99ecf20)’, ‘Name’,
‘Nobody’) called at
/usr/lib/perl5/vendor_perl/5.8.8/DBIx/SearchBuilder/Record.pm line 1072

DBIx::SearchBuilder::Record::LoadByCol(‘RT::CurrentUser=HASH(0x99ecf20)’,
‘Name’, ‘Nobody’) called at /usr/local/rt-3.6.3/lib/RT/CurrentUser.pm line 253
RT::CurrentUser::LoadByName(‘RT::CurrentUser=HASH(0x99ecf20)’, ‘Nobody’)
called at /usr/local/rt-3.6.3/lib/RT.pm line 185
RT::Init() called at ./user_timesheet.pl line 13

What might the problem be? Script:
#!/usr/bin/perl

use warnings;
use strict;
use lib ‘/usr/local/rt-3.6.3/lib’;
use lib ‘/usr/local/rt-3.6.3/local/lib’;
use RT;
use RT::Tickets;
use RT::Users;
use MIME::Lite;

RT::LoadConfig();
RT::Init();

Declare our global variables

my (@days, @months, @years, @date);
my $time = time();
my %environment;
my %timeworked;

for (1 … 7) {
$time -= 246060;
@date = (localtime($time))[3 … 5];
push @days, (sprintf ‘%02d’, $date[0]);
push @months,(sprintf ‘%02d’,$date[1] + 1);
push @years, $date[2] + 1900;
}

my $startDate = join “-”, $months[$#months], $days[$#days], $years[$#years];
my $endDate = join “-”, $months[0], $days[0], $years[0];
my $searchStart = join “-”, $years[$#years], $months[$#months], $days[$#days];
my $searchEnd = join “-”, $years[0], $months[0], $days[0];
print “Search Start → $searchStart\n”;
print “Search End → $searchEnd\n”;

my $emailTo = “msnyder@servervault.com”; #“rbates@servervault.com,
jcurran@servervault.com”;
my $emailFrom = “root”;
#my $emailBcc = “msnyder@servervault.com”;
my $emailSubj = “RT User Timesheet for The Week Ending $endDate”;
my $emailMsg = “Attached is a file containing billable time committed by
each user for the week ending $endDate. You’ll find it to be a bit rudimentary
but it contains the data I’ve been asked to extract. There are two problems
left to solve at this point. One is to convert each user’s ID number to his or
her name. The other is to get the query to work with any date and cover
anything that is open and has been worked on in the last week as well as
anything resoved in the last week. I have a help request in for both of these.”;

my $tix = new RT::Tickets(RT::SystemUser);
$tix->FromSQL(‘Queue = “CustomerCare” AND (LastUpdated > "’ . $searchStart . ‘"
AND LastUpdated < "’ . $searchEnd . ‘")’);

while (my $ticket = $tix->Next) {
my $env = $ticket->FirstCustomFieldValue(‘Environment’);
unless ($env) {
warn “warning " . $ticket->id. " has no environment”;
next
}

    my $transactions = $ticket->Transactions;
    while (my $transaction  = $transactions->Next) {
            my $creator     = $transactions->CreatorObj;
            next unless ($transaction->TimeTaken);
            next unless $creator->Privileged;
            $timeworked{$creator->Name} += $transaction->TimeWorked;
    }

    foreach my $user (keys %timeworked) {
            my @endTime;
            my $temp        = $timeworked{$user} / 60;
            my @temp        = split /\./, $temp;
            $endTime[0]     = $temp[0];
            $endTime[1]     = $timeworked{$user} % 60;
            my $wholeTime   = sprintf '%d:%02d', @endTime[0,1];
            $environment{$env}{$user} = $wholeTime;
    }

}

The nitty gritty of the script

open TIMESHEET, “>/work_reports/weekly/timesheet_weekof_$endDate.txt”;

print TIMESHEET “Timesheet for $startDate to $endDate\n\n\n”;
#printf TIMESHEET “\nUser Timesheet for $startDate to $endDate\n”;

foreach my $env (sort keys %environment) {
#Print the header for our data
print TIMESHEET “$env”, “\n”;
printf TIMESHEET “%10s%8s\n”, “User”, “hh:mm”;
print TIMESHEET (“-” x 30);
print TIMESHEET “\n”;
foreach my $name (sort keys %{ $environment{$env} }) {
printf TIMESHEET “%10s%8s\n”, “$name”, “$environment{$env}{$name}”;
}
print TIMESHEET “\n”;
}

close TIMESHEET;

Prepare and send the email which with the report to all necessary parties.

my $fullEmail = new MIME::Lite(From => $emailFrom,
To => $emailTo,

Bcc => $emailBcc,

                              Subject => $emailSubj,
                              Type    => "multipart/mixed");

$fullEmail->attach(Type => “TEXT”,
Data => $emailMsg);

$fullEmail->attach(Type => “text/plain”,
Path =>
“/work_reports/weekly/timesheet_weekof_$endDate.txt”,
Disposition => “attachment”);

$fullEmail->send(“sendmail”, “/usr/sbin/sendmail -t”);

exit;

Mathew
If we don’t protect the freedom of speech, how will we know who the assholes are?
http://theillien.blogspot.com

2

Disregard this. It ended up being a corrupted database. Reinstating it from
our production server fixed the problem.

Mathew

Mathew Snyder wrote: