{"id":65063,"date":"2007-04-17T21:14:00","date_gmt":"2007-04-17T21:14:00","guid":{"rendered":"https:\/\/blogs.technet.microsoft.com\/heyscriptingguy\/2007\/04\/17\/how-can-i-tack-a-prefix-onto-the-telephone-numbers-of-all-the-users-in-my-domain\/"},"modified":"2007-04-17T21:14:00","modified_gmt":"2007-04-17T21:14:00","slug":"how-can-i-tack-a-prefix-onto-the-telephone-numbers-of-all-the-users-in-my-domain","status":"publish","type":"post","link":"https:\/\/devblogs.microsoft.com\/scripting\/how-can-i-tack-a-prefix-onto-the-telephone-numbers-of-all-the-users-in-my-domain\/","title":{"rendered":"How Can I Tack a Prefix Onto the Telephone Numbers of All the Users in My Domain?"},"content":{"rendered":"

\"Hey, \n

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

— JB<\/P>\"Spacer\"\"Hey,\"Script<\/A> \n

Hey, JB. It\u2019s 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\u2019s wedding. Yes, yet another a wedding: the third he\u2019s had to attend in the last six months. But that\u2019s all right; after all, is there anything more fun and exciting than a wedding?<\/P>\n

Actually, come to think of it, just about everything<\/I> is more fun and exciting than a wedding. Nevertheless, the Scripting Guy who writes this column has to rate Tim and Tiffany\u2019s wedding as the best he has ever had the \u2026 privilege \u2026 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\u2019t pay much attention to that. What he did<\/I> 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. <\/P>\n

Amen!<\/P>\n

It probably goes without saying that the Scripting Guy who writes this column isn\u2019t 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\u2019s a question that can be answered just this easy, and just this quick:<\/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 ADsPath, telephoneNumber FROM ” & _\n        “‘LDAP:\/\/dc=fabrikam,dc=com’ WHERE objectCategory=’user'”  \nSet objRecordSet = objCommand.Execute<\/p>\n
objRecordSet.MoveFirst\nDo Until objRecordSet.EOF\n    strUser = objRecordSet.Fields(“ADsPath”).Value\n    strPhoneNumber = “425-” & objRecordSet.Fields(“telephoneNumber”).Value\n    Set objUser =  GetObject(strUser)\n    objUser.telephoneNumber = strPhoneNumber\n    objUser.SetInfo\n    objRecordSet.MoveNext\nLoop\n<\/PRE>\n
Following a long-standing Scripting Guys tradition, we\u2019re going to explain only part of this script. That\u2019s 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\u2019t just have to take our word for it; instead, you can take a look at this two-part Tales From the Script<\/B><\/A> series that explains the mystery behind such terms as ADODB.Connection<\/I> and ADsDSOObject<\/I>.<\/P>\n
But we won\u2019t totally shirk our responsibilities here. Instead, we\u2019ll at least take a minute or two to explain this line of code:<\/P>
objCommand.CommandText = _\n    “SELECT ADsPath, telephoneNumber FROM ” & _\n        “‘LDAP:\/\/dc=fabrikam,dc=com’ WHERE objectCategory=’user'”\n<\/PRE>\n
As you probably guessed, this is the line of code that specifies the criteria to be used when searching Active Directory. What we\u2019re doing here is searching the fabrikam.com domain. What exactly are we searching for? We\u2019re searching only for user accounts; we know that because we included a WHERE clause limiting the returned data to objects where the objectCategory<\/B> is equal to user<\/I>. (Which, in turn, means we\u2019ll get back only user accounts.) What if we wanted to search for computer accounts instead? No problem; we\u2019d just change the CommandText<\/B> property so it looked like this:<\/P>
objCommand.CommandText = _\n    “SELECT ADsPath, telephoneNumber FROM ” & _\n        “‘LDAP:\/\/dc=fabrikam,dc=com’ WHERE objectCategory=’computer'”\n<\/PRE>\n
That was easy enough. And as long as we\u2019re on the subject of returned data, whenever we search Active Directory we need to specify the attribute values we want to get back. (Don\u2019t try a \u201cSelect * FROM\u201d command; you\u2019ll be very disappointed.) In this script we\u2019re asking to get back values for two attributes: ADsPath<\/B>, which is roughly equivalent to an Active Directory file path (thus enabling us to uniquely locate an individual user account); and telephoneNumber,<\/B> which just happens to be the user\u2019s telephone number. <\/P>\n
After specifying our search criteria we then call the Execute<\/B> method, which runs the query and returns a recordset consisting of all the users in Active Directory, along with their ADsPath and phone number:<\/P>
Set objRecordSet = objCommand.Execute\n<\/PRE>\n
In other words, that gives us a list of all our users and<\/I> their current phone numbers. All we need to do now is stick a prefix onto each of those phone numbers.<\/P>\n
So how do we intend to do that? To begin with, it\u2019s important to note that Active Directory searches are read-only; we can\u2019t write a query that changes<\/I> attribute values. (If you\u2019re wondering why we didn\u2019t 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\u2019re 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:<\/P>
strUser = objRecordSet.Fields(“ADsPath”).Value\n<\/PRE>\n
What we\u2019re 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:<\/P>
LDAP:\/\/CN=Ken Myer,OU=Finance,dc=fabrikam,dc=com\n<\/PRE>\n
After getting the ADsPath we then use this line of code to construct a new phone number for this first user:<\/P>
strPhoneNumber = “425-” & objRecordSet.Fields(“telephoneNumber”).Value\n<\/PRE>\n
Here we\u2019re sticking the prefix 425-<\/I> to the front of each phone number; that\u2019s because (for this sample script) we\u2019re adding that area code to each number. As you can see, this is a very simple line of code: we\u2019re just combining the prefix 425-<\/I> with the user\u2019s current phone number (objRecordSet.Fields(“telephoneNumber”).Value<\/B>), and then assigning the resulting value to a variable named strPhoneNumber.<\/P>\n
We should also point out that, for simplicity\u2019s sake, we\u2019re 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\u2019s OK; it\u2019s easy enough to build some safeguards into the script that will help prevent you from assigning the \u201cphone number\u201d 425-<\/I> to someone who doesn\u2019t currently have a phone number, or from giving someone else a phone number like 425-425-555-1981 <\/I>(because this user\u2019s phone number already included the area code). For example, you can use the IsNull<\/B> function to verify that a user actually has<\/I> a phone number before you add the prefix:<\/P>
If Not IsNull(objRecordSet.Fields(“telephoneNumber”).Value) Then\n<\/PRE>\n
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<\/I> Null, what if it doesn\u2019t<\/I> have a value? In that case, configure the script so that it doesn\u2019t even bother with the phone number for this user, but instead loops around and tries the next user in the recordset.<\/P>\n
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:<\/P>
If Left(objRecordSet.Fields(“telephoneNumber”).Value, 4) <> “425-” Then\n<\/PRE>\n
See how that works? Here we\u2019re using the Left<\/B> function to look at the first 4 characters in the telephone number. If the first four characters are already 425-<\/I> then we won\u2019t do anything; we\u2019ll just loop around and work with the next record in the recordset. If the first four characters aren\u2019t 425-<\/I> then we\u2019ll go ahead add the prefix.<\/P>\n
Pretty easy stuff, and you should be able to add that code in without much trouble.<\/P>\n
Now we\u2019re ready to change the phone number, something we do using these three lines of code:<\/P>
Set objUser =  GetObject(strUser)\nobjUser.telephoneNumber = strPhoneNumber\nobjUser.SetInfo\n<\/PRE>\n
In the first line we\u2019re simply binding to the user account in Active Directory. In line 2, we\u2019re 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<\/B> method and actually write the new phone number to Active Directory.<\/P>\n
Whatever you do, don\u2019t leave out SetInfo; if you do, the phone number will not<\/I> be changed. And don\u2019t leave out this line of code, either:<\/P>
objRecordSet.MoveNext\n<\/PRE>\n
Leave out that <\/I>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.<\/P>\n
Just like the typical wedding ceremony.<\/P>\n
Anyway, that should do it, JB. Let us know if you run into any problems here.<\/P>\n
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<\/B><\/A>. That\u2019s not that big of a deal (although he did make a nice catch); what was<\/I> a big deal is that, during the reception, the \u201cyoung man who caught the garter and the young lady who caught the bouquet\u201d 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<\/B><\/A>). (Actually, the young lady who caught the bouquet was happy to dance. The Scripting Son? Not so happy.)<\/P>\n
So is the Scripting Son a good dancer? Let\u2019s put it this way: have you ever seen Fred Astaire or Gene Kelly dance? You have? OK; in that case we won\u2019t lie to you and say that the Scripting Son is a good dancer; you obviously know what good dancing looks like. Instead, we\u2019ll say this<\/I>: you know how the guards at Buckingham Palace<\/B><\/A> 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.<\/P><\/p>\n","protected":false},"excerpt":{"rendered":"
Hey, Scripting Guy! How can I tack a prefix onto the telephone numbers of all the users in my domain?— JB Hey, JB. It\u2019s 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 […]<\/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,168,3,20,5],"class_list":["post-65063","post","type-post","status-publish","format-standard","has-post-thumbnail","hentry","category-scripting","tag-active-directory","tag-domains","tag-scripting-guy","tag-user-accounts","tag-vbscript"],"acf":[],"blog_post_summary":"
Hey, Scripting Guy! How can I tack a prefix onto the telephone numbers of all the users in my domain?— JB Hey, JB. It\u2019s 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 […]<\/p>\n","_links":{"self":[{"href":"https:\/\/devblogs.microsoft.com\/scripting\/wp-json\/wp\/v2\/posts\/65063","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=65063"}],"version-history":[{"count":0,"href":"https:\/\/devblogs.microsoft.com\/scripting\/wp-json\/wp\/v2\/posts\/65063\/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=65063"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/devblogs.microsoft.com\/scripting\/wp-json\/wp\/v2\/categories?post=65063"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/devblogs.microsoft.com\/scripting\/wp-json\/wp\/v2\/tags?post=65063"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}