How Can I Tack a Prefix Onto the Telephone Numbers of All the Users in My Domain?

ScriptingGuy1

Hey, Scripting Guy! How can I tack a prefix onto the telephone numbers of all the users in my domain?

— JB

Hey, JB. It’s Monday morning as this column is being written, and the Scripting Guy who actually writes this column is still a bit tired from driving across the state and back in order to attend his niece’s wedding. Yes, yet another a wedding: the third he’s had to attend in the last six months. But that’s all right; after all, is there anything more fun and exciting than a wedding?

Actually, come to think of it, just about everything is more fun and exciting than a wedding. Nevertheless, the Scripting Guy who writes this column has to rate Tim and Tiffany’s wedding as the best he has ever had the … privilege … of attending. Is that because of the obvious love and affection between the bride and groom? Well, to tell you the truth, the Scripting Guy who writes this column didn’t pay much attention to that. What he did pay attention to was this: the entire ceremony, from start to finish, was over in 19 minutes. No slide shows of the happy couple as children; no poems read by lifelong friends; no songs written and performed by a second-cousin; none of that. Instead it was do you take this man/woman to be your lawful wedded husband/wife; we do; you may now kiss the bride. Oh, and Scripting Guy who writes that column, you may now dig into the roast pork loin with apple-brandy sauce.

Amen!

It probably goes without saying that the Scripting Guy who writes this column isn’t much of one for pomp and circumstances; in his view, the quicker he can get something done the quicker he can get to those Tuscan potatoes. Which is one reason why he liked this question about tacking a prefix onto the phone numbers of all the users in Active Directory; that’s a question that can be answered just this easy, and just this quick:

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, telephoneNumber FROM ” & _
        “‘LDAP://dc=fabrikam,dc=com’ WHERE objectCategory=’user'”  
Set objRecordSet = objCommand.Execute
objRecordSet.MoveFirst
Do Until objRecordSet.EOF
    strUser = objRecordSet.Fields(“ADsPath”).Value
    strPhoneNumber = “425-” & objRecordSet.Fields(“telephoneNumber”).Value
    Set objUser =  GetObject(strUser)
    objUser.telephoneNumber = strPhoneNumber
    objUser.SetInfo
    objRecordSet.MoveNext
Loop

Following a long-standing Scripting Guys tradition, we’re going to explain only part of this script. That’s because the first 9 or so lines are boilerplate code for searching Active Directory; they can be used pretty much as-is in any Active Directory search script you write. But you don’t just have to take our word for it; instead, you can take a look at this two-part Tales From the Script series that explains the mystery behind such terms as ADODB.Connection and ADsDSOObject.

But we won’t totally shirk our responsibilities here. Instead, we’ll at least take a minute or two to explain this line of code:

objCommand.CommandText = _
    “SELECT ADsPath, telephoneNumber FROM ” & _
        “‘LDAP://dc=fabrikam,dc=com’ WHERE objectCategory=’user'”

As you probably guessed, this is the line of code that specifies the criteria to be used when searching Active Directory. What we’re doing here is searching the fabrikam.com domain. What exactly are we searching for? We’re searching only for user accounts; we know that because we included a WHERE clause limiting the returned data to objects where the objectCategory is equal to user. (Which, in turn, means we’ll get back only user accounts.) What if we wanted to search for computer accounts instead? No problem; we’d just change the CommandText property so it looked like this:

objCommand.CommandText = _
    “SELECT ADsPath, telephoneNumber FROM ” & _
        “‘LDAP://dc=fabrikam,dc=com’ WHERE objectCategory=’computer'”

That was easy enough. And as long as we’re on the subject of returned data, whenever we search Active Directory we need to specify the attribute values we want to get back. (Don’t try a “Select * FROM” command; you’ll be very disappointed.) In this script we’re asking to get back values for two attributes: ADsPath, which is roughly equivalent to an Active Directory file path (thus enabling us to uniquely locate an individual user account); and telephoneNumber, which just happens to be the user’s telephone number.

After specifying our search criteria we then call the Execute method, which runs the query and returns a recordset consisting of all the users in Active Directory, along with their ADsPath and phone number:

Set objRecordSet = objCommand.Execute

In other words, that gives us a list of all our users and their current phone numbers. All we need to do now is stick a prefix onto each of those phone numbers.

