[rt-users] Porting Data

[David Smithson]

Hi list!  I have successfully completed phase 1 of our data migration
project.  I thought I'd share my scripts with you all.  They will not be
useful out of the box since our current ticketing system is proprietary,
and I have to admit that my programming skills are pretty weak and I was
way too lazy to try and use the RT API ( in fact I don't think it would
have worked ).  I'm not sure if these are worthy of a wiki page, since
they are so specific to our own migration.  Nevertheless, here are my
scripts.  Let me know if you find them useful, primitive, sucky,
whatever.  I'm interested to know what looks useful and what you think
could have been done in a more efficient way.  Currently, users and
queues import rather swiftly, then it takes about 15 hours to import
12000 tickets ( this is a very rough estimate, since I haven't actually
imported all of the tickets yet. )

All of the scripts pull data from CSV files, exported from MS SQL.  They
all rely on DBI, DBD::CSV, and DBD::mysql.  Since I'm dumb, I just used
Excel to pull from the data source and saved to CSV.  Before saving, I
had to do some simple transformations on columns that relate to datetime
fields.

This one adds the users from the old system:

----
#!/usr/bin/perl -w

use DBI;
use strict;

# MySQL config
my $mysql_host='localhost';
my $mysql_user='user';
my $mysql_pass='pass';
my $mysql_data='rt3';

#MySQL connect
my $mysql_dsn =
"DBI:mysql:database=$mysql_data;host=$mysql_host;port=3306";
my $dbh = DBI->connect($mysql_dsn, $mysql_user, $mysql_pass);

