Jira LDAP Migration using SQL

[#1]

If you have to migrate your Jira user population from one LDAP domain to another it can be quite a hairy process with all sorts of gotchas:

• The user names don’t match in both domains
• And neither do other properties such as full names or emails.
• The domains could have management policies that prevent you from adding other properties to entries
• Users have left and their entries in both domains are disabled
• The work on migration on the domains themselves is a moving target outside of your control
• The cutover from one domain to another cannot have the Jira server down for an extended period
• The user would also like a new unicorn

In this case I chose to use SQL to re-map all user entries from old to new values. It works! But as with all Jira database interventions there are caveats

• If you break Jira it’s all on you, not support
• Make sure you have backups before starting and know how to restore them
• Are you comfortable with DB tasks
• make sure Jira service is not running - it won’t be happy
• Do this several times in a test system and test the results - comparing filter results may be sufficient. We got many errors initially and refined queries to massage them out.

You will also need to know how long this will take for planning purposes.

There is a conventional approach described in this knowledge article

https://confluence.atlassian.com/jirakb/migrating-between-external-ldap-directories-with-different-username-formats-873950445.html

I wish I’d read that first! But we would still have had issues to resolve. In it, approach one assumes you are able to request property changes - I wasn’t. Approach two requires scripting to deal with volumes - which was an option.

I will document how I approached it. I hope the SQL supplied will give you other ideas for approaches or will help you generate data for scripting. SQL examples are for  MySQL database. You will have to change the syntax slightly for other databases.

Tables with user id values are:

• jiraissue
• customfieldvalue
• projectroleactor
• userassociation
• filtersubscription
• searchrequest
• favouriteassociation
• worklog

I did have the data from the LDAP dept that mapped old names to new names which was used to drive all the SQL statements.

Data Mapping

Create a temp user lookup table to use in updates

create table if not exists userxref
(olduser VARCHAR(30), newuser varCHAR(30));

use the mapping data to generate insert statements. 

e.g.

INSERT INTO userxref VALUES ('olduserval', ‘newuserval');

 I used excel formulas for generation as the mapping data was supplied as a spreadsheet.

From this point onwards Jira must be shut down.

Jira standard user fields

SET SQL_SAFE_UPDATES = 0; -- allows sql updates without primary key value

update jiraissue 
set jiraissue.reporter= ifnull((
 select distinct ifnull(newuser, olduser) from userxref
 where jiraissue.reporter=userxref.olduser),
 jiraissue.reporter)


update 
 jiraissue 
set jiraissue.assignee= ifnull((
 select distinct ifnull(newuser, olduser) from userxref
 where jiraissue.assignee=userxref.olduser),
 jiraissue.assignee)

update 
 jiraissue 
set jiraissue.creator= ifnull((
 select distinct ifnull(newuser, olduser) from userxref
 where jiraissue.creator=userxref.olduser),
 jiraissue.creator)

 Jira custom user fields

You will need to identify the id’s of all custom user picker fields

SELECT * FROM jiradb.customfield
where customfieldtypekey in (‘com.atlassian.jira.plugin.system.customfieldtypes:multiuserpicker’,'com.atlassian.jira.plugin.system.customfieldtypes:userpicker')

 Also check if you have any plugin user fields. I didn’t.

Use the list of customfield id’s in the following statement:

update 
 customfieldvalue 
set stringvalue= ifnull((
 select distinct ifnull(newuser, olduser) from userxref
 where stringvalue=userxref.olduser),
 stringvalue)
where customfield in (11000,110001)

 For Project Roles

 update 
 projectroleactor 
set roletypeparameter= ifnull((
 select distinct ifnull(newuser, olduser) from userxref
 where roletypeparameter=userxref.olduser),
 roletypeparameter)

 For user associations ( filters, dashboards)

 update 
 userassociation 
set source_name= ifnull((
 select distinct ifnull(newuser, olduser) from userxref
 where source_name=userxref.olduser),
 source_name)

For Filter Subscriptions 

 update 
 filtersubscription 
set username= ifnull((
 select distinct ifnull(newuser, olduser) from userxref
 where username=userxref.olduser),
 username)

 For Filter owners

 update 
 searchrequest 
set authorname= ifnull((
 select distinct ifnull(newuser, olduser) from userxref
 where authorname=userxref.olduser),
 authorname);
 

update 
 searchrequest 
 set username= ifnull((
 select distinct ifnull(newuser, olduser) from userxref
 where username=userxref.olduser),
 username);

Filter definitions are more involved as they as xml clobs. The update statement should take the general form:

 update 
 searchrequest 
 set reqcontent= replace(reqcontent, 'oldnameval','newnameval')
 where reqcontent like ‘%oldnameval%'

This SQL can be generated from the user mapping temp table

 select
 concat(
 ' update 
 searchrequest 
 set reqcontent= replace(reqcontent,''',olduser,''',','''',newuser,
''') where reqcontent like ''%',olduser,'%'';') as query
 from userxref;

 and then run the output.

For Favourites

 update 
 favouriteassociations 
set username= ifnull((
 select distinct ifnull(newuser, olduser) from userxref
 where username=userxref.olduser),
 username)

 For Worklogs

update 
 worklog 
set author= ifnull((
 select distinct ifnull(newuser, olduser) from userxref
 where author=userxref.olduser),
 author);
update 
 worklog 
set updateauthor= ifnull((
 select distinct ifnull(newuser, olduser) from userxref
 where updateauthor=userxref.olduser),
 updateauthor);

For group membership updates I used REST calls.

First I created a view for group memberships

 drop view groupmap;

create view groupmap
as select parent_name
,us.display_name
,us.user_name
,us.email_address
, ifnull((
 select distinct ifnull(newuser, olduser) from userxref
 where us.user_name=userxref.olduser),
 us.user_name) as newval
from cwd_membership mem
join cwd_user us on us.id = mem.child_id

Then use that view in a query to generate curl statements to call the REST API.

 SELECT *,
concat(
'curl -insecure -u admin:password -X POST --data "{\\"name\\": \\”',newval,'\\"}" -H "Content-Type: application/json" https://localhost:8080/rest/api/2/group/user?groupname=', parent_name
) as curl
FROM jiradb.groupmap
where user_name != newval

e.g.

curl -insecure -u admin:password -X POST --data "{\"name\": \”newuserval\"}" -H "Content-Type: application/json" https://localhost:8080/rest/api/2/group/user?groupname=jira-administrators

 Existing users were left in place as they would not count towards licence one the old domain was disabled.

At this point:

• Jira can be restarted
• Check logs for any unexpected errors - dry running in dev with recent backups should create a smooth process fro production changes.
• Disable old domain.
• Reindex Jira to pick up data changes.
• Run any comparison filters you may have prepared

Voila! Let users loose on the new world.

[congstar GmbH]

Are comments on issues also adressed with those statements shown above?

[Tom Lister]

Hi @congstar GmbH 

I recently used SQL above to migrate a cloud service desk system to hosted server.

I needed the following SQL's. Comments are stored in Jira Actions.

update jiraaction set author= coalesce(( select distinct coalesce(newuser, olduser) from userxref where author=userxref.olduser), author);

update "AO_54307E_ORGANIZATION_MEMBER" set "USER_KEY"= coalesce(( select distinct coalesce(newuser, olduser) from userxref where "USER_KEY"=userxref.olduser), "USER_KEY");

update os_historystep set caller= coalesce(( select distinct coalesce(newuser, olduser) from userxref where caller=userxref.olduser), caller); 

update os_historystep set owner= coalesce(( select distinct coalesce(newuser, olduser) from userxref where owner=userxref.olduser), owner);