Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

  • Congratulations TouchToneTommy on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

How do I formulate LDAP queries / filters in Active Directory?

LDAP Queries in AD

How do I formulate LDAP queries / filters in Active Directory?

by  PScottC  Posted    (Edited  )
Title: Writing LDAP queries: Filters, Common Attributes, and Samples
by: Paul S. Chapman
edited: 2/28/2005

=================================================
CONVENTIONS AND DEFINITIONS
=================================================
[ul][li]()[/li]
[ul circle][li]Parenthesis encapsulate filters and separate them into sections[/li][/ul]
[/ul]

[ul][li]{}[/li]
[ul circle][li]Braces (or curly brackets) encapsulate my notes about the contents of the filter[/li][/ul]
[/ul]

[ul][li]CAPITAL LETTERS[/li]
[ul circle][li]Plain English title[/li][/ul]
[/ul]

[ul][li]mIxEd cAsE[/li]
[ul circle][li]Attribute name in AD[/li][/ul]
[/ul]

[ul][li]Bit Value[/li]
[ul circle][li]Decimal value of a binary number (ie. 001000010 bin=34 dec). If you want to examine multiple bit values, simply add them up (ie. 2+65536=65538). Use the "Bitwise AND" or "Bitwise OR" filters for searches based on these values.[/li][/ul]
[/ul]

[ul][li]Attribute[/li]
[ul circle][li]Attributes are the properties of an Object in AD.[/li][/ul]
[/ul]

[ul][li]Value[/li]
[ul circle][li]Values are data related to the attributes with which they are associated. They may be "present" or "not present" (blank). Data may be in plain text, hex, numeric, or other formats.[/li][/ul]
[/ul]

[ul][li]Class[/li]
[ul circle][li]Objects in AD are categorized into Classes. This document only discusses 3 classes: Users, Computers, and Groups.[/li][/ul]
[/ul]

[ul][li]Integer8 Date[/li]
[ul circle][li]The value represents the number of 100-nanosecond intervals since 12:00 AM January 1, 1601.[/li]
[li]At the bottom of this document you can find the text of a VBScript that will convert normal dates to Integer8 dates so that you can use them in searches. Simply cut and paste the VB into a text file with a .vbs extension and use it from a command prompt.[/li]
[li]NOTE: Remember, the lower this number is the further back in time it is. Use <= for "on or before" queries.[/li][/ul]
[/ul]


=================================================
WHERE TO GET INFORMATION
=================================================
[ul][li]Microsoft's MSDN website[/li][/ul]

[ul][li]Google[/li][/ul]

[ul][li]ADSI Edit[/li]
[ul circle][li]**USE EXTREME CAUTION WHEN USING THIS UTILITY. DO NOT MANUALLY CHANGE ANY VALUES WHILE USING THIS UTILITY.**[/li]
[li]This utility allows you to see the actual attribute names and the values they use so that you can perform searches. This utility is included with the Windows 2000 Server or Windows Server 2003 support pack.[/li][/ul]
[/ul]


=================================================
FILTERS
=================================================
******BASE FILTERS******
WILDCARD...........................*
PRESENCE...........................(attribute=*)
EQUALITY...........................(attribute=value)
PARTIAL MATCH......................(attribute={partial value}*)
PARTIAL MATCH......................(attribute=*{partial value})
PARTIAL MATCH......................(attribute=*{partial value}*)
PARTIAL MATCH......................(attribute=*{partial value 1}*{partial value 2}*)
GREATER THAN or EQUAL..............(attribute>=value)
LESS THAN or EQUAL.................(attribute<=value)
APPROXIMATELY EQUAL................(attribute~=value)

******BOOLEAN FILTERS******
AND................................(&(filter1)(filter2))
AND................................(&(filter1)(filter2)(filter3))
OR.................................(|(filter1)(filter2))
OR.................................(|(filter1)(filter2)(filter3))
NOT................................(!(filter1))
BITWISE AND........................(attribute:1.2.840.113556.1.4.803:={bitvalue in decimal})
BITWISE OR.........................(attribute:1.2.840.113556.1.4.804:={bitvalue in decimal})

******SPECIAL CHARACTERS IN "VALUE" FIELDS******
(..................................\28
)..................................\29
*..................................\2A
\..................................\5C

******CORRELARY FILTERs******
NOT PRESENT (BLANK FIELD)..........(!(attribute=*))


=================================================
COMMON OBJECT CLASSES AND ATTRIBUTES
=================================================

******USER ACCOUNT******
CLASS..............................objectCategory=person [red]AND[/red] objectClass=user

