Solved

Vbscript to update telephone numbers in AD

Posted on 2008-06-23
22
3,079 Views
Last Modified: 2010-05-18
Hi,
Can you please help me with a vbscript to update the Business phone and Business phone 1 attribute in AD with changes.

We already have phone numbers in Businnes phone and Business phone 1 attributes where some users will have only one information and some users will have both.

I would like to get all the information to an excel sheet correct them as per our needs and then update the attributes and if the information on the excel sheet is blank it can be left blank in the AD attribute

Can some one help me in the right direction?

regards
Chandru
0
Comment
Question by:chandru_sol
  • 11
  • 9
  • 2
22 Comments
 
LVL 65

Expert Comment

by:RobSampson
Comment Utility
Hello Chandru, welcome back!

OK, so this question is in two parts.
1) Extract the required fields from AD to an Excel spreadsheet
2) Update the fields from the Excel spreadsheet back into AD

So, to start with part 1, would you be able to post a screenshot of the fields where the Business phone number are entered?  Or you can just type it, like User Properties --> General tab --> Click other, etc..

This will enable me to test the export better...

Regards,

Rob.
0
 
LVL 12

Author Comment

by:chandru_sol
Comment Utility
Thanks Rob!!

This is General Tab Telephone number and when we press other button the other numbers are displayed

regards
Chandru
0
 
LVL 65

Expert Comment

by:RobSampson
Comment Utility
OK, try this for the export part.  It will create a CSV file.  Open it in Excel, and "Save As" a normal XLS file if you like.

Regards,

Rob.
strOutputFile = "UserPhoneNumbers.csv"
 

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 
 

Set objRootDSE = GetObject("LDAP://RootDSE")

objCommand.CommandText = _

    "SELECT samAccountName,adsPath FROM 'LDAP://" & objRootDSE.Get("defaultNamingContext")& "' WHERE objectClass='person' AND objectCategory='user'"

Set objRecordSet = objCommand.Execute
 

strDetails = """Login Name"",""Display Name"",""Phone Numbers"""

objRecordSet.MoveFirst

Do Until objRecordSet.EOF

	Set objUser = GetObject(objRecordset.Fields("adsPath").Value)

	If TypeName(objUser.otherTelephone) = "String" Then

		strOtherNums = objUser.otherTelephone

	ElseIf TypeName(objUser.otherTelephone) = "Variant()" Then

		strOtherNums = Join(objUser.otherTelephone, ";")

	Else

		strOtherNums = ""

	End If

	strDetails = strDetails & VbCrLf & """" & objRecordSet.Fields("samAccountName").Value & """,""" & objUser.CN & """,""" & strOtherNums & """"

	objRecordSet.MoveNext

Loop
 

Set objFSO = CreateObject("Scripting.FileSystemObject")

Set objOutputFile = objFSO.CreateTextFile(strOutputFile, True)

objOutputFile.Write strDetails

objOutputFile.Close

Set objOutputFile = Nothing

Set objFSO = Nothing
 

MsgBox "Finished exporting. Please see " & strOutputFile

Open in new window

0
 
LVL 12

Author Comment

by:chandru_sol
Comment Utility
Hi Rob,

It is not getting all the telephone numbers

regards
Chandru
0
 
LVL 65

Expert Comment

by:RobSampson
Comment Utility
This is only getting the telephone Numbers from the General Tab --> Telephone number and the numbers from the Other button next to that.

Are there other fields where you have numbers?

Rob.
0
 
LVL 12

Author Comment

by:chandru_sol
Comment Utility
i think the script has only the other telephone attribute and the telephone attribute is not included

Hope this is right
0
 
LVL 65

Expert Comment

by:RobSampson
Comment Utility
Yes, you are right, I didn't include the primary telephoneNumber attribute.  Try this.

Rob.
strOutputFile = "UserPhoneNumbers.csv"

 

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 

 

Set objRootDSE = GetObject("LDAP://RootDSE")

objCommand.CommandText = _

    "SELECT samAccountName,adsPath FROM 'LDAP://" & objRootDSE.Get("defaultNamingContext")& "' WHERE objectClass='person' AND objectCategory='user'"

Set objRecordSet = objCommand.Execute

 

strDetails = """Login Name"",""Display Name"",""TelephoneNumber"",""otherTelephoneNumber"""

objRecordSet.MoveFirst

