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