Hey, Scripting Guy! How can I query Active Directory for a list of all the accounts that were created on or between two dates?

— PG

Hey, PG. You know, August is a propitious month for the Scripting Guys and this column: after all, today’s edition marks the beginning of the fourth year of Hey, Scripting Guy! Our very first column debuted on August 2, 2004, and – other than an occasional holiday or vacation day – we’ve run a new column every weekday ever since. How many columns does that add up to? Well, you do the math.

No, seriously: you’ll have to do the math. We kind of lost track of things after the 500^th column appeared last year.

Of course, that brings up an obvious question. “Gosh, after writing all those columns you must have some pretty interesting stories to tell, don’t you?” And you’d think so, wouldn’t you? As it turns out, however, we don’t have any stories to tell. Instead, all we have is a script that lists all the Active Directory user accounts that were created on or between two dates:

On Error Resume Next

Const ADS_SCOPE_SUBTREE = 2
dtmCreationDate1 = “20070701000000.0Z”
dtmCreationDate2 = “20070731000000.0Z”
Set objConnection = CreateObject(“ADODB.Connection”)
Set objCommand =   CreateObject(“ADODB.Command”)
objConnection.Provider = “ADsDSOObject”
objConnection.Open “Active Directory Provider”
Set objCommand.ActiveConnection = objConnection
objCommand.Properties(“Page Size”) = 1000
objCommand.Properties(“Searchscope”) = ADS_SCOPE_SUBTREE 
objCommand.CommandText = _
    “SELECT Name, whenCreated FROM ‘LDAP://dc=fabrikam,dc=com’ WHERE objectClass=’user’ ”  & _
        “AND whenCreated>='” & dtmCreationDate1 & “‘ AND whenCreated<='” & dtmCreationDate2 & “‘” 
Set objRecordSet = objCommand.Execute
objRecordSet.MoveFirst
Do Until objRecordSet.EOF
    Wscript.Echo objRecordSet.Fields(“Name”).Value, objRecordSet.Fields(“whenCreated”).Value
    objRecordSet.MoveNext
Loop

We know that many of you are concerned that, after four years, Hey, Scripting Guy! might begin to stray from its original intention (which is entirely possible considering the fact that none of us can remember what its original intention even was). But take it from the Scripting Guys: you have nothing to worry about there. If you need proof, try this: as we’ve consistently done for the last 3 years, today’s column on searching Active Directory includes the following disclaimer:

See? We’re every bit as consistent – and lazy – as we’ve always been.

What we will talk about today is the SQL query we use to retrieve a collection of all the user accounts that were created between two dates (in this case, July 1, 2007 and July 31, 2007). Before we do that, however, let’s take a quick peek at the following two lines of code:

dtmCreationDate1 = “20070701000000.0Z”
dtmCreationDate2 = “20070731000000.0Z”

Believe it or not, these two lines of code assign our start date (July 1, 2007) and our end date (July 31, 2007) to the variables dtmCreationDate1 and dtmCreationDate2. Granted, these don’t really look like dates; that’s because of the date-time format (“generalized time”) required when doing an Active Directory search. Trust us, however; the value 20070701000000.0Z can be broken down like this:

Now that we know what our two variables are for we can turn our attention to the SQL query that returns all the user accounts created on or between July 1, 2007 and July 31, 2007:

objCommand.CommandText = _
    “SELECT Name, whenCreated FROM ‘LDAP://dc=fabrikam,dc=com’ WHERE objectClass=’user’ ”  & _
        “AND whenCreated>='” & dtmCreationDate1 & “‘ AND whenCreated<='” & dtmCreationDate2 & “‘” 
Set objRecordSet = objCommand.Execute

What are we doing here? What we’re doing is requesting two attribute values (Name and whenCreated) from the fabrikam.com domain. Of course, we don’t want this information for all the objects in the fabrikam.com domain; instead, we only want those objects that meet the following criteria:

If you follow the logic (and, yes, this might be the first time in four years that one of these columns has actually had some logic) you’ll see that this limits the returned data to user accounts created during July, 2007. Which is exactly what we wanted the returned data to be limited to.

In case you don’t trust us, we added a Do Until loop that runs until we reach the end of our recordset (that is, the end of the returned data). Inside that loop we echo back the value of the first object’s Name and whenCreated attributes, then use the MoveNext method to move on and repeat the process with the next item in the recordset:

Do Until objRecordSet.EOF
    Wscript.Echo objRecordSet.Fields(“Name”).Value, objRecordSet.Fields(“whenCreated”).Value
    objRecordSet.MoveNext
Loop

The net result? Something very similar to this:

Ken Myer 7/31/2007 6:38:40 PM
Pilar Ackerman 7/29/2007 7:22:06 PM
Jonathan Haas 7/26/2007 7:28:42 PM
Gail Erickson 7/24/2007 7:45:14 PM
Carol Phillips 7/17/2007 9:55:16 PM
Kim Abercrombie 7/14/2007 6:11:31 PM
Dylan Miller 7/06/2007 3 :29:44 PM

And there you have it, PG: all the user accounts created on or between July 1, 2007 and July 31, 2007.

Before we go, we’d like to add that we are reasonably proud of Hey, Scripting Guy! and its longevity; we daresay that no other daily column published on TechNet has been in business as long as we have. (Due, in large part, to the fact that no one but the Scripting Guys are dumb enough to publish a daily column on TechNet.) So then why aren’t we holding a birthday party in honor of the occasion? That’s easy: we had a big celebration a year ago when the historic 500^th column was published. For the Scripting Guys, one party every four years or so is about all we can handle.