Hey, Scripting Guy! How Can I Modify the Email Addresses of All the Users in an OU and Its SubOUs?

ScriptingGuy1

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. That’s not because this a particularly difficult question for us to answer; it’s not. Nor is that because this is a question that wouldn’t be of much interest to a lot of other people; it is. It’s just that this question involves searching for something (user accounts in Active Directory), and the Scripting Guy who writes this column isn’t particularly … adept … at searching for things. In fact, over the holiday season, in an attempt to hide a Christmas present from the Scripting Son, he succeeded only in hiding that present from – you guessed it – himself.

The whole thing started shortly before Christmas, when the Scripting Grandpa called to ask what the Scripting Son would like for Christmas. “Well,” said the Scripting Guy who writes this column. “He’d really like satellite radio for his car.”

“OK,” said the Scripting Grandpa. “We’ll get him a radio for his car.”

“Um, satellite radio.”

“That’s what I said: radio. We’ll get him a nice radio for his car.”

“You know what? Why don’t I just pick that up for you?”

And so the Scripting Guy who writes this column did just that. When he returned home from the store, however, the Scripting Son was there, and unexpectedly so: the Scripting Son had intended to spend the day at a friend’s house, but his friend was sick. The fact that the Scripting Son was home, coupled with the fact that the store used see-through plastic bags, posed a problem: how was the Scripting Guy who writes this column supposed to sneak the present into the house?

Fortunately, the Scripting Guy who writes this column came up with a simple, yet clever, ruse, and managed to get the radio into the house and past the Scripting Son. A few days later, the Scripting Family started off on the long drive to Grandpa and Grandma’s. Although his mother had warned him not to expect too much for Christmas (“Remember, a few days ago you got a really nice present for your birthday”) the Scripting Son was undeterred. “I’m not worried,” he said. “Grandpa and Grandma will get me something.”

Grandpa and Grandma will get me something?!?!?! Uh-oh. The Scripting Guy who writes this column suddenly realized that he didn’t pack – or even wrap – the satellite radio. In fact, he hadn’t even seen the satellite radio. He must have left it at the house.

After a quick U-turn the Scripting Guy who writes this column raced home and ran upstairs to get the satellite radio. Gone! Somehow he had lost the Scripting Son’s Christmas present!

Pretty exciting story so far, huh? Heck, we’re on the edge of our seats and we know how it ends. Before we reveal that exciting, cliff hanger-type ending, however, let’s take time out to do a little work. More specifically, let’s take time out to show you how to write a script that can modify the email addresses for all the users in an OU and its subOUs:

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 AdsPath FROM 'LDAP://OU=Finance,DC=fabrikam,DC=com' WHERE objectCategory='user'"  
Set objRecordSet = objCommand.Execute

objRecordSet.MoveFirst
Do Until objRecordSet.EOF
    Set objUser = GetObject(objRecordSet.Fields("AdsPath").Value)
    strEmailAddress = objUser.Mail
    strEmailAddress = LCase(strEmailAddress)
    objUser.Mail = strEmailAddress
    objUser.SetInfo
    objRecordSet.MoveNext
Loop

As usual, we aren’t going to discuss Active Directory search scripts in any detail today; if you need that kind of background information we suggest you take a look at the two-part Tales From the Script series Dude, Where’s My Printer?

What we will do, however, is take a quick peek at the SQL query we use to retrieve information from Active Directory:

objCommand.CommandText = _
    "SELECT AdsPath FROM 'LDAP://OU=Finance,DC=fabrikam,DC=com' WHERE objectCategory='user'"

Two 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 equal to user. As you probably figured out for yourself, this ensures that the only objects returned by our query are user account objects.)

To begin with, notice that we start our search in the Finance OU:

SELECT AdsPath FROM ‘LDAP://OU=Finance,DC=fabrikam,DC=com’ WHERE objectCategory=’user

Is that unusual? Yes, it is; typically we start a search in the domain root (DC=fabrikam,DC=com); that’s 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.

What’s 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 property:

objCommand.Properties("Searchscope") = ADS_SCOPE_SUBTREE

That tells the script to search not only the Finance OU (that is, not only the target container) but also any subOUs or subcontainers.

Pretty easy, huh?

You might also have noticed that the only attribute we requested back from the search is the ADsPath attribute. Why didn’t we ask for the mail attribute, especially when you considerthe fact that the whole idea of the script is to modify the value of the mail attribute?

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’t use database techniques to update or modify Active Directory; in other words, you can’t write any sort of Update query that modifies the mail attribute. Instead, we have to individually connect to each and every user account and “manually” modify the value of this attribute. (Don’t worry; that sounds painfully slow, but it’s actually quite fast.) In order to individually bind to each user account all we need is the ADsPath attribute; therefore, that’s all we asked for. (The Scripting Guys aren’t the least bit greedy. At least not when it comes to requesting information from Active Directory.)

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:

Set objUser = GetObject(objRecordSet.Fields("AdsPath").Value)

Once we’ve made the connection we then grab the user’s current email address and assign it to a variable named strEmailAddress:

strEmailAddress = objUser.Mail

And from there we use VBScript’s LCase function to convert all the characters in strEmailAddress to their lowercase equivalents:

strEmailAddress = LCase(strEmailAddress)

The net result? If the email address started out looking like this:

KenMyer@Fabrkam.COM

It will now look like this, with all the uppercase letters converted to their lowercase equivalents:

kenmyer@fabrkam.com

Now all we have to do is assign the value of strEmailAddress to the user’s mail attribute. That’s what we do here:

objUser.Mail = strEmailAddress
objUser.SetInfo

As you can see, there’s nothing fancy about these two lines of code: we simply assign strEmailAddress to the Mail attribute, then call the SetInfo 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 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’ll have modified the email addresses of all the users in the Finance OU and its subOUs.

Which is just exactly what we wanted to do.

That should do it, TB. Admittedly, it’s 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’s easy enough to write a script that retrieves the newly-modified addresses.

Now for the moment you’ve all been waiting for: what did happen to the Scripting Son’s 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’s. 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.

And that’s 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’s only in real life where he has trouble.

ScriptingGuy1

Follow