chandru_sol
asked on
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
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
ASKER
Thanks Rob!!
This is General Tab Telephone number and when we press other button the other numbers are displayed
regards
Chandru
This is General Tab Telephone number and when we press other button the other numbers are displayed
regards
Chandru
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.
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
ASKER
Hi Rob,
It is not getting all the telephone numbers
regards
Chandru
It is not getting all the telephone numbers
regards
Chandru
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.
Are there other fields where you have numbers?
Rob.
ASKER
i think the script has only the other telephone attribute and the telephone attribute is not included
Hope this is right
Hope this is right
Yes, you are right, I didn't include the primary telephoneNumber attribute. Try this.
Rob.
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
ASKER
Thanks Rob!!
Can you please help me with the second part of the script?
Can you please help me with the second part of the script?
ASKER
Hi Rob,
Any luck with the update attribute script
regards
Chandru
Any luck with the update attribute script
regards
Chandru
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.
Rob.
Here, try this....it will also write a new CSV file that will log the changes made....
Regards,
Rob.
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
ASKER
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
If i need to add other phone numbers at the end i can add in the next column?
Is that right
regards
Chandru
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.
>> 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.
ASKER
The script works fine!!
The log file is created and the old telephone number and new telephone number are the same
regards
Chandru
The log file is created and the old telephone number and new telephone number are the same
regards
Chandru
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
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
https://www.experts-exchange.com/questions/23516965/vbscript-for-pulling-the-AD-attribute-in-Dial-in-tab-for-users-in-AD.html
regards
Chandru
I will test this script and let you know shortly
Rob in the meantime can you help me with an easy question for you
https://www.experts-exchange.com/questions/23516965/vbscript-for-pulling-the-AD-attribute-in-Dial-in-tab-for-users-in-AD.html
regards
Chandru
Hi Chandru, any luck with this one?
Rob.
Rob.
ASKER
Hi Rob,
Tested and it is working fine.
Thanks for your help
regards
Chandru
Tested and it is working fine.
Thanks for your help
regards
Chandru
ASKER
Thanks Rob!
YOU ALWAYS ROCK!!
YOU ALWAYS ROCK!!
ASKER
Hi Rob,
Can this script be changed to update the other telephone number in the Pager attribute of the users?
regards
Chandru
Can this script be changed to update the other telephone number in the Pager attribute of the users?
regards
Chandru
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 otherfacsimileTelephoneNum ber however a simple find replace does not work.
Could you please give me some pointers to modify your script for this purpose?
Thank you!
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 otherfacsimileTelephoneNum
Could you please give me some pointers to modify your script for this purpose?
Thank you!
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
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.