Cleaning up autocreated users

Looking for some advice on a mess in my RT (3.4.5) user table.

Recently our org merged and all users got new email addresses.
Unfortunately, our RT user table wasn’t ready for it and we now have a
large number of autocreated users, most duplicating existing users. So,
we have, say, asparks@merklequris.com as well as asparks…

Causes additional problems, in that I cannot change the email addresses on
the original accounts – as it conflicts now with the autocreated users.

Anyone have any advice on handling this situation, to get rid of these
autocreated users? And how can I prevent autocreation going forward?

Any recommendations highly appreciated. Thanks.
-Alan

Alan Sparks, UNIX/Linux Systems Administrator asparks@doublesparks.net

Hello!On 6/16/06, Alan Sparks asparks@doublesparks.net wrote:

Looking for some advice on a mess in my RT (3.4.5) user table.

Recently our org merged and all users got new email addresses.
Unfortunately, our RT user table wasn’t ready for it and we now have a
large number of autocreated users, most duplicating existing users. So,
we have, say, asparks@merklequris.com as well as asparks…

Causes additional problems, in that I cannot change the email addresses on
the original accounts – as it conflicts now with the autocreated users.

Anyone have any advice on handling this situation, to get rid of these
autocreated users? And how can I prevent autocreation going forward?

Here’s how I’d approach it. This assumes you make god back

First, stop your RT web instance and mail gateway; you don’t want
anyone affecting the database until you’re done.

Now back up your database just in case this all goes horribly pear-shaped.

Now do something like this in the database:

update users set emailaddress = [...] where emailaddress like

‘%@newdomain.com’;

…where […] is some non-conflicting email address that is still
valid. Perhaps user@mailhost.newdomain.com?

Now update all your existing users with a similar SQL statement:

update users set emailaddress = [...]
  where emailaddress not like '%@mailhost.newdomain.com';

…where […] this time is the address that you want to migrate to
(e.g. user@newdomain.com)

And I’m using […] because I don’t know how to do the string magic
you’ll want to do in SQL. Hopefully you do, or someone else on the
list can volunteer.

Now do the prevention. I’d probably use the
CanonicalizeEmailAddressMatch regex in RT_Config.pm (copy it to
RT_SiteConfig.pm first, of course) to match both the old and new
domain names, then set CanonicalizeEmailAddressReplace to output the
new addresses only.
I think that should cover it.

Good luck!

–j
Jim Meyer, Geek at Large purp@acm.org