Hey, Scripting Guy! How can I get a list of all the objects that have been added to Active Directory since a specified date?

— KR

Hey, KR. First of all we’d like to apologize if our answer here is still a little soggy. No doubt you’ve seen cartoons where someone receives a package in the mail, they hear it ticking and – suspecting the worse – they immediately plunge the box into a bathtub full of water. (Fortunately, in cartoons, there’s always a bathtub full of water whenever you need one.) Inevitably, of course, they discover that the package wasn’t a bomb after all, just a clock.

Hey, we didn’t say it was all that funny, just something that happens over and over again.

To tell you the truth, we have the same reaction any time we get asked a question involving dates and Active Directory: we immediately plunge the email into a bathtub full of water. (Which, fortunately, we also have available whenever we need one.) That’s because working with dates and times in Active Directory can be a bit … interesting …. (Yes, like the infamous date-time values that represents the number of 100-nanosecond intervals that have occurred since January 1, 1601. Which – coincidentally enough – was also the last time any of the Scripting Guys got a raise.)

Of course, and, inevitably, as soon as we pull the email out of the bathtub we discover the question really wasn’t so bad after all. Such was the case here. Although the date-time value for the whenCreated attribute (which tells us when an object was created) is a little bit tricky, at least it doesn’t deal with nanoseconds or random fluctuations in the space-time continuum. That means we can use it in a relatively simple Active Directory search script. In fact, we can use it in a script just like this one:

On Error Resume Next

Const ADS_SCOPE_SUBTREE = 2
dtmCreationDate = “20060101000000.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, objectCategory, whenCreated FROM ‘LDAP://dc=fabrikam,dc=com’ WHERE ”  & _
        “whenCreated>='” & dtmCreationDate & “‘” 
Set objRecordSet = objCommand.Execute
objRecordSet.MoveFirst
Do Until objRecordSet.EOF
    Wscript.Echo objRecordSet.Fields(“Name”).Value
    Wscript.Echo objRecordSet.Fields(“objectCategory”).Value
    Wscript.Echo objRecordSet.Fields(“whenCreated”).Value
    objRecordSet.MoveNext
Loop

As usual, we’re not going to bother explaining most of the code shown here; if you’re not familiar with scripts used for searching Active Directory we recommend you take a look at the two-part Tales from the Script series Dude, Where’s My Printer? (And yes, we know: by now anyone who’s ever read this column has already memorized the URL to Dude, Where’s My Printer? But we offer it once again, just in case.)

What we will explain is how to work with the whenCreated attribute. You might notice that, early on in the script, we assign a value to the variable dtmCreationDate:

dtmCreationDate = “20060101000000.0Z”

This is actually the date January 1, 2006. Admittedly, it doesn’t look like January 1, 2006, so let’s show you how we went from January 1, 2006 to 20060101000000.0Z:

What if we wanted to use 1:35 PM on November 27^th, 2005? That would look like this, keeping in mind that, using a 24-hour clock, 1:35 PM is equal to 13 hours and 35 minutes:

Or, programmatically:

dtmCreationDate = “20051127133500.0Z”

See? A little quirky, but not too bad. Besides, once you understand how to format the date-time value then you’re home free. With a properly-formatted date-time value in hand we can write a query for conducting the search:

objCommand.CommandText = _
    “SELECT Name, objectCategory, whenCreated FROM ‘LDAP://dc=fabrikam,dc=com’ WHERE ”  & _
        “whenCreated>='” & dtmCreationDate & “‘”

Notice what we’re looking for: all the objects that were created after the 0 hour (00 hours, 00 minutes, 00 seconds) on January 1, 2006. In other words, show us all the objects that have been added to Active Directory during the year 2006. We should also point out that we’re retrieving the Name, objectCategory, and whenCreated attributes; that means we’ll end up with output similar to this:

KenMyer
CN=User,CN=Schema,CN=Configuration,DC=fabrikam,,DC=com
1/3/2006 8:55:27 AM

In other words, we’ll know the name of the object, the type of object (in this case, a user account) and the date and time the object was created. And, fortunately, we don’t have to do any crazy re-formatting in order to display the value of the whenCreated attribute: it automatically gets displayed as a standard date-time value. We only have to use the 20060101000000.0Z syntax when we’re using whenCreated in a Where clause.

And now, if you’ll excuse us, we have to go dry our clothes. Again. (We really do need to stop overreacting any time someone asks us a question, don’t we?)