Skip to main content

Filter Records

If you have large amounts of data stored within your Active Directory and want to improve your integration performance, then you can filter the records before they are synchronised or sometimes loaded.

Data Sync has a couple of options available when filtering Active Directory records: LDAP Filter, Filter Box, and Dynamic Columns.

You can combine the use of each filter, for example you could use an LDAP filter with the filter box.
If you are finding that your synchronisation is taking longer than expected, combining or changing filter type might improve performance.

Filtering AD Records

Read on to find out how to use each type of filter.

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

LDAP Filtering​

The standard filtering option for Active Directory records is to use an LDAP Filter.

The basic connections to Active Directory use a simple LDAP Filter to return either the user, contact, group or computer records. You can extend this by editing the filter to suit your needs.

To edit the LDAP filter go to the connection properties below your columns and look for the property LDAPFilter. Click onto the ellipsis in the field to open the editor and edit the default filter to return the results you need. Once you are done click OK.

LDAP Filter

You then need to refresh the connection by clicking onto the refresh icon in the datasource toolbar to apply the filter. To preview the results click onto the Preview button in the datasource toolbar or Preview A in the schema map if you only want to view the columns added to the schema map. Data Sync will now only return the data that matches your filter.

Refresh and Preview

Below are a couple of examples of LDAP filters.

  • Return users that are members of the CRM Team Users Group : (&(objectClass=User)(memberOf=CN=CRM Team Users,CN=Users,DC=corp,DC=litware,DC=inc))
  • Return users where the attribute PasswordNeverExpires is set to True: (&(objectCategory=person)(objectClass=user)(userAccountControl:1.2.840.113556.1.4.803:=65536))

We have more details on LDAP Filter types and examples in our documentation page here.

Filter Function​

Another option is to use the built in Data Sync filter function, which can be found at the bottom of the source window or by clicking onto the filter button in the datasource toolbar.

Filter Location

To use this you simply enter in a C# function to filter your data. Either into the filter box or into the text field that opens when you click onto the filter button. Using the filter button will open a code editor window that will help you build the function with intellisense.

You might want to use this filter the records for contacts that have a specific last name or first name. For example to search for users with the last name Smith, you could use the expression LastName == "Smith".

info

Please note that column names must be in a C# friendly format i.e. not contain spaces or special character (except for underscores).

Filter Box Example

To apply the filter and check the data that is being returned click the Preview A button in the schema map toolbar. Make sure to add the columns you wish to preview to the schema map before clicking Preview A.

Preview A

Using the example expression above only the contacts with the surname Smith will be returned.

You can combine multiple expressions into one filter, for example to return only accounts that are enabled but are not the specific accounts of Lee Power and Andrew Fuller you can use the expression:

DSSAMAccountName != "Lee.Power" && DSSAMAccountName != "Andrew.Fuller" && DSUACDisabled == false

Dynamic Columns​

You can also construct your filter using Dynamic Columns. This option works well if your filter is complex as you can clearly define any parameters within the code, which would otherwise become too confusing in a standard LDAP Filter.

With Dynamic Column filtering just override the BeginRow() method and apply your filtering with .NET Code. This will then be evaluated at runtime and only the rows that return true from this function will be included in the resulting data set.

If you have a large AD it's still a good idea to partially filter the results with an LDAP Filter to maybe limit the OU then apply the .NET filter for the trickier parts.

The below filter is a simple one that filters the records so that only records where the DS-User-MemberOf column contains Manager are returned.

You will notice that the -'s have been removed from the column name as these are not C# friendly. If you use intellisense in Dynamic Columns you will find the DS- columns present without the -'s. 

public override bool BeginRow()
    {
        if ( this.DSUserMemberOf.Contains("Managers"))
            return true;
        
        return false;
    }

Dynamic Column Filtering