Tuesday, April 15, 2014

Using LDAP Saved Queries for Active Directory

In Active Directory, if you have more than one account in the same container, you can mass select them by CTRL+Clicking or SHIFT+Clicking them.  Once selected as a collection (I will refrain from using the term "group" in order to avoid "confusion"), you can enable or disable them, move them into a group, or modify many of their properties at the same time.  And yes, sometimes I do the same thing with scripts in PowerShell.
The challenge in using this ability in the GUI comes when the users that you want to manage live in different OUs, which prevents them from being selected simultaneously.   But fear not!  You can flatten the OU structure of an Active Directory domain in order to find and manage related accounts quickly by using Saved Queries!  Saved Queries will also allow you to find accounts based upon properties in a way that would otherwise be vastly time consuming.

Saved Queries are found in the Active Directory Users and Computers console.  Right click on saved queries, and create a new query. 

 Give your query a useful name, determine if you will limit the scope to less than the domain (perhaps an OU?) and then click "define query".

Now you can see that anything you can find with Active Directory Find can be found and saved here. Choose "custom search" from the drop down of options at the top.
Then go to the advanced tab, you will be presented with a blank LDAP query field.  This is where you will enter your queries.

 After clicking OK and OK, you can see your result set. You may need to click Refresh to see up to date information



 I will now present several queries for your benefit, and explain what they do.
All Users Query: (objectCategory=person)(objectClass=user)
This query is a simple tool that allows you to have a logical search container that finds every user, no matter what OU they are hiding in.  Now you can seek, search, and sort to your hearts content within this structure.

Where's Bob Query: (objectCategory=person)(objectClass=user)(name=Bob)
This query finds any user named Bob, no matter where he is hiding.  I would just use the common query tool rather than the custom query to find him, but I want you to see the syntax in order to make sense of the next query...

Standard Users Query: (objectCategory=person)(objectClass=user)(!name=SUPPORT_388945a0)(name=*)(!name=Guest)(!name=Administrator)(!name=Krbtgt)
So, sometimes what is important is what you DON'T want to see!  Where (name=Bob) found the account we wanted, (!name=Administrator) indicates what we want to be filtered out.  The exclamation point acts as the boolean operator "NOT" in this query.

Disabled Users - (objectCategory=person)(objectClass=user)(userAccountControl:1.2.840.113556.1.4.803:=2)
This query finds all disabled users by their userAccountControl value.  Again, this one would be easy enough to do with a common query, where it is just a checkbox to find these accounts.  In fact, that is exactly what I did to create this query.  But on the main query page (before the editor), you can see the LDAP query that the common query created.  Again, I can use this to look for something that is NOT a common query...

NOT Disabled Users - (objectCategory=person)(objectClass=user)(!UserAccountControl:1.2.840.113556.1.4.803:=2)
Once again, the exclamation point before the setting makes it invert the selection, now finding all accounts that have not been disabled.  Remember that first query that flattened all the users?  Many organizations disable accounts instead of deleting them when people leave the company.  That means that with the first query you would find tons of old user accounts.  This query eliminates them from the display.

Locked Out Accounts - (ObjectCategory=Person)(ObjectClass=User)(LockoutTime>=1)
Finding accounts that are locked out so that they can be unlocked and have their password reset is a common issue.  Now, instead of trying to find the locked out account (which has no distinguishing icon, unlike disabled accounts), you can have Active Directory Users and Computers find it for you!

Only Temporary Accounts that will Expire - (objectCategory=person)(objectClass=user)(!accountexpires=9223372036854775807)(!accountexpires=0)
When an user account is created for a contract worker or temp worker, they are often given user expiration dates.  Default accounts will either have 0 or that huge number you see above as their value.  Again, this query dives in, finds the temp accounts in any region or department they may be located in, and brings them to the surface, perhaps so that you can reset their expiration date to something later, or delete or disable their account early.

All Computers - (objectCategory=computer)
You guessed it.  This finds all computers, no matter where they might be hiding in your AD structure.
Used Computer Accounts - (&(sAMAccountType=805306369)(objectCategory=computer)(operatingSystem=*))
When a computer joins the domain, it populates it's own operating system field. This query uses the "*" as a wildcard character, which will find ALL operating systems, meaning that the field can be anything... except blank.

Prestaged Computer Accounts - (&(sAMAccountType=805306369)(objectCategory=computer)(!operatingSystem=*))
When a computer joins the domain, it populates it's own operating system field.  Therefore, by searching for all users accounts where the operating system is NOT filled with anything, you can find prestaged computer accounts that are set up ahead of time by administrators to support future clients.
 You can learn a lot by using the basic queries and backsolving - even when you use a basic query the first properties sheet (before you go into define query) displays the LDAP query!


You can also find out a great deal by configuring accounts differently and then viewing their properties in adsiedit.msc or in the advanced view ADUC properties tab.


One last note about saved queries.  Once you create them, they are saved with the console, NOT in Active Directory.  That means other users will not be able to see them.  Even you won't be able to see them if you open a different MMC console!  Fortunately, you can right click and export them as XML files, and import them into any other MMC where needed.  You may wish to export them and have them available on a network drive... just in case.

Good luck, and let me know if there are any saved queries that you would like to figure out!

No comments: