Suman Chakrabarti - SharePoint and ASP.NET blog : MOSS Profile Search LDAP query to remove disabled and service accounts

Thursday, June 28, 2007 10:28 AM Suman Chakrabarti

MOSS Profile Search LDAP query to remove disabled and service accounts

To put together the right query to get users that are not disabled accounts nor service accounts, you will need to add the following parameters to the query.

Update: I found a better approach to removing disabled accounts

(!userAccountControl:1.2.840.113556.1.4.803:=2) - removes disabled accounts
(!userAccountControl=65536) - removes accounts with password set to never expire

Your LDAP string should look something like this:

(&(objectCategory=person)(objectClass=user)(!userAccountControl:1.2.840.113556.1.4.803:=2)(!userAccountControl=65536))

For more ADSI userAccountControl flags go here: http://msdn2.microsoft.com/en-us/library/aa772300.aspx

Filed under: SPS 2003/WSS 2, SharePoint, ADSI, Search

Comment Notification

If you would like to receive an email when updates are made to this post, please register here

Subscribe to this post's comments using RSS

Comments

# re: MOSS Profile Search LDAP query to remove disabled and service accounts

Friday, August 17, 2007 5:40 PM by Bob

How would you add a check to see if there was also an email address. For example, I would like to do what you suggest above only also make sure that there is a valid email address. Does the filter support a wild card so you could try (mail=*.com)

# re: MOSS Profile Search LDAP query to remove disabled and service accounts

Saturday, August 18, 2007 3:32 PM by Suman Chakrabarti

The search you supplied will definitely do so. Wildcards work great in LDAP queries. The query structure for LDAP is such

(&()()()) – this is to AND everything within the parens

(|()()()) – this is to OR everything within the parens

Make sure you make your email query like so:

(&(objectCategory=Person)(objectClass=User)(!userAccountControl=512)(!userAccountControl=66050)(mail=*.com))

# re: MOSS Profile Search LDAP query to remove disabled and service accounts

Friday, August 24, 2007 10:08 AM by Bob

This query works; however, returns back fewer than the expected results. After researching this more, all of our active users are in two branch OUs underneath one parent OU and have a vaild email address. There are other branch OUs also.

I tried this query, but it doesn't seem to work. I basically am looking for all users in one or the other of two OUs under a main OU with a valid email address.

ParentOU

Branch1OU (I want users in this branch)

Branch2OU (I want users in this branch)

Branch3OU (I don't want users from this branch)

(&(objectCategory=Person)(objectClass=User)(mail=*com)(|(memberOf=CN=Authenticated Users,OU=Branch1,DC=domain,DC=local)(memberOf=CN=Authenticated Users,OU=Branch2,DC=domain,DC=local)))

Thoughts on what is wrong with this query?

# re: MOSS Profile Search LDAP query to remove disabled and service accounts

Friday, August 31, 2007 12:19 PM by Suman Chakrabarti

The memberof field is actually for storing that the user is a member of a group.

If you are trying to get information for specific OUs, it will be easier to make separate sources for each OU. The OU is specified in the LDAP path and can't be filtered on in the query. You may want to only use the filter for users who are not disabled instead of the "password never expires" filter.

[edited]
To limit your query to a specific OU, you'll need to change your memberof query to the following:

(memberof:1.2.840.113556.1.4.1941:=(CN=Authenticated Users,OU=Branch1,DC=domain,DC=local)))

There's more information here:
http://msdn2.microsoft.com/en-us/library/aa746475.aspx

# re: MOSS Profile Search LDAP query to remove disabled and service accounts

Wednesday, September 05, 2007 6:59 AM by Martijn Zoomers

Suman, that won't work since you can only specify one import connection per domain.

# re: MOSS Profile Search LDAP query to remove disabled and service accounts

Wednesday, September 05, 2007 10:48 AM by Suman Chakrabarti

I tried a dozen ways and you're right. I've modified my comment.

# re: MOSS Profile Search LDAP query to remove disabled and service accounts

Wednesday, October 24, 2007 9:49 AM by richrockwell

Thanks, this is very helpful and solved a nagging problem I had.

I would like to be able to do the same thing with my peoplepicker queries (such as for adding users to SharePoint groups). I am familiar with many of the stsadm peoplepicker options and am using some of them, but is there a way to do something similar to this to ignore inactive accounts and/or those with no emails in the peoplepicker queries? As it stands, my list of people who have profiles (imported via Shared Services Provider) are seriously out of sync with the results I get from peoplepicker.

# MOSS Profile Search LDAP Query that Removes Disabled and Service Accounts

Thursday, October 25, 2007 4:09 AM by Aidan Garnish

MOSS Profile Search LDAP Query that Removes Disabled and Service Accounts

# MOSS Profile Search LDAP Query that Removes Disabled and Service Accounts

