[rt-users] How to import users from a crm

Joe Casadonte joe.casadonte at oracle.com
Sun Feb 17 12:38:57 EST 2008


On 2/17/2008 11:36 AM, Tommie Van Mechgelen wrote:
> Dear,
> 
> we have a crm system with our customers.  this sytem can only export 
> it's contents to csv.
> 
> So I want to write an import script to import the emailaddress and names 
> to RT.  What is the easiest way and best way to do this? import them to 
> mysql directly?  Use commandline tool?  Or is there an example available?

Here's a perl script that I use to add new users.  The source is a CSV 
file with the following columns:

Comment - put a '#' in here to skip this row
Last Name
First Name
Email Address
City
State
Country
Group Membership - I think I have an 'x' in the column if the user is a 
member of that group

The script started out small and grew a little from there.  Add whatever 
other fields make sense for you, take out what doesn't, and replace the 
groups to your liking.  If you know Perl, you should be able to figure 
out what it's doing.  If you don't know Perl, you should find someone 
who does to help you out (seriously, your life will be much, much easier 
if you know Perl).

Use the script at your own risk......


#!/usr/bin/perl -w

#***** some pragmas *****
use strict;
use 5.008;

use lib "/opt/rt3/lib";

#***** include some files *****
use Getopt::Long;

use RT;
use RT::Interface::CLI qw(CleanEnv GetCurrentUser);
use RT::Group;
use RT::User;
use RT::User_Overlay;

#***** constants *****
our($EMAIL_DOMAIN) = "oracle.com";

$RT::MinimumPasswordLength ||= 6;
our($MINLEN) = ($RT::MinimumPasswordLength > 6 ? 
$RT::MinimumPasswordLength : 6);
our($MAXLEN) = ($RT::MinimumPasswordLength > 8 ? 
$RT::MinimumPasswordLength : 8);

our($GUSERS)   = 'Users';
our($GDEVMGR)  = 'DevMgr';
our($GEXECS)   = 'Execs';
our($GSUPPORT) = 'Support';
our($GISR)     = 'ISR';
our($GENGINF)  = 'EngInf';
our($GEIMGR)   = 'EIMgr';
our($GROUPNAMES) = [$GUSERS, $GDEVMGR, $GEXECS, $GSUPPORT, $GISR, 
$GENGINF, $GEIMGR];

#***** global vars *****
our($Groups) = {};

#***************************************************************************
#***************************************************************************
#***************************************************************************
#***************************************************************************
#***************************************************************************

#**** parse command-line args *****
my($opts) = {};
GetOptions($opts, "skipadd", "resetpasswd", "user=s") || die qq([ERROR] 
Invalid argument(s)\n);

#***** setup RT environment *****
CleanEnv();
RT::LoadConfig();
RT::Init();

my($user) = GetCurrentUser();
die qq([ERROR] No RT user found.\n) unless $user->Id;

#***** load all of the groups *****
foreach my $groupname (@$GROUPNAMES) {
	my($group) = RT::Group->new($RT::SystemUser);
	$group->LoadUserDefinedGroup($groupname);
	die qq([ERROR] Cannot load group "$groupname"\n) unless $group->id;

	$Groups->{$groupname} = $group;
}

#***** get ready to parse CSV file *****
my($fname) = shift;
open(IN, "<$fname") || die qq([ERROR] Cannot open "$fname" for input - 
$!\n);

