NAB2AD
From Ninet.org Projects Wiki
Fix CSV
Export the NAB as a csv file called NAB.csv
Ok first I have removed the fields I didnt need using a linux box & the following code
cat NAB.csv | cut -f 2,8,18,21,23,24 -d"," > NAB-rformat.csv unix2dos NAB-rformat.csv
Column - Contents
2 - Email Address
8 - Mobile Number
18 - Phone Number
21 - Fax Number
23 - Job Title
24 - Department
Delete NAB.csv and rename NAB-rformat.csv to NAB.csv OR change the name of the infile in the script.
Add Details to AD
If you want to see what values will be changed without committing changes then comment out the lines with "objUser.SetInfo"
' NAB2AD vbs script
' Copyright 2008 Ryan McLean
Option Explicit
'**********************
' Variable Declarations
'**********************
'Setting Vars
Dim logging, maildomainname
'log vars
Dim logtxt, objFSO, objFile
' LDAP vars
Dim oProvider, oOU, oDom
'Query vars
Dim qQuery, oConnection, oCommand, oRecordSet, usr
Dim brokenupn, updatedTele, updatedEmail, upn, updatedFax, updatedMobile, updatedTitle, updatedDepartment
Dim arrFinal()
Dim objUser
Dim inFile, NumberOfFields
'*********
' Settings
'*********
' Turn Logging on
logging = true
' ending for userPrincipalName
maildomainname = "@example.com"
inFile = "NAB.csv"
NumberOfFields = 7
' Defines level to start search from
oProvider = "'LDAP://"
oOU = "ou=Employees,"
oDom = "dc=example, dc=com'"
'' *********
'' Constants
'' *********
' Log File
Const LOG_FILE = "NAB2AD.log"
' Constants for File opening modes
Const forREAD = 1
Const forWRITE = 2
Const forAPPEND = 8
'*****
' MAIN
'*****
ParseCSV
AccessAD
' Procedures
sub AccessAD
' Used to retrieve the users distingusided name
qQuery = "Select distinguishedName from " & oProvider _
& oOU & oDom & "where objectclass='user'"
' Used to retrieve the users distingusided name
qQuery = "Select distinguishedName from " & oProvider _
& oOU & oDom & "where objectclass='user'"
' Create connection and Execute above query
Set oConnection = CreateObject("ADODB.Connection")
Set oCommand = CreateObject("ADODB.Command")
oConnection.Open "Provider=ADsDSOObject;"
oCommand.ActiveConnection = oConnection
oCommand.CommandText = qQuery
Set oRecordSet = oCommand.Execute
While NOT oRecordSet.EOF
' Get the LDAP Record
' use distinguished name to provide a direct link to the use
usr = "LDAP://" & oRecordSet.Fields("distinguishedName")
' Create a user object
Set objUser = GetObject(usr)
' Bool values
brokenupn = false
updatedEmail = false
updatedTele = false
updatedFax = false
updatedMobile = false
updatedTitle = false
updatedDepartment = false
' Determine what the userPrincipalName should be
upn = objUser.sAMAccountName & maildomainname
' Check the csv file for the user and add details from that
updatefromcsv
' Fix the userPrincipalName if incorrect
if (objUser.userPrincipalName <> upn) then
brokenupn = true
end if
' Set the email address to be the same as the userPrincipalName
if IsEmpty(objUser.mail) then
objUser.Put "mail", objUser.userPrincipalName
'objUser.SetInfo
updatedEmail = true
end if
'Build Logtxt
logtxt = now() & ", " & objUser.cn & "," & objUser.mail & "," _
& objUser.telephoneNumber & "," & objUser.mobile & "," _
& objUser.facsimileTelephoneNumber & "," & objUser.title & "," & objUser.department & ","
' Determine what was fixed for the log
if (brokenupn) then
logtxt = logtxt & "true,"
else
logtxt = logtxt & "false,"
end if
if (updatedEmail) then
logtxt = logtxt & "true,"
else
logtxt = logtxt & "false,"
end if
if (updatedTele) then
logtxt = logtxt & "true,"
else
logtxt = logtxt & "false,"
end if
if (updatedMobile) then
logtxt = logtxt & "true,"
else
logtxt = logtxt & "false,"
end if
if (updatedFax) then
logtxt = logtxt & "true,"
else
logtxt = logtxt & "false,"
end if
if (updatedTitle) then
logtxt = logtxt & "true,"
else
logtxt = logtxt & "false,"
end if
if (updatedDepartment) then
logtxt = logtxt & "true"
else
logtxt = logtxt & "false"
end if
logit(logtxt)
oRecordSet.MoveNext
Wend
end sub
sub updatefromcsv
Dim i
for i = LBound(arrFinal) To UBound(arrFinal)
if (arrFinal(i,0) = "") and (arrFinal(i,1) = "") and (arrFinal(i,2) = "") _
and (arrFinal(i,3) = "") and (arrFinal(i,4) = "") then
'Do Nothing
else
if (objuser.sAMAccountName = arrFinal(i,0)) then
' FOR DEBUGGING
'WScript.Echo "Common Name: " & arrFinal(i,0) & vbcrlf _
'& " EMail: " & arrFinal(i,1) & vbcrlf _
'& "Mobile: " & arrFinal(i,2) & vbcrlf _
'& "Telephone: " & arrFinal(i,3) & vbcrlf _
'& "Fax: " & arrFinal(i,4) & vbcrlf _
'& "Title: " & arrFinal(i,5) & vbcrlf
'Update Phone Number
if (objUser.telephoneNumber <> arrFinal(i,3)) and (arrFinal(i,3) <> "") then
objUser.Put "telephoneNumber", arrFinal(i,3)
'objUser.SetInfo
updatedTele = true
end if
'Update Mobile
if (objUser.mobile <> arrFinal(i,2)) and (arrFinal(i,2) <> "") then
objUser.Put "mobile", arrFinal(i,2)
'objUser.SetInfo
updatedMobile = true
end if
'update Fax number
if (objUser.facsimileTelephoneNumber <> arrFinal(i,4)) and (arrFinal(i,4) <> "") then
objUser.Put "facsimileTelephoneNumber", arrFinal(i,4)
'objUser.SetInfo
updatedFax = true
end if
'update Title
if (objUser.title <> arrFinal(i,5)) and (arrFinal(i,5) <> "") then
objUser.Put "title", arrFinal(i,5)
'objUser.SetInfo
updatedTitle = true
end if
'update Department
if (objUser.department <> arrFinal(i,6)) and (arrFinal(i,6) <> "") then
objUser.Put "department", arrFinal(i,6)
'objUser.SetInfo
updatedDepartment = true
end if
i = UBound(arrFinal)
end if
end if
next
end sub
sub ParseCSV
Dim arrTxtArray()
Dim arrTemp
Dim objFSO, objTextFile
Dim intSize, strNextLine
Dim i, x, unamepos
intSize = 0
Set objFSO = CreateObject("Scripting.FileSystemObject")
Set objTextFile = objFSO.OpenTextFile (infile, ForREAD)
Do Until objTextFile.AtEndOfStream
strNextLine = objTextFile.ReadLine
ReDim Preserve arrTextArray(intSize)
arrTextArray(intSize) = strNextLine
intSize = intSize + 1
Loop
objTextFile.Close
ReDim arrFinal(intSize, NumberOfFields)
intSize = 0
for i = LBound(arrTextArray) To UBound(arrTextArray)
if (arrTextArray(i) <> ",,,,,") then
arrTemp = Split (arrTextArray(i), ",")
unamepos = InStr(arrTemp(0),"@")
unamepos = unamepos-1
arrFinal(intSize,0) = Left (arrTemp(0), unamepos)
for x = LBound(arrTemp) To UBound(arrTemp)
arrFinal(intSize,x+1) = arrTemp(x)
next
intSize = intSize + 1
end if
next
end sub
sub LogIt(logtxt)
' Log format:
'' <time>,<CN>,<email>,<telephone>,<MobileNumber>,<FaxNumber>,<JobTitle>,<Department>
''<upnBroken>,<mailUpdated>,<telephoneUpdated>,<MobileUpdated>,<FaxUpdated>,<JobTitleUpdated>,<DepartmentUpdated>
if (logging) then
Set objFSO = CreateObject("Scripting.FileSystemObject")
IF objFSO.FileExists(LOG_FILE) Then
Set objFile = objFSO.OpenTextFile(LOG_FILE, forAPPEND)
Else
Set objFile = objFSO.CreateTextFile(LOG_FILE)
objFile.Close
Set objFile = objFSO.OpenTextFile(LOG_FILE, forWRITE)
objFile.Writeline "Time,Common Name,Email Address,Telephone Number,Mobile Number,Fax Number,Job Title," _
& "Department,UPN Broken?,Email Updated?,Telephone Updated?,Mobile Updated?,Fax Updated?, Job Title Updated?,Department Updated?"
End If
objFile.Writeline logtxt
objFile.close
end if
end sub
