[rt-users] Convert Other Ticketing System DB to RT?
Kenneth Crocker
kfcrocker at lbl.gov
Fri Aug 19 12:09:51 EDT 2011
Michael,
Sorry for the delay. Went on vacation and when I got back I had a list of a
zillion things to do and they were all emergencies. Like "which rush job do
you want me to rush ahead of which rush job?" kind of thing.
Anyway, attached is the perl program that we had written (I sure couldn't do
it. I'm a perl dummy. Just ask Kevin or Ruslan) to convert a flat file (from
a legacy DB ticket system) into the RT system. Obviously, you will have
different fields, etc. but the logic should be helpful. Weused the RT API to
get the records into RT.
Hope this helps.
Kenn
LBNL
On Thu, Aug 4, 2011 at 7:33 PM, Jason Ledford <jledford at biltmore.com> wrote:
> What database access do you have to those older tickets and what type of
> database is it? Mine was a homebrew asp app with access db. I created a
> page in asp that queried the db and wrote the page to look like the offline
> tool. So I turned off all email notification and then ran through about
> 15000 tickets in the offline tool. I broke it up in 5000 chunks. I would
> be happy to share what I created if it is helpful.****
>
> ** **
>
> *From:* rt-users-bounces at lists.bestpractical.com [mailto:
> rt-users-bounces at lists.bestpractical.com] *On Behalf Of *Michael Steen
> *Sent:* Thursday, August 04, 2011 6:12 PM
>
> *To:* rt-users at lists.bestpractical.com
> *Subject:* [rt-users] Convert Other Ticketing System DB to RT?****
>
> ** **
>
> All,
>
> I am wondering if anyone has successfully converted a database from another
> ticketing system to RT. I found a thread from 2008 about OTRS (
> http://www.gossamer-threads.com/lists/rt/users/81165) in which Jesse said
> that most people abandon their former ticketing systems to start fresh with
> RT.
>
> In my case, I have about 1200 tickets that I would like to save currently
> living in a Support Center ticketing system by Quality Unit. If anyone has
> had success in converting a DB, and/or if there is any existing
> documentation I haven't found, please let me know. Also, if anyone has met
> with failure attempting to do something like this, I would appreciate your
> feedback, as well. I'd like to avoid wasted effort if it's not worth it in
> the long run.
>
> Thanks,
>
> Mike****
>
>
> --------
> 2011 Training: http://bestpractical.com/services/training.html
>
-------------- next part --------------
An HTML attachment was scrubbed...
URL: <http://lists.bestpractical.com/pipermail/rt-users/attachments/20110819/c0c33c9b/attachment.htm>
-------------- next part --------------
#!/tools/bin/perl -w
#------------------------------------------------------------------------------
# convert change request data from a Flat File from a Legacy sys to RT.
# Turn OFF RT and then change your RT_SiteConfig.pm to turn off logging.
# Be sure to TURN OFF all scrips either before being RT down or via DBA/SQL.
# scrips will try to run and logging will make the program run FOREVER!!
# flat file input can be created by via Data::Dumper;
# Obviously, field names will be different. OUR Data Names are all CAPS_CAPS
# We built tickets, CF's into two different queues, ES-HRIS & ES-LETS
# We also have an extra Ticket Status value of "QA testing".
#------------------------------------------------------------------------------
use strict;
use lib "/$RTHome/rt/perl/lib";
use lib "/$RTHome/rt/lib";
use RT;
use RT::Ticket;
use RT::Transaction;
use RT::Attachment;
use MIME::Entity;
#------------------------------------------------------------------------------
# process all requests into tickets
#------------------------------------------------------------------------------
sub Read_Temp_File
{
my ( $fh ) = @_;
my
(
$in,
$objectId,
$evt,
$VAR1, #Dumper default variable name
$k,
$n,
);
while ( $in = <$fh> )
{
chomp $in;
eval $in; #instantiates $VAR1 as hash object of change_request and associated change_events
#print $VAR1->{REQUEST}, ' ', $VAR1->{DESCR_SHORT}, "\n";
foreach $k ( qw/MOVED_TO_PRODUCTION_DT QA_APPROVAL_DT/ )
{
if ( $VAR1->{$k} gt ' ' )
{
if ( $VAR1->{$k} > '2038' || $VAR1->{$k} < '2001' )
{
$VAR1->{$k} = '2038-01-01'; #max RT date year
}
}
}
$objectId = New_Ticket( $VAR1 ); #ObjectId = Ticket.id
foreach $evt ( @{$VAR1->{EVENTS}} )
{
$evt->{ObjectId} = $objectId;
$evt->{TransactionId} = New_Transaction( $evt ); #TransactionId = Transaction.id
New_Attachment( $evt );
}
}
}
#------------------------------------------------------------------------------
# create Ticket for one request
#------------------------------------------------------------------------------
sub New_Ticket
{
my ( $req ) = @_;
my
(
$ticket,
$mimeObj,
$ticketid,
$transaction_object,
$err,
$rc,
$status,
$cc,
$ccid,
$grp,
$mem,
$memberid,
$priority,
$rejected,
%ticket_vals,
%req_type_HRIS,
%req_type_LETS,
);
%req_type_LETS =
(
B => "Batch",
D => "Data Problems",
E => "Enhancements",
G => "Role removal",
L => "LETS Forms",
M => "LETSLite",
O => "Other",
R => "Reports",
T => "Weblets",
U => "Urgent Production Problem",
W => "Weblets Forms",
);
%req_type_HRIS =
(
A => "Access",
C => "Crystal",
G => "Upgrade",
H => "HRIS Online",
I => "LANOPS",
K => "Shell Script",
L => "LDRS Data Problems",
M => "HR Data Correction",
O => "Other",
P => "Perl Batch",
Q => "PS Query",
S => "SQR",
T => "WebLETS",
U => "UNIX",
V => "Inquiry",
W => "HR Web Apps",
X => "QA Test w/o System Change",
);
$ticket = RT::Ticket->new( $RT::SystemUser );
$mimeObj = MIME::Entity->build(Data => $req->{DESCR_LONG}, Type => 'text/plain');
if ( $req->{PRIORITY} le '5' )
{
$priority = $req->{PRIORITY};
$rejected = 0;
}
else
{
$priority = '5';
$rejected = 1;
}
%ticket_vals =
(
Subject => $req->{DESCR_SHORT},
MIMEObj => $mimeObj,
Creator => User_Id_For_Name( $req->{INITIATED_BY} ), #API bug won't look up corresponding id for name
Owner => $req->{DEVELOPER},
Requestor => $req->{INITIATED_BY},
Priority => $priority,
InitialPriority => $priority,
FinalPriority => $priority,
Created => $req->{CREATE_DT},
Started => $req->{DEVELOPER_ASSIGNED_DT},
Resolved => $req->{MOVED_TO_PRODUCTION_DT},
LastUpdated => $req->{ACTION_DT},
Status => StatusLookup( $req, $rejected ),
);
if ( $req->{TEAM} eq 'L' )
{
$ticket_vals{Queue} = 'ES-LETS';
}
else
{
$ticket_vals{Queue} = 'ES-HRIS';
}
( $ticketid, $transaction_object, $err ) = $ticket->Create( %ticket_vals );
print "New_Ticket request=", $req->{REQUEST}, " $err \n"; #debug
die "New_Ticket Error: $err \n" unless $ticketid;
( $rc, $err ) = $ticket->AddCustomFieldValue( Field => 'Description', Value => $req->{DESCR_LONG} );
( $rc, $err ) = $ticket->AddCustomFieldValue( Field => 'Reference Number', Value => $req->{REQUEST} );
( $rc, $err ) = $ticket->AddCustomFieldValue( Field => 'Work-Completed Date', Value => $req->{DEVELOPER_APPROVAL_DT} );
print "Work-Completed Date $rc $err\n";
( $rc, $err ) = $ticket->AddCustomFieldValue( Field => 'QA Approval Date', Value => $req->{QA_APPROVAL_DT} );
print "QA Approval Date $rc $err\n";
if ( $req->{TEAM} eq 'L' )
{
( $rc, $err ) = $ticket->AddCustomFieldValue( Field => 'LETS-QA Approvers List', Value => $req->{QA_PERSON} );
( $rc, $err ) = $ticket->AddCustomFieldValue( Field => 'LETS-Migrators List', Value => $req->{ZZ_PROMOTER} );
( $rc, $err ) = $ticket->AddCustomFieldValue( Field => 'LETS-Request Type', Value => $req_type_lets{$req->{REQUEST_TYPE}} );
print "LETS-Request Type $rc $err\n";
}
else
{
( $rc, $err ) = $ticket->AddCustomFieldValue( Field => 'HRIS-QA Approvers List', Value => $req->{QA_PERSON} );
( $rc, $err ) = $ticket->AddCustomFieldValue( Field => 'HRIS-Migrators List', Value => $req->{ZZ_PROMOTER} );
( $rc, $err ) = $ticket->AddCustomFieldValue( Field => 'HRIS-Request Type', Value => $req_type_hris{$req->{REQUEST_TYPE}} );
print "HRIS-Request Type $rc $err\n";
}
if ( $req->{CC} )
{
foreach $cc ( split( /[,\s+]/, $req->{CC} ) )
{
if ( $cc && $cc =~ /xxx\.xxx/i ) #xxx is your email address stuff like yahoo.com
{
( $cc ) = $cc =~ /(.*?)\@/; #remove @domain.ext
$ccid = User_Id_For_Name( $cc );
unless ( $ccid == 1 ) #skip Nobody
{
print "Adding CC $cc $ccid\n"; #debug
$ticket->AddWatcher( Type => 'Cc', PrincipalId => $ccid );
}
}
}
}
return $ticketid; #Ticket.id
}
#------------------------------------------------------------------------------
# create email-like mime object from event as attachment.
#------------------------------------------------------------------------------
sub New_Attachment
{
my ( $evt ) = @_;
my
(
$att,
$mimeObj,
$id,
$transaction_object,
$err,
);
$mimeObj = MIME::Entity->build(
Subject => substr( $evt->{DESCR_LONG}, 0, 77 ) . '...',
Data => $evt->{DESCR_LONG},
Date => $evt->{CREATE_DT},
From => $evt->{INITIATED_BY},
);
$att = RT::Attachment->new( $RT::SystemUser );
( $id, $transaction_object, $err ) = $att->Create(
TransactionId => $evt->{TransactionId},
Attachment => $mimeObj,
);
die "New_Attachment Error $err \n" unless $id;
}
#------------------------------------------------------------------------------
# create Transaction for change_event
#------------------------------------------------------------------------------
sub New_Transaction
{
my ( $evt ) = @_;
my
(
$trn,
$id,
$transaction_object,
$err,
);
$trn = RT::Transaction->new( $RT::SystemUser );
( $id, $transaction_object, $err ) = $trn->Create(
ObjectId => $evt->{ObjectId},
ObjectType => 'RT::Ticket',
Type => 'Comment',
Created => $evt->{CREATE_DT},
);
die "New_Transaction Error $err \n" unless $id;
return $id;
}
#------------------------------------------------------------------------------
# status logic based on dates
#------------------------------------------------------------------------------
sub StatusLookup
{
my ( $req, $rejected ) = @_;
my
(
$status,
);
if ( $rejected )
{
$status = 'rejected';
}
elsif ( $req->{MOVED_TO_PRODUCTION_DT} gt ' ' )
{
$status = 'resolved';
}
elsif ( $req->{QA_APPROVAL_DT} gt ' ' )
{
$status = 'QA testing';
}
elsif ( $req->{DEVELOPER_APPROVAL_DT} gt ' ' )
{
$status = 'QA testing';
}
elsif ( $req->{DEVELOPER_ASSIGNED_DT} gt ' ' )
{
$status = 'open';
}
else
{
$status = 'new';
}
return $status;
}
#------------------------------------------------------------------------------
# return Id for LDAP Name - case insensitive
#------------------------------------------------------------------------------
{ #scope
my $ids;
sub User_Id_For_Name
{
my ( $name ) = @_;
my
(
$users,
$u,
);
unless ( $ids->{XXXXXXX} ) #XXXXXX is the UserID of the person running the job
{
$users = RT::Users->new($RT::SystemUser);
$users->UnLimit();
while ( $u = $users->Next() )
{
$ids->{uc $u->Name} = $u->id;
}
}
return $ids->{uc $name} || 1; #default Nobody
}
} #scope
#------------------------------------------------------------------------------
# main
#------------------------------------------------------------------------------
my
(
$fh,
$fname,
);
RT::LoadConfig();
RT::Init();
$fname = '/var/tmp/chg_req_2_rt.txt';
$fh = IO::File->new( "$fname" ) or die "cannot open $fname $!\n";
Read_Temp_File( $fh );
$fh->close();
More information about the rt-users
mailing list