{"id":63623,"date":"2007-11-09T00:31:00","date_gmt":"2007-11-09T00:31:00","guid":{"rendered":"https:\/\/blogs.technet.microsoft.com\/heyscriptingguy\/2007\/11\/09\/hey-scripting-guy-how-can-i-get-the-name-and-whencreated-values-for-all-the-users-in-active-directory\/"},"modified":"2007-11-09T00:31:00","modified_gmt":"2007-11-09T00:31:00","slug":"hey-scripting-guy-how-can-i-get-the-name-and-whencreated-values-for-all-the-users-in-active-directory","status":"publish","type":"post","link":"https:\/\/devblogs.microsoft.com\/scripting\/hey-scripting-guy-how-can-i-get-the-name-and-whencreated-values-for-all-the-users-in-active-directory\/","title":{"rendered":"Hey, Scripting Guy! How Can I Get the Name and whenCreated Values for All the Users in Active Directory?"},"content":{"rendered":"

\"Hey, <\/H2>\n

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<\/P>\"Spacer\"\"Hey,\"Script<\/A> \n

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<\/B><\/A>. We say \u201cassuming\u201d all has gone according to plan simply because the Scripting Guys haven\u2019t 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.<\/P>\n\n<\/THEAD>\n\n\n
\n

Note<\/B>. How did she miss Timbuktu, Katmandu, and Walla Walla, Washington on that flight? Hey, keep it down; we don\u2019t want to give the airlines any ideas!<\/P><\/TD><\/TR><\/TBODY><\/TABLE>\n

<\/DIV>\n

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.<\/P>\n

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

What if you didn\u2019t get to go to Barcelona and IT Forum? Well, don\u2019t feel bad. For one thing, you don\u2019t have to spend an entire day on an airplane. For another, as a way of making it up to you, we\u2019re publishing two<\/I> Hey, Scripting Guy! columns today. That\u2019s right. We aren\u2019t sure when we\u2019ll next have access to the Internet, so we decided to publish two columns today. You can find today\u2019s column, well, right here, and you can find tomorrow\u2019s column right here<\/B><\/A>.<\/P>\n

Well, we don\u2019t know if getting two Hey, Scripting Guy! columns in a single day is better<\/I> than going to Paris and Barcelona. We\u2019d have to call it a tie.<\/P>\n

Now, had we decided to do three<\/I> columns, well \u2026. But we were afraid no one could take that much excitement in a single day.<\/P>\n

Speaking of excitement, this ought to get your heart pounding. Here\u2019s 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:<\/P>

On Error Resume Next<\/p>\n
Const ADS_SCOPE_SUBTREE = 2<\/p>\n
Set objConnection = CreateObject(“ADODB.Connection”)\nSet objCommand =   CreateObject(“ADODB.Command”)\nobjConnection.Provider = “ADsDSOObject”\nobjConnection.Open “Active Directory Provider”\nSet objCommand.ActiveConnection = objConnection<\/p>\n
objCommand.Properties(“Page Size”) = 1000\nobjCommand.Properties(“Searchscope”) = ADS_SCOPE_SUBTREE <\/p>\n
objCommand.CommandText = _\n    “SELECT Name, whenCreated FROM ‘LDAP:\/\/dc=fabrikam,dc=com’ WHERE objectCategory=’user'” <\/p>\n
Set objRecordSet = objCommand.Execute<\/p>\n
objRecordSet.MoveFirst<\/p>\n
Set objFSO = CreateObject(“Scripting.FileSystemObject”)\nSet objFile = objFSO.CreateTextFile(“C:\\Scripts\\Users.csv”)<\/p>\n
Do Until objRecordSet.EOF\n    strLine = Chr(34) & objRecordSet.Fields(“Name”).Value & Chr(34) & “,”\n    strLine = strLine & Chr(34) & objRecordSet.Fields(“whenCreated”).Value & Chr(34)\n    objFile.WriteLine strLine\n    objRecordSet.MoveNext\nLoop<\/p>\n
objFile.Close\nobjRecordset.Close\nobjConnection.Close\n<\/PRE>\n
OK, let\u2019s take a few minutes to talk about this script and how it works. Oh, and it will<\/I> only take a few minutes: that\u2019s because we aren\u2019t going to talk about the boilerplate code used to set up and configure an Active Directory search script. Instead, we\u2019re going to refer you to the classic Tales From the Script<\/I> series Dude, Where\u2019s My Printer?<\/B><\/A>, two articles that explain Active Directory searching in far more detail than we can go into today.<\/P>\n\n<\/THEAD>\n\n\n
\n
Note<\/B>. You say you\u2019re a Windows PowerShell user and you\u2019re feeling left out? You say that you\u2019d<\/I> like to be able to search Active Directory, too? Hey, cheer up: there\u2019s no reason why you can\u2019t search Active Directory using Windows PowerShell. And as part of next week\u2019s festivities, we\u2019ll have the article \u2013 and a whole bunch of sample scripts \u2013 to prove it. Which is yet another reason for everyone to come back to the Script Center next week.<\/P><\/TD><\/TR><\/TBODY><\/TABLE>\n
<\/DIV>\n
One thing we will<\/I> talk about (if only for a second) is the query we use to retrieve a collection of all the users in our domain:<\/P>
objCommand.CommandText = _\n    “SELECT Name, whenCreated FROM ‘LDAP:\/\/dc=fabrikam,dc=com’ WHERE objectCategory=’user'”\n<\/PRE>\n
As you can see, in this query we\u2019re asking the script to return values for two different attributes: Name<\/B> and whenCreated<\/B>. That\u2019s 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<\/I> work:<\/P>
objCommand.CommandText = _\n    “SELECT * FROM ‘LDAP:\/\/dc=fabrikam,dc=com’ WHERE objectCategory=’user'”\n<\/PRE>\n
The Active Directory search provider doesn\u2019t allow you to use the asterisk (or any other character) as a wildcard meaning, \u201cBring me back the values of all<\/I> the attributes.\u201d No can do.<\/P>\n\n<\/THEAD>\n\n\n
\n
Note<\/B>. To be honest, however, that\u2019s 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\u2019s a good chance you\u2019ll overwhelm either the network or the domain controller conducting the search. Or both.<\/P><\/TD><\/TR><\/TBODY><\/TABLE>\n
<\/DIV>\n
In addition, we specify the search location (LDAP:\/\/dc=fabrikam,dc=com<\/B>, 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:<\/P>
WHERE objectCategory=’user’\n<\/PRE>\n
What if we wanted to restrict returned data to computer accounts? Hey, no problem; just replace the value user<\/I> with computer<\/I>:<\/P>
WHERE objectCategory=’computer’\n<\/PRE>\n
Etc.<\/P>\n
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: <\/P>
Set objRecordSet = objCommand.Execute\n<\/PRE>\n
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<\/B>, then use the CreateTextFile<\/B> method to create a text file named C:\\Scripts\\Users.csv:<\/P>
Set objFSO = CreateObject(“Scripting.FileSystemObject”)\nSet objFile = objFSO.CreateTextFile(“C:\\Scripts\\Users.csv”)\n<\/PRE>\n
Once we have a blank text file we set up a Do Until loop that runs until the recordset\u2019s EOF<\/B> (end-of-file) property is True; that\u2019s 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:<\/P>
strLine = Chr(34) & objRecordSet.Fields(“Name”).Value & Chr(34) & “,”\nstrLine = strLine & Chr(34) & objRecordSet.Fields(“whenCreated”).Value & Chr(34)\n<\/PRE>\n
What\u2019s 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<\/B>) and whenCreated (objRecordSet.Fields(“whenCreated”).Value<\/B>) attributes. But what are all those Chr(34)<\/B> things for?<\/P>\n
Well, as you probably know, the one bugaboo that can wreak havoc with a CVS file is an \u201cextra\u201d comma. Suppose our first user is named Pilar Ackerman. Her line in the text file will look like this:<\/P>
Pilar Ackerman,11\/1\/2007\n<\/PRE>\n
What\u2019s wrong with that? Nothing; each line is supposed to consist of two fields, separated by a comma, and that\u2019s exactly what we have here:<\/P>\n\n\n\n\n
\u2022<\/TD>\n\n
Pilar Ackerman<\/P><\/TD><\/TR>\n
\u2022<\/TD>\n\n
11\/1\/2007<\/P><\/TD><\/TR><\/TBODY><\/TABLE>\n
Now, suppose our next user is named Ken Myer, Jr. His line in the text file will look like this:<\/P>
Ken Myer, Jr.,11\/1\/2007\n<\/PRE>\n
What\u2019s wrong with that? Unfortunately, quite a bit: any application trying to parse the text file is going to think that the comma between Myer<\/I> and Jr.<\/I> is being used to separate two fields. Which, in turn, means that the application is going to think that this line consists of three<\/I> fields:<\/P>\n\n\n\n\n\n
\u2022<\/TD>\n\n
Ken Myer<\/P><\/TD><\/TR>\n
\u2022<\/TD>\n\n
Jr.<\/P><\/TD><\/TR>\n
\u2022<\/TD>\n\n
11\/1\/2007<\/P><\/TD><\/TR><\/TBODY><\/TABLE>\n
Eep.<\/P>\n
Fortunately, there\u2019s an easy way to work around this issue; all you have to do is enclose each field in double quote marks, like so:<\/P>
“Ken Myer, Jr.”,”11\/1\/2007″\n<\/PRE>\n
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:<\/P>\n\n\n\n\n
\u2022<\/TD>\n\n
Ken Myer, Jr.<\/P><\/TD><\/TR>\n
\u2022<\/TD>\n\n
11\/1\/2007<\/P><\/TD><\/TR><\/TBODY><\/TABLE>\n
That\u2019s more like it.<\/P>\n
To make a long story just a tiny bit longer, that\u2019s<\/I> 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:<\/P>\n\n\n\n\n\n\n
\u2022<\/TD>\n\n
A double quote mark (Chr(34)<\/B>).<\/P><\/TD><\/TR>\n
\u2022<\/TD>\n\n
The value of the Name attribute (objRecordSet.Fields(“Name”).Value<\/B>).<\/P><\/TD><\/TR>\n
\u2022<\/TD>\n\n
Another double quote mark (Chr(34)<\/B>).<\/P><\/TD><\/TR>\n
\u2022<\/TD>\n\n
A comma (used to separate this field from the next field).<\/P><\/TD><\/TR><\/TBODY><\/TABLE>\n
Put those all together, and the variable strLine will have a value similar to this:<\/P>
“Ken Myer, Jr.”,\n<\/PRE>\n
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:<\/P>
“Ken Myer, Jr.”,”11\/1\/2007″\n<\/PRE>\n
Best of all, that also makes strLine ready to be written to our text file, something we do using the WriteLine<\/B> method:<\/P>
objFile.WriteLine strLine\n<\/PRE>\n
From there we call the MoveNext<\/B> method to repeat this process with the next record in the recordset. When we\u2019ve 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.<\/P>\n
As for the Scripting Guys, at the moment they\u2019re looking at a 4\u00bd hour flight from Seattle to Atlanta, then a 9 hour flight from Atlanta to Paris. That\u2019s definitely a long time to spend traveling from one place to another. Nevertheless, that\u2019s still faster than Scripting Guy Jean Ross can drive the 10 miles from Microsoft to her house during rush hour.<\/P><\/p>\n","protected":false},"excerpt":{"rendered":"
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 Hey, MM. Well, assuming all has gone according to plan, by the time most of your read this column the Scripting Guys will […]<\/p>\n","protected":false},"author":595,"featured_media":87096,"comment_status":"open","ping_status":"closed","sticky":false,"template":"","format":"standard","meta":{"_acf_changed":false,"footnotes":""},"categories":[1],"tags":[7,3,20,5],"class_list":["post-63623","post","type-post","status-publish","format-standard","has-post-thumbnail","hentry","category-scripting","tag-active-directory","tag-scripting-guy","tag-user-accounts","tag-vbscript"],"acf":[],"blog_post_summary":"
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 Hey, MM. Well, assuming all has gone according to plan, by the time most of your read this column the Scripting Guys will […]<\/p>\n","_links":{"self":[{"href":"https:\/\/devblogs.microsoft.com\/scripting\/wp-json\/wp\/v2\/posts\/63623","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/devblogs.microsoft.com\/scripting\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/devblogs.microsoft.com\/scripting\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/devblogs.microsoft.com\/scripting\/wp-json\/wp\/v2\/users\/595"}],"replies":[{"embeddable":true,"href":"https:\/\/devblogs.microsoft.com\/scripting\/wp-json\/wp\/v2\/comments?post=63623"}],"version-history":[{"count":0,"href":"https:\/\/devblogs.microsoft.com\/scripting\/wp-json\/wp\/v2\/posts\/63623\/revisions"}],"wp:featuredmedia":[{"embeddable":true,"href":"https:\/\/devblogs.microsoft.com\/scripting\/wp-json\/wp\/v2\/media\/87096"}],"wp:attachment":[{"href":"https:\/\/devblogs.microsoft.com\/scripting\/wp-json\/wp\/v2\/media?parent=63623"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/devblogs.microsoft.com\/scripting\/wp-json\/wp\/v2\/categories?post=63623"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/devblogs.microsoft.com\/scripting\/wp-json\/wp\/v2\/tags?post=63623"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}