• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 3166
  • Last Modified:

Vbscript to update telephone numbers in AD

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
chandru_sol
Asked:
chandru_sol
  • 11
  • 9
  • 2
1 Solution
 
RobSampsonCommented:
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
 
chandru_solAuthor Commented:
Thanks Rob!!

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

regards
Chandru
0
 
RobSampsonCommented:
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
Free Tool: SSL Checker

Scans your site and returns information about your SSL implementation and certificate. Helpful for debugging and validating your SSL configuration.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

 
chandru_solAuthor Commented:
Hi Rob,

It is not getting all the telephone numbers

regards
Chandru
0
 
RobSampsonCommented:
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
 
chandru_solAuthor Commented:
i think the script has only the other telephone attribute and the telephone attribute is not included

Hope this is right
0
 
RobSampsonCommented:
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
 
chandru_solAuthor Commented:
Thanks Rob!!

Can you please help me with the second part of the script?
0
 
chandru_solAuthor Commented:
Hi Rob,

Any luck with the update attribute script

regards
Chandru
0
 
RobSampsonCommented:
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
 
RobSampsonCommented:
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
 
chandru_solAuthor Commented:
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
 
RobSampsonCommented:
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
 
chandru_solAuthor Commented:
The script works fine!!

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

regards
Chandru
0
 
RobSampsonCommented:
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
 
chandru_solAuthor Commented:
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
 
RobSampsonCommented:
Hi Chandru, any luck with this one?

Rob.
0
 
chandru_solAuthor Commented:
Hi Rob,

Tested and it is working fine.

Thanks for your help

regards
Chandru
0
 
chandru_solAuthor Commented:
Thanks Rob!

YOU ALWAYS ROCK!!
0
 
chandru_solAuthor Commented:
Hi Rob,

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

regards
Chandru
0
 
susnewyorkCommented:
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
 
susnewyorkCommented:
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
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Get expert help—faster!

Need expert help—fast? Use the Help Bell for personalized assistance getting answers to your important questions.

  • 11
  • 9
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now