Purpose

To import delegated LDAP users from Confluence into Crowd. You can create 2 CSV (Comma Separated Values) files from your database of users and memberships that are in the Delegated Directory and import them into Crowd. The following examples are for MySQL only. (for other databases, please refer to your database manual on how to generate a CSV file from a query).

Solution

First, run this query to identify the ID of the Delegated Directory (in the Confluence Database):

SELECT * FROM cwd_directory;

Record the ID of the Delegated Directory, then use it in the following queries:

• To generate the Group Memberships CSV file:

  SELECT 'Username', 'GroupName'
  UNION
  (
  SELECT
  u.user_name,
  g.group_name
  FROM
  cwd_user u
  JOIN cwd_directory d
  ON
  u.directory_id = d.id
  JOIN cwd_membership m
  ON
  u.id = m.child_user_id
  JOIN cwd_group g
  ON
  g.id = m.parent_id
  WHERE u.directory_id = <id>
  ORDER BY 2 ASC, 1 ASC
  INTO OUTFILE '/path/to/memberships.csv'
  FIELDS TERMINATED BY ','
  ENCLOSED BY '"'
  LINES TERMINATED BY '\n');

  Replace /path/to/memberships.csv with the full path to the memberships csv file that you wish to generate, and <id> with the ID of the Delegated Directory from the earlier SELECT query

• To generate the User CSV File:

  SELECT 'FirstName', 'LastName', 'Username', 'EmailAddress', 'Password'
  UNION
  (
  SELECT
  u.first_name,
  u.last_name,
  u.user_name,
  u.email_address,
  u.credential AS password
  FROM
  cwd_user u
  WHERE u.directory_id = <id>
  ORDER BY 2 ASC, 1 ASC
  INTO OUTFILE '/path/to/users.csv'
  FIELDS TERMINATED BY ','
  ENCLOSED BY '"'
  LINES TERMINATED BY '\n'
  );

  Replace /path/to/users.csv with the full path to the users csv file that you wish to generate, and <id> with the ID of the Delegated Directory from the earlier SELECT query

After this is done, login to Crowd, then click on Users, followed by Import Users. Select the CSV Importer:

Specify the destination directory as a Delegated Directory in Crowd (that you might have created much earlier). Then specify the path to both the users.csv and memberships.csv that are generated with the SQL queries earlier, something like this:

Then specify the mapping accordingly, something like this:

Click Continue and Voila! You're done!