Move work logs to another user.

[Marat]

Hi

We are faced with the need to transfer some users to another domain. Old accounts will be blocked. Is it possible to automatically transfer all work log of users to a new account?

[Tom Lister · Accepted solution]

I  have migrated users from one domain to another. My method was SQL to update the user reference in multiple tables affected. I can post details if you're interested in that route.

affected tables are:

jiraissue

customfieldvalue

projectroleactor

userassociation

filtersubscription

searchrequest

favouriteassociation

[Marat]

Please tell me.

[Tom Lister]

Hi @Marat

I'll post in sections as I get it from my notes. Caveat - this is very low level stuff. We ran it dozens of times in test systems before we were happy with testing.

All of this should be performed with JIRA shutdown.

When complete, restart and reindex.

MySQL syntax

Create a temp user lookup table to use in updates

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

INSERT INTO userxref VALUES ('ab', 'cd');

1) for jiraissue updates to fixed 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)
 

[Tom Lister]

next for custom field user pickers you will need to get the field id's to use in SQL

SET SQL_SAFE_UPDATES = 0;

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

for project role actors

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

for user associations

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

[Tom Lister]

for filter subscriptions

SET SQL_SAFE_UPDATES = 0;

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

for search requests

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 are xml clobs. The update query is of the form

 update 
 searchrequest 
 set reqcontent= replace(reqcontent, 'oldname','newname')
 where reqcontent like '%oldname%'

for each user to be migrated. You can generate the statements from the temp  userxref table with

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

[Tom Lister]

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);

[Tom Lister]

final step is to update group memberships

As I recall I didn't get a satisfactory SQL method for doing this and eventually used REST API calls or Groovy scripts. I'll add a final note when I track it down.

JIRA will need to be started and reindexed for group updates

[Marat]

I am waiting for the final note. Ty Tom.

[Tom Lister]

Hi @Marat

bit short of time this morning so this an answer in principal only that I can't test but should be good. I'll check in later.

Create a view to map new user values for members with old users

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

use this to create a query to return curl commands to call REST API for each user

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\": \”nimda\"}" -H "Content-Type: application/json" https://localhost:8080/rest/api/2/group/user?groupname=jira-administrators

Удачи!

[Marcelo Sosa]

Please I have opened a ticket for Atlassian to work on this. It would be helpful if you could vote and spread the word in case we find more support for this.

https://jira.atlassian.com/browse/JRACLOUD-77171

Thank you