Hey, Scripting Guy! How Can I Get the Name and whenCreated Values for All the Users in Active Directory?

Hey, Scripting Guy! How Can I Get the Name and whenCreated Values for All the Users in Active Directory?

  • Comments 1
  • Likes

Hey, Scripting Guy! Question

Hey, Scripting Guy! How can I get the Name and whenCreated values for all the users in my Active Directory domain and then write that information to a comma-separated values file?

-- MM

SpacerHey, Scripting Guy! AnswerScript Center

Hey, MM. Well, assuming all has gone according to plan, by the time most of your read this column the Scripting Guys will be on a plane and winging their way towards Barcelona and TechEd IT Forum. We say “assuming” all has gone according to plan simply because the Scripting Guys haven’t had much luck with airplanes in the past six months: not only have they both had flights that were cancelled and flights that were delayed, but earlier this year Scripting Guy Jean Ross spent 21 hours trying to get from Seattle to Dayton, Ohio, a journey that went from Seattle to Denver to Atlanta and then, after a night spent at the Atlanta airport, finally to Dayton.

Note. How did she miss Timbuktu, Katmandu, and Walla Walla, Washington on that flight? Hey, keep it down; we don’t want to give the airlines any ideas!

In theory, the Scripting Guys will fly to Atlanta, spend a few hours lounging around the airport (giving Jean a chance to visit all her old haunts and the friends she made during her stay there), then hop another plane for a nine-hour flight to Paris. They'll spend a couple days sightseeing in Paris, then zip down to Barcelona on Sunday in order to get ready for IT Forum.

By the way, did we mention that the Scripting Guys will be manning a booth (booth 22) in the Ask the Experts section of the conference? Well, we should have, because that’s exactly what we’ll be doing. If you happen to be in the neighborhood, drop by and say hi; we’ll give you a copy of Dr. Scripto’s Fun Book and, who knows? You might even win one of the coveted Dr. Scripto bobblehead dolls.

What if you didn’t get to go to Barcelona and IT Forum? Well, don’t feel bad. For one thing, you don’t have to spend an entire day on an airplane. For another, as a way of making it up to you, we’re publishing two Hey, Scripting Guy! columns today. That’s right. We aren’t sure when we’ll next have access to the Internet, so we decided to publish two columns today. You can find today’s column, well, right here, and you can find tomorrow’s column right here.

Well, we don’t know if getting two Hey, Scripting Guy! columns in a single day is better than going to Paris and Barcelona. We’d have to call it a tie.

Now, had we decided to do three columns, well …. But we were afraid no one could take that much excitement in a single day.

Speaking of excitement, this ought to get your heart pounding. Here’s a script that can retrieve the Name and whenCreated attributes for all the users in a domain, and then write that information to a comma-separated values file:

On Error Resume Next

Const ADS_SCOPE_SUBTREE = 2

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 objectCategory='user'" 
 
Set objRecordSet = objCommand.Execute

objRecordSet.MoveFirst

Set objFSO = CreateObject("Scripting.FileSystemObject")
Set objFile = objFSO.CreateTextFile("C:\Scripts\Users.csv")

Do Until objRecordSet.EOF
    strLine = Chr(34) & objRecordSet.Fields("Name").Value & Chr(34) & ","
    strLine = strLine & Chr(34) & objRecordSet.Fields("whenCreated").Value & Chr(34)
    objFile.WriteLine strLine
    objRecordSet.MoveNext
Loop

objFile.Close
objRecordset.Close
objConnection.Close

OK, let’s take a few minutes to talk about this script and how it works. Oh, and it will only take a few minutes: that’s because we aren’t going to talk about the boilerplate code used to set up and configure an Active Directory search script. Instead, we’re going to refer you to the classic Tales From the Script series Dude, Where’s My Printer?, two articles that explain Active Directory searching in far more detail than we can go into today.

Note. You say you’re a Windows PowerShell user and you’re feeling left out? You say that you’d like to be able to search Active Directory, too? Hey, cheer up: there’s no reason why you can’t search Active Directory using Windows PowerShell. And as part of next week’s festivities, we’ll have the article – and a whole bunch of sample scripts – to prove it. Which is yet another reason for everyone to come back to the Script Center next week.

One thing we will talk about (if only for a second) is the query we use to retrieve a collection of all the users in our domain:

objCommand.CommandText = _
    "SELECT Name, whenCreated FROM 'LDAP://dc=fabrikam,dc=com' WHERE objectCategory='user'"

As you can see, in this query we’re asking the script to return values for two different attributes: Name and whenCreated. That’s important, by the way: you must specifically list each and every attribute you want the script to return. And before you ask, no, this query will not work:

objCommand.CommandText = _
    "SELECT * FROM 'LDAP://dc=fabrikam,dc=com' WHERE objectCategory='user'"

