How Can I Copy My Users’ Phone Number Attribute to Their Description Attribute?


Hey, Scripting Guy! Question

Hey, Scripting Guy! How can I copy a user’s phone number to his or her description for all the users in Active Directory?

— DL

SpacerHey, Scripting Guy! AnswerScript Center

Hey, DL. You know, the Scripting Guy who writes this column considers today’s column to be his all-time favorite. Why? Because while you’re at work reading this he hasn’t even gotten out of bed yet. (Or, if he has, he’s just lying around the house eating doughnuts and watching TV.) Talk about the sweet life, huh?

Ah, yes, that’s a good question: how can he write and publish a column if he’s sleeping in past 10:00 AM? What is he, a master of the dark arts or something?

No, of course not. He’s simply – um, wait a second, we mean yes, yes he is a master of the dark arts, an evil sorcerer who will place a terrible spell on you unless you send him $19.95. No checks; credit cards or money orders only. Operators are standing by.

What’s that? You say you’re a little scared, but you’d like to see a demonstration of his powers before sending in the money? Well, we can do that, but, just remember, you asked for it. Hold on a second while we summon the Demons of the Netherworld (thanks goodness for instant messaging, huh?).

OK, here we go. By the power of Grayskull, we command thee to copy the telephone number attribute to the description, and for each and every user in a domain:

On Error Resume Next
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://dc=fabrikam,dc=com' WHERE objectCategory='user'"
Set objRecordSet = objCommand.Execute
Do Until objRecordSet.EOF
    strUserPath = objRecordSet.Fields("AdsPath").Value
    Set objUser = GetObject(strUserPath)
    strTelephone = objUser.telephoneNumber
    objUser.Description = strTelephone

Sorry about that. The smoke will clear up in a second but the smell of brimstone might linger for a little while.

OK. As it turns out, we don’t really have time to discuss this script in minute detail, at least not the part that explains how to search Active Directory; after all, Oprah will be on any minute now. But don’t despair; if you need to know the fundamentals of conducting an Active Directory search take a look at our two-part series Dude, Where’s My Printer? available now for the low introductory price of just $19.95. Operators are standing by.

Come to think of it, that two-part series might actually be available for no charge. But you might want to send in the money anyway, just to be on the safe side.

About all we will do today is point out that this is the query used to select the value of the ADsPath attribute for all the users (objectCategory=’user’) in the domain:

objCommand.CommandText = "SELECT AdsPath FROM 'LDAP://dc=fabrikam,dc=com' WHERE objectCategory='user'"

Why the ADsPath attribute? Well, ADsPath returns a connection string that makes it easy to bind to a user account in Active Directory. For example:

LDAP://cn=Ken Myer,ou=Finance,dc=fabrikam,dc=com

That’s important, because when we search Active Directory our queries are read-only; we can’t write some sort of Update query that automatically copies a user’s telephone number to his or her description attribute. Instead, we have to bind to each individual user account and copy the telephone number to the description. (We know, that sounds slow and tedious. But the script does all the work and, unless you have several million user accounts, it all happens remarkably fast as well.)

When we call the Execute method and run our query we get back a recordset containing the ADsPath for each user in the domain. We then set up a Do Until loop that runs until the value of the recordset’s EOF (End-Of-File) property is true; in other words, until we’ve looped through all the records in the recordset:

Do Until objRecordSet.EOF

The first thing we do inside the loop is grab the value of the ADsPath attribute and store it in a variable named strUserPath; we then use that variable to connect to the actual account object for that user. That’s what these two lines of code are for:

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

After we make the connection we take the value of the telephoneNumber attribute and store it in a variable named strTelephone. We then use these two lines of code to set the user’s Description attribute to his or her telephone number:

objUser.Description = strTelephone

Whatever you do, don’t leave out the call to the SetInfo method; that’s the method that actually writes the changes back to Active Directory. If you don’t call SetInfo the change will be made in memory, but not in Active Directory.

After that we call the MoveNext method to move to the next record in the recordset and then repeat the process for the next user.

And yes, we could have combined a couple of these lines of code in order to make the script a tiny bit smaller. However, we thought this method would be a little easier for people to follow.

Seeing as how Microsoft’s lawyers are breathing down our necks we should probably come clean here and state, for the record, that the Scripting Guy who writes this column is not an evil sorcerer and has no magic powers. (He used to be able to juggle a little, but that probably doesn’t count.) Instead, the Scripting Guy who writes this column is actually on vacation until the first of the year; however, he wrote a few extra columns before he left, the better to get everyone through this week and tide you over until he gets back. What a wonderful and selfless act, huh?

Note. OK, he didn’t really do it to be nice; he only wrote these columns because the Scripting Editor said he should. And we always do whatever the Scripting Editor says, because, like all editors everywhere, she really is a practitioner of the dark arts. What, you thought Peter Costantini was born a newt?

But take heart, Peter: she can’t hold a grudge forever.


Discussion is closed.

Feedback usabilla icon