Print | posted on Tuesday, February 22, 2011 7:46 PM
Planning and implementing Exclusion Filters for SharePoint Server 2010 User Profile Synchronization (UPS) is without doubt one of the most important aspects of any UPS deployment. By making use of Exclusion Filters we can narrow down the objects we sync with. Exclusion Filters reduce the amount of “junk” in the Profile database and can significantly decrease the time taken to perform synchronization runs.
I will be posting more about Exclusion Filters in general soon, but for this post I will concentrate on the most commonly used filter – that of the userAccountControl attribute in Active Directory. This is by far the one I am asked about most, and is commonly misunderstood.
The userAccountControl attribute is used to store the account options for an AD user object. These options include the status of the account (e.g. disabled, or locked out). Each option is a property flag. These flags are cumulative and thus there is only ever one value for userAccountControl.
Therefore it makes sense to create an exclusion filter to avoid synchronizing disabled accounts and so on. This one is easy and finds it’s way into most documentation such as the very good Configure profile synchronization. However it is a bit more tricky when dealing with other property flags and combinations of them.
Let’s start by taking a look at the core capability and exclude disabled accounts.
Once we select the userAccountControl attribute in the Edit Connection Filters page, it will refresh and populate the operators available to us:
Now this is where the fun starts. We could choose the Equals operator to filter on specific values. However, while possible, this is a very bad idea and can lead to unexpected results.
The decimal value of the ADS_UF_ACCOUNTDISABLE property flag is 2. But if we enter 2 in here and save the filter and then run a sync, disabled accounts will still be synced.
Remember that this attribute is cumulative. There is never going to be an account with a value of 2. For an account to be disabled it has to be an account in the first place! A normal disabled user account with no other property flags will be 514:
- Normal User Account (ADS_UF_NORMAL_ACCOUNT) = 512
- Disabled Account (ADS_UF_ACCOUNTDISABLE) = 2
But we shouldn’t use 514 either, because what happens if a disabled account also has the ADS_UF_HOMEDIR_REQUIRED property flag set? Then the value becomes 522. If we want to exclude all disabled accounts, we have a lot of work to do to create filters for each of the potential combinations. That’s just silly.
This is where the Bit on equals operator comes into play. This allows us to create filters which filter based upon a bit value.
If we go ahead and create a filter using Bit on equals 2 for the userAccountControl attribute, disabled accounts will always be excluded regardless of other property flags present. It doesn’t matter what the value, the second bit will always be “on”. To illustrate this let’s take the same example:
- Disabled User Account = 514 (In binary that’s 1000000010)
- Disabled User Account with Home Directory Required = 522 (In binary that’s 1000001010)
As you can see in both cases the “second bit is on”. And this is why we need the filter for disabled accounts to be “Bit on equals = 2”.
The problem here is we need convert the property flag into binary to determine the bit(s) we wish to check, and therefore which value to enter into the filter value text box.
As you may have noticed if you enter a number into the filter value when using the bit on equals operator and save the connection, the number changes when you go back in to view or edit it. Try it with 10 or 36. These values will magically become 16 and 54! This is a known bug and is fixed in the October 2010 CU and later. From the KB:
“You set a numeric value for a filter in a synchronization connection in SharePoint Server 2010. Then you save the filter. In this situation, you notice that the numeric value in the filter has unexpectedly increased when you view the filter again.”
There will never be a bit on equals value to check greater than 25 when using the standard AD property flags (for this attribute).
The next thing to do is to combine filters. Say we want to exclude disabled accounts and also accounts which have the Password Never Expires (ADS_UF_DONT_EXPIRE_PASSWD) flag set (these are commonly service accounts).
We need to add two filters to catch both:
- Disabled Account = 2 (In binary that’s 10)
- Password Never Expires = 65536 (In binary that’s 10000000000000000)
Therefore we need a “Bit on equals 17” for the accounts with the Password Never Expires flag in addition to the “Bit on equals 2” for disabled accounts.
But watch out, there’s a classic gotcha on the Edit Connection Filters page. When we add multiple filters the default operator between them is AND. This means that if we don’t change this, the only accounts that will be filtered in this case will be those that are both disabled and don’t have password expiry. That’s likely to be nowhere near as many as we want.
So we must create them using the OR operator:
If we’ve created them by accident with the AND operator, we have to remove them and recreate them. It gets worse. Take another look at the filter list above. Yup, that’s right, it doesn’t display which operator was applied! There is no way to tell from this UI which one was used. This is easily the number one reason why filters don’t work as expected and why planning and documenting your filters is paramount!
Let’s work through one more example. Say we wish to exclude all accounts which are either disabled, locked out, or for which the password has expired. These are the filters we would need:
- userAccountControl Bit on equals 2 OR
- userAccountControl Bit on equals 5 OR
- userAccountControl Bit on equals 24 OR
So that’s that. This isn’t difficult. But it’s certainly not obvious on first glance, it’s not documented, and it’s not the sort of thing the average SharePoint administrator is comfortable with. The Edit Connection Filters page doesn’t exactly help. Fundamentally this is about converting the property flags into binary to determine the bit(s) we wish to check, and therefore which value to enter into the filter value text box.
There is a couple of nice, old school web pages on property flags:
But wouldn’t it be nice if there was a handy reference table of all the possible bit on equals values for the various property flags, so you don’t need to crack out calc.exe in scientific mode? Not even SharePoint MCMs can think in binary once we get over about 16 or so :).
Well, here it is:
I will be posting more on Exclusion Filters over the next few weeks, until then happy filtering!