Merging existing users and LDAP

Infos:

  • Used Zammad version: 3.6
  • Used Zammad installation source: package
  • Operating system: CentOS 7.9

Hello,

I would like to merge existing users with LDAP users but some existing users are also in LDAP so I would like to transfer existing tickets to the correspondent new LDAP users.

What is the suggested way to do it? Can I manually replace id from users table?

For a more detailed explanation, let’s say that I have an existing user with id 20 (in users table) and a new one with id 35, coming from LDAP integration.

So I would like to move tickets of user 20 to user 35 and then remove user 20. Note that the new user (35) doesn’t have any activity at the time of merging/changing ID

I migrated succesfully all users. Here’s a SQL snippet code to change user ID 73 to 68 and user ID 79 to 81.

Just update the list in CASE statement and WHERE clause with your user list
Use at your own risk!

karma_users table should be changed manually (i.e. remove rows with new user IDs before running the script)

UPDATE public.histories
SET created_by_id = CASE created_by_id
                    WHEN 73	THEN 68
                    WHEN 79	THEN 81
                    END
                    WHERE created_by_id IN (73, 79);


UPDATE public.ticket_articles
SET updated_by_id = CASE updated_by_id
                    WHEN 73	THEN 68
                    WHEN 79	THEN 81
                    END
                    WHERE updated_by_id IN (73, 79);


UPDATE public.ticket_articles
SET created_by_id = CASE created_by_id
                    WHEN 73	THEN 68
                    WHEN 79	THEN 81
                    END
                    WHERE created_by_id IN (73, 79);


UPDATE public.karma_activity_logs
SET user_id = CASE user_id
              WHEN 73	THEN 68
              WHEN 79	THEN 81
              END
              WHERE user_id IN (73, 79);


UPDATE public.ticket_articles
SET origin_by_id = CASE origin_by_id
                   WHEN 73	THEN 68
                   WHEN 79	THEN 81
                   END
                   WHERE origin_by_id IN (73, 79);


UPDATE public.roles_users
SET user_id = CASE user_id
              WHEN 73	THEN 68
              WHEN 79	THEN 81
              END
              WHERE user_id IN (73, 79);


UPDATE public.cti_caller_ids
SET user_id = CASE user_id
              WHEN 73	THEN 68
              WHEN 79	THEN 81
              END
              WHERE user_id IN (73, 79);


UPDATE public.taskbars
SET user_id = CASE user_id
              WHEN 73	THEN 68
              WHEN 79	THEN 81
              END
              WHERE user_id IN (73, 79);


UPDATE public.online_notifications
SET user_id = CASE user_id
              WHEN 73	THEN 68
              WHEN 79	THEN 81
              END
              WHERE user_id IN (73, 79);


UPDATE public.online_notifications
SET created_by_id = CASE created_by_id
                    WHEN 73	THEN 68
                    WHEN 79	THEN 81
                    END
                    WHERE created_by_id IN (73, 79);


UPDATE public.online_notifications
SET updated_by_id = CASE updated_by_id
                    WHEN 73	THEN 68
                    WHEN 79	THEN 81
                    END
                    WHERE updated_by_id IN (73, 79);


UPDATE public.stores
SET created_by_id = CASE created_by_id
                    WHEN 73	THEN 68
                    WHEN 79	THEN 81
                    END
                    WHERE created_by_id IN (73, 79);


UPDATE public.activity_streams
SET created_by_id = CASE created_by_id
                    WHEN 73	THEN 68
                    WHEN 79	THEN 81
                    END
                    WHERE created_by_id IN (73, 79);


UPDATE public.stats_stores
SET created_by_id = CASE created_by_id
                    WHEN 73	THEN 68
                    WHEN 79	THEN 81
                    END
                    WHERE created_by_id IN (73, 79);


UPDATE public.karma_users
SET user_id = CASE user_id
              WHEN 73	THEN 68
              WHEN 79	THEN 81
              END
              WHERE user_id IN (73, 79);


UPDATE public.tickets
SET owner_id = CASE owner_id
               WHEN 73	THEN 68
               WHEN 79	THEN 81
               END
               WHERE owner_id IN (73, 79);


UPDATE public.tickets
SET customer_id = CASE customer_id
                  WHEN 73	THEN 68
                  WHEN 79	THEN 81
                  END
                  WHERE customer_id IN (73, 79);


UPDATE public.tickets
SET created_by_id = CASE created_by_id
                    WHEN 73	THEN 68
                    WHEN 79	THEN 81
                    END
                    WHERE created_by_id IN (73, 79);


UPDATE public.tickets
SET customer_id = CASE customer_id
                  WHEN 73	THEN 68
                  WHEN 79	THEN 81
                  END
                  WHERE customer_id IN (73, 79);


UPDATE public.tickets
SET updated_by_id = CASE updated_by_id
                    WHEN 73	THEN 68
                    WHEN 79	THEN 81
                    END
                    WHERE updated_by_id IN (73, 79);


UPDATE public.recent_views
SET created_by_id = CASE created_by_id
                    WHEN 73	THEN 68
                    WHEN 79	THEN 81
                    END
                    WHERE created_by_id IN (73, 79);

Dear future anon finding this thread via a wild internet safari / search:

Please note that above mentioned methods are dangerous and not supported.
Running above commands may lead to a broken system - don’t do it if you’re super sure what you’re doing.


A wild guess:
Login name and/or email address of your local user and your ldap users did not match.
This is the reason why Zammad couldn’t recognize the users correctly.

Your method is a very radical version which may break users and their relations.

Are you saying that if I matched email addresses of the local users with LDAP users they were matching automatically?

I agree with you that is dangerous, I had a DB backup. Remember to always backup your DB/files before making unsupported changes

Yes, that should work without any trouble.

Thank you very much,

that’s good to know!

1 Like