Do Until objRecordSet.EOF

	Set objUser = GetObject(objRecordset.Fields("adsPath").Value)

	If TypeName(objUser.otherTelephone) = "String" Then

		strOtherNums = objUser.otherTelephone

	ElseIf TypeName(objUser.otherTelephone) = "Variant()" Then

		strOtherNums = Join(objUser.otherTelephone, ";")

	Else

		strOtherNums = ""

	End If

	strDetails = strDetails & VbCrLf & """" & objRecordSet.Fields("samAccountName").Value & """,""" & objUser.CN & """,""" & objUser.TelephoneNumber & """,""" & strOtherNums & """"

	objRecordSet.MoveNext

Loop

 

Set objFSO = CreateObject("Scripting.FileSystemObject")

Set objOutputFile = objFSO.CreateTextFile(strOutputFile, True)

objOutputFile.Write strDetails

objOutputFile.Close

Set objOutputFile = Nothing

Set objFSO = Nothing

 

MsgBox "Finished exporting. Please see " & strOutputFile

Open in new window

0
 
LVL 12

Author Comment

by:chandru_sol
Comment Utility
Thanks Rob!!

Can you please help me with the second part of the script?
0
 
LVL 12

Author Comment

by:chandru_sol
Comment Utility
Hi Rob,

Any luck with the update attribute script

regards
Chandru
0
 
LVL 65

Expert Comment

by:RobSampson
Comment Utility
Yep, working on it....very close now....the multi-valued phone attributes (which can be empty, a string, or an array) are a bit of trouble, but nearly there....

Rob.
0
 
LVL 65

Expert Comment

by:RobSampson
Comment Utility
Here, try this....it will also write a new CSV file that will log the changes made....

Regards,

Rob.
strInputFile = Replace(WScript.ScriptFullName, WScript.ScriptName, "") & "UserPhoneNumbers.csv"

strOutputFile = "UserPhoneNumbers_Change_Log.csv"
 

Set objExcel = CreateObject("Excel.Application")

Const xlUp = -4162

objExcel.Visible = True

Set objWB = objExcel.Workbooks.Open(strInputFile, False, False)

Set objSheet = objWB.Sheets(1)
 

Const ADS_SCOPE_SUBTREE = 2

Const ADS_PROPERTY_CLEAR = 1

Const ADS_PROPERTY_UPDATE = 2

Const ADS_PROPERTY_APPEND = 3

Const ADS_PROPERTY_DELETE = 4
 

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

 

Set objRootDSE = GetObject("LDAP://RootDSE")
 

strDetails = """Login Name"",""Display Name"",""Old TelephoneNumber"",""New TelephoneNumber"",""Old otherTelephoneNumber"",""New otherTelephoneNumber"""
 

