Jaa

How to Create Custom Active Directory LDAP Searches

  • Artikkeli

A nice feature in Windows Server Active Directory is the ability for an administrator to create saved queries in Active Directory USers % Computers to return common information within the Directory. The queries you can create through the GUI are pretty basic so to get the real benefit you need to create a "Custom Search", click the Advanced tab and enter an LDAP query. The only problem is... you have to enter an LDAP query. 

For a lot of administrators, you come up against the LDAP query box, start to research how to write an LDAP query, get interrupted and never finish learning how to create an LDAP query. I know in my case I am not doing AD administration everyday so when the task of creating an LDAP query for a customer arises it has usually been so long since the last time I wrote one that I have forgotten how. So I was creating some queries for a customer today and decided I would post them here for future use. The LDAP code listed below can be cut and pasted into the the query editor in AD and saved.

LDAP reference material:

LDAP Query Basics
https://technet.microsoft.com/en-us/library/aa996205(EXCHG.65).aspx

XADM: Browsing and Querying Using the LDP
https://support.microsoft.com/?id=255602 

Search Filter Syntax
https://msdn2.microsoft.com/en-us/library/aa746475.aspx 

Creating More Efficient Microsoft Active Directory-Enabled Applications (create efficient LDAP queries)
https://msdn.microsoft.com/en-us/library/ms808539.aspx 

How to query Active Directory by using a bitwise filter
https://support.microsoft.com/kb/269181 

 

Also see the post below on creating queries for individual UserAccountControl flags.

How to use the UserAccountControl flags to manipulate user account properties
https://support.microsoft.com/kb/305144 

https://blogs.msdn.com/b/muaddib/archive/2008/10/08/query-individual-properties-of-the-useraccountcontrol-active-directory-user-property.aspx

Now on to the queries.

 All XP Computers
Although this can be done easy enough with the GUI, I wanted to show the syntax so it can be used as a building block for more complex theories. One thing to notice is the query parameter "objectCategory=computer". By including this as part of our query we reduce the number of objects that have to be searched making for a faster query and less performance impact on the DC performing the query.
(&(objectCategory=computer)(operatingSystem=Windows XP*))

Windows XP Computers with Service Pack 2 Installed
(&(objectCategory=computer)(operatingSystem=Windows XP Professional)(operatingSystemServicePack=Service Pack 2))

Windows XP Computers with Service Pack 1 Installed
(&(operatingSystem=Windows XP*l)(operatingSystemServicePack=Service Pack 1)))

Windows XP Computers with No Service Pack Installed
This one is structured a Little different. Notice the "!" before operating SystemServicePack and the "*". The "!" means NOT so the statement reads "NOT equal to anything" instead of NULL or empty quotes ("") like some other languages.
(&(operatingSystem=Windows XP Professional)(!operatingSystemServicePack=*))) 

Windows Server 2003 No Service Pack 1
(&((objectCategory=computer))(operatingSystem=Windows Server 2003)(!operatingSystemServicePack=*)))

Windows Server 2003 Service Pack 1 Installed
(&(objectCategory=computer)(operatingSystem=Windows Server 2003)(operatingSystemServicePack=Service Pack 1)) 

Windows 2000 Professional
(&(objectCategory=computer)(operatingSystem=Windows 2000 Professional))

Windows 2000 Server
(&(objectCategory=computer)(operatingSystem=Windows 2000 Server))

All Windows Server 2003 Servers
(&((objectCategory=computer))(operatingSystem=Windows Server 2003))

SQL Servers (running on Windows 2003) (please verify in your environment)
(&(objectCategory=computer)(servicePrincipalName=MSSQLSvc*)(operatingSystem=Windows Server 2003))

SQL Servers any Windows Server OS
(&(objectCategory=computer)(servicePrincipalName=MSSQLSvc*)(operatingSystem=Windows Server*))

Windows Vista SP1
(&(objectCategory=computer)(operatingSystem=Windows Vista*)(operatingSystemServicePack=Service Pack 1))

Windows Server 2008 Enterprise
(&(objectCategory=computer)(operatingSystem=Windows Server® 2008 Enterprise)(operatingSystemServicePack=Service Pack 1))

Windows Server 2008 (all versions)
(&(objectCategory=computer)(operatingSystem=Windows Server® 2008*))

Windows Server 2008 R2 Enterprise
(&(objectCategory=computer)(operatingSystem=Windows Server 2008 R2 Enterprise))

Sample User Attribute Query (ExtensionAtrribute5)
(&(objectCategory=user)(&(extensionAttribute5>=20080101)(extensionAttribute5<=20080520)))

WIndows Server 2008 ALL
(&(objectCategory=computer)(operatingSystem=Windows Server 2008*))

Windows Server 2008 RTM
(&(objectCategory=computer)(operatingSystem=Windows Server 2008 *)(!operatingSystemServicePack=*))

Windows Server 2008 SP1
(&(objectCategory=computer)(operatingSystem=Windows Server 2008*)(operatingSystemServicePack=Service Pack 1))

Windows 7 RTM
(&(objectCategory=computer)(operatingSystem=Windows 7*)(!operatingSystemServicePack=Service Pack 1))

Windows 7 SP1
(&(objectCategory=computer)(operatingSystem=Windows 7*)(operatingSystemServicePack=Service Pack 1))

