How Do I Generate a CSV Export of Users, and Memberships From a Specific Directory in Crowd?

CSV Exports are useful to aid non-conventional directory migration of users. Example of such migration includes the migration of LDAP directory users into Crowd Internal Directory, which is not possible with the conventional directory importer, due to this: CWD-947 - Cannot import from AD into Crowd Internal Directory (not delegated), but possible with CSV export and import. To generate CSV exports from a specific directory in Crowd, please follow the steps below:

1. Run this query to identify the ID of the Directory you wish to create the CSV export from (in the Crowd Database):

   1  SELECT * FROM cwd_directory;

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

2. To generate the Group Memberships CSV file:

   MySQL:

   1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 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_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 Directory from the earlier SELECT query

   Other Databases:

   1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16  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_id JOIN cwd_group g ON g.id = m.parent_id WHERE u.directory_id = <id> ORDER BY 2 ASC, 1 ASC

   Use your native database tools to generate a CSV export out of the output from this query

3. To generate the User CSV File:

   MySQL:

   1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 SELECT 'Username', 'First Name', 'Last Name', 'Email Address', 'Password' UNION ( SELECT u.user_name, u.first_name, u.last_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 Directory from the earlier SELECT query

   Other Databases:

   1 2 3 4 5 6 7 8 9 10 SELECT u.user_name, u.first_name, u.last_name, u.email_address, u.credential AS password FROM cwd_user u WHERE u.directory_id = <id> ORDER BY 2 ASC, 1 ASC

   Use your native database tools to generate a CSV export out of the output from this query

And you're done! Both CSV exports are now ready to be imported back to Crowd, to any directory of your choice.

To import, login to Crowd, then click on Users, followed by Import Users. Select the CSV Importer:

Specify the destination directory in Crowd. 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!