So how do we intend to do that? To begin with, it’s important to note that Active Directory searches are read-only; we can’t write a query that changes attribute values. (If you’re wondering why we didn’t do some sort of SQL Update query, well, now you know.) Instead, we have to individually connect to each and every user account and then change the phone numbers one-by-one. Admittedly, we could have made this script a tiny bit shorter by combining a few of the lines of code we’re about to discuss. However, in order to make things crystal clear we decided to to break the process into somewhat more-discrete steps, beginning with this:

strUser = objRecordSet.Fields(“ADsPath”).Value

What we’re doing here is grabbing the ADsPath for the first user in the recordset and storing it in a variable named strUser; that value is going to look something like this:

LDAP://CN=Ken Myer,OU=Finance,dc=fabrikam,dc=com

After getting the ADsPath we then use this line of code to construct a new phone number for this first user:

strPhoneNumber = “425-” & objRecordSet.Fields(“telephoneNumber”).Value

Here we’re sticking the prefix 425- to the front of each phone number; that’s because (for this sample script) we’re adding that area code to each number. As you can see, this is a very simple line of code: we’re just combining the prefix 425- with the user’s current phone number (objRecordSet.Fields(“telephoneNumber”).Value), and then assigning the resulting value to a variable named strPhoneNumber.

We should also point out that, for simplicity’s sake, we’re assuming that all users have a phone number and that none of these phone numbers already include the area code. Depending on your setup those might not be safe assumptions to make. But that’s OK; it’s easy enough to build some safeguards into the script that will help prevent you from assigning the “phone number” 425- to someone who doesn’t currently have a phone number, or from giving someone else a phone number like 425-425-555-1981 (because this user’s phone number already included the area code). For example, you can use the IsNull function to verify that a user actually has a phone number before you add the prefix:

If Not IsNull(objRecordSet.Fields(“telephoneNumber”).Value) Then

In other words, if the telephoneNumber is not Null (that is, if it does have a value) then go ahead and add the prefix. What if the phone number is Null, what if it doesn’t have a value? In that case, configure the script so that it doesn’t even bother with the phone number for this user, but instead loops around and tries the next user in the recordset.

As for checking to see if the area code has already been added to the phone number, well, this line of code should do the trick:

If Left(objRecordSet.Fields(“telephoneNumber”).Value, 4) <> “425-” Then

See how that works? Here we’re using the Left function to look at the first 4 characters in the telephone number. If the first four characters are already 425- then we won’t do anything; we’ll just loop around and work with the next record in the recordset. If the first four characters aren’t 425- then we’ll go ahead add the prefix.

Pretty easy stuff, and you should be able to add that code in without much trouble.

Now we’re ready to change the phone number, something we do using these three lines of code:

Set objUser =  GetObject(strUser)
objUser.telephoneNumber = strPhoneNumber
objUser.SetInfo

In the first line we’re simply binding to the user account in Active Directory. In line 2, we’re assigning the new phone number (stored in the variable strPhoneNUmber) to the telephoneNumber attribute for that account. And then in line 3 we call the SetInfo method and actually write the new phone number to Active Directory.

Whatever you do, don’t leave out SetInfo; if you do, the phone number will not be changed. And don’t leave out this line of code, either:

objRecordSet.MoveNext

Leave out that line, and the script will never move on to the next record in the recordset; instead, your script will drag on forever and ever, never getting anywhere at all.

Just like the typical wedding ceremony.

Anyway, that should do it, JB. Let us know if you run into any problems here.

In addition to the food, we should note that there was one other factor that made this last wedding worthwhile: the Scripting Son caught the garter. That’s not that big of a deal (although he did make a nice catch); what was a big deal is that, during the reception, the “young man who caught the garter and the young lady who caught the bouquet” were asked to come to the front of the room. At that point, the two of them were forced to dance with one another (while they played the song The Chapel of Love). (Actually, the young lady who caught the bouquet was happy to dance. The Scripting Son? Not so happy.)

So is the Scripting Son a good dancer? Let’s put it this way: have you ever seen Fred Astaire or Gene Kelly dance? You have? OK; in that case we won’t lie to you and say that the Scripting Son is a good dancer; you obviously know what good dancing looks like. Instead, we’ll say this: you know how the guards at Buckingham Palace are famous for never moving and never changing expression? Based on his dancing alone, we have no doubt that the Scripting Son could get himself a job at Buckingham Palace any time he wants one.

ScriptingGuy1

Follow