[rt-users] Cleaning up autocreated users

[Jim Meyer]

Hello!

On 6/16/06, Alan Sparks <asparks at 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 at 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 at 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 at 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 at acm.org