Using Date-Related Attributes in Saved Queries

 Executive Summary:

Not too long ago, I wrote “Using Saved Queries for Active Directory Management” for Scripting Pro VIP (October, InstantDoc ID 97087). In that article, I covered installing saved queries, importing and exporting saved queries, and using bitwise operators within LDAP queries. I also provided a wide variety of LDAP queries that readers could use to produce some very useful results from within Active Directory (AD). Unfortunately, I couldn’t fit in how to use saved queries to query certain attributes that are date-related—attributes such as Account Expiration date (accountExpires), Password Last Set date (pwdLastSet), and Account Created date (whenCreated). By using these attributes within your LDAP queries, you can do things like search AD for users who haven’t changed their password in more than 90 days, passwords that will soon expire, users who have expired accounts, accounts that will expire soon, and accounts that were created before, after, or between specific dates. These types of queries can come in handy for security reports or when you need to perform proactive actions such as alerting remote users that they need to change their password. You might even want to produce lists of how many accounts were created in a certain month or between specific time periods to make management aware of your growing administration duties. You can also query computers on the whenCreated attribute to produce similar reports about how many computers are added to AD each month. Let’s delve further into how you can use the accountExpires, pwdLastSet, and whenCreated attributes in your saved queries. As we do so, I’ll show you how to work with two different date formats.

Understanding Date Formats
The whenCreated attribute values are in a straightforward format, so constructing a query that evaluates this attribute isn’t difficult. For example, here’s what the whenCreated attribute would contain for an account that was created at midnight on July 1, 2007, Coordinated Universal Time (UTC): 20070701000000.0Z. The last six zeros represent hours, minutes, and seconds (i.e., hhmmss). “0Z” indicates that there’s no time differential from GMT. (The “Z,” incidentally, stands for Zulu, which is a nickname for UTC.) Creating saved queries by using the whenCreated attribute is relatively painless, and I’ll discuss doing so in more detail and show you some LDAP query strings that use this attribute a little later.

The other two date-related attributes, accountExpires and pwdLastSet, are a little trickier when it comes to entering the correct query syntax to represent a date in time. The value of these two attributes is in an unusual format that you’ll be familiar with if you’ve ever used VBScript to evaluate either of these attributes. The format is based on the number of 100 nanosecond intervals that have occurred between midnight on January 1, 1601, and the date that represents the account expiration date or password last set date. For example, take the date and time 08/01/2007, 12:00:01 A.M. Eastern time (United States). If an object being evaluated had either an accountExpires date or pwdLastSet date of 08/01/2007 12:00:01 A.M., the attribute value would actually contain a value like the following: 128304144010000000. You can see what I mean when I say this is an unusual number—it’s not anything like the whenCreated attribute, and it’s not something you can convert easily in your head. Furthermore, it’s important to note that the value of this attribute is stored in AD in the form of UTC time, so you might need to take your time zone and daylight saving time (DST) differentials into account when evaluating or querying this attribute. I say “you might need” to take these differentials into account because in some cases you might not have to be exact and in other cases you might want to be as exact as you can be. By the way, the number above translates to 8/1/2007 4:00:01 A.M. UTC.

Converting Date Formats
To set up queries that can evaluate, for example, which accounts expire on, before, or after a certain date or whether passwords were not set in the last 90 days, you’ll need a way to convert the date you have in mind to a format of 100 nanosecond intervals from midnight 1/1/1601. A nanosecond is one-billionth of a second. One billion contains nine zeros. If you want to convert it into chunks of 100 (to get intervals of 100 nanoseconds), divide 1 billion by 100 and you come up with a number that ends with seven zeros. That’s what the seven trailing zeros represent in my example, whereas the leading numbers represent the number of seconds that fall between midnight on 1/1/1601 and one second after midnight on 8/1/2007 Eastern time in the United States. So, all you need to do is take the number of seconds between midnight 1/1/1601 and the date and time you want to use in your query, tack on 7 zeros, and there you have it.

Here’s a quick code snippet to show you how to produce this string value by using a given date and time of August 1, 2007, 12:00:01 A.M.:

