The “From Query” dialog is similar to the “From OU” dialog, but it allows you to include a search filter to limit the user accounts returned in the query. For example, you might just be interested in enabled user accounts, accounts that have been created in the last few days or users from a specific department. Click the “From Query” button on the toolbar to launch the dialog below.
Search Root (Part 1)
In part 1 of the “From Query” dialog, you can select one or more containers/organizational units to search. The default is to search the entire domain (unless you configured a root in the connection). If you would like to specify a different search root(s), click the “Change” button and the dialog box below will appear:
Select the containers you would like to search and the appropriate search scope. If you select “Subtree”, all child containers are included in the search. The “One Level” option can be used to limit the search to only users that are direct children of the specified search roots.
Filter (Part 2)
In part two, you are able to enter a filter to use for the search by either entering the required filter manually, or selecting from an existing pre-defined filter. You can type your own filter into the textbox using the LDAP search filter dialect as described in this article. This allows you to write some very powerful searches without any restrictions of a query builder, but it isn’t very user friendly. A number of pre-defined queries are available to select from the drop down to save you from writing your own filters by scratch. These filters can either be run as is or modified to suite your own requirements. Pre-defined filters are discussed in more detail in the next section. Click the “OK” button when you are ready to search for user accounts.
Pre-Defined Queries
A number of pre-defined queries are available to select from the “Common Filters” drop down. These make it easy to run common queries such as recently created user accounts, enabled user accounts and soon to expire user accounts.
Some of these queries have parameters, which are placeholders for the actual value used in the query. For example a query may contain a parameter that is to be replaced by the current date in filetime (large integer) format.
Date Parameters
Date parameters can be specified inside two hash symbols. Date parameters allow you to specify a date in a user friendly format and have it converted to either a larger integer (filetime) or generalized time at runtime.
e.g.
##FT:2007-09-30## converts to 128355804000000000
##GT:2007-09-30## converts to 20070930000000.0+0Z (Time zone dependant)
You can also use the keyword “Now” for the current date.
##FT:Now## = Todays date.
##FT:Now+d1## = Tomorrows date.
##FT:Now-d1## = Yesterdays date.
##FT:Now+m3## = 3 Months from todays date.
##FT:Now-y2## = 2 years ago
Sample LDAP Queries
Account expires in 7 days
User accounts that expire in the next 7 days (but have not already expired).
(&(objectCategory=person)(objectClass=user)(!accountExpires=0)(accountExpires>=##FT:Now+d1##)(accountExpires<=##FT:Now+d8##))
Please note that the LDAP query above specifies two date parameters that will be converted to large integer dates at runtime.
Account has Expiry Date
All user accounts that have a specified account expires date.
(&(objectCategory=person)(objectClass=user)(!accountExpires=9223372036854775807)(!accountExpires=0))
Account Is Expired
This query finds all the user accounts that have expired by searching for accounts that have an expiry date less than or equal to the current date. Please note that if the accountExpires attribute is the current date, the account has already expired. The value shown in Active Directory Users and Computers is actually the previous days date (Expires End Of).
(&(objectCategory=person)(objectClass=user)(!accountExpires=0)(accountExpires<=##FT:Now##))
Account Never Expires
All the user accounts that never expire.
(&(objectCategory=person)(objectClass=user)(|(accountExpires=9223372036854775807)(accountExpires=0)))
Disabled User Accounts
(&(objectCategory=person)(objectClass=user)(userAccountControl:1.2.840.113556.1.4.803:=2))
Enabled User Accounts
(&(objectCategory=person)(objectClass=user)(!userAccountControl:1.2.840.113556.1.4.803:=2))
Locked User Accounts
All the user accounts that have been locked out by the domain lockout policy. Please note that this query does not take into account the lockout duration. An account may have automatically been unlocked depending on your domain policy.
(&(objectCategory=person)(objectClass=user)(lockoutTime>=1))
Must change password at next logon
All the user accounts that are forced to change their password at next logon
(&(objectCategory=person)(objectClass=user)(pwdLastSet=0))
Never Logged on to the domain
All the user accounts that have never logged on to the domain.
(&(objectCategory=person)(objectClass=user)(|(lastlogon=0)(!(lastlogon=*)))
Password Never Expires
All users that are exempt from the “Maximum Password Age” policy. These user accounts are never forced to change their password.
(&(objectCategory=person)(objectClass=user)(userAccountControl:1.2.840.113556.1.4.803:=65536))
Recently Created User Accounts
User accounts that have been created in the last two days. The date can be modified to suite your own needs and will be converted to generalized time format in the runnable LDAP Query.
(&(objectCategory=person)(objectClass=user)(whenCreated>=##GT:Now-d2##))
Users with Exchange Mailbox
All user accounts with an exchange mailbox.
(&(objectCategory=person)(objectClass=user)(msExchHomeServerName=*))
Users with old Passwords
User accounts where the password hasn’t been changed for 3 months.
(&(objectCategory=person)(objectClass=user)(pwdLastSet<=##FT:Now-m3##)(!pwdLastSet=0))
More LDAP Queries?
You can add additional LDAP Queries or modify exising queries by editing the “LDAPFilters.xml” file in the Bulk AD Users program directory. Before you edit the file, you might want to make a backup copy.