MOSS 2007 allows you to import profiles from a user store to populate user information in SharePoint. 90% of the time this is an import from Active Directory (AD). While AD is used to store users, it's also used to store other non-user resources like conference rooms and projectors. These are things that you want to have calendars so you can schedule them. That's a good use of technology. However, depending on how your AD is laid out your users and resources end up together in the same Organizational Unit (OU). Unlike chocolate and peanut butter, users and resources aren't always great together if you're using SharePoint profile import. You don't necessarily want all your conference rooms cluttering up your SharePoint profiles. There's good news and bad news about that. The good news is that you can tweak your profile import with LDAP query strings. The bad news is that there are only three people in the whole world that understand LDAP queries. The worse news is that I'm not one of them.
When this comes up there's normally much searching of Bing, gnashing of teeth and swearing when LDAP queries need to be tweaked to fix profile imports on MOSS. It's always a horrible experience for consultant and customer alike. This came up again a couple of weeks ago. A customer had a SharePoint environment that was importing a lot of non-user resources and they wanted it to stop. They had a separate AD team at their company so they couldn't just move the resources into another OU or give them a custom attribute or something easy to search on. I was tasked with finding an LDAP query string that would rid them of all their non-user resources without any help from their AD team. A task nearly impossible for humans. I did find a way…
While trying to figure out how best to accomplish this I asked a buddy of mine, Jason, who deals with AD a lot. I'd link to his blog, but he hates blogs. Probably because he rarely has anything intelligent to post (not that that stops me). So here's your shoutout, Jason. Anyway, as I was whining about my lot in life, Jason came up with probably the only smart thing I've ever heard him say, "Why don't you use ADUC to create your LDAP query strings?"
"ADUC" is the Active Directory Users and Computers snap-in used to manage users and computers in your AD. Unbeknownst to me, ADUC has a search function that creates these cursed LDAP query strings with a GUI. More importantly, after you've perfectly crafted your LDAP search string, you can copy it out and paste it right into SharePoint. As an added bonus, ADUC gives you immediate results on your search, so you don't have to run a profile import in SharePoint every time to see if it worked. It worked like a champ for the customer, so I thought I'd outline it all here.
Here's the before picture:
You can see we've got some users here and a whole lot of conference rooms. Here's what our SharePoint profile import settings look like:
We're just importing the whole domain, which is why we're getting all the extra garbage in our imports. Normally we'd set our "Import profile data from" setting to "Custom Source", cross our fingers and hope for the best. We're going to do that, but with a better plan. To start, we'll open up ADUC and start whittling down the results until we get what we want. Normally ADUC is installed only on Domain Controllers (DC) but it can be loaded on any server or workstation version of Windows by installing the Server Admin tools. Let's fire up ADUC and see what our users look like:
I added some extra columns to the Users view to see what information I had to work with. I ended up going with the Job Title column, as all the real users had that populated. Now I just need an LDAP query that says "Only show me objects that have Job Title" populated. No problem for ADUC. Here's a breakdown of how I created the query:
I right clicked on Saved Queries and picked New Query. Then I named it "Only users" and clicked "Define Query." On the next box I clicked "Users, contacts…" from the Find dropdown., then I clicked the Advanced tab. Then I used the Field dropdown to pick a Field and the appropriate Condition and Value that I want. In the screenshot I defined a condition where I only get objects that have a Job Title present. When you click OK the New Query box will show you the query that was created for you. It looks like this:
The Query string at the bottom is what we'll use, if it gives us the results we want. When we hit the final OK box we'll see the results of the query we generated:
As you can see we now are only getting users, which is what we wanted. Now go back in to that query (via Edit) and copy out the Query string. It should look like this:
(&(&(|(&(objectCategory=person)(objectSid=*)(!samAccountType:1.2.840.113556.1.4.804:=3))(&(objectCategory=person)(!objectSid=*))(&(objectCategory=group)(groupType:1.2.840.113556.1.4.804:=14)))(objectCategory=user)(objectClass=user)(title=*)))
That's quite a mouthful. Now we'll paste this in to SharePoint. Go back into your Profile Import settings and select "Custom Source." Then go edit the Content Source, we need to change the User Filter. Paste our new query into there:
Then go back in and run a Full Import of your profiles. Here's what it should look like:
Looks like we pulled it off.
In your organization your query will likely need different formatting to match how your users and resources are configured in AD, but now you have a tool to quickly and easily build these queries and try them out.
Hope that helps,
tk