The Saved Queries in Active Directory Users and Computers (ADUC) mmc console allow you to create complex LDAP filters to select Active Directory objects. These queries can be saved, edited and copied to other computers. You can use the Active Directory saved queries for quickly and efficiently find AD objects based on a various criteria. Saved Queries can help you quickly perform common AD object administration tasks: display the list of all disabled accounts in a domain, select all users of a company who have mailboxes on a given Exchange server, etc.
When using saved LDAP queries, the administrator can perform group operations with objects from different OUs (containers) of Active Directory. For example, you can perform bulk lock/unlock/enable/disable, move, delete, rename operations under AD objects/accounts. Such queries in the ADUC console allow you to bypass the hierarchical structure of OUs in Active Directory and collect all the necessary objects in a flat table view.
Active Directory Saved Queries were first introduced in Windows Server 2003 and got further support in the later Windows Server versions. To use saved AD queries, you must have the ADUC console installed on your computer (is a part of RSAT administration tools).
How to Create a Saved Query in the Active Directory MMC Console?
Let’s take a look at a few typical examples of using saved LDAP queries in the Active Directory Users and Computers console to search objects. Suppose, we have to display the list of active user accounts, their department names and e-mail addresses.
- Open the ADUC console (
dsa.msc), right-click Saved Queries and select New – > Query;
- In the Name box, specify the name of the saved query to be displayed in the ADUC console.
- In the Query root field, you can specify the container (OU) in which you want to search. By default, the search by the query criteria is performed across the entire AD domain. In our example, we’ll narrow the search scope by selecting Brasil container;
- Then click on the Define Query button, and select the Custom Search in Find drop down list;
- Go to Advanced tab and copy the following LDAP query into Enter LDAP query box. This query selects enabled user account (see other examples of LDAP queries in the table below):
- Save the changes by clicking OK;
- Select the created query in ADUC console, press F5 to build the object list. As a result, a list of users will appear in the right window that matches your LDAP query;
- In order to display the additional user attributes (e-mail address, department, etc.), open View menu in ADUC console and select Add/Remove Columns;
- Add the columns you want. We have added 3 additional fields: User Logon Name, E-Mail Address, Department;
- The resulting list of user accounts can be saved to a CSV or TXT file for further analysis and import into Excel. To do it, right-click on the saved query and select the Export List menu item.Note.You can also get data from AD using PowerShell and save it directly to an Excel file.
In ADUC console, you can create a number of different saved queries organize them in a tree structure. In this way, you can create a convenient collection of LDAP queries to quickly perform common AD administration tasks.
The ADUC mmc snap-in supports several modes of building Active Directory saved queries. It is not necessary to manually specify the LDAP filter code each time. You can create your AD query with a simple graphical wizard. You simply select different attributes of AD objects and use them to search objects according to the criteria you want. For example, to list all Windows Server computer objects in a domain:
- Find -> Computers;
- Go to the Advanced tab ;
- Fields -> Operating System;
- Stars with -> specify your criteria ‘
Windows Server *‘
*(you can specify ‘
*Server*‘). Multiple search criteria can be added to your saved query.
Save the query and refresh the object list it in the ADUC console. The list will show all Windows Server objects in your domain.
The saved queries are stored locally on the computer on which they were created. The XML file containing the settings is located here: C:\Users\%USERNAME%\AppData\Roaming\Microsoft\MMC\DSA). To transfer AD saved queries between computers, there is a feature to import/export the queries as XML files in dsa.msc (Export Query Definition/Import Query Definition).
Useful Saved Query Examples for Active Directory MMC
The following table contains examples of commonly used LDAP queries to select Active Directory objects. You can save them to your ADUC console for daily use.
|Saved ADUC Query||LDAP Filter|
|Search for ‘|
|Search for user accounts with ‘|
|List empty Active Directory groups (with no users)|
|Users with the “Password never expires” option enabled|
|Users who have not changed their password for more than 3 months|
|Find users who have “Sales” in the department field|
|Users with the empty Profile Path attribute|
|Active user accounts with expired passwords|
|All AD users, except disabled|
|Locked AD user accounts|
|Users with e-mail addresses|
|Users without e-mail addresses|
|Users hidden from the Exchange Address Book (GAL):|
|The list of accounts never logged on to the domain (the information on last logon time can be obtained in a more convenient view in Additional Account Info tab in AD)|
|User accounts created in a specific time period (in 2019)|
|AD users created this year|
|Computers running Windows 10|
|Computers running a specific Windows 10 build (for example Windows 10 1909 have build number 18363)|
|Find all Windows Server 2016 except domain controllers|
|All Microsoft SQL servers|
|All Exchange distribution groups|
|Find AD object with a specific SID|
Using LDAP Filters in PowerShell
You can use the above LDAP filters to find AD objects in the PowerShell console. Most cmdlets from the PowerShell Active Directory module have a special LdapFilter parameter. You need to specify your LDAP query in this parameter. For example:
Get-ADUser -LdapFilter "(&(objectCategory=person)(objectClass=user)(department=*Sales department*))"| ft -a DisplayName,department
Get-ADUser, Get-ADComputer, and Get-ADGroup cmdlet are specialized cmdlets and used to find objects of a certain type – users, computers, or groups. If you don’t know the type of AD object you want, or if you need information about all types of objects, use the more common Get-ADObject cmdlet. For example, to search for an object by SID:
Get-ADObject -LdapFilter "(objectSID=S-1-5-21-87654321-12345678-5566443311-1231)" -Properties * -SearchBase “OU=DE,DC=woshub,DC=com"| ft -a DisplayName,Title