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);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!
This topic was automatically closed 120 days after the last reply. New replies are no longer allowed.