How Can I Get a List of User Accounts Created On Or Between Two Dates?

ScriptingGuy1

Hey, Scripting Guy! Question

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

SpacerHey, Scripting Guy! AnswerScript Center

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 500th 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:

We won’t discuss the ins and outs of searching Active Directory; that goes beyond what we can do in this column. But don’t worry: we would never leave you hanging like that, wondering how an Active Directory search script works. If you need (or want) more information on the principles behind Active Directory search scripts, take a look at our two-part Tales from the Script series Dude: Where’s My Printer?

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:

The 2007 represents the year.

The 07 represents the month (July), in two-digit format.

The 01 represents the day (the first), also in two-digit format.

The 000000 represents, respectively, the hour, minutes, and seconds, all in two-digit, 24-hour format. (For example, 8:35 AM would be coded as 083500.)

The .0Z represents the time differential between local time and Greenwich Mean Time. Because Active Directory stores date-time values using Greenwich Mean Time, we’ve left this offset at 0. If we wanted to specify an offset 8 hours later than Greenwich Mean Time we’d do something like this: .0+0800. Eight hours earlier than Greenwich Mean Time would result in this: .0-0800 (note the minus sign). Etc.

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:

The objectClass is equal to “user”. What if we wanted a list of computer accounts created during the month of July? No problem; just change “user” to “computer”. What if we wanted a list of all the accounts (regardless of objectClass) created during the month of July? Again, no problem; in that case, just leave out the objectClass= clause altogether.

The value of the object’s whenCreated attribute must be greater than or equal to July 1, 2007 (represented by the variable dtmCreationDate1).

The value of the object’s whenCreated attribute must be less than or equal to July 31, 2007 (represented by the variable dtmCreationDate2).

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

Note. That’s a good point: even though we had to use that crazy generalized time format to specify values for the whenCreated attribute we didn’t have to do anything to get real date-time values (e.g., 7/26/2007 7:28:42 PM) displayed on screen. As it turns out, we only have to use the generalized time format when querying Active Directory; when we echo back the value of the whenCreated attribute the value is automatically converted into something a little easier to read.

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 500th column was published. For the Scripting Guys, one party every four years or so is about all we can handle.

Note. Well, except for the Scripting Editor, of course, who parties pretty much non-stop; she doesn’t even know the meaning of the word “quit.” (Which is too bad, because we’ve been trying to get her to quit for almost 3 years now, and with no such luck.)

0 comments

Discussion is closed.

Feedback usabilla icon