For intRow = 2 To objSheet.Cells(65536, "A").End(xlUp).Row

	strLoginName = CStr(objSheet.Cells(intRow, "A").Value)

	strCN = CStr(objSheet.Cells(intRow, "B").Value)

	strTelephoneNumber = CStr(objSheet.Cells(intRow, "C").Value)

	strOtherPhones = CStr(objSheet.Cells(intRow, "D").Value)

	

	objCommand.CommandText = _

	    "SELECT adsPath FROM 'LDAP://" & objRootDSE.Get("defaultNamingContext")& "' WHERE objectClass='person' AND objectCategory='user' AND samAccountName = '" & strLoginName & "'"

	Set objRecordSet = objCommand.Execute

	 

	objRecordSet.MoveFirst

	Do Until objRecordSet.EOF

		Set objUser = GetObject(objRecordset.Fields("adsPath").Value)

		boolUpdatePhone = False

		If CStr(objUser.telephoneNumber) <> strTelephoneNumber Then

			If strTelephoneNumber = "" Then

				objUser.PutEx ADS_PROPERTY_CLEAR, "telephoneNumber", 0

			Else

				objUser.telephoneNumber = strTelephoneNumber

			End If

			boolUpdatePhone = True

		End If

		boolUpdateOtherPhones = False

		If TypeName(objUser.otherTelephone) = "Empty" Then

			If strOtherPhones <> "" Then

				boolUpdateOtherPhones = True

				strOldOtherPhones = ""

			End If

		ElseIf TypeName(objUser.otherTelephone) = "String" Then

			If strOtherPhones <> CStr(objUser.otherTelephone) Then

				boolUpdateOtherPhones = True

				strOldOtherPhones = CStr(objUser.otherTelephone)

			End If

		ElseIf TypeName(objUser.otherTelephone) = "Variant()" Then

			strOldOtherPhones = ""

			For Each strNum In objUser.otherTelephone

				If strOldOtherPhones = "" Then

					strOldOtherPhones = strNum

				Else

					strOldOtherPhones = strNum & ";" & strOldOtherPhones

				End If

			Next

			If strOtherPhones <> CStr(strOldOtherPhones) Then

				boolUpdateOtherPhones = True

			End If

		End If

		If boolUpdatePhone = True Or boolUpdateOtherPhones = True Then

			strDetails = strDetails & VbCrLf & """" & CStr(objUser.samAccountName) & """,""" & CStr(objUser.CN) & """"

			If boolUpdatePhone = True Then

				strDetails = strDetails & ",""" & CStr(objUser.telephoneNumber) & """,""" & strTelephoneNumber & """"

				objUser.SetInfo

				'MsgBox "telephoneNumber was updated for " & strLoginName

			Else

				strDetails = strDetails & ","""","""""

				'MsgBox "telephoneNumber was not updated for " & strLoginName

			End If

			If boolUpdateOtherPhones = True Then

				strDetails = strDetails & ",""" & strOldOtherPhones & """,""" & strOtherPhones & """"

				'MsgBox "otherTelephone was updated for " & strLoginName

				objUser.PutEx ADS_PROPERTY_CLEAR, "otherTelephone", 0

				objUser.SetInfo

				If strOtherPhones <> "" Then objUser.PutEx ADS_PROPERTY_APPEND, "otherTelephone", Split(strOtherPhones, ";")

				objUser.SetInfo

			Else

				strDetails = strDetails & ","""","""""

				'MsgBox "otherTelephone was not updated for " & strLoginName

			End If

		Else

			'MsgBox "No details were updated for " & strLoginName

		End If

		objRecordSet.MoveNext

	Loop

Next
 

Set objFSO = CreateObject("Scripting.FileSystemObject")

Set objOutputFile = objFSO.CreateTextFile(strOutputFile, True)

objOutputFile.Write strDetails

objOutputFile.Close

Set objOutputFile = Nothing

Set objFSO = Nothing
 

objWB.Close False

objExcel.Quit

Set objExcel = Nothing
 

MsgBox "Finished exporting. Please see " & strOutputFile

Open in new window

0
How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

 
LVL 12

Author Comment

by:chandru_sol
Comment Utility
So the format of the excel file will be the same as the file we exported right.

If i need to add other phone numbers at the end i can add in the next column?

Is that right

regards
Chandru
0
 
LVL 65

Expert Comment

by:RobSampson
Comment Utility
Hi,

>> So the format of the excel file will be the same as the file we exported right.

Yes, just modify the file that was exported by the Part 1 script.

>> If i need to add other phone numbers at the end i can add in the next column?

Not exactly.  You have two columns for phone numbers:
TelephoneNumber      otherTelephoneNumber

The Telephone number column is for one phone number, which will be the primary telephone number.
The otherTelephoneNumber column will hold all other numbers, which get added to the "Other" button in the user properties.

To have mutple "other" numbers, then in that column D, separate the numbers by a semi-colon, eg
1111;2222

will add two "other" numbers.  So, for extra phone numbers, just add a semicolon and another number
1111;2222;3333

Regards,

Rob.
0
 
LVL 12

Author Comment

by:chandru_sol
Comment Utility
The script works fine!!

The log file is created and the old telephone number and new telephone number are the same

regards
Chandru
0
 
LVL 65

Accepted Solution

by:
RobSampson earned 500 total points
Comment Utility
Hey, you're right.  I didn't notice that when I tested it.

This should work.

Rob.
strInputFile = Replace(WScript.ScriptFullName, WScript.ScriptName, "") & "UserPhoneNumbers.csv"

strOutputFile = "UserPhoneNumbers_Change_Log.csv"
 

Set objExcel = CreateObject("Excel.Application")

Const xlUp = -4162

objExcel.Visible = True

Set objWB = objExcel.Workbooks.Open(strInputFile, False, False)

Set objSheet = objWB.Sheets(1)
 

Const ADS_SCOPE_SUBTREE = 2

Const ADS_PROPERTY_CLEAR = 1

Const ADS_PROPERTY_UPDATE = 2

Const ADS_PROPERTY_APPEND = 3

Const ADS_PROPERTY_DELETE = 4
 

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

 

Set objRootDSE = GetObject("LDAP://RootDSE")
 

strDetails = """Login Name"",""Display Name"",""Old TelephoneNumber"",""New TelephoneNumber"",""Old otherTelephoneNumber"",""New otherTelephoneNumber"""
 

For intRow = 2 To objSheet.Cells(65536, "A").End(xlUp).Row

	strLoginName = CStr(objSheet.Cells(intRow, "A").Value)

	strCN = CStr(objSheet.Cells(intRow, "B").Value)

	strTelephoneNumber = CStr(objSheet.Cells(intRow, "C").Value)

	strOtherPhones = CStr(objSheet.Cells(intRow, "D").Value)

	

	objCommand.CommandText = _

	    "SELECT adsPath FROM 'LDAP://" & objRootDSE.Get("defaultNamingContext")& "' WHERE objectClass='person' AND objectCategory='user' AND samAccountName = '" & strLoginName & "'"

	Set objRecordSet = objCommand.Execute

	 

	objRecordSet.MoveFirst

	Do Until objRecordSet.EOF

		Set objUser = GetObject(objRecordset.Fields("adsPath").Value)

		boolUpdatePhone = False

		If CStr(objUser.telephoneNumber) <> strTelephoneNumber Then

			strOldPhone = CStr(objUser.telephoneNumber)

			boolUpdatePhone = True

			If strTelephoneNumber = "" Then

				objUser.PutEx ADS_PROPERTY_CLEAR, "telephoneNumber", 0

			Else

				objUser.telephoneNumber = strTelephoneNumber

			End If

		End If

		boolUpdateOtherPhones = False

		If TypeName(objUser.otherTelephone) = "Empty" Then

			If strOtherPhones <> "" Then

				boolUpdateOtherPhones = True

				strOldOtherPhones = ""

			End If

		ElseIf TypeName(objUser.otherTelephone) = "String" Then

			If strOtherPhones <> CStr(objUser.otherTelephone) Then

				boolUpdateOtherPhones = True

				strOldOtherPhones = CStr(objUser.otherTelephone)

			End If

		ElseIf TypeName(objUser.otherTelephone) = "Variant()" Then

			strOldOtherPhones = ""

			For Each strNum In objUser.otherTelephone

				If strOldOtherPhones = "" Then

					strOldOtherPhones = strNum

				Else

					strOldOtherPhones = strNum & ";" & strOldOtherPhones

				End If

			Next

			If strOtherPhones <> CStr(strOldOtherPhones) Then

				boolUpdateOtherPhones = True

			End If

		End If

		If boolUpdatePhone = True Or boolUpdateOtherPhones = True Then

			strDetails = strDetails & VbCrLf & """" & CStr(objUser.samAccountName) & """,""" & CStr(objUser.CN) & """"

			If boolUpdatePhone = True Then

				strDetails = strDetails & ",""" & strOldPhone & """,""" & strTelephoneNumber & """"

				objUser.SetInfo

				'MsgBox "telephoneNumber was updated for " & strLoginName

			Else

				strDetails = strDetails & ","""","""""

				'MsgBox "telephoneNumber was not updated for " & strLoginName

			End If

			If boolUpdateOtherPhones = True Then

				strDetails = strDetails & ",""" & strOldOtherPhones & """,""" & strOtherPhones & """"

				'MsgBox "otherTelephone was updated for " & strLoginName

				objUser.PutEx ADS_PROPERTY_CLEAR, "otherTelephone", 0

				objUser.SetInfo

				If strOtherPhones <> "" Then objUser.PutEx ADS_PROPERTY_APPEND, "otherTelephone", Split(strOtherPhones, ";")

				objUser.SetInfo

			Else

				strDetails = strDetails & ","""","""""

				'MsgBox "otherTelephone was not updated for " & strLoginName

			End If

		Else

			'MsgBox "No details were updated for " & strLoginName

		End If

		objRecordSet.MoveNext

	Loop

Next
 

Set objFSO = CreateObject("Scripting.FileSystemObject")

Set objOutputFile = objFSO.CreateTextFile(strOutputFile, True)

objOutputFile.Write strDetails

objOutputFile.Close

Set objOutputFile = Nothing

Set objFSO = Nothing
 

objWB.Close False

objExcel.Quit

Set objExcel = Nothing
 

MsgBox "Finished exporting. Please see " & strOutputFile

Open in new window

0
 
LVL 12

Author Comment

by:chandru_sol
Comment Utility
Thanks Rob!!

I will test this script and let you know shortly

Rob in the meantime can you help me with an easy question for you

http://www.experts-exchange.com/Programming/Languages/Visual_Basic/VB_Script/Q_23516965.html

regards
Chandru
0
 
LVL 65

Expert Comment

by:RobSampson
Comment Utility
Hi Chandru, any luck with this one?

Rob.
0
 
LVL 12

Author Comment

by:chandru_sol
Comment Utility
Hi Rob,

Tested and it is working fine.

Thanks for your help

regards
Chandru
0
 
LVL 12

Author Closing Comment

by:chandru_sol
Comment Utility
Thanks Rob!

YOU ALWAYS ROCK!!
0
 
LVL 12

Author Comment

by:chandru_sol
Comment Utility
Hi Rob,

Can this script be changed to update the other telephone number in the Pager attribute of the users?

regards
Chandru
0
 
LVL 1

Expert Comment

by:susnewyork
Comment Utility
RobSampson,
Hi, I was trying to modify your script to extract\update "fax" and "other fax numbers" out of AD.
I know the filed names are: facsimileTelephoneNumber and otherfacsimileTelephoneNumber however a simple find replace does not work.
Could you please give me some pointers to modify your script for this purpose?
Thank you!
 
0
 
LVL 1

Expert Comment

by:susnewyork
Comment Utility
This is how I modified the export script, however it doesnt work.
strstrOutputFile = "UserFAXNumbers.csv"

 

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 

 

Set objRootDSE = GetObject("LDAP://RootDSE")

objCommand.CommandText = _

    "SELECT samAccountName,adsPath FROM 'LDAP://" & objRootDSE.Get("defaultNamingContext")& "' WHERE objectClass='person' AND objectCategory='user'"

Set objRecordSet = objCommand.Execute

 

strDetails = """Login Name"",""Display Name"",""facsimilefacsimileTelephoneNumber"",""otherfacsimilefacsimileTelephoneNumber"""

objRecordSet.MoveFirst

Do Until objRecordSet.EOF

	Set objUser = GetObject(objRecordset.Fields("adsPath").Value)

	If TypeName(objUser.otherfacsimilefacsimileTelephoneNumber) = "String" Then

		strOtherNums = objUser.otherfacsimilefacsimileTelephoneNumber

	ElseIf TypeName(objUser.otherfacsimilefacsimileTelephoneNumber) = "Variant()" Then

		strOtherNums = Join(objUser.otherfacsimilefacsimileTelephoneNumber, ";")

	Else

		strOtherNums = ""

	End If

	strDetails = strDetails & VbCrLf & """" & objRecordSet.Fields("samAccountName").Value & """,""" & objUser.CN & """,""" & objUser.facsimilefacsimileTelephoneNumber & """,""" & strOtherNums & """"

	objRecordSet.MoveNext