ACCOUNT DISABLED...................userAccountControl={contains bit value of 2}
PASSWORD NOT REQUIRED..............userAccountControl={contains bit value of 32}
PASSWORD NEVER EXPIRES.............userAccountControl={contains bit value of 65536}
SMARTCARD REQUIRED.................userAccountControl={contains bit value of 262144}
ACCOUNT TRUSTED FOR DELEGATION.....userAccountControl={contains bit value of 524288}
ACCOUNT CANNOT BE DELEGATED........userAccountControl={contains bit value of 1048576}
USE DES ENCRYPTION.................userAccountControl={contains bit value of 2097152}
DON'T REQUIRE KERBEROS PRE-AUTH....userAccountControl={contains bit value of 4194304}
PREVIOUS PASSWORD CHANGE...........pwdLastSet {Integer8 Date, use .vbs to convert conventional dates, use "<=" for all dates before or ">=" for all dates after}
LAST LOGON.........................lastLogon {Integer8 Date as above, value of 0 or blank means NEVER}
REQUIRE P/W CHANGE AT NEXT LOGON...pwdLastSet {Value of 0, any other value is previous p/w change date}
GROUP MEMBERSHIP...................memberOf {Specify exact DN of group, no wildcards allowed}
EXCHANGE USER......................msExchHomeServerName {wildcard, or complete or partial servername}
EXCHANGE USER (OPTIONAL)...........homeMDB {wildcard, or complete or partial database name}
DESCRIPTION........................description {wildcard, or complete or partial text}
FIRST NAME.........................givenName {wildcard, or complete or partial text}
LAST NAME..........................sn {wildcard, or complete or partial text}
EMPLOYEE TYPE......................employeeType {wildcard, or complete or partial text}
EMPLOYEE NUMBER....................employeeID {wildcard, or complete or partial text}
DEPARTMENT NUMBER..................department {wildcard, or complete or partial text}
LOGON NAME.........................sAMAccountName {wildcard, or complete or partial text}
USER PRINCIPAL NAME (UPN)..........userPrincipalName {wildcard, or complete or partial text}


******COMPUTER ACCOUNT******
CLASS..............................objectClass=computer

OS NAME............................operatingSystem {ie. Windows 2000 Professional, use "PARTIAL" filter}
OS SERVICE PACK....................operatingSystemServicePack {ie. Service Pack 4, use "PARTIAL" filter}
OS VERSION.........................operatingSystemVersion {ie. 5.0 (2195), use "PARTIAL" filter}
DOMAIN CONTROLLER..................userAccountControl={contains bit value of 8192}
ACCOUNT TRUSTED FOR DELEGATION.....userAccountControl={contains bit value of 524288}
DESCRIPTION........................description {wildcard, or complete or partial text}


******GROUP OBJECTS******
CLASS..............................objectClass=group

DESCRIPTION........................description {wildcard, or complete or partial text}
NAME...............................CN {wildcard, or complete or partial text}
SECURITY GROUP.....................groupType {contains bit value of 2147483648}
GLOBAL GROUP.......................groupType {contains bit value of 2}
DOMAIN LOCAL GROUP.................groupType {contains bit value of 4}
UNIVERSAL GROUP....................groupType {contains bit value of 8}


=================================================
SAMPLE QUERY SEGMENTS
=================================================

[ul][li]Object is a user[/li]
[ul circle][li](&(objectCategory=person)(objectClass=user))[/li][/ul]
[/ul]

[ul][li]Account is not disabled[/li]
[ul circle][li](!(userAccountControl:1.2.840.113556.1.4.803:=2))[/li][/ul]
[/ul]

[ul][li]Object is an Enabled User[/li]
[ul circle][li](&(objectCategory=person)(objectClass=user)(!(userAccountControl:1.2.840.113556.1.4.803:=2)))[/li][/ul]
[/ul]

[ul][li]Account is assigned an Exchange Mailbox[/li]
[ul circle][li](|(homeMDB=*)(msExchHomeServerName=*))[/li][/ul]
[/ul]

[ul][li]User is member of group (Specify exact DN of Group. Use ADSIEdit to determine if unsure.)[/li]
[ul circle][li](memberOf=CN=your group name,OU=your ou,DC=yourdomain,DC=tld)[/li][/ul]
[/ul]

[ul][li]Account Password Never Expires not set[/li]
[ul circle][li](!(userAccountControl:1.2.840.113556.1.4.803:=65536))[/li][/ul]
[/ul]

[ul][li]Password set on or before 8/9/2004 (Specify Integer8 Date, Use VB script to determine)[/li]
[ul circle][li](pwdLastSet<=127365120000000000)[/li][/ul]
[/ul]

[ul][li]Change Password at next logon flag[/li]
[ul circle][li](pwdLastSet=0)[/li][/ul]
[/ul]

[ul][li]Account has never logged on[/li]
[ul circle][li](|(lastLogon=0)(!(lastLogon=*)))[/li][/ul]
[/ul]


=================================================
SAMPLE FULL QUERIES (NOTE: If you cut and paste from this document, remove any extra carriage returns)
=================================================

[ul][li][Object is an [blue]Enabled[/blue] User Account] [red]and[/red] [Has an Exchange mailbox] [red]and[/red] [Not members of the All Mail Users D/L][/li]
[ul circle][li](&(&(&(objectCategory=person)(objectClass=user)(!(userAccountControl:1.2.840.113556.1.4.803:=2)))(|(homeMDB=*)(msExchHomeServerName=*)))(!(memberOf=CN=your group name,OU=your ou,DC=yourdomain,DC=tld)))[/li][/ul]
[/ul]

[ul][li][Object is a User Account] [red]and[/red] [P/W never expires flag is not set [red]and[/red] not disabled] [red]and[/red] [P/W changed on or before 8/9/04] [red]and[/red] [Password change flag is not set][/li]
[ul circle][li](&(&(&(&(objectCategory=person)(objectClass=user))(!(userAccountControl:1.2.840.113556.1.4.804:=65538)))(pwdLastSet<=127365120000000000))(!(pwdLastSet=0)))[/li][/ul]
[/ul]

[ul][li][Object is an [blue]Enabled[/blue] User Account] [red]and[/red] [P/W changed on or before 8/9/04] [red]or[/red] [Password change flag is set [red]and[/red] Last Logon is never] [red]or[/red] [Last Logon is >90 days][/li]
[ul circle][li](&(&(objectCategory=person)(objectClass=user)(!(userAccountControl:1.2.840.113556.1.4.803:=2)))(|(&(pwdLastSet=0)(|(lastLogon=0)(!(lastLogon=*))))(lastLogon<=127439424000000000)(&(pwdLastSet<=127365120000000000)(!(userAccountControl:1.2.840.113556.1.4.803:=65536)))))[/li][/ul]
[/ul]

[ul][li][Object is a Group] [red]and[/red] [type of Global and Security][/li]
[ul circle][li](&(objectClass=group)(groupType:1.2.840.113556.1.4.803:=2147483650))[/li][/ul]
[/ul]

[ul][li][Object is a Computer] [red]and[/red] [P/W changed on or before 8/9/04][/li]
[ul circle][li](&(objectClass=computer)(pwdLastSet<=127365120000000000))[/li][/ul]
[/ul]

[ul][li][Object is a Computer] [red]and[/red] [Is a Domain Controller] [red]and[/red] [Is running Windows NT 4][/li]
[ul circle][li](&(&(objectClass=computer)(userAccountControl:1.2.840.113556.1.4.803:=8192))(operatingSystemVersion=4*))[/li][/ul]
[/ul]


=================================================
DATE TO INTEGER8 VBSCRIPT
=================================================
Simply copy and paste the following text into a file called DateToInteger8.vbs and execute from a command prompt.

Code:
' DateToInteger8.vbs
' VBScript program demonstrating how to convert a datetime value to
' the corresponding Integer8 (64-bit) value. The Integer8 value is the
' number of 100-nanosecond intervals since 12:00 AM January 1, 1601,
' in Coordinated Universal Time (UTC). The conversion is only accurate
' to the nearest second, so the Integer8 value will always end in at
' least 7 zeros.
'
' ----------------------------------------------------------------------
' Copyright (c) 2004 Richard L. Mueller
' Hilltop Lab web site - http://www.rlmueller.net
' Version 1.0 - June 11, 2004
'
' You have a royalty-free right to use, modify, reproduce, and
' distribute this script file in any way you find useful, provided that
' you agree that the copyright owner above has no warranty, obligations,
' or liability for such use.

Option Explicit

Dim dtmDateValue, dtmAdjusted, lngSeconds, str64Bit
Dim objShell, lngBiasKey, lngBias, k

If Wscript.Arguments.Count <> 1 Then
  Wscript.Echo "Required argument <DateTime> missing"
  Wscript.Echo "For example:"
  Wscript.Echo ""
  Wscript.Echo "cscript DateToInteger8.vbs ""2/5/2004 4:58:58 PM"""
  Wscript.Echo ""
  Wscript.Echo "If the date/time value has spaces, enclose in quotes"
  Wscript.Quit
End If

dtmDateValue = CDate(Wscript.Arguments(0))

' Obtain local Time Zone bias from machine registry.
Set objShell = CreateObject("Wscript.Shell")
lngBiasKey = objShell.RegRead("HKLM\System\CurrentControlSet\Control\" _
  & "TimeZoneInformation\ActiveTimeBias")
If UCase(TypeName(lngBiasKey)) = "LONG" Then
  lngBias = lngBiasKey
ElseIf UCase(TypeName(lngBiasKey)) = "VARIANT()" Then
  lngBias = 0
  For k = 0 To UBound(lngBiasKey)
    lngBias = lngBias + (lngBiasKey(k) * 256^k)
  Next
End If

' Convert datetime value to UTC.
dtmAdjusted = DateAdd("n", lngBias, dtmDateValue)

' Find number of seconds since 1/1/1601.
lngSeconds = DateDiff("s", #1/1/1601#, dtmAdjusted)

' Convert the number of seconds to a string
' and convert to 100-nanosecond intervals.
str64Bit = CStr(lngSeconds) & "0000000"
Wscript.Echo "Integer8 value: " & str64Bit
Register to rate this FAQ  : BAD 1 2 3 4 5 6 7 8 9 10 GOOD
Please Note: 1 is Bad, 10 is Good :-)

Part and Inventory Search

Back
Top