# CSV prep
my $csv_dbh = DBI->connect(qq{DBI:CSV:csv_sep_char=\,});
$csv_dbh->{'csv_tables'}->{'users'} = { 'file' =>
'/dump/final/users.csv'};
my $users = $csv_dbh->prepare("SELECT
id_User,FirstName,LastName,Email,CellPhone,Pager,DirectPhone,HomePhone,S
treetAddress,City,State,ZipCode,UserName,InActiveYesNo FROM users");
$users->execute() or die "Cannot execute: " . $users->errstr();

# open (CSV, "/dump/client.csv");

my $sth_users = $dbh->prepare("INSERT INTO Users
(id,Name,EmailAddress,RealName,Creator,Gecos,HomePhone,WorkPhone,MobileP
hone,PagerPhone,Address1,City,State,Zip,LastUpdatedBy) VALUES
(?,?,?,?,?,?,?,?,?,?,?,?,?,?,?)");
my $sth_principals = $dbh->prepare("INSERT INTO Principals
(id,PrincipalType,ObjectId,Disabled) VALUES (?,?,?,?)");
my $sth_groups = $dbh->prepare("INSERT INTO Groups
(id,Name,Description,Domain,Type,Instance) VALUES (?,?,?,?,?,?)");
my $sth_groupmembers =$dbh->prepare("INSERT INTO GroupMembers
(GroupId,MemberId) VALUES (?,?)");

while(my $row = $users->fetchrow_hashref) {
	print "$row->{'UserName'}\n";
	my $username;
	if($row->{'UserName'} eq '') {
		$username = "\L$row->{'FirstName'}.$row->{'LastName'}";
	} else {
		$username = $row->{'UserName'}
	}
 
$sth_users->execute($row->{'id_User'},$username,$row->{'Email'},$row->{'
FirstName'}.$row->{'LastName'},'12',$username,$row->{'HomePhone'},$row->
{'DirectPhone'},$row->{'CellPhone'},$row->{'Pager'},$row->{'StreetAddres
s'},$row->{'City'},$row->{'State'},$row->{'ZipCode'},'12');
	
$sth_principals->execute($row->{'id_User'},'User',$row->{'id_User'},$row
->{'InActiveYesNo'});
	my $principal_group_id = int($row->{'id_User'})+1000;
	$sth_groups->execute($principal_group_id,'User
'.$row->{'id_User'},'ACL equiv. for user
'.$row->{'id_User'},'ACLEquivalence','UserEquiv',$row->{'id_User'});
	
$sth_principals->execute($principal_group_id,'Group',$principal_group_id
,$row->{'InActiveYesNo'});
	$sth_groupmembers->execute($row->{'id_User'},$row->{'id_User'});
	$sth_groupmembers->execute('3',$row->{'id_User'});
}

$users->finish();
$csv_dbh->disconnect();
$sth_users->finish();
$sth_principals->finish();
$sth_groups->finish();
$sth_groupmembers->finish();
$dbh->disconnect();
----


This one imports the "queues".

----
#!/usr/bin/perl -w

use DBI;
use strict;

# MySQL config
my $mysql_host='localhost';
my $mysql_user='user';
my $mysql_pass='pass';
my $mysql_data='rt3';

#MySQL connect
my $mysql_dsn =
"DBI:mysql:database=$mysql_data;host=$mysql_host;port=3306";
my $dbh = DBI->connect($mysql_dsn, $mysql_user, $mysql_pass);

# CSV prep
my $csv_dbh = DBI->connect(qq{DBI:CSV:csv_sep_char=\,});
$csv_dbh->{'csv_tables'}->{'clients'} = { 'file' =>
'/dump/final/client.csv'};
# $inactive = $csv_dbh->prepare("SELECT * FROM clients where
InActiveYesNo=1");
my $active = $csv_dbh->prepare("SELECT id_Client,Name,InActiveYesNo FROM
clients");
# $inactive->execute() or die "Cannot execute: " . $sth->errstr();
$active->execute() or die "Cannot execute: " . $active->errstr();

# open (CSV, "/dump/client.csv");

my $sth = $dbh->prepare("INSERT INTO Queues
(id,Name,CorrespondAddress,CommentAddress,InitialPriority,FinalPriority,
DefaultDueIn,Disabled) VALUES (?,?,?,?,?,?,?,?)");

while(my $row = $active->fetchrow_hashref) {
        # my
($id,$idownercompany,$name,$phone,$www,$email,$additionaldomainnames,$no
tes,$ownercompanyyesno,$defaultuserinterfaceyesno,$iddefaultengineer,$in
activeyesno) = split /,/;
	my $name = $row->{'Name'};
	$name =~ s/,Inc\.|,|Inc|Inc\.//g;
	$name =~ s/\./ /g;
	$name =~ s/\(.*\)//g;
	$name =~ s/z-//g;
	my $address = $name;
	$address =~ s/\s+//g;
 
$sth->execute($row->{'id_Client'},$name,"$address\@rt.activsupport.com",
"comment-$address\@rt.activsupport.com",'1','100','1',$row->{'InActiveYe
sNo'});
}

$active->finish();
$csv_dbh->disconnect();
$sth->finish();
$dbh->disconnect();
----


And finally, this one imports the tickets.

----
#!/usr/bin/perl -w

use DBI;
use strict;

# MySQL config
my $mysql_host='localhost';
my $mysql_user='rt_user';
my $mysql_pass='rt_pass';
my $mysql_data='rt3';

#MySQL connect
my $mysql_dsn =
"DBI:mysql:database=$mysql_data;host=$mysql_host;port=3306";
my $dbh = DBI->connect($mysql_dsn, $mysql_user, $mysql_pass);

# CSV prep
my $csv_dbh = DBI->connect(qq{DBI:CSV:csv_sep_char=\,});
$csv_dbh->{'csv_tables'}->{'task'} = { 'file' =>
'/dump/final/Task.csv'};
$csv_dbh->{'csv_tables'}->{'report'} = { 'file' =>
'/dump/final/report.csv'};
my $task = $csv_dbh->prepare("SELECT * FROM task");
my $report = $csv_dbh->prepare("SELECT
id_Report,id_Client,id_User,TotalTime,ReportTime,ReportDate,ArrivalTime,
DepartureTime,InternalNotes FROM report");
$task->execute() or die "Cannot execute: " . $task->errstr();
$report->execute() or die "Cannot execute: " . $report->errstr();

my $sth_tickets = $dbh->prepare("INSERT INTO Tickets
(id,EffectiveId,Queue,Type,IssueStatement,Resolution,Owner,Subject,Initi
alPriority,FinalPriority,Priority,TimeEstimated,TimeWorked,Status,TimeLe
ft,Creator,Started,Created,Due,Resolved,LastUpdatedBy,LastUpdated,Disabl
ed) VALUES (?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?)");
my $sth_transactions = $dbh->prepare("INSERT INTO Transactions
(id,ObjectType,ObjectId,TimeTaken,Type,Creator,Created) VALUES
(?,?,?,?,?,?,?)");
my $sth_attachments = $dbh->prepare("INSERT INTO Attachments
(TransactionId,Parent,ContentType,ContentEncoding,Content,Creator,Create
d) VALUES (?,?,?,?,?,?,?)");

while(my $row = $report->fetchrow_hashref) {
	print "$row->{'id_Report'}\n";
	my @time_started = $dbh->selectrow_array("SELECT
CONVERT_TZ('$row->{'ReportDate'}
$row->{'ArrivalTime'}','America/Los_Angeles','GMT')");
	my @time_created = $dbh->selectrow_array("SELECT
CONVERT_TZ('$row->{'ReportTime'}','America/Los_Angeles','GMT')");
	my @time_resolved = $dbh->selectrow_array("SELECT
CONVERT_TZ('$row->{'ReportDate'}
$row->{'DepartureTime'}','America/Los_Angeles','GMT')");
	my $total_time = int($row->{'TotalTime'})*60;
 
$sth_tickets->execute($row->{'id_Report'},$row->{'id_Report'},$row->{'id
_Client'},'ticket','0','0',$row->{'id_User'},"ND Report #
$row->{'id_Report'}",'1','100','100',$total_time,$total_time,'resolved',
'0',$row->{'id_User'}, at time_started, at time_created, at time_resolved, at time_r
esolved,$row->{'id_User'}, at time_resolved,'0');
	my $transactions = $csv_dbh->selectall_arrayref("SELECT * FROM
Task.csv WHERE id_Report=$row->{'id_Report'}", { Slice => {} });
	foreach my $transaction ( @$transactions ) {
		my $tranid = int($transaction->{'id_Task'})+100000;
	
$sth_transactions->execute($tranid,'RT::Ticket',$row->{'id_Report'},'0',
'Comment',$row->{'id_User'},$row->{'ReportTime'});
	
$sth_attachments->execute($tranid,'0','text/plain','none',"Problem
Description:\n\n$transaction->{'ProblemDescription'}\n\nAction
Taken:\n\n$transaction->{'ActionTaken'}\n\nNotes:\n\n$transaction->{'Not
es'}",$row->{'id_User'},"$row->{'ReportDate'} $row->{'ArrivalTime'}");
	}
}

$sth_tickets->finish();
$sth_transactions->finish();
$sth_attachments->finish();
$csv_dbh->disconnect();
$dbh->disconnect();
----

I learned quite a bit about RT's schema in the process of creating
these, ugly as they are.  This was important to us, because we wanted to
retain information for metrics and history and totally demolish the old
decrepit system we have.


David Smithson
________________________________________
ActivSupport, Inc.
Your Flexible IT Partner
Microsoft Gold Partner -- Small Business Specialist
http://www.activsupport.com
Director of Technical Services
Direct: (415) 869 2991 
________________________________________
Technical Support Hotline: (415) 979 9285 
CLICK HERE FOR ONLINE SUPPORT