Guy's Scripting Ezine number 20 - VBscripts for CSVDE and Exchange

IPAM will assist you in managing IP addresses.

To let you into a secret, this utilities is fun to use, even if you don't have a pressing need to calculate your IP address space.

Get a free evaluation copy of Orion IPAM

Is your server running slowly? Check with SolarWinds ipMonitor

Get a free evaluation copy of ipMonitor

This week’s secret

It will be 28 years before another Feb 29th falls on a Sunday. I wonder what Windows 2032 will be like on Sunday February 29th 2032?

Down to business. My speciality is getting people started. This week my mission is to help you create users with mailboxes. The technique relies on CSVDE importing user accounts from a spreadsheet into Active Directory.

Our Project - To bulk import users with Mailboxes

Assemble the toolkit.

CSVDE is a built-in command found on Windows 2000 and later systems. Some say CSV stands for comma separated VARIABLE, whilst others will tell you that the V is for value, what matters is that CSV is a format that a whole host of programs can translate easily. The letters DE in CSVDE stand for Data Exchange and that sums up our task, to extract data from a spreadsheet and import it into Active Directory.

Excel is the best tool to manipulate LDAP fields provide a holding pen for the data. The very first row of the import spreadsheet holds a full list of LDAP properties. Our job is to 'see the woods for the trees' and select the correct properties for our exchange mailboxes, whilst weeding out unnecessary properties. The key skill is cross-matching numerous LDAP fields with the user's property sheet that you see in Active Directory.

When it comes to importing data, VBScript is a possible alternative to CSVDE. If you need a quick straightforward import favour CSVDE, if you have a tricky job involving passwords then use VBScript. Incidentally, all bulk import methods rely on spreadsheets (More on VBScripts and mailboxes in a future ezine.)

Methods and Techniques

My assumption is that you have an Active Directory domain with Exchange 2000 / 2003 installed. While our goal is to import users, our first aim is to EXPORT a test user which you know has all the Exchange Mailbox tabs. What I have in mind is reverse engineering, we know the answer from the test user, the LDAP fields in the spreadsheet reveal how that user object was put together.

I have divided the project into four phases, Export from Active Directory, clean up the LDAP columns in Excel, add more users and finally, import into Active Directory.

Phase A - Exporting from Active Directory

1) Navigate to Active Directory Users and Computers, create a test OU; trust me - it will be easier to filter the Active Directory objects if you have the exchange user in a test OU.

2) From your test OU, create a test user with a mailbox. It is essential that you have installed exchange 2000 / 2003 or else you will not see the mailbox properties. This test user will act as template that we will copy to create as many other users as you need.

3) Before we open Excel we need a .csv file. So, go to the command prompt and type:

Note 2: -d is a filter, we do not want to be overloaded with objects that are of no interest to Exchange.

Note 4: Export is the default behaviour of CSVDE, so you do not need -e to extract the objects from Active Directory. However, you do need a filename and that is taken care of by the -f exchange.csv. Note the .csv extension.

Phase B - Over to Excel

Remember the goal is to prepare a .csv file to import users and provide them with exchange mailboxes.

1) Open your exchange.csv file in excel. Now we are ready to interpret the LDAP properties which you see in the first row of the spreadsheet.

2) One of my aims is to simplify this .csv file. So, Column C = Ou, this will not be needed and can be deleted, as can Row 2.

Note: DN and distinguishedName are really the same property, as are cn and Name. So I would keep DN and cn and delete the columns with distinguishedName and Name.

sn title description physicalDeliveryOfficeName telephoneNumber givenName displayName department company

4) These properties are not needed and may get in the way, so delete these columns.

badPwdCount codePage countryCode badPasswordTime lastLogoff lastLogon pwdLastSet primaryGroupID accountExpires logonCount whenCreated whenChanged uSNCreated uSNChanged name objectGUID objectCategory gPLink dSCorePropagationData objectSid sAMAccountType groupType

Phase C - Create your new users

1) Assuming you have removed the OU row, your test user is now in Row 2. Copy your user in row 2, then paste into 3 / 4 new rows. In the production scenario you could drag and drop to copy this hundreds of times, but for now 3 / 4 new users will be more manageable when practicing.

2) Each user must have a unique DN or distinguishedName. Here is an example of how I edited row 3 to create a Barabara Shepherd user.

I edited both the above fields to say BarbaraSheph. For a large number of users, you could save time by using a formulae, for example in the mailNickname column you could say =p2 or what ever column letter represents sAMAccountName.

Note 2: You may like to check then alter values in what I call the 'Optional Columns'.

Phase D - Import then Verify

1) When your spreadsheet is ready, go to the File menu and Save as, CSV (comma delimited). For example exchange2.csv

3a) An improvement would be adding the - k switch, which ignores errors like 'Object already exists'.

An important final step is to verify that the import has worked, switch to your OU in Active Directory Users and Computers, and rigorously check that you have the same number of tabs that the original user had.

Check that the email address has been created, and check the home server is what you expected.

Tip, in Windows 2000, select the View menu Advanced, now you should see an extra exchange tab.

Summary

This really is a mission impossible. However if you have a test Exchange 2000 or 2003 machine, then you will almost certainly have Active Directory.

In truth I have had no negative feedback, so either this is so hard no-one could do it, or else it works as I designed it - providing you have the time to test.

Computer Training Software - Recommended Training Videos

Guy Thomas recommends Computer Training Software.

Their topics and material are ideal for getting you started with VBScript. The videos are easy to follow and you can control the pace. Try their free demo material and then see if you want to buy the full package. See more about VB Script Training CD.

Guy's Scripting Ezine 20 - CSVDE and Exchange

Guy's Scripting Ezine No 20 - CSVDE and Exchange