dteCnv = CDate("8/1/2007 12:00:01 AM")nan1601 = DateDiff("s",#1/1/1601#,dteCnv)str1601 = CStr(nan1601) & "0000000"msgbox str1601

The CDate function in the first line of code converts a valid date and time expression into an actual Date type value. That value is then used within the DateDiff function in the second line to determine the number of seconds between January 1, 1601 and August 1, 2007. The CStr function in the third line converts the number of seconds into a string and adds seven zeros. That string expression is stored to a variable that represents the number of 100-nanosecond intervals between the two dates. With this simple snippet, you can easily figure out what to enter into your saved queries to evaluate attributes like accountExpires.

Dealing with Time Zone Differentials
Keep in mind that unless you’re in the GMT time zone and aren’t concerned with DST, your results won’t account for your time zone or DST differentials. So, depending on how accurate your queries need to be, you might or might not want to adjust the calculated nanosecond values accordingly. Let’s say you’re looking for an AD object with “expired accounts” or “password last set” values that are older than 180 days. In this situation you might not feel the need to account for the time zone and DST differentials. After all, what’s a few hours when you’re looking for object attributes that represent timestamps that are more than several thousand hours old? But if you need to find out which accounts will expire within the next 48 hours, then you’ll probably want to adjust your calculation to compensate for time zone and DST. I’ve written an HTML Application (HTA) called Convert and Clip, which Figure 1 shows, that you can use for either accountExpires or pwdLastSet. This mini-app takes time zone into consideration and lets you choose whether to include the DST differential.

If you’re querying whenCreated dates, you might want to stick with the simple UTC-formatted date YYYMMDDHHMMSS.0Z. "0Z," as I mentioned earlier, indicates that no time differential will be calculated. The value signifies that the time you want to evaluate is GMT or has already been converted to GMT (i.e., UTC). If your local time zone isn’t GMT and you want to account for time zone and DST differentials, you can specify a time differential value. For example, on the East Coast in the United States, the standard-time differential is -5 hours, which means that the time is 5 hours earlier than the time in Greenwich, England. During DST, this differential is reduced by 1 hour, making it -4 hours. To specify a differential when evaluating an attribute such as whenCreated, use this format:

YYYYMMDDHHMMSS.0[+/-]HHMM 

If I construct a query string that takes my time differential into account and I want to find an account that was created at 1:00 P.M. on August 1, 2007, my query string would look like this:

(whenCreated=20070801130000.0-0400)

If I were working in New Zealand and wanted to find an account that was created on December 5, 2007, at 6:30 P.M. standard time, my query would look like this:

(whenCreated=20071205183000.0+1200)

In comparing the two date formats, you can see that constructing a UTC date string to query whenCreated, for instance, is much easier. Coming up with a precise representation of 100-nanosecond intervals between 1/1/1601 and another date for use in accountExpires or pwdLastSet queries requires a bit of programming. I’ve shown you how to come up with a rough estimate of the latter number. The only other piece that needs elaboration is adjusting your calculated date string to account for time zone and DST differentials. There’s a relatively easy way to get both differentials: by using the WMI Win32_TimeZone class. The two relevant properties from this class are Bias and DaylightBias.

Bias is the number of minutes between UTC and local time. Translations between UTC and local time are based on this formula: UTC = local time - bias. Keep in mind that if your time bias is a negative number such as -300 (as on the East Coast of the United States), the equation becomes UTC = local time - (-300), which translates to UTC = local time + 300. If you know that the attribute value contains a date that fell within the DST period, you might want to compensate for that hour. If your territory observes DST, then the DaylightBias property will contain a value of -60. Let’s take the example above, in which the calculated bias is +300. If you add -60, you end up with 240, which basically brings that time zone 1 hour closer to UTC, or GMT. On the other side of the Atlantic where DST is observed, as in Brussels, Madrid, or Paris, the time zone is 1 hour later than GMT during DST. For example, in Paris the bias is +60 minutes, whereas the New York bias is -300 minutes. Therefore, for Paris, the UTC calculation would look like this: UTC = local time -(60) which equates to -60. If you then add (-60) for Daylight saving bias you end up with -120. So UTC time for Paris would be the local time minus 120 minutes. It bears repeating that bias is important because AD time values are in the form of UTC. So if a user’s account expires, the UTC value of that account’s accountExpires attribute will have a UTC value that is several hours earlier if it originates on the East Coast of the United States, and a UTC value that is 2 hours later if it originates in Brussels.

All of the foregoing analysis can be summed up simply by using a couple of date functions and the Win32_TimeZone class with code such as that in Listing 1. The code at callout A starts by acquiring the two time differential values Bias and DaylightBias by utilizing the Win32_TimeZone class and storing those values to variables. The code at callout B gets the user-provided Date value from the Convert and Clip HTA, converts that into a date, and stores it in a variable called dteCnv. You can then subtract the bias number of minutes from the converted date by using the DateAdd function. (Remember that if the bias is a negative number, the result of subtracting a negative number from the local time is the equivalent of adding a positive amount to the date and time being calculated.) Next, determine whether the checkbox for calculating DST in the Convert and Clip HTA was checked. If checked, then take what you have so far for the converted date, use the DateAdd function again, and add (-60) minutes to that date. If not checked, then leave the converted date as is.

The code at callout C gives the number of seconds between 1/1/1601 and the date that the DateDiff function has converted to UTC (and DST, if applicable). The last line of code simply concatenates the number of seconds with a string of seven zeros, resulting in a string that you can plug into a saved query. Originally, I used the MsgBox function to present a message window showing me the string, but I didn’t like having to eyeball it and rekey it into my queries, so I devised a very basic copy-to-clipboard routine to capture this string so that I could just paste it into my query. I’ll briefly cover the clipboard code after I show you some of the queries you can use to produce useful reports based on the attributes whenCreated, accountExpires, and pwdLastSet. For some of these queries you’ll need a way to determine a date by specifying a given number of days from the current date, either in the past or into the future. My HTA lets you enter a positive or negative number of days to go forward or back. It will automatically determine the date and do the same conversion on that date as it does for the date that you enter as a string, as Figure 1 shows.

Three Useful Saved Queries
Let’s look at three possibilities for saved queries. The query names are pretty verbose, but you can shorten them to your liking. The first one checks for accounts for which the user hasn’t changed the password in more than 90 days, the password is not set to never expire, and the account is not disabled and doesn’t have the Must Change Password at Next Login Box checked.

Password Not Set in 90 Days   Not Never Expire Not Disabled   Not Must Change Next Login  (Note: Use ConvertAndClip1601-  DateToClipboard.hta)(objectCategory=person)  (objectClass=user)  (pwdLastSet<=  128220192000000000)  (!useraccountcontrol:  1.2.840.113556.1.4.803:=65536)  (!useraccountcontrol:  1.2.840.113556.1.4.803:=2)  (!pwdLastSet=0)

You can use this query to find accounts that haven’t changed their password in more than 90 days (pwdLastSet<=128220192000000000). The query doesn’t include (i.e., filters out) accounts whose password never expires (!useraccountcontrol:1.2.840.113556.1.4.803:=65536); disabled accounts (!useraccountcontrol:1.2.840.113556.1.4.803:=2); and accounts that are required to change their password at the next login (!pwdLastSet=0).

You’ll need to generate a new string to accurately represent a date that’s 90 days from the current date, then replace 128220192000000000 with that new calculated date string. This is probably the biggest hassle with date-related queries: You need to change the date string for any date that isn’t static or a fixed point in time if you want to run the query at a later date. But for the most part, it’s not much work if you have the tools to simplify the task.

The second query is:

Accounts created After 7-1-07(objectCategory=user)  (whenCreated>=20070701000000.0Z)

This query finds accounts that were created after July 1, 2007. Because this query has a static date reference, you wouldn’t have to recalculate the date string.

The third query is:

Account Expires between July 25 07  and Aug 1(objectCategory=person)  (objectClass=user)  (accountexpires>=  128297952000000000)  (accountexpires<=  128305728000000000) 

This query simply looks for dates that are greater than or equal to 7/25/07 and less than or equal to 8/1/07. This one also uses static dates, so you wouldn’t have to recalculate the date strings.

With these samples in hand, you have the basic ingredients to devise a variety of queries with just minor changes to dates and criteria. If you want to find out which users are required to change their password at next login, for example, you could construct a query string like this:

(objectCategory=person)  (objectClass=user)(pwdLastSet=0) 

I think that the most important query among these samples is the first. You’ll want to take a close look at these accounts, particularly if they’re administrator accounts whose passwords haven’t changed in more than 90 days. Yes, it’s a pain having to change passwords every 60 to 90 days, but it must be done. You’ll want to make sure that your admin users aren’t sidestepping your password policy requirements.

More on the Clipboard Routine
I promised to cover the clipboard code from callout C, so let’s take a quick look at it. I created this clipboard routine by using an instance of the Microsoft Internet Explorer (IE) Application object and a blank browser page with the following lines of code:

Set IE = CreateObject _  ("InternetExplorer.Application")IE.Navigate "about:blank"

As long as you don’t make the instance of IE visible (i.e., by including IE.Visible = True), this blank page remains hidden.

To copy the converted date string to the clipboard, I used the SetData method of the clipboardData object as follows:

IE.document.parentwindow. _  clipboardData.SetData "text", str1601

Str1601 is the variable I created that contains the calculated 100-nanoseconds value I’ll use within my query.

I like to see what the value in the clipboard is before I actually paste it into my script or saved query, so I retrieve the contents from the clipboard by using the GetData method of the clipboardData object. Then I store that into a variable called copyStr as follows:

copyStr = IE.document.parentwindow. _  clipboardData.GetData("text")

I then use the MsgBox function to display the contents of the copyStr variable. You can forgo the message box showing you what was in the clipboard, but I like to see it before I paste it.

Finally, I simply close the instance of IE with the Quit method:

IE.Quit

All you have to do now is use Ctrl+V to paste the 100-nanosecond value into your query string. You can find out more about the clipboardData object, the setData method, and the getData method at msdn2.microsoft.com/en-us/library/ms535220.aspx.

If you use IE 7.0, note that a new default security feature in IE 7.0 prompts you for permission, as Figure 2 shows, when you attempt to copy anything into or from the clipboard by using a script. You can change this behavior within IE, but doing so isn’t recommended. For the few times a week I use this utility, I simply click on the Allow access button and live with the slight annoyance. If you use IE 6.0 or earlier, you won’t be bothered with this feature, unless the default setting has been modified.

Although I don’t recommend changing the default of this IE security setting, you can do so if you’re absolutely sure you don’t copy and leave anything of value in your clipboard that could possibly be hijacked from a Web site and you use this utility or a rendition of the copy-to-clipboard code often enough that the warning pop-up is overly annoying. In IE, go to Tools, Internet Options. In the Internet Options dialog box, select the Security tab and click the Custom Level button. In the Settings dialog box, scroll down to the Scripting settings and find Allow Programmatic clipboard access, which Figure 3 shows. Change the setting from Prompt to Enable.