Wednesday, November 14, 2007 2:43 PM by Mirrored Blogs

The usual LDAP query to import user profiles from AD into MOSS is: (&(objectCategory=person)(objectClass

# re: MOSS Profile Search LDAP query to remove disabled and service accounts

Thursday, April 17, 2008 1:04 PM by KubuS

None of the queries with & at the beginning has worked for me. No matter how I tried to put those queries stsadm always gave me an error like "... was unexpected at this time." where '...' is some part in the query after '&'.

Have anybody had such problem?

Without the'&' all properties are just OR-ed which is even worse.

# re: MOSS Profile Search LDAP query to remove disabled and service accounts

Friday, April 18, 2008 2:51 PM by Suman Chakrabarti

Hey Kubus,

This LDAP query is meant to be used in the SSP admin on the Import Connections page of the user profile manager. There is a section that asks for the LDAP query, and an field for the filter.

Furthermore, the (!userAccountControl=65536) is a really difficult property to have correct. Some service accounts may not have all the properties flagged in a manner that it removes them properly. Please add up the bit flags using the link listed in the body of the message.

# re: MOSS Profile Search LDAP query to remove disabled and service accounts

Friday, April 25, 2008 8:38 AM by TariqYounas

Is it will be helpful in a scenario?

If we not need to appear inactive users in people search?

# re: MOSS Profile Search LDAP query to remove disabled and service accounts

Monday, May 05, 2008 11:14 AM by Henry Cheung

Yes, I deleted inactive users profiles in Sharepoint, but people search can still search for the inacitve users. How do we fix this? Does anyone has any ideas?

# Next level :-) Can I get the users from specified OU's?

Monday, August 25, 2008 4:58 AM by Tomer

Is there a way to get users and groups from more then one ou?

I would like to get ONLY from 2 OU's and not from the whole ou's below the main one...

10x in advance!

Tomer

# re: MOSS Profile Search LDAP query to remove disabled and service accounts

Monday, August 25, 2008 2:54 PM by Jeff

Just to recap then, in Shared Services Administration, User Profile and Properties, Manage Connections, I replace the search base with:

(memberof:1.2.840.113556.1.4.1941:=(CN=Authenticated Users,OU=Branch1,DC=domain,DC=local)))

and leave the user filter as:

(&(objectCategory=Person)(objectClass=User))

Thanks

# re: MOSS Profile Search LDAP query to remove disabled and service accounts

Monday, August 25, 2008 3:14 PM by Jeff

Oops, reread and tried:

(&(objectCategory=Person)(objectClass=User)(memberof:1.2.840.113556.1.4.1941:=(OU=Users,OU=Domain Accounts,DC=domain,DC=com)))

but still getting an error of:

A test import based on current input returned no results. Please re-check the search base and filter.

Any ideas?

# re: MOSS Profile Search LDAP query to remove disabled and service accounts

Tuesday, November 25, 2008 10:10 PM by Yvonne

User filter setting -->

(&(objectCategory=person)(objectClass=user)(!(userAccountControl:1.2.840.113556.1.4.803:=2)))

But the UserProfile_Full table, I select bDeleted=1, still return so many rows...

Who can tell me why ?

Name (required)
Your URL (optional)
Comments (required)
Remember Me?

Suman Chakrabarti - SharePoint and ASP.NET blog

Search

Tags

News

Archives

Favorite Blogs

I'm reading...

Links

MOSS Profile Search LDAP query to remove disabled and service accounts

Comment Notification

Comments

# re: MOSS Profile Search LDAP query to remove disabled and service accounts

# re: MOSS Profile Search LDAP query to remove disabled and service accounts

# re: MOSS Profile Search LDAP query to remove disabled and service accounts

# re: MOSS Profile Search LDAP query to remove disabled and service accounts

# re: MOSS Profile Search LDAP query to remove disabled and service accounts

# re: MOSS Profile Search LDAP query to remove disabled and service accounts

# re: MOSS Profile Search LDAP query to remove disabled and service accounts

# MOSS Profile Search LDAP Query that Removes Disabled and Service Accounts

# MOSS Profile Search LDAP Query that Removes Disabled and Service Accounts

# re: MOSS Profile Search LDAP query to remove disabled and service accounts

# re: MOSS Profile Search LDAP query to remove disabled and service accounts

# re: MOSS Profile Search LDAP query to remove disabled and service accounts

# re: MOSS Profile Search LDAP query to remove disabled and service accounts

# Next level :-) Can I get the users from specified OU's?

# re: MOSS Profile Search LDAP query to remove disabled and service accounts

# re: MOSS Profile Search LDAP query to remove disabled and service accounts

# re: MOSS Profile Search LDAP query to remove disabled and service accounts

Leave a Comment