Active Directory Conflict Objects
(&(cn=*CNF:*))

 

USER ACCOUNT CONTROL EXAMPLES

UAC - Smart Card Login Enforced on The User Object
(&(objectCategory=person)(userAccountControl:1.2.840.113556.1.4.803:=262144) )

UAC - PWD Never Expires
(&(objectCategory=person)(userAccountControl:1.2.840.113556.1.4.803:=65536))

UAC - CAC Enabled Accounts (no disabled accounts or password never expires)
(&(objectCategory=person)(objectClass=user)(!userAccountControl:1.2.840.113556.1.4.803:=2)(!userAccountControl:1.2.840.113556.1.4.803:=65536)(userAccountControl:1.2.840.113556.1.4.803:=262144)(userPrincipalName=1*@mil))

UAC - Not CAC Enabled (no disabled accounts or password never expires)
(&(objectCategory=person)(objectClass=user)(!userAccountControl:1.2.840.113556.1.4.803:=2)(!userAccountControl:1.2.840.113556.1.4.803:=65536)(!userPrincipalName=1*@mil))

UAC - Determine User Location Based on Home Directory Server Name (no disabled accounts or password nevr expires)
(&(objectCategory=person)(objectClass=user)(!userAccountControl:1.2.840.113556.1.4.803:=2)(!userAccountControl:1.2.840.113556.1.4.803:=65536)(userPrincipalName=1*@mil)(|(homeDirectory=*922*)(homeDirectory=*263*)(homeDirectory=*JJLL*S1*)))

 

 So you get the idea of the basic syntax When you create your own queries make sure you use the the actual attribute name and not the label visible in the Active Directory Users and Computers interface. You can find the attribute names by using ADSIEDIT.MSC. Right click an object and select properties from the context menu. Scroll through the list of attributes till you find the one you are looking for. You should also copy the actual value from within ADSIEDIT.MSC and paste it into you query string to prevent typing errors (in case you type as bad as I do). Make sure when you enter the search string into the query editor there are no carriage returns or extra characters after the last parenthesis.

Read the article "Creating More Efficient Microsoft Active Directory-Enabled Applications" referenced above to make sure you are writing efficient queries that won't bring your LDAP server to its knees. 

One other useful tip. Once you have created some saved queries, you can export them as XML files and share them with others. They can be imported into another management console in the same domain or a different domain.

One final tip. You can copy and paste the code from the samples above. Paste it into a text editor like Notepad first to remove all the formatting imposed by the HTML page.

Comments

  • Anonymous
    January 04, 2008
    I am trying to perform a query (search) on active directory user accounts that havenet been used in 30/60/90 days etc.  I am running on a win 2000 mixed active directory domain.  Is there any programs or a way to query this information?

  • Anonymous
    January 04, 2008
    Take a look at the DSQUERY command query for stale computer/user accounts as well as stale passwords.  Some sample command lines are listed below: Run DSQUERY on accounts in a specific OU DSQUERY USER "CN=TEST ACCOUNTS, DC=COntoso, DC=com"  -INACTIVE 10 The following command will search the domain by default and will return a max of 1000 objects: DSQUERY USER -limit 1000 -inactive 10 The following command builds on the previous one and retrieves the SamAccount and UPN from each user: DSQUERY USER -limit 1000 -inactive 10 | DSGET  USER -samid -upn The following command builds on the previous one and disables the user account DSQUERY USER -limit 1000 -inactive 10 | DSMOD USER -disabled yes Stale Passwords - dsquery user -stalepwd 60 |dsget user –samid -upn dsquery computer -inactive 10 dsquery computer -inactive 10 -limit 300 | dsget computer -samid dsquery user -stalepwd 60 |dsget user –samid -upn

  • Anonymous
    March 18, 2010
    Hi, Thanks for the great post! Do you know the LDAP query to find all SharePoint servers known to the AD? jmp

  • Anonymous
    March 19, 2010
    Try creating a query similar to the SQL example above ( e.g. (&(objectCategory=computer)(servicePrincipalName=MSSQLSvc*)(operatingSystem=Windows Server 2003))  ).  Changing the SPN to the SPN format for Sharepoint.

  • Anonymous
    May 31, 2010
    Hi, I want to search computers from Active Directory by querying AD in sharepoint. Can you help on this ? Thanks.

  • Anonymous
    May 31, 2010
    @Saran you will need to use Active Directory Services Interface (ADSI) to access Active Directory with scripts.  You can embed these scripts in the HTML on your SharePoint site.  More information can be found here - technet.microsoft.com/.../default.aspx.

  • Anonymous
    July 21, 2010
    Thanks so much for this!  I was trying to figure out how to filter computers by service pack, and the built-in filter options wouldn't do it.  Other sites I went to brought me tantalizingly close to the answer, but were never quite what I needed, and having never written LDAP queries before, I didn't know how to modify them.  I'm glad I found your examples, which are perfect.

  • Anonymous
    January 02, 2012
    Hi Guys, I need to export the list of user by Saved Query in AD with Filter "Last Logon Days and Initials Tab" as my effort I am able to... If anyone has idea please let me know the procedure...Regards Rajnish

  • Anonymous
    December 19, 2012
    Wow thanks ,,,, this is very helpfully :)