Wednesday, January 27, 2016

Using a CSV list to create Active Directory contacts

I’ve recently been asked to convert a set of contacts exported from Outlook as a CSV file into Active Directory contacts. This was the first time I’ve had to do something like this and this exercise made me realize that Outlook contacts actually had much more fields available than Active Directory contacts so if anyone is about to embark on this task, note that there are going to be many fields that you would not be able to bring into Active Directory.

The way I approached this was to begin by determining what fields were available for Active Directory contacts. This can be easily determined by reviewing the tabs and fields of an Active Directory contact in Active Directory Users and Computers:

imageimage

imageimage

From here, the next step is to determine what fields in the Contact object maps to the ones that we will use to import via a CSV file. To do this, I executed the following csvde command to export existing contacts from AD to review the fields:

csvde -f exportContacts.csv -d OU=Contacts,DC=domain,DC=com

image

Opening up this CSV file will list the following available attributes:

  • DN
  • objectClass
  • ou
  • distinguishedName
  • instanceType
  • whenCreated
  • whenChanged
  • uSNCreated
  • uSNChanged
  • name
  • objectGUID
  • objectCategory
  • dSCorePropagationData
  • cn
  • sn
  • givenName
  • displayName
  • memberOf
  • proxyAddresses
  • targetAddress
  • mailNickname
  • internetEncoding
  • countryCode
  • legacyExchangeDN
  • textEncodedORAddress
  • mail
  • msExchHideFromAddressLists
  • msExchPoliciesIncluded
  • msExchRecipientDisplayType
  • msExchVersion
  • showInAddressBook
  • msExchUMDtmfMap
  • altRecipientBL
  • initials
  • company
  • mAPIRecipient
  • msExchALObjectVersion
  • telephoneNumber
  • homeMTA
  • extensionData
  • homeMDB
  • garbageCollPeriod
  • mDBUseDefaults
  • userAccountControl
  • codePage
  • pwdLastSet
  • primaryGroupID
  • objectSid
  • accountExpires
  • sAMAccountName
  • sAMAccountType
  • userPrincipalName
  • ipPhone
  • lastLogonTimestamp
  • msExchHomeServerName
  • msExchMailboxSecurityDescriptor
  • msExchUserAccountControl
  • msExchMailboxGuid
  • msExchPoliciesExcluded
  • msExchMDBRulesQuota
  • msExchUserCulture
  • msExchRecipientTypeDetails
  • altRecipient
  • badPwdCount
  • badPasswordTime
  • lastLogoff
  • lastLogon
  • logonCount
  • lockoutTime
  • deliverAndRedirect
  • msDS-
  • SupportedEncryptionTypes

Opening up a CSV file that was created from exporting Outlook contacts will display the following attributes:

  • Title
  • First Name
  • Middle Name
  • Last Name
  • Suffix
  • Company
  • Department
  • Job Title
  • Business Street
  • Business Street 2
  • Business Street 3
  • Business City
  • Business State
  • Business Postal Code
  • Business Country/Region
  • Home Street
  • Home Street 2
  • Home Street 3
  • Home City
  • Home State
  • Home Postal Code
  • Home Country/Region
  • Other Street
  • Other Street 2
  • Other Street 3
  • Other City
  • Other State
  • Other Postal Code
  • Other Country/Region
  • Assistant's Phone
  • Business Fax
  • Business Phone
  • Business Phone 2
  • Callback
  • Car Phone
  • Company Main Phone
  • Home Fax
  • Home Phone
  • Home Phone 2
  • ISDN
  • Mobile Phone
  • Other Fax
  • Other Phone
  • Pager
  • Primary Phone
  • Radio Phone
  • TTY/TDD Phone
  • Telex
  • Account
  • Anniversary
  • Assistant's Name
  • Billing Information
  • Birthday
  • Business Address
  • PO Box
  • Categories
  • Children
  • Directory Server
  • E-mail Address
  • E-mail Type
  • E-mail Display Name
  • E-mail 2 Address
  • E-mail 2 Type
  • E-mail 2 Display Name
  • E-mail 3 Address
  • E-mail 3 Type
  • E-mail 3 Display Name
  • Gender
  • Government ID Number
  • Hobby
  • Home Address PO Box
  • Initials
  • Internet
  • Free Busy
  • Keywords
  • Language
  • Location
  • Manager's Name
  • Mileage
  • Notes
  • Office Location
  • Organizational ID Number
  • Other Address PO Box
  • Priority
  • Private Profession
  • Referred By
  • Sensitivity
  • Spouse
  • User 1
  • User 2
  • User 3
  • User 4
  • Web Page

**Notice how much more attributes are available for an Outlook contact so if you are to convert these into AD contacts then you will need to determine where to put the additional information.

I’ve created the following table that maps what AD attribute maps to which CSV fields:

 

AD Attribute

Value

DN

CN=First name Initia. Last name,OU=Test,OU=Contacts,DC=domain,DC=com

objectClass

contact

ou

 

distinguishedName

CN=First name Initia. Last name,OU=Test,OU=Contacts,DC=domain,DC=com

instanceType

4

whenCreated

20160110232419.0Z

whenChanged

20160110232548.0Z

uSNCreated

53027203

uSNChanged

53027234

name

First name Initia. Last name

objectGUID

X'01709c730e93d24db092bac6744cda3c'

objectCategory

CN=Person,CN=Schema,CN=Configuration,DC=domain,DC=com

cn

First name Initia. Last name

sn

Last name

c

BM

l

City Address

st

State/provice Address

title

Job Title

description

Description

postalCode

Zip/Postal Code Address

postOfficeBox

P.O. Box Address

physicalDeliveryOfficeName

Office

telephoneNumber

Telephone Number General

facsimileTelephoneNumber

Fax

givenName

First name

initials

Initia

displayName

Display name

info

Notes

co

Bermuda

department

Department

company

Company

streetAddress

Street

wWWHomePage

Web Page

countryCode

60

ipPhone

IP phone

mail

E-mail General

homePhone

Home

mobile

Mobile

pager

Pager

With the above information, simply create a spreadsheet with the AD attribute as the column headings, fill in the appropriate values for the contacts, save it as a CSV file then use the csvde command below to import and create the AD contacts:

csvde -I -f c:\ContactsImport.csv

image

No comments: