Keeping Your Active Directory Pantry In Order

Last week I found a long-forgotten, almost empty bag of bread in the back of my pantry. Some bizarre reaction had resulted in the mold hardening and warping the plastic, which left an oily residue on my fingers as I rapidly flung it across the kitchen into my rubbish bin. In a perfectly natural comparative leap, I remembered the moldy bread incident as I began pondering a post about using LDAP queries for account maintenance. It is as important to keep your directory in order as it is your pantry in order to avoid nastiness.

Active Directory is a treasure trove of information related to accounts, use and other compliance related attributes. With a little scripting knowledge or a willingness to play with the custom query input option in the Active Directory Users and Computers console (dsa.msc), you can quickly start building queries to gather all sorts of interesting data.

You'll generally want to wrap your final work into a script of some kind, but it's hard to beat the custom LDAP query window when you're building and testing your query language.

The focus here is on user accounts. LDAP queries are extremely flexible, and if there is an attribute that contains the data you're interested in (and you have the appropriate permissions), you should be able to pull the values of interest. Here are five key attributes that I will focus on in this post:

• lastLogon: Specific to each domain controller, this stores the timestamp the an account was last authenticated by the domain controller. This attribute is not replicated.
• lastLogonTimeStamp: This stores similar data to the lastLogon attribute, with two caveats. Firstly, it is a replicated value, unlike the lastLogon attribute. Secondly, the value is not updated for every logon, and in a default environment is accurate to between 9 and 14 days. This attribute is primarily intended to help identify inactive accounts, not to track logon activity. See this TechNet blog post for more details.
• pwdLastSet: The date and time the password was last changed.
• accountExpires: The date of expiration set on an account.
• userAccountControl: This is actually a bitmask attribute that contains multiple account properties in the form of flags. It is an integer value, but each bit represents a particular settings. A complete list of the values stored in this attribute is provided in the MSDN library.

Before delving into the queries, it is worth noting that all the timestamp values above (the first four of the five attributes) are stored in Integer8 format, and provide you with a value representing the number 100-nanosecond intervals since 12:00:00 AM on January 1, 1601 (referred to as "ticks"). Thinking about time in ticks is a good way to get a headache, so it's usually necessary to convert human readable dates and times into ticks for query purposes. There's no point reinventing the wheel, there are plenty of resources describing how to perform this conversion, including for VBScript and Excel. Perhaps the easiest is PowerShell though:

• Create an Integer8 date from a human readable date:

PS> (Get-Date "MM/DD/YYYY").ToFileTime()

• Create a human readable date from an Integer8 date:

PS> [datetime]::FromFileTime("XXXXXXXXXXXXXXXXXX")

With those two commands, you can freely convert back and forth between types. For these queries, we'll have a date in mind (given the accuracy of some of the timestamps and the duration we'll explore in terms of thing like inactivity, the specific time is not really of interest.)

To start building and testing queries, we'll use the ADUC console advanced custom search functionality:

1. Launch the ADUC console (dsa.msc)
2. Make sure you're connected to the correct domain if in a multi-domain environment
3. Note the domain controller that you are connected to (it will display the name next to the top level item in the console, e.g. "Active Directory Users and Computer [<domaincontroller>.<domain>]
4. If desired, change the domain controller or domain by right clicking the top level element, and choosing "Change Domain Controller..." or "Change Domain..."
5. Right click the domain in the console, and select "Find..."
6. In the Find drop-down select "Custom Search"
7. Click the "Advanced" tab below the find drop-down

The interface should look something like this, but instead of Entire Directory", you should see your domain:

Query Basics

All of our queries will contain some basic elements. This isn't a full LDAP tutorial, so some prior knowledge is of LDAP and queries is assumed. Queries in this post will be limited to user objects, so every query will contain (objectCategory=person)(objectClass=user). The other important point is that conditional statements can be built by grouping conditions with AND (&) or OR (|) operators, and we can also check if something is false with the NOT operator (!). For example:

• (&(objectCategory=person)(objectClass=user)(|(test1)(test2)) would generate a result if the object was a user and test1 OR test2 returned true
• (&(objectCategory=person)(objectClass=user)(objectCategory=User)(&(test1)(!test2)) would generate a result if the object was a user and both test1 AND NOT test2 returned true

With that, on to some queries!

lastLogon

Remember that this is not a replicated value, so while in the find tool it will pull values from the domain controller that we are connected to. This means that in a script, you would need to poll every domain controller, and find the most recent value. The best way to acquire this from a compliance perspective would be centrally aggregate your logs, and poll those for the most recent successful authentication event. Because we're interested in a particular cutoff point, let's define our date value of interest as April 5, 2010. We want to find any accounts with a lastLogon value of on or earlier than that date (i.e., a stale, moldy bread account).

First, generate an Integer8 formatted timestamp:

PS> (Get-Date "4/5/2010").ToFileTime() = 129149208000000000

Then, enter the following query in the custom search field and hit "Find Now":

(&(objectCategory=person)(objectClass=user)(lastLogon<=129149208000000000))

Remember, this list is based on the perspective of the domain controller you are connected to and querying! Because we don't have any other filtering taking place, this will show all user accounts, including disabled accounts, or accounts in an expired state.

lastLogonTimestamp

The query here is similar to above, but is based on the replicated version of the value. A little more background on that value; it is updated only if the last update to the value is more than that period defined (msDS-LogonTimeSyncInterval) or set as default (14 days) in Active Directory. As a result, when we're interested in accounts that haven't been logged into for a year, we're well past this 14 day window. Any accounts that were not used up until April 4th, but then used on that day will force an update of the attribute (because it will have been almost a year since that was last updated), and as a result won't be included in our result list. The reason this value is not updated with every single authentication success is simply to minimize the massive amounts of replication data that would result if this was updated with every event.

Using the last example as a reference, our query becomes:

(&(objectCategory=person)(objectClass=user)(lastLogonTimestamp<=129149208000000000))

pwdLastSet

We can look for accounts that haven't had the password changed since April 5, 2010 using the following query. Note that we exclude the value of 0. A value of 0 in this attribute generally means the account is configured to require a password change at next logon (if the account is also set to not permit the account to never expire). As a result, we have to use the NOT operator:

(&(objectCategory=person)(objectClass=user)(&(pwdLastSet<=129149208000000000)(!pwdLastSet=0)))

accountExpires

The accountExpires attribute is useful when we're looking for accounts that aren't explicitly disabled, but are in an expired state to prevent use. This is a good practice for managing vendor accounts where access may be needed on a regular basis (e.g. monthly), but for a short period of time, perhaps only for a day or two. It also becomes useful for pairing with other searches, related to password changes, disabled states, etc. A basic query to find accounts that are in an expired state, and where the expiration is set to a date on or before April 5, 2010:

(&(objectCategory=person)(objectClass=user)(&(accountexpires<=129149208000000000)(!accountexpires=0)(!accountexpires=9223372036854775807)))

This query is a little more complicated, as we have to check for an expiration date on or before our date of interest, and also need to make sure the expiration field is not in a null state. Per this MSDN reference article for the accountExpires attribute, an account configured to never expires will have a value of 0 or 9223372036854775807, so we make sure our account is not set to either of those. Technically, the check for 9223372036854775807 is not necessary, as the initial date check would exclude that state.

userAccountControl

If you're still reading, then you've arrived at the funnest attribute of the five. Attributes like userAccountControl store properties in the form of bitwise flags, the state of a particular flag (in the sequence of bits) indicates the state of that particular property. First, we need to select some properties that we want to query. Below are five commonly referenced values when managing accounts, taken from the MSDN library document referenced earlier.

 

Note that in the MSDN document, the attribute-id is 1.2.840.113556.1.4.8; this will be important in a moment. To actually query flags, we need to use what's called a LDAP matching rule control. To use a matching rule control, we need an attribute, and object ID (OID) and a comparison value. We have the first two, and the third will be based on the values in the above table.

To make a comparison, we either need to use the LDAP_MATCHING_RULE_BIT_AND rule (1.2.840.113556.1.4.803), or the LDAP_MATCHING_RULE_BIT_OR rule (1.2.840.113556.1.4.804) for our attribute OID (the AND rule adds a 03 suffix to denote the AND operation, and the OR rule adds a 04 suffix). We are interested solely in matching all the bits in our property comparison, and so will use the LDAP_MATCHING_RULE_BIT_AND rule (1.2.840.113556.1.4.803).

That's really the most complex part. Now we know how to build the tests, we just use the LDAP query syntax from earlier. For example we can quickly build the following queries:

User accounts that are not disabled (i.e. are enabled) and have the password never expires option set:

(&(objectCategory=person)(objectClass=user)(&(!userAccountControl:1.2.840.113556.1.4.803:=2)(UserAccountControl:1.2.840.113556.1.4.803:=65536)))

User accounts that are enabled but that have an expired password (shouldn't someone be complaining?):

(&(objectCategory=person)(objectClass=user)(&(userAccountControl:1.2.840.113556.1.4.803:=2)(UserAccountControl:1.2.840.113556.1.4.803:=8388608)))

And we can pull in other attributes to make our searches even more robust.

User accounts that are enabled, have an expired password, and haven't been logged into since April 5, 2010? Stale bread!:

(&(objectCategory=person)(objectClass=user)(&(!userAccountControl:1.2.840.113556.1.4.803:=2)(lastLogonTimestamp<=129149208000000000)(UserAccountControl:1.2.840.113556.1.4.803:=8388608)))

Go forth, query, and clean out your user pantry!