PowerShell: Filter Results with Active Directory Module Cmdlets
Introduction
The Active Directory PowerShell modules support two parameters to filter results. The -LDAPFilter parameter for LDAP syntax filters and the -Filter parameter for PowerShell syntax filters. The documentation indicates that PowerShell filters should be enclosed in braces (also called curly braces). However, many examples use single or double quotes instead. This article documents some of the resulting issues.
Filter or Where Clause
There are two ways to restrict the output of an AD cmdlet like Get-ADUser. First, you can use the -LDAPFilter or -Filter parameters to filter the output. Second, you can pipe the results to the Where-Object cmdlet. Where possible, the first method is more efficient for two reasons.
- Filtering is done on the domain controller instead of the local client. The domain controller is more likely to be a server class computer optimized for queries.
- Filtering results in a smaller resultset sent over the network from the domain controller to the client. In contrast, the Where-Object cmdlet only filters on the local client after the resultset has been sent from the remote computer.
For example, you could retrieve all users with a department that starts with "IT" using the Where-Object cmdlet as follows:
Get-ADUser -Filter * -Properties department | Where-Object {$_.department -Like "it*"} | Select sAMAccountName, department
The resultset from the Get-ADUser statement includes all users in the domain. A more efficient method to get the same results would use a filter, similar to below:
Get-ADUser -Filter {department -Like "it*"} -Properties department | Select sAMAccountName, department
Now only the users needed are included in the resultset from Get-ADUser. In a test domain with 2,150 users (7 of which have "IT" departments) the first command above took 4 times as long as the second (average of 10 trials each with 16 minutes between trials). The difference could be substantial in a domain with ten's of thousands of users.
Also, note that the statements above use the -Properties parameter to specify only the properties needed. The default properties exposed by the cmdlet are always included, like sAMAccountName in this case. If you request all properties, with -Properties *, the resultset will include many properties for each user. The resultset will be much smaller if you only specify the extended properties needed, like department in this case. Repeating the last command above in the test domain with 2,150 users, but requesting all properties (with -Properties *) required 75% more time on average to complete. The default and extended properties exposed by the Get-ADUser cmdlet are documented in Active Directory: Get-ADUser Default and Extended Properties.
LDAP Filter Syntax
You can use the -LDAPFilter parameter to specify an LDAP syntax filter. This is the same syntax as that used in VBScript and command line utilites like dsquery and adfind. This allows you to leverage your experience with these other technologies. You can also use LDAP syntax filters in Active Directory Users and Computers (ADUC). On the "View" menu, select "Filter Options", then "Create Custom", click the "Customize" button, click the "Advanced" tab, and enter your filter, as shown below.
The equivalent PowerShell code to retrieve all users with a department that starts with "IT" using the -LDAPFilter parameter would be as follows:
Get-ADUser -LDAPFilter "(department=it*)" -Properties department | Select sAMAccountName, department
A similar query can be done at the command prompt of a domain controller using the dsquery utility. The extra clauses involving objectCategory and objectClass restrict the output to user objects (if necessary).
dsquery * -Filter "(&(objectCategory=person)(objectClass=user)(department=it*))" -attr sAMAccountName department
Notice the following about LDAP syntax filters (in contrast with PowerShell syntax filters):
- The filter is a quoted string with each clause in parentheses (there are three clauses in the last example above).
- The supported operators include "=", ">=", and "<=". The operators ">" and "<" are not supported.
- Clauses can be combined with the following operators: "&" (the AND operator), "|" (the OR operator), and "!" (the NOT operator).
- Instead of "(a<b)" you can use "(!(a>=b))". Instead of "(a>b)" you can use "(!(a<=b))".
- Only Active Directory attributes, designated by their LDAPDisplayName, are recognized. The PowerShell properties, such as Modified and Enabled, are not supported.
- PowerShell datetime values, such as those retrieved using the Get-Date cmdlet are not supported. The datetime values must be converted into the Generalized-Time format or the LargeInteger format, depending on the attribute (See Using Date Attributes below).
The following table shows some example LDAP syntax filters with the department attribute. The results would be similar using other Active Directory string attributes. In these examples the variable $Dept is defined as follows:
$Dept = "IT Department"
| Filter | Result |
| -LDAPFilter "(department=$Dept)" | Works |
| -LDAPFilter "(department='$Dept')" | No Results |
| -LDAPFilter "(department=""$Dept"")" | No Results |
| -LDAPFilter '(department=$Dept)' | No Results |
| -LDAPFilter "(department='$Dept')" | No Results |
| -LDAPFilter '(department="$Dept")' | No Results |
| -LDAPFilter "(department=IT Department)" | Works |
| -LDAPFilter "(department=IT*)" | Works |
Some of the filters above do not raise an error but never produce results. The filter should be enclosed in double quotes, and any variables or constants should not be quoted.
PowerShell Filter Syntax
The PowerShell Active Directory module cmdlets support an extended form of the PowerShell Expression Language. PowerShell documentation indicates that PowerShell syntax filters should be enclosed in braces. But there are many examples where single quotes or double quotes are used instead. As you might expect, this affects how the filter is interpreted.
Using String Attributes
The following table shows some example PowerShell syntax filters using string properties, like Department. Some filters result in error, others do not raise an error but never produce results. The variable $Dept is defined as previously.
| Filter | Result |
| -Filter {department -eq "IT Department"} | Works |
| -Filter {department -eq $Dept} | Works |
| -Filter {department -eq "$Dept"} | No Results |
| -Filter {department -eq '$Dept'} | No Results |
| -Filter "department -eq $Dept" | Error |
| -Filter 'department -eq $Dept' | Works |
| -Filter {department -eq "it*"} | No Results |
| -Filter {department -Like "it*"} | Works |
| -Filter "department -Like ""it*""" | Works |
| -Filter "department -Like 'it*'" | Works |
| -Filter 'department -Like "it*"' | Works |
| -Filter 'department -Like ''it*''' | Works |
| -Filter {department -ge "IT"} | Works |
Some of these results may not be expected. For example, you might expect enclosing a variable in a quoted string to work. The best policy might be to always enclose PowerShell syntax filters in braces, and to refrain from quoting variables.
The last example using the "-ge" operator is only useful in rare situations. The filter will result in any departments that are lexicographically greater than or equal to "IT". For example, it will return "Test Department", because "T" is greater than "I".
Using DN Attributes
DN attributes are those where the value must be a distinguished name. Examples are member, memberOf, and manager. The wildcard character, "*", is not allowed within DN values. The following table shows results from various filters with the DN attribute manager. In these examples, the variable $DN is defined similar to the following:
$DN = "cn=Jim Smith,ou=Sales,ou=West,dc=MyDomain,dc=com"
| Filter | Result |
| -Filter {manager -eq "cn=Jim Smith,ou=Sales,ou=West,dc=MyDomain,dc=com"} | Works |
| -Filter {manager -eq $DN} | Works |
| -Filter "manager -eq $DN" | Error |
| -Filter 'manager -eq $DN' | Works |
| -Filter 'manager -eq "$DN"' | Error |
| -Filter "manager -eq '$DN'" | Works |
| -Filter {manager -eq "$DN"} | Error |
| -Filter {manager -eq '$DN'} | Error |
| -Filter {manager -Like "cn=Jim Smith,ou=Sales,*"} | Error |
The last example in the table is one case where you must pipe the results to the Where-Object cmdlet. The desired filter is not allowed. The following can be used instead:
Get-ADUser -Filter * -Properties manager | Where-Object {$_.manager -Like "cn=Jim Smith,ou=Sales,*"} | Select sAMAccountName, manager
Even here, it would help to filter the resultset as much as possible, to avoid retrieving all users in a large domain. In this case, it would be better to filter on users that have any value for the manager attribute. This would eliminate users with no value assigned. For example:
Get-ADUser -LDAPFilter "(manager=*)" -Properties manager | Where-Object {$_.manager -Like "cn=Jim Smith,ou=Sales,*"} | Select sAMAccountName, manager
Using Date Attributes
Dates in Active Directory can be in one of two formats. Some attributes use Generalized-Time format, a string in the form "YYYYMMDDhhmmss.0Z". For example "20141006133000.0Z" corresponds to October 6, 2014, at 13:30:00 (in UTC). Handling these dates is discussed here: Active Directory: Generalized-Time Attributes. AD attributes in Generalized-Time format include whenChanged. The AD module cmdlets expose the property Modified, which is the same as whenChanged, but converted into a PowerShell date in the local time zone.
Other date attributes in Active Directory are in LargeInteger format (also called Integer8). These are 64-bit integers representing dates as the number of 100-nanosecond intervals since 12:00 am, January 1, 1601. Handling these dates is discussed here: Active Directory: Large Integer Attributes. Examples of AD attributes in this format include accountExpires and pwdLastSet. The corresponding properties exposed by the PowerShell AD cmdlets are AccountExpirationDate and PasswordLastSet. Again, these PowerShell properties convert the AD attributes into PowerShell datetime values in the local time zone.
If you use the -LDAPFilter parameter, you must code either the Generalized-Time string or the Large Integer value, depending on the attribute. PowerShell datetime values will not be recognized. However, if you use the -Filter parameter, you can use the Generalized-Time string values, Large Integer values, or PowerShell datetime values. All will be recognized. The variables in the following table are defined similar to below:
# PowerShell date 30 days in the past.
$PSDate = (Get-Date).AddDays(-30)
# Generalized-Time value corresponding to October 6, 2014, 13:30:00.
$GTDate = "20141006133000.0Z"
# LargeInteger (Integer8) datetime value corresponding to October 14, 2014 (U.S. Central Time).
$LargeIntDate = "130579128000000000"
| Filter | Result |
| -LDAPFilter "(whenChanged>=$GTDate)" | Works |
| -LDAPFilter '(whenChanged>=$GTDate)' | No Results |
| -LDAPFilter "(whenChanged>='$GTDate')" | No Results |
| -LDAPFilter '(whenChanged>="$GTDate")' | No Results |
| -LDAPFilter "(whenChanged>=20141006123500.0Z)" | Works |
| -Filter {Modified -ge $PSDate} | Works |
| -Filter {Modified -ge $GTDate} | Works |
| -Filter {Modified -ge "$((Get-Date).AddDays(-30))"} | No Results |
| -Filter {Modified -ge "09/26/2014 16:06:22"} | Works |
| -Filter {Modified -ge '09/26/2014 16:06:22'} | Works |
| -Filter {whenChanged -ge $PSDate} | Works |
| -Filter {whenChanged -ge $GTDate} | Works |
| -Filter {modifyTimeStamp -ge $PSDate} | Works |
| -Filter {modifyTimeStamp -ge $GTDate} | Works |
| -Filter {AccountExpirationDate -gt $PSDate} | Works |
| -Filter {AccountExpirationDate -gt "10/16/2014"} | Works |
| -Filter {accountExpires -ge $LargeIntDate} | Works |
| -Filter {accountExpires -ge "10/01/2014"} | No Results |
| -LDAPFilter "(accountExpires>=130579128000000000) | Works |
| -LDAPFilter "(accountExpires>=$LargeIntDate) | Works |
See Also
- Active Directory: Generalized-Time Attributes
- Active Directory: Large Integer Attributes
- Active Directory: Syntaxes of Attributes
- Active Directory: LDAP Syntax Filters
- Active Directory: PowerShell AD Module Properties
- Active Directory PowerShell Cmdlet Properties
- Active Directory: Get-ADUser Default and Extended Properties
- Active Directory: Get-ADObject Default and Extended Properties
- Wiki: Active Directory Domain Services (AD DS) Portal
- Active Directory: Glossary
Other Resources
- about_ActiveDirectory_Filter
- Get-ADUser
- Referencing Variables and Variable Values
- Dsquery *
- Active Directory PowerShell - Advanced Filter
- Active Directory PowerShell - Advanced Filter (Part - II)
- Filter or LDAP filter (Richard Siddaway's blog)
- Use PowerShell to Query AD DS and Not Use LDAP Dialect (Hey, Scripting Guy! Blog)
- Get-ADUser Filter that's entirely variables (post in The Official Scripting Guys Forum!)