will<\/i> do, however, is take a quick peek at the SQL query we use to retrieve information from Active Directory:<\/p>\nobjCommand.CommandText = _\n \"SELECT AdsPath FROM 'LDAP:\/\/OU=Finance,DC=fabrikam,DC=com' WHERE objectCategory='user'\"\n<\/pre>\nTwo things stand out here. (Three if you count the fact that we added a Where clause that limits the retrieved data to objects that have an objectCategory<\/b> equal to user<\/i>. As you probably figured out for yourself, this ensures that the only objects returned by our query are user account objects.)<\/p>\n
To begin with, notice that we start our search in the Finance OU:<\/p>\n
SELECT AdsPath FROM ‘LDAP:\/\/OU=Finance,DC=fabrikam,DC=com’<\/b> WHERE objectCategory=’user<\/p>\n
Is that unusual? Yes, it is; typically we start a search in the domain root (DC=fabrikam,DC=com); that\u2019s because, typically, we want to search the entire domain. With this script, however, we only want to search the Finance OU and its subOUs. Consequently, we start the search in the Finance OU rather than the domain root.<\/p>\n
What\u2019s that? How does the script know to search the subOUs of the Finance OU as well as the Finance OU itself? Well, at the beginning of the script we defined a constant named AD_SCOPE_SUBTREE and set the value to 2; we then assigned this constant to the Searchscope<\/b> property: <\/p>\nobjCommand.Properties(\"Searchscope\") = ADS_SCOPE_SUBTREE\n<\/pre>\nThat tells the script to search not only the Finance OU (that is, not only the target container) but also any subOUs or subcontainers.<\/p>\n
Pretty easy, huh?<\/p>\n
You might also have noticed that the only attribute we requested back from the search is the ADsPath<\/b> attribute. Why didn\u2019t we ask for the mail<\/b> attribute, especially when you considerthe fact that the whole idea of the script is to modify the value of the mail attribute?<\/p>\n
Good question and, for once, at least, we have an equally good answer. Although you can use database techniques to search Active Directory, you can\u2019t use database techniques to update or modify Active Directory; in other words, you can\u2019t write any sort of Update query that modifies the mail attribute. Instead, we have to individually connect to each and every user account and \u201cmanually\u201d modify the value of this attribute. (Don\u2019t worry; that sounds painfully slow, but it\u2019s actually quite fast.) In order to individually bind to each user account all we need is the ADsPath attribute; therefore, that\u2019s all we asked for. (The Scripting Guys aren\u2019t the least bit greedy. At least not when it comes to requesting information from Active Directory.)<\/p>\n
After we execute our query we set up a Do Loop to loop through all the records in the resulting recordset. The first thing we do inside this loop? Why, we use this line of code to bind to the user account for the first user in the recordset, of course:<\/p>\n
Set objUser = GetObject(objRecordSet.Fields(\"AdsPath\").Value)\n<\/pre>\nOnce we\u2019ve made the connection we then grab the user\u2019s current email address and assign it to a variable named strEmailAddress:<\/p>\n
strEmailAddress = objUser.Mail\n<\/pre>\nAnd from there we use VBScript\u2019s LCase<\/b> function to convert all the characters in strEmailAddress to their lowercase equivalents:<\/p>\nstrEmailAddress = LCase(strEmailAddress)\n<\/pre>\nThe net result? If the email address started out looking like this:<\/p>\n
KenMyer@Fabrkam.COM\n<\/pre>\nIt will now look like this, with all the uppercase letters converted to their lowercase equivalents:<\/p>\n
kenmyer@fabrkam.com\n<\/pre>\nNow all we have to do is assign the value of strEmailAddress to the user\u2019s mail attribute. That\u2019s what we do here:<\/p>\n
objUser.Mail = strEmailAddress\nobjUser.SetInfo\n<\/pre>\nAs you can see, there\u2019s nothing fancy about these two lines of code: we simply assign strEmailAddress to the Mail attribute, then call the SetInfo<\/b> method to write the change back to Active Directory. Having modified the email address for the first user in the recordset we call the MoveNext<\/b> record and then repeat the process with the next record. By the time we reach the end of the recordset, and the end of the Do loop, we\u2019ll have modified the email addresses of all the users in the Finance OU and its subOUs.<\/p>\n
Which is just exactly what we wanted to do.<\/p>\n
That should do it, TB. Admittedly, it\u2019s a little different than what you asked for; you originally asked how you could save email information to a spreadsheet, change the letter case in the spreadsheet, then rewrite the modified email addresses back to Active Directory. As you can see, though, this approach is far easier. And, of course, if you still want the email addresses in a spreadsheet, well, it\u2019s easy enough to write a script that retrieves the newly-modified addresses. <\/p>\n
Now for the moment you\u2019ve all been waiting for: what did<\/i> happen to the Scripting Son\u2019s satellite radio? As it turns out, the way the Scripting Guy who writes this column managed to sneak the radio into the house was by taking it out of the see-through plastic bag and wrapping it in an old towel that he had in the trunk of the car. He then proceeded to lay the towel in the middle of the room alongside all the other presents that had to be wrapped and taken to Grandpa and Grandma\u2019s. And there the towel sat, even as he dutifully wrapped all the other presents. In fact, the towel continued to sit there even when he returned home, frantically looking for the present; he actually had to step over the towel a couple of times as he looked behind the bookcase and in the closet. It was only after several minutes of desperate searching, and only after he pretty much gave up, that he remembered why there was an old towel lying in the middle of the floor.<\/p>\n
And that\u2019s<\/i> why the Scripting Guy who writes this column was a little reluctant to help TB search for something. Fortunately, though, the Scripting Guy who writes this column usually handles himself reasonably well in scripting life. It\u2019s only in real life where he has trouble.<\/p>\n","protected":false},"excerpt":{"rendered":"
Hey, Scripting Guy! I need to get a list of all the users in an OU and its subOUs, then change their email addresses so those addresses only user lowercase letters. How can I do that? — TB Hey, TB. You know, we had to think twice before going ahead and answering this question. […]<\/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,43,3,8,198,5],"class_list":["post-63333","post","type-post","status-publish","format-standard","has-post-thumbnail","hentry","category-scripting","tag-active-directory","tag-ous","tag-scripting-guy","tag-searching-active-directory","tag-users","tag-vbscript"],"acf":[],"blog_post_summary":"
Hey, Scripting Guy! I need to get a list of all the users in an OU and its subOUs, then change their email addresses so those addresses only user lowercase letters. How can I do that? — TB Hey, TB. You know, we had to think twice before going ahead and answering this question. […]<\/p>\n","_links":{"self":[{"href":"https:\/\/devblogs.microsoft.com\/scripting\/wp-json\/wp\/v2\/posts\/63333","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=63333"}],"version-history":[{"count":0,"href":"https:\/\/devblogs.microsoft.com\/scripting\/wp-json\/wp\/v2\/posts\/63333\/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=63333"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/devblogs.microsoft.com\/scripting\/wp-json\/wp\/v2\/categories?post=63333"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/devblogs.microsoft.com\/scripting\/wp-json\/wp\/v2\/tags?post=63333"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}
![\"Hey,](\"https:\/\/devblogs.microsoft.com\/wp-content\/uploads\/sites\/29\/2019\/02\/q-for-powertip.jpg\" "\\"Hey,")
<\/p>\n![\"Spacer\"](\"https:\/\/devblogs.microsoft.com\/scripting\/wp-content\/uploads\/sites\/29\/2019\/05\/spacer.gif\")
![\"Hey,](\"https:\/\/devblogs.microsoft.com\/wp-content\/uploads\/sites\/29\/2019\/02\/a-for-powertip.jpg\" "\\"Hey,")
![\"Script](\"http:\/\/img.microsoft.com\/library\/media\/1033\/technet\/images\/scriptcenter\/ad.jpg\" "\\"Script")
<\/a> <\/p>\n