1 #!/usr/bin/perl
  2 # rt-updatedb.pl
  3 #   Copyright Worcester College 2006
  4 #   This script written by Michael Howe (michael.howe@worc.ox.ac.uk) for Worcester
  5 #     College.
  6 #   Released under the GNU GPL v2.
  7 #   Version: $Id: rt-updatedb.pl 61 2006-02-06 22:56:21Z michael $
  8 #
  9 # Usage
 10 #   Try 'perldoc rt-updatedb.pl', or read the bottom of the script.
 11 #
 12 
 13 use strict;
 14 use warnings;
 15 use Getopt::Long;
 16 use DBI;
 17 use File::Basename;
 18 # Import RT lib:
 19 use lib "/usr/share/request-tracker3.4/lib";
 20 use RT::Interface::CLI qw(CleanEnv);
 21 use RT::User;
 22 use RT::Group;
 23 
 24 #
 25 # These variables should be updated appropriately, and can also be set via the
 26 # command line.
 27 #
 28 # Database type:
 29 my $emaildb_type = 'mysql';	# should be a dbi driver name, eg mysql or Pg
 30 # Database host:
 31 my $emaildb_host = 'localhost';
 32 # Database name:
 33 my $emaildb_name = 'emaildb';
 34 # Database table that contains user data:
 35 my $emaildb_table = 'email';
 36 # User with permission to access the database:
 37 my $emaildb_user = '';
 38 # Password for above user:
 39 my $emaildb_pass = '';
 40 
 41 my $rtdb_type = 'mysql';
 42 my $rtdb_host = 'localhost';
 43 my $rtdb_name = 'rtdb';
 44 my $rtdb_user = '';
 45 my $rtdb_pass = '';
 46 # Array of users to ignore (not add to RT):
 47 my @ignore_users = qw( itss0044 );
 48 # Array of email addresses to ignore:
 49 my @ignore_emails = qw( stuart.sharp laurence.kinsella );
 50 # Example:
 51 # my @ignore_emails = qw( stuart.sharp@worc.ox.ac.uk it-support@worc.ox.ac.uk);
 52 
 53 # File with usernames/emails to ignore, one per line
 54 my $ignore_file = '';
 55 
 56 # Hash of groups.  Each key is the 'status' from the email database table.
 57 #  The value of $groupmap{$key} is the RT group name to add the users to.
 58 my %groupmap = (
 59 	undergrad => 'JCR',
 60 	postgrad => 'MCR',
 61 	college => 'STAFF',
 62 	senmem => 'SCR',
 63 );
 64 # Print output?
 65 my $verbose = 0;
 66 # You don't need to change this.
 67 my $help = 0;
 68 # This should be set to make it execute.
 69 my $execute = 0;
 70 #
 71 # End variables that should need changing
 72 #
 73 
 74 # Other variables:
 75 my $procname = basename($0);
 76 my $SystemUser;
 77 my $CurrentUser;
 78 my $CurrentGroup;
 79 
 80 #
 81 # resetrt
 82 #   This sets the RT objects up to be suitable to work with.
 83 #
 84 sub resetrt {
 85 	CleanEnv();
 86 	RT::LoadConfig();
 87 	RT::Init;
 88 	$SystemUser =  RT::CurrentUser->new();
 89 	$SystemUser->LoadByName('RT_System');
 90 	$CurrentUser = RT::CurrentUser->new();
 91 	$CurrentGroup = RT::Group->new($SystemUser);
 92 }
 93 
 94 #
 95 # checkgroups
 96 #   This checks that the RT groups in the %groupmap hash actually exist, and
 97 #   complains (and deletes the groupmap item).
 98 #
 99 sub checkgroups {
100 	# Loop through all of the statuses
101 	foreach ( keys( %groupmap ) ) {
102 		# Attempt to load the group that goes with the status:
103 		my $group = $CurrentGroup->LoadUserDefinedGroup( $groupmap{$_});
104 		# If it can't get the ID of the group, we assume that group
105 		# doesn't exist and delete it.
106 		unless ( $CurrentGroup->id =~ m{(\d+)} ) {
107 			print "Group $groupmap{$_} doesn't exist.\nNot adding people with the status '$_'\n";
108 			delete( $groupmap{$_} );
109 		}
110 	}
111 }
112 
113 #
114 # verbose
115 #   If the verbose flag is set this prints out more info.
116 #
117 sub verbose(@) {
118 	print ("$procname: ", @_, "\n") if ($verbose);
119 }
120 
121 #
122 # usage
123 #   Prints a usage statement.
124 #
125 sub usage {
126 	print <<EOF;
127 $0: updates the requesttracker database with a list of users from the emaildb.
128 
129 Options:
130 	--help		Display this message
131 	--verbose	Display more info on what the script is doing
132 	--execute	NEW: use this flag to make the command execute (helps to
133 			prevent mistakes)
134 	--emaildb_host	Host where the database is running.  Defaults to
135 			localhost
136 	--emaildb_type	Type of database (MYSQL, Postges, etc).  For MYSQL use 
137 			'mysql', for Postgres use 'Pg'.
138 	--emaildb_name	Set email database
139 	--emaildb_table	Set email table
140 	--emaildb_user	Set email database username
141 	--emaildb_pass	Set email database password
142 	--ignore_user	Sets a username to not include.  Can be used multiple
143 			times
144 	--ignore_email	Sets an email address to not include.  Can be used
145 			multiple times.  Will assume domain of worc.ox.ac.uk if
146 			not specified
147 	--ignore_file	File with usernames (no @) and/or _fully qualified_
148 			email addresses to ignore
149 	--group	database_status=RT_group
150 			Sets an RT group to assign people with the set database
151 			status to.  Can be used multiple times.  Multiple
152 			database statuses can be assigned to the same RT group
153 
154 Note these options can also be changed in the script.
155 
156 Example usage:
157 
158 $0 --verbose --ignore_email stuart.sharp --ignore_user itss0044 \ 
159 --group undergrad=JCRUsers --group postgrad=MCRUsers --group senmem=StaffUsers \
160 --group staff=StaffUsers
161 
162 Will verbosely ignore stuart.sharp\@worc.ox.ac.uk and itss0044.  Will add all 
163 undergrads to the JCRUsers group; all postgrads to the MCRUsers group; and all
164 staff and senmems to the StaffUsers group.
165 
166 EOF
167 }
168 
169 # This code needs to come first.
170 # It grabs the options and sets variables accordingly.
171 # See 'perldoc Getopt::Long' for details of how it works.
172 GetOptions(
173 	'help|h' =>		\$help,
174 	'verbose|v' =>		\$verbose,
175 	'execute|e' =>		\$execute,
176 	'emaildb_type=s' =>	\$emaildb_type,
177 	'emaildb_host=s' =>	\$emaildb_host,
178 	'emaildb_name=s' =>	\$emaildb_name,
179 	'emaildb_table=s' =>	\$emaildb_table,
180 	'emaildb_user=s' =>	\$emaildb_user,
181 	'emaildb_pass=s' =>	\$emaildb_pass,
182 	'ignore_user=s' =>	\@ignore_users,
183 	'ignore_email=s' =>	\@ignore_emails,
184 	'ignore_file=s' =>	\$ignore_file,
185 	'group=s' =>		\%groupmap,
186 );
187 
188 
189 # Check we don't just want help:
190 if ($help or not $execute) {
191 	usage();
192 	exit(0);
193 }
194 
195 # Set up RT.
196 resetrt();
197 
198 #  Check the groups we've been given.
199 #  Afterwards reset our RT objects, just in case.
200 checkgroups();
201 #resetrt();
202 
203 # Grab the ignore list from a file if it exists:
204 if ($ignore_file) {
205 	# We _should_ die if the file can't be found, because otherwise we may
206 	# add users that really _shouldn't_ exist.
207 	( -f $ignore_file ) or die("Can't read the ignore file '$ignore_file': $!\n");
208 	# Again, die if we can't open it.
209 	open( IF, $ignore_file ) or die("Can't open the ignore file '$ignore_file': $!\n");
210 	
211 	# Loop through each line of the file.
212 	while (<IF>) {
213 		# Remove newlines
214 		chomp;
215 		# Don't want blank lines
216 		next if ( m{^$} );
217 		# If we've got an @ followed by a period, it's an email address.
218 		# Even if it's not supposed to be.
219 		if ( $_ =~ m{@.*\..*} ) {
220 			verbose("Adding '$_' to email addresses to ignore");
221 			push @ignore_emails, $_;
222 		} else {
223 			# If it's not an email address it's a username
224 			verbose("Adding '$_' to usernames to ignore");
225 			push @ignore_users, $_;
226 		}
227 	}
228 	# Close the handle.  But if we can't, don't die.
229 	close(IF) or print "Can't close '$ignore_file' ($!) - continuing anyway\n";
230 }
231 
232 
233 # Make it allow comma-separated ignores too, just in case.
234 # This allows the format --ignore_user foo,bar,baz as well as --ignore-user foo
235 # --ignore_user bar --ignore-user baz
236 # Not widely publicised, but just in case, as other apps do it.
237 @ignore_users = split(/,/, join(',',@ignore_users));
238 @ignore_emails = split(/,/, join(',',@ignore_emails));
239 
240 #
241 # Email database query.
242 #
243 
244 	# This works for $email_type=mysql and Pg.
245 	my $edbh = DBI->connect("dbi:$emaildb_type:database=$emaildb_name;host=$emaildb_host", "$emaildb_user", "$emaildb_pass", {
246 		RaiseError => 1,
247 		}) or die "Can't connect to db '$emaildb_name': $!\n";
248 	# Create the sql to select statuses (surround each status with single
249 	# quotes, and separate with commas:
250 	my $statuses = "'" . join( "', '", keys( %groupmap ) ) . "'";
251 	# Hash to say what emails/users we _don't_ want to select
252 	my %ignore = (
253 		emails => '',
254 		users => '',
255 	);
256 
257 	# Create the SQL to ignore emails
258 	if ( @ignore_emails ) {
259 		$ignore{emails} = "AND e.email NOT IN ( '";
260 		foreach ( @ignore_emails ) {
261 			# Auto-complete to @worc.ox.ac.uk if there's no @
262 			# Note that this works on command-line set emails only -
263 			# because emails set from file must be fully-qualified.
264 			$_ .= "\@worc.ox.ac.uk" if ( $_ !~ m{@} );
265 		}
266 		# Same trick as with $statuses above.
267 		$ignore{emails} .= join( "', '", @ignore_emails) . "' )";
268 		verbose( "Ignoring email addresses '" . join( " '", @ignore_emails ) . "'\n" );
269 	}
270 
271 	# Create the sql to ignore usernames
272 	if ( @ignore_users ) {
273 		$ignore{users} = "AND e.username NOT IN ( '" . join( "', '", @ignore_users) . "' )";
274 		verbose( "Ignoring usernames '" . join( " '", @ignore_users ) . "'\n" );
275 	}
276 	# Build the query
277 	my $esql = "SELECT
278 		e.username,
279 		e.email,
280 		LEFT(e.bod_id, 7) AS bod_id,
281 		e.surname,
282 		e.firstname,
283 		e.status FROM $emaildb_table AS e 
284 		WHERE e.status IN ($statuses)
285 		$ignore{users}
286 		$ignore{emails}
287 		ORDER BY e.username
288 		;";
289 	# Prepare and execute the query
290 	my $equery = $edbh->prepare( $esql );
291 	$equery->execute();
292 	# Get a reference to an array of the results of the query
293 	my $array_ref = $equery->fetchall_arrayref( {
294 		username => 1,
295 		email => 1,
296 		bod_id => 1,
297 		surname => 1,
298 		firstname => 1,
299 		status => 1 }
300 	);
301 #
302 # End of email database query.
303 #
304 
305 my ($uid, $uname, $rtn, $msg);
306 
307 
308 # Loop through each row returned from the database, and check their user details
309 # against the RT database.
310 my $rdbh = DBI->connect("dbi:$rtdb_type:database=$rtdb_name;host=$rtdb_host",
311 "$rtdb_user", "$rtdb_pass", {
312 		RaiseError => 1,
313 		AutoCommit => 1,
314 		}) or die "Can't connect to db '$rtdb_name': $!\n";
315 my $sth = $rdbh->prepare("UPDATE Users SET Name = ? WHERE EmailAddress = ?");
316 
317 foreach my $row (@$array_ref) {
318 	unless ( ( $row->{username} ) && ( $row->{email} ) && ($row->{bod_id})){
319 		print "User '$row->{username}' (email '$row->{email}', bod_id '$row->{bod_id}') is being ignored";
320 		next;
321 	}
322 	print "Updating " . $row->{username} . " from " . $row->{email} . "\n";
323 	$sth->execute( $row->{username}, $row->{email} );
324 }
325 #$rdbh->commit;
326 
327 # Loop through each row returned from the database and try to add/update RT.
328 foreach my $row (@$array_ref) {
329 	# Sanity check - if username, email, or bod_id isn't set for a user,
330 	# we'll skip them and print a warning.
331 	unless ( ( $row->{username} ) && ( $row->{email} ) && ($row->{bod_id})){
332 		print "User '$row->{username}' (email '$row->{email}', bod_id '$row->{bod_id}') is being ignored";
333 		next;
334 	}
335 	$uname = '';
336 	# Check if the user already exists.  While this will slow down the first
337 	# run, as they won't exist, it should make successive runs faster.
338 	( $rtn, $msg ) = $CurrentUser->LoadByEmail( $row->{email} );
339 	if ( 'Found Object' eq $msg ) {
340 		$uid = $CurrentUser->PrincipalId;
341 		$uname = $CurrentUser->Name;
342 		if ( $uname ne $row->{username} ) {
343 			print "Error: user $row->{username} has a RT username of'$uname' when it should be $row->{username}.  Ignoring them.\n";
344 			next;
345 		}
346 		verbose( "Setting $row->{username}'s password to $row->{bod_id}");
347 		$CurrentUser->UserObj->SetDisabled(0);
348 		my ( $prtn, $pmsg ) = $CurrentUser->UserObj->SetPassword( $row->{bod_id} );
349 		print "prtn: $prtn\tpmsg: $pmsg\n";
350 		$CurrentUser->UserObj->SetPrivileged(0);
351 	} else {
352 		# User doesn't exist - let's go create them!
353 		verbose("User " . $row->{'username'} . " does not exist, attempting to create");
354 		my $new_entry = new RT::User($SystemUser);
355 		verbose("\t$row->{username}\t$row->{bod_id}\n" );
356 		( $uid, $msg ) = $new_entry->Create(
357 					Name => $row->{username},
358 					Password => $row->{bod_id},
359 					EmailAddress => $row->{email},
360 					RealName => "$row->{firstname} $row->{surname}",
361 				);
362 		# If it went right, $uid will be non-zero.
363 		if ( $uid ) {
364 			verbose("User " . $row->{'username'} . " created successfully (uid $uid).");
365 		} else {
366 			warn "(Error: $msg)\n";
367 		}
368 	}
369 	my $grouploaded = $CurrentGroup->LoadUserDefinedGroup( $groupmap{$row->{'status'}} );
370 	if ( 
371 		(	( 'Found Object' eq $grouploaded ) ||
372 			( 'Fetched from cache' eq $grouploaded )
373 		) && ( $uid )
374 	) {
375 		# Add user to group
376 		# If they are already a member, remove them from the group and
377 		# re-add them.
378 		my ( $return, $gmsg ) = $CurrentGroup->AddMember( $uid );
379 		if ( 'Group already has member' eq $gmsg ) {
380 			# return = 0 (ie unsuccessful), but we don't mind
381 			verbose( "User " . $row->{'username'} 
382 			. " is already a member of group " 
383 			. $groupmap{$row->{'status'}} );
384 			# Attempt to delete user from group:
385 			my ( $delrtn, $delmsg ) = $CurrentGroup->DeleteMember( $uid );
386 			if ( $delrtn and 'Member deleted' eq $delmsg ) {
387 				# Deletion was successful.
388 				verbose( "Removed user from group." );
389 				# Attempt to add user to group again
390 				my ( $addrtn, $addmsg ) = $CurrentGroup->AddMember( $uid );
391 				if ( $addrtn and 'Member added' eq $addmsg ) {
392 					# All was successful - let the user know
393 					verbose( "User added to group "
394 					. "successfully" );
395 				} else {
396 					# We couldn't readd them.  Let the user
397 					# know about this.
398 					verbose( "Couldn't add user '"
399 					. $row->{'username'} . " to group '"
400 					. $groupmap{$row->{'status'}}
401 					. "' successfully - returned code "
402 					. "'$addrtn', message '$addmsg'" );
403 				}
404 			} else {
405 				# We couldn't delete the user.
406 				verbose( "Couldn't remove user from group "
407 				. "successfully - returned code "
408 				. "'$delrtn', message '$delmsg'" );
409 			}
410 		} elsif ( 0 == $return ) {
411 			# no success
412 			warn "(Error: $gmsg)"
413 		}
414 	} elsif ( $uid ) {
415 		# User's all good, but group isn't.
416 		# This _shouldn't_ happen, thanks to the checkgroups() function,
417 		# but you never know.
418 		warn "(Error: Group ". $groupmap{$row->{'status'}}. " could not be loaded for user " . $row->{'username'} . ")\n";
419 		verbose( "Reason for error: '$grouploaded'" );
420 	}
421 	
422 }
423 
424 
425 # Cleanup.
426 my $rc = $edbh->disconnect or print $edbh->errstr;
427 
428 #
429 # And here is the end of the script.  Thank you for flying with perl, we hope
430 # you enjoyed it.
431 # Below is documentation in the POD format (man perldoc; perldoc rt-updatedb.pl)
432 #
433 
434 =head1 Name
435 
436 rt-updatedb.pl - script to add RT users from an email database
437 
438 =head1 SYNOPSIS
439 
440 B<rt-updatedb.pl> [B<--help>] [B<--verbose>]
441 [B<--emaildb_host <hostname>>]
442 [B<--emaildb_type <database_type>>]
443 [B<--emaildb_name <emaildb_name>>]
444 [B<--emaildb_table <emaildb_table>>]
445 [B<--emaildb_user <user>>]
446 [B<--emaildb_pass <pass>>]
447 [B<--ignore_user <username>>]
448 [B<--ignore_email <emailaddress>>]
449 [B<--ignore_file <file>>]
450 [B<--group [database_status=RT_group] [database_status=RT_group]>]
451 
452 =head1 DESCRIPTION
453 
454 I<rt-updatedb.pl> will create users for Request Tracker given the data from a
455 MYSQL database with email addresses, usernames, statuses, names and university
456 card numbers.
457 
458 =head1 OPTIONS
459 
460 =over 5
461 
462 =item B<--help>
463 
464 Prints out a statement on how to use the script.
465 
466 =item B<--verbose>
467 
468 Describes what the script is actually doing
469 
470 =item B<--emaildb_host> I<hostname>
471 
472 The host that the database is running on.  Note that this file must be run on
473 the host that RT is running on.
474 
475 =item B<--emaildb_type> I<type>
476 
477 The database type, specified as a valid perl driver name.  Defaults to mysql.
478 
479  Driver names:
480 	Database	Perl Driver
481 	---------------------------
482 	MYSQL		mysql
483 	Postgres	Pg
484 
485 =item B<--emaildb_name> I<database_name>
486 
487 The name of the MYSQL database that contains the email data.
488 
489 =item B<--emaildb_table> I<table_name>
490 
491 The table in the database that contains the email data.
492 
493 =item B<--emaildb_user> I<user_name>
494 
495 The MYSQL user that has permissions to read the L<--emaildb_table|/--emaildb_table table_name> table.
496 
497 =item B<--emaildb_pass> I<password>
498 
499 Password for the MYSQL user from L<--emaildb_user|/--emaildb_user user_name>.  Note that this is
500 I<INSECURE> if you do not trust all the users on your machine.  If in doubt,
501 specify it in the script instead.
502 
503 =item B<--ignore_user> I<username>
504 
505 A username to ignore when fetching the email lists, so the user is not readded
506 to request tracker.  Probably best used to exclude IT support staff.
507 
508 Example:
509 
510 	rt-updatedb.pl --ignore_user stuarts
511 
512 Would ignore the person with the username stuarts.
513 
514 Can be used multiple times to specify multiple people to ignore.
515 
516 =item B<--ignore_email> I<email address>
517 
518 Like L<ignore_user|/--ignore_user username>, this is a username to ignore when fetching the email
519 lists, so the user is not readded to request tracker.  It will qualify any
520 addresses given without domain (and @ symbol) to @worc.ox.ac.uk Probably best used to exclude it support staff.
521 
522 Like L<--ignore_user|/--ignore_user username>, can be used multiple times to
523 specify multiple people to ignore.
524 
525 =item B<--ignore_file> I<filename>
526 
527 Will read a list of usernames and/or fully-qualified email addresses that should
528 be ignored from I<filename>.  Will die if file cannot be read.
529 
530 Example file:
531 
532 	jcr-support@worc.ox.ac.uk
533 	
534 	stuarts
535 	
536 	itss0044
537 
538 Would ignore the address jcr-support@worc.ox.ac.uk and the users stuarts and
539 itss0044.
540 
541 
542 =item B<--group> I<status>=I<RT_Group>
543 
544 Allows you to set the Request Tracker group for all people with a particular
545 status.  rt-updatedb.pl will not process people who aren't listed as being in
546 one of these groups.
547 
548 Example:
549 	rt-updatedb.pl --group undergrad=JCR --group postgrad=MCR --group senmem=STAFF --group college=STAFF
550 
551 =back
552 
553 =head1 EMAIL DATABASE STRUCTURE
554 
555 The email database should have a table with the following fields: username (eg
556 itss0044), email (eg it-support@worc.ox.ac.uk), bod_id (will take the FIRST
557 SEVEN digits, surname, firstname, status.  Each user with a status listed in the
558 %groupmap hash (taken from L<--group|/--group status=RT_Group>) will be selected.
559 
560 =head1 RT SETUP
561 
562 Request Tracker should be configured to give all groups listed as RT_Groups
563 (L<--group|/--group status=RT_Group>) the following permissions on the
564 appropriate queue:
565 
566 S<CreateTicket, ReplyToTicket, SeeQueue, ShowTicket>
567 
568 ShowTicketComments could optionally be set; without it requestors cannot see the
569 comments made on their tickets.
570 
571 The users are added as 'non-privileged' users, so they see the RT Self-Service
572 interface, rather than the full-blown interface that 'privileged' users get.
573 
574 For example, using B<--group undergrad=JCRUsers> the group JCRUsers would need
575 to exist with the above permissions for the jcr support queue.  When the script
576 is run, everyone with the 'undergrad' status will be added to the JCRUsers
577 group, with the ability to see their own tickets, and create new ones.
578 
579 =head1 BUGS
580 
581 Users are never removed.  This means that if they move status groups (eg
582 undergrad -> postgrad), they will be able to access both queues when added.
583 
584 B<--input_file> does not accept usernames with an '@' symbol; it instead parses
585 them as email addresses.
586 
587 There should probably be some way to 'trial' this - ie to do everything _bar_
588 create the user.
589 
590 =head1 SECURITY
591 
592 This is a perl script.  There are almost certainly bugs.  Don't run in an
593 environment where you don't trust the system and the users, and beware of the
594 L<--emaildb_pass|/--emaildb_pass password> option, which will show up your password in process lists.
595 
596 =head1 LICENSE
597 
598 Released under the GNU GPL v2.
599 Enjoy.
600 
601 =head1 AUTHOR
602 
603 Original creator: Michael Howe <michael.howe@worc.ox.ac.uk>
604 
605 Created on request for Worcester College L<http://www.worc.ox.ac.uk>.
606 
607 =head1 COPYRIGHT
608 
609 Copyright Worcester College 2006
610 
611 =cut
612 
613 
614 # vim: tw=80
615 


syntax highlighted by Code2HTML, v. 0.9.1