Export Group Membership Details
Data Sync can be useful to assign users to groups in Active Directory and also export a list of users that are members of groups in your Active Directory.
You might want an export to review current group membership, check that the correct users are in the correct groups, or be used to update another business system e.g. a SharePoint list.
Requirements
Before getting started you need to ensure you have the following:
- Windows 10 or Windows Server
- Downloaded & Installed Data Synchronisation Studio
- A data set with your user details listed
- Access to Active Directory
Active Directory Group Connector Options
When connecting to Active Directory Groups you have a few options available, which you use depends on how your data is structured and whether you want to create groups at the same time.
The Group Membership connector returns a list of all the groups within the connected Active Directory environment or organisational unit and the users that are members as separate rows. This uses the Active Directory V2 - Group Members connector, and can only be used to add and remove users from existing groups. An example of the dataset this produces can be seen below:
The Groups connector returns a list of all the groups and a column with an array of the users that are members. This uses the Active Directory V2 - Users/Contacts/Groups/Computers connector with the Groups connection and can be used to create groups if they do not already exist and add users to those groups. An example of how this looks as a dataset is:
If your data has a list of users with an array column containing the groups they are members of, then you can use the Active Directory V2 - Users/Contacts/Groups/Computers connector with the Users connection. An example of how this looks as a dataset is:
We cover using each of these in different scenarios in the next sections.
If you need to get your data from group members to an array of members then you can do so with a SQL statement. The statement below aggregates the members for each group into an array separated by a semi colon, grouped by the group name.
SELECT [Group],
STRING_AGG([Members], ';') WITHIN GROUP (ORDER BY [Members] ASC )
FROM [Groups]
GROUP BY [Group]
You can then use this data set with Active Directory V2 - Users/Contacts/Groups/Computers connector.
Export Group Membership
To export the Group Membership you need to first connect to Active Directory. Decide how you would like your data returning either separate rows for each member in a group or an array of members against each group.
For this example we will use the Group Membership connector to return a list of all the groups and all the users contained in the groups.
Start by connecting to your Active Directory Groups Members. To do this click open the connection window and go to Active Directory > Active Directory V2 - Group Members.
Then decide if you want to export it to a business system or as a simple CSV, Excel, XML or JSON file.
Export to a File
To export your members list to a file, you can either use the create functions from the tools menu or you can use the export options on the preview tab.
Start by adding the columns you want to include in your export to the schema map.
Then to export to a file, click onto Preview A. You can then choose from the export options which file type you would like and then save the file. Choose between an Excel spreadsheet, a CSV, XML or JSON file.
Alternatively you can use the tools menu to create a new CSV or XML file. Just go to Tools and select the option you would like. Select the location to save the file and click save.
Then check the schema mapping, and make sure you have defined a key column. The key column for the group membership is going to be a composite key as the group is not unique. Your mapping might look similar to this:
To synchronise the data just click Compare A > B and sync the results.
Export to a Business System
To export your members list to another business system, for example a SharePoint List or a SQL Table, you can simply connect your target to the list or table if it already exists.
If you need to create a new list or SQl table you can make use of the quick create functions to create them. Just open the Tools menu and select the option you would like to use. For example to create a new SQL table you can select Create Sql Table and then follow the instructions in the wizard to create it. The columns will be populated based upon the columns/attributes added to the schema map.
Make sure to set your key columns, this needs to be identifying so that each record appears as unique. In this example we need to use a composite key, so both the Group AccountName and the User AccountName.
To add the data to the table click Compare A > B followed by Synchronise and then Start. You can preview the results before synchronising to make sure the data presents as expected. Below you can see an example of the preview results, where we have 11 records to add.
Make sure to save your project for future use. You might want to schedule it to run on a regular basis using either the Run Tool or Ouvvi Automation Server.
Manage Group Membership
To manage group membership in Active Directory you can use a source dataset to add and remove users from groups. It depends on how your data is presented in your source system as to which Active Directory connector you will use in Data Sync.
If your dataset has the users listed against the groups, for example:
- Group 1 | User A
- Group 1 | User B
- Group 2 | User A
Then you will use the Active Directory V2 - Group Membership connector.
If your dataset lists the groups and the members within that group as an array (separated by a semi-colon) then you can use the normal Active Directory V2 - Users/Contacts/Groups/Computers connector.
- Group 1 | User 1;User 2
- Group 2 | User 1
- Group 3 | User1;User3;User5
To create new groups you will need to ensure you are connected using the Groups option in the Default Attributes connection property as all the other types do not support group creation.
In the following example we have groups with the members listed as an array, this will be being updated with data stored in a SQL Table. There will be a few groups listed in the source that are not yet in Active Directory.
Connect to your Source Data
Start by opening Data Sync and connecting to your source data. You can use any of the built in connectors in Data Sync or build your own connector if we do not have one readily available.
For this example we are connecting to a SQL Table that has the group AccountName and a column containing an array of the users that should be members. This dataset looks like this:
Connect to your Active Directory Groups
You now need to connect to Active Directory. To do this click onto Connect Datasource in the target window and go to Active Directory > Active Directory V2 - Users/Contacts/Groups/Computers.
As this project is specifically updating groups we need to change the DefaultAttributes property to Groups. If you wanted to modify other attributes alongside the group membership then you could leave the DefaultAttributes property set to Users, but we discuss this in more detail in the next section.
Then enter in the LDAP path to your Active Directory instance, and enter in any credentials you need to access AD. You can enter in the full LDAP path (including the server name) or just the server name.
If you just use the server name your path would look similar to: LDAP://dc01.
Otherwise an example path connecting to a specific OU could be: LDAP://dc01/OU=Test,DC=demo,DC=simego,DC=com.
We have more details on finding the LDAP Path in our documentation.
Then click Connect & Create Library Connection to save the connection to the connection library. Enter in a name for the connection into the windows that pops up and click OK. You can refresh the connection library window (to the left of the Data Sync window) and your Active Directory connection will now be visible in the list.
This only needs to be done once per OU as you will be able to access other objects such as Users and Contacts from the connection window.
If you have already saved your connection to AD you can select the Groups object from your AD connection.
Map the columns
Now map the source columns to their corresponding attributes in Active Directory. Make sure to map the user array to the DS-MemberNames column as this will handle the lookup of the user names for you.
Make sure to set the Group SAMAccountName as the key column so that each group can be identified.
Preview the Results & Sync
Once you have configured the schema map you can preview the results before synchronising.
To do this click on the Compare A > B button in the toolbar, this will display the number of records to be added, updated and deleted. Please note that deletes are disabled by default but will still show in the compare results. To enable them please set EnableDelete to True in the target connection properties.
You can preview the data changes by clicking onto each section. In this example we have 2 groups to create and 2 groups to update. Clicking onto the result rows will show the data to be changed.
For updates any changes are highlighted in yellow.
Please note that deletes are disabled by default but will still show in the results if the group is not present in the source data. To enable deletes set EnableDelete to True on the target connection properties.
Once you are ready to apply the changes to Active Directory click onto the Synchronise button and then click Start.
Your users will now be added to their relevant groups, and if the group did not exist it will now be created.
Make sure to save your project so that you can use it again in the future.
If you would like to schedule your project to run automatically you can do so using either the Run Tool or Ouvvi Automation Server with both event and time based triggers.
Adding Groups to User records
You can also manage group membership when adding and updating existing users in Active Directory. Your user records need to have the groups they are a member of listed in a column separated by a semi-colon.
An example of how this might look in a dataset is:
Connect to Active Directory as normal using the Active Directory V2- Users/Contacts/Groups/Computers connector and make sure the DefaultAttributes is set to users.
Then map your group array column to the schema map and make sure to include a key column to identify the user. We recommend using the SAMAccountName as this will always be unique. In this example we are also updating the manager email address so this is also added to the schema map.
Then run the compare and preview the results. Once you are satisfied that the columns are mapped correctly click Synchronise and then Start to begin the sync.
Please note that if your groups do not already exist in Active Directory then an error will be thrown. You will need to use the Groups connector to be able to create new groups.
For more ideas on how you can use Data Sync with Active Directory check out our AD solutions page. If you're looking to synchronise Active Directory group members to a SharePoint security group take a look at our other post here.