while (<IN>) {
	chomp;

	#***** skip blanks and comments *****
	next if m{^\s*$};
	next if m{^\s*#};

	#***** parse the line *****
	my($cols) = [split(/,/)];
	map($_ ||= "", @$cols);
	map(s{^\s+}{}, @$cols);
	map(s{\s+$}{}, @$cols);

	my($coment, $lastname, $firstname, $email, $city, $state, $country,
	   $gusers, $gdevmgr, $gexecs, $gsupport, $gisr, $genginf, $geimgr) = 
@$cols;

	next if (($opts->{'user'}) && ($email ne $opts->{'user'}));

	if (! $email) {
		print qq([WARNING] User "$firstname $lastname" ($.) has no email 
address -- skipping\n);
		next;
	}

	#***** massage it some *****
	my($realname) = "$firstname $lastname";
	my($username) = $email;
	my($fullemail) = "$email\@$EMAIL_DOMAIN";

	#***** get new user object *****
	my $user = RT::User->new($RT::SystemUser);

	#***** generate a password *****
     my($password) = $user->GenerateRandomPassword($MINLEN, $MAXLEN) ;

	#***** load/create the user *****
	#----- try to load user (first by email, then by name) -----
	my ($id, $msg) = $user->LoadByEmail($fullemail);
	if (! $user->id) {
		($id, $msg) = $user->Load($username);
	}

	if ($opts->{'skipadd'}) {
		#----- we expected the user to be there already, so it's an error it 
they are not -----
		die qq([ERROR] Could not load user "$username" -- $msg\n) unless 
$user->id;

		#----- only reset password if asked -----
		$user->SetPassword($password) if ($opts->{'resetpasswd'});
	}
	else {
		if ($user->id) {
print "Modifying: $username...\n";
			#----- reset password, realname and such -----
			$user->SetName($username);
			$user->SetPassword($password);
			$user->SetRealName($realname);
			$user->SetEmailAddress($fullemail);
			$user->SetPrivileged(!0);
			$user->SetLang('en');

			$user->SetCity($city) if $city;
			$user->SetState($state) if $state;
			$user->SetCountry($country) if $country;
		}
		else {
print "Adding: $username...\n";
			my($args) = {
				Name         => $username,
				Password     => $password,
				RealName     => $realname,
				EmailAddress => $fullemail,
				Privileged   => !0,
				Lang         => 'en',
			};

			$args->{'City'} = $city if $city;
			$args->{'State'} = $state if $state;
			$args->{'Country'} = $country if $country;

			my ($id, $msg) = $user->Create(%$args);
			die qq([ERROR] Could not create user "$username" -- $msg\n) unless 
$user->id;
		}
	}

	#***** add to the appropriate group *****
	my($groupname) = $GUSERS;
	$groupname = $GDEVMGR if $gdevmgr;
	$groupname = $GEXECS if $gexecs;
	$groupname = $GSUPPORT if $gsupport;
	$groupname = $GISR if $gisr;
	$groupname = $GENGINF if $genginf;
	$groupname = $GEIMGR if $geimgr;

	my($status, $errmsg) = $Groups->{$groupname}->AddMember($user->id);
	die qq([ERROR] Cannot add user ") . $user->id . qq(" to group 
"$groupname" - $errmsg\n) unless $status;

	#***** send out email *****
     my $template = RT::Template->new($RT::SystemUser);
	$template->LoadGlobalTemplate('ORA_NewAccountCreated');

     unless ( $template->Id ) {
         $RT::Logger->crit(qq(Unable to load template 
"ORA_NewAccountCreated"));
		die qq([ERROR] Unable to load template "ORA_NewAccountCreated"\n);
     }

     my($result, $message) = $template->Parse(Argument => {UID => 
$username, PWD => $password});

     if (! $result) {
         $RT::Logger->warning("Template object failed to parse - $message");
         die("[ERROR] Template object failed to parse - $message\n");
     }

     my $MIMEObj = $template->MIMEObj;

     $MIMEObj->head->set('To', $user->EmailAddress);
     $MIMEObj->head->set('From', $RT::CorrespondAddress);

     if ( $RT::MailCommand eq 'sendmailpipe' ) {
         eval {
             open( MAIL, "|$RT::SendmailPath $RT::SendmailArguments" ) 
|| die $!;
             print MAIL $MIMEObj->as_string;
             close(MAIL);
         };
         if ($@) {
             $RT::Logger->crit("Could not welcome message. -" . $@ );
             die("[ERROR] Could not welcome message. -" . $@ );
		}
     }
     else {
         my @mailer_args = ($RT::MailCommand);

         local $ENV{MAILADDRESS};

         if ( $RT::MailCommand eq 'sendmail' ) {
             push @mailer_args, split(/\s+/, $RT::SendmailArguments);
         }
         elsif ( $RT::MailCommand eq 'smtp' ) {
			#----- silence warnings on the following three vars -----
			$RT::SMTPFrom ||= ""; $RT::SMTPServer ||= ""; $RT::SMTPDebug ||= "";

             $ENV{MAILADDRESS} = $RT::SMTPFrom || 
$MIMEObj->head->get('From');
             push @mailer_args, ( Server => $RT::SMTPServer );
             push @mailer_args, ( Debug  => $RT::SMTPDebug );
         }
         else {
             push @mailer_args, $RT::MailParams;
         }

         unless ( $MIMEObj->send(@mailer_args) ) {
             $RT::Logger->crit("Could not send welcome message." );
             die "[ERROR] Could not send welcome message.\n";
         }
     }
}

#***************************************************************************
#*****  EOF  *****  EOF  *****  EOF  *****  EOF  *****  EOF  ***************


-- 
Regards,


joe
Joe Casadonte
joe.casadonte at oracle.com

==========                                                  ==========
== The statements and opinions expressed here are my own and do not ==
== necessarily represent those of Oracle Corporation.               ==
==========                                                  ==========



More information about the rt-users mailing list