Summary

How to identify and correct invalid email addresses using either the migration assistant or making direct changes through the database.

Overview

When migrating from server or Data Center to the cloud, the Migration Assistant apps perform a series of pre-checks to make sure that the source instance doesn't contain users with invalid email addresses. As the email address serves as the primary identifier in the cloud, it is essential for it to be valid. Content migrated from Jira and Confluence will be associated with users in cloud based on their email addresses.

The Migration Assistant identifies individuals with email addresses that don't meet the necessary criteria. This assessment can be conducted at any stage prior to the migration process.

Detect Invalid Email Addresses 

The following section outlines the steps to identify invalid email addresses on the server or Data Center.

With the help of Jira Cloud Migration Assistant

To identify users with invalid addresses using JCMA, follow the steps below:

1. In Jira server or Data Center, go to Jira Administration > System

2. In the left panel, locate the Import and Export category, and select Migrate to cloud. The Migration Assistant home screen appears. 

   Migration Assistant home

   

3. Find the Assess and prepare your users card and click on Begin assessing
4. Connect to your destination cloud site:

   1. If you already have a cloud site, select Choose cloud site or choose it from the drop-down.

   2. If you don’t have a cloud site yet, select Get Jira cloud trial to try one for free. Learn more about Cloud migration trials
5. Click on Begin assessing. This will take you to a page with empty results
6. To initiate the assessment, select Begin assessing once again

7. Once the assessment is complete, the results will be visible, including any invalid email addresses. The screenshot provided below gives a clear representation of the assessment's findings.

   Invalid email address assessment error in JCMA

   

With the help of Confluence Cloud Migration Assistant 

To identify users with invalid addresses using CCMA, follow the steps below:

1. Go to Confluence Administration > look for the Atlassian Cloud category > select Migration Assistant. The Migration Assistant home screen appears 

   Migration Assistant Home

   

2. Find the Assess and prepare your users card and click on it.

3. Connect to your destination cloud site:

   1. If you already have a cloud site, select Choose cloud site or choose it from the drop-down.

   2. If you don’t have a cloud site yet, select Get Confluence cloud trial to try one for free. Learn more about Cloud migration trials

4. Click on Begin assessing. This will take you to a page with empty results.

5. To initiate the assessment, select Begin assessing once again.

6. Once the assessment is complete, the results will be visible, including any invalid email addresses. The screenshot provided below gives a clear representation of the assessment's findings.

   Invalid email address assessment error in CCMA

   

From the server/DataCenter Database 

Invalid email addresses can be identified by executing the following queries in the database: 

SELECT email_address
     , user_name 
  FROM cwd_user
 WHERE email_address !~ '^[A-Za-z0-9._%-]+@[A-Za-z0-9.-]+[.][A-Za-z]+$'
    OR length(substring(email_address from '(.*)@')) > 64
    OR email_address IS NULL;

SELECT email_address
     , user_name
  FROM cwd_user
 WHERE NOT REGEXP_LIKE (email_address, '^[A-Za-z0-9\.\!\#\$\%\&\''\*\+\-\/\=\?\^\_\|\~\(\)]+@([A-Za-z0-9_-]+\.?)+$', 'i')
    OR instr(email_address, '@') > 64
    OR email_address IS NULL;

SELECT email_address
     , user_name
  FROM cwd_user
 WHERE email_address NOT REGEXP '^[A-Za-z0-9\.\!\#\$\%\&\'\*\+\-\/\=\?\^\_\|\~\(\)]+@([A-Za-z0-9_-]+\.?)+$'
    OR LENGTH(substring_index(email_address, '@', 1)) > 64
    OR email_address IS NULL;

SELECT email_address
     , user_name
  FROM cwd_user
 WHERE NOT email_address LIKE '%_@%__%.__%'
    OR NOT PATINDEX('%[^a-z,0-9,@,.,_,\-]%', email_address) = 0
    OR charindex('@', email_address) > 64
    OR email_address IS NULL;

Fix Invalid Email Addresses

The invalid email addresses can be fixed by the following method:

• Manually on the source user directory.
• Directly on the UI of server or Data Center instance
• The automatic options from the Migration Assistant can be chosen to update the users during the migration.
• Alternatively, the data can be updated manually in the database of the server/Datacenter.

Manually on the source user directory.

Create valid email addresses (e.g. user@atlassian.com) and update them in the user directory, whether it is an LDAP server or server/Data Center instance.

Directly on the UI of server or Data Center instance

1. On the Jira server or Data Center instance:

   1. Select Administration > User management.

   2. Find the user you’d like to update using the filters at the top of the page.

   3. Select Edit next to the username.

   4. Update the email address, and select Update.

2. On the Confluence server or Data Center instance:

   1. Select Administration > User management.

   2. Find the user you’d like to update using the filters at the top of the page.

   3. Click on the User > Edit details

   4. Update the email address, and select Submit.

Using Migration Assistant

After performing the assessment outlined in the "Detect Invalid Email Address" section, the Migration Assistant will proceed to correct any invalid email addresses. It's crucial to understand that this method only impacts accounts created in the cloud during migration and does not modify the source directory. The automated adjustments are targeted at non-critical accounts, like old or testing ones, while updates for active users working in the cloud should come from your user directory.

To automatically fix invalid email addresses:

1. In the Migration Assistant, view the results in the Assess and prepare your users section.

2. When viewing the results, select Fix invalid emails.

3. Choose one of the options to fix invalid emails. For more information on each option, see 

   • Fix invalid email addresses on Jira

   • Fix invalid email addresses on Confluence

Manually update users in the database

The following query will update all those invalid users to the format: invaliduser-<id>@domain.com

Make sure to adjust the queries by adding a specific domain or changing the format.  Once completed, restart the Server or Data Center instance. 

UPDATE cwd_user 
SET email_address = 'invaliduser-' || id || '@domain.com', 
    lower_email_address = 'invaliduser-' || id || '@domain.com'
WHERE lower_email_address IN (SELECT lower_email_address FROM cwd_user WHERE lower_email_address !~ '^[A-Za-z0-9._%-]+@[A-Za-z0-9.-]+[.][A-Za-z]+$');

UPDATE cwd_user
SET email_address = 'invalideuser-' || id || '@domain.com',
    lower_email_address = 'invalideuser-' || id || '@domain.com'
    WHERE  lower_email_address in (SELECT lower_email_address
FROM cwd_user
WHERE NOT REGEXP_LIKE (lower_email_address, '^[A-Za-z0-9\.\!\#\$\%\&\''\*\+\-\/\=\?\^\_\|\~\(\)]+@([A-Za-z0-9_-]+\.?)+$', 'i'));

UPDATE cwd_user
SET email_address = 'invalideuser-' || id || '@domain.com',
    lower_email_address = 'invalideuser-' || id || '@domain.com'
WHERE email_address NOT REGEXP '^[A-Za-z0-9\.\!\#\$\%\&\'\*\+\-\/\=\?\^\_\|\~\(\)]+@([A-Za-z0-9_-]+\.?)+$';

UPDATE cwd_user
SET email_address = CONCAT('invaliduser-',id,'@domain.com'),
    lower_email_address = CONCAT('invaliduser-',id,'@domain.com')
    WHERE lower_email_address in (SELECT lower_email_address
FROM cwd_user WHERE not lower_email_address LIKE '%_@__%.__%'or not PATINDEX('%[^a-z,0-9,@,.,_,\-]%', lower_email_address) = 0);