The Active Directory search provider doesn’t allow you to use the asterisk (or any other character) as a wildcard meaning, “Bring me back the values of all the attributes.” No can do.

Note. To be honest, however, that’s for your own good. After all, the typical Active Directory user account can have over 300 attributes. If you have several thousand users in your domain and you try bringing back 300+ values for each of those users, well, there’s a good chance you’ll overwhelm either the network or the domain controller conducting the search. Or both.

In addition, we specify the search location (LDAP://dc=fabrikam,dc=com, which simply points to the root of the Fabrikam.com domain). Oh, and we use this Where clause to restrict returned data to user accounts:

WHERE objectCategory='user'

What if we wanted to restrict returned data to computer accounts? Hey, no problem; just replace the value user with computer:

WHERE objectCategory='computer'

Etc.

Once we define our query we then run this line of code to conduct the search and return a recordset (objRecordset) containing the Name and whenCreated attributes for all the users in our domain:

Set objRecordSet = objCommand.Execute

Now that we have all the information we want safely tucked away in a recordset, our next chore is to save that information to a CSV file. The first two steps in that process? Create an instance of the Scripting.FileSystemObject, then use the CreateTextFile method to create a text file named C:\Scripts\Users.csv:

Set objFSO = CreateObject("Scripting.FileSystemObject")
Set objFile = objFSO.CreateTextFile("C:\Scripts\Users.csv")

Once we have a blank text file we set up a Do Until loop that runs until the recordset’s EOF (end-of-file) property is True; that’s just a fancy way of saying that we want to keep looping through the recordset until we run out of records to process. Inside that loop, the first thing we do is execute these two lines of code:

strLine = Chr(34) & objRecordSet.Fields("Name").Value & Chr(34) & ","
strLine = strLine & Chr(34) & objRecordSet.Fields("whenCreated").Value & Chr(34)

What’s going on here? Well, this is where we piece together the first line in our text file; that is, the Name and whenCreated values for the first user in the recordset. OK, that makes sense; we see references to both the Name (objRecordSet.Fields("Name").Value) and whenCreated (objRecordSet.Fields("whenCreated").Value) attributes. But what are all those Chr(34) things for?

Well, as you probably know, the one bugaboo that can wreak havoc with a CVS file is an “extra” comma. Suppose our first user is named Pilar Ackerman. Her line in the text file will look like this:

Pilar Ackerman,11/1/2007

What’s wrong with that? Nothing; each line is supposed to consist of two fields, separated by a comma, and that’s exactly what we have here:

Pilar Ackerman

11/1/2007

Now, suppose our next user is named Ken Myer, Jr. His line in the text file will look like this:

Ken Myer, Jr.,11/1/2007

What’s wrong with that? Unfortunately, quite a bit: any application trying to parse the text file is going to think that the comma between Myer and Jr. is being used to separate two fields. Which, in turn, means that the application is going to think that this line consists of three fields:

Ken Myer

Jr.

11/1/2007

Eep.

Fortunately, there’s an easy way to work around this issue; all you have to do is enclose each field in double quote marks, like so:

"Ken Myer, Jr.","11/1/2007"

When you do that, the text file parser will ignore any commas inside the double quote marks; in turn, that means your application will correctly see this line as consisting of two fields:

Ken Myer, Jr.

11/1/2007

That’s more like it.

To make a long story just a tiny bit longer, that’s what all those Chr(34) things are for. Chr(34) is simply a way to represent the double quote mark (") when constructing a string value. Thus our first line of code is simply combining these pieces:

A double quote mark (Chr(34)).

The value of the Name attribute (objRecordSet.Fields("Name").Value).

Another double quote mark (Chr(34)).

A comma (used to separate this field from the next field).

Put those all together, and the variable strLine will have a value similar to this:

"Ken Myer, Jr.",

In line 2, we then take that value and add a double quote, the value of the whenCreated attribute, and a closing set of double quotes. That makes strLine equal to this:

"Ken Myer, Jr.","11/1/2007"

Best of all, that also makes strLine ready to be written to our text file, something we do using the WriteLine method:

objFile.WriteLine strLine

From there we call the MoveNext method to repeat this process with the next record in the recordset. When we’ve finished writing all the records to the text file we close the file (along with our Recordset and Connection objects), and then congratulate ourselves on another job well done.

As for the Scripting Guys, at the moment they’re looking at a 4½ hour flight from Seattle to Atlanta, then a 9 hour flight from Atlanta to Paris. That’s definitely a long time to spend traveling from one place to another. Nevertheless, that’s still faster than Scripting Guy Jean Ross can drive the 10 miles from Microsoft to her house during rush hour.

Your comment has been posted.   Close
Thank you, your comment requires moderation so it may take a while to appear.   Close
Leave a Comment