Loop

 

Set objFSO = CreateObject("Scripting.FileSystemObject")

Set objOutputFile = objFSO.CreateTextFile(strOutputFile, True)

objOutputFile.Write strDetails

objOutputFile.Close

Set objOutputFile = Nothing

Set objFSO = Nothing

 

MsgBox "Finished exporting. Please see " & strOutputFile

Open in new window

0

Featured Post

Threat Intelligence Starter Resources

Integrating threat intelligence can be challenging, and not all companies are ready. These resources can help you build awareness and prepare for defense.

Join & Write a Comment

In this article we want to have a look at the directory attributes which are used by Microsoft to store the so called Security Identifiers (SID). These SIDs plays an important role in delegating and granting permissions and in authentication of trus…
Well hello again!  Glad to see you've made it this far without giving up.  In this, the fourth installment of my popular series, I'm going to cover functions and subroutines, what they are, and why they are useful.  Just in case you stumbled onto th…
In this seventh video of the Xpdf series, we discuss and demonstrate the PDFfonts utility, which lists all the fonts used in a PDF file. It does this via a command line interface, making it suitable for use in programs, scripts, batch files — any pl…
Access reports are powerful and flexible. Learn how to create a query and then a grouped report using the wizard. Modify the report design after the wizard is done to make it look better. There will be another video to explain how to put the final p…

744 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

12 Experts available now in Live!

Get 1:1 Help Now