Tuesday, August 07, 2007

Create user and resource accounts in your Exchange 2007 lab with Exchange Management Shell

Sumatra runs client data through a series of rigorous tests as part of our QA process. We insert client calendar into Exchange in our “sandbox” test lab during one phase of testing. This requires we build user and resource accounts in bulk. Our prior home-grown tools do not work in Exchange 2007 because Microsoft killed CDOEX in Exchange 2007. This gave us a chance to learn Exchange Management Shell (EMS) and rework our tools for public use.

The EMS vocabulary is obtuse, but not hard.

To create the accounts, you only need two steps:

  1. Export the account list (do resources and user accounts separately)

  2. Build the mailboxes.

First, create two CSV (export) files - one for resources, one for users.

The script we wrote requires five columns of data – the OU, the descriptive name, the alias, the UPN, and the account type. You can build this file in Excel, or Sumatra customers can use data from the Users table. (If you’ve got a database v8.7.5.2+ from Sumatra, the query you can start with is called “N_X”.)

Here’s how each of the columns are defined:

  1. OU: “Users”
    Note : If you have different OUs for your users and resources, remember to change them here.
  2. Name: Trim(Trim([firstname]) & " " & Trim([lastname]))
  3. Alias: Login
  4. UPN: [Login] & "@lab.sumatra.local"
    Note : Remember to change the lab.sumatra.local to your test lab domain
  5. Type
    Note : The criteria will be Individual OR Resource
  6. Foreign (uncheck the show box, and apply a criteria of false)

    Here’s a screen shot of the query:

If you are running MS Access, save the query, then right-hand click on the query name, and Export. (If you are running the N_X query, it produces a table because it “prompts” you for information. Export that table.) You want to export:

  1. As type “Text”,
  2. Enter the file name with an extension of .CSV. Select the export button.
  3. Output the fields as “Delimited”,
  4. The delimiter is a comma,
  5. Check “Include field Names on the First Row”, and
  6. Select the Text Qualifier as {none} - not quote (“)
  7. Select “Finish”

Modify the query, and repeat the export process for “resources”.

Here’s a sample export of resource account information

Now, run Exchange Management Shell, and “cut and paste” these three commands:

Create a variable “Temp” that contains a password (if you want one)

$Temp = ConvertTo-SecureString "N0Pwd4U" -asPlainText –Force

Import the users. Remember that you will have to make at least three changes:

  1. The “path” to the CSV file

  2. The Organizational Unit

  3. The Database Name

  4. (optional) The Password

    Import-CSV "C:\Sumatra\UserExport\tmp_UserList.csv" ForEach-Object -Process {New-Mailbox -Name $_.Name -Alias $_.Alias -UserPrincipalName $_.UPN -OrganizationalUnit "lab.sumatra.local/client/Test" -DisplayName $_.Name -Database "Striper\First Storage Group\FSG_MBX_DB1" -Password $Temp -ResetPasswordOnNextLogon $false}

Import the resources. As above, remember to change the path, the OU, the DB Name.

Import-CSV "C:\Sumatra\UserExport\tmpResList.csv" ForEach-Object -Process {New-Mailbox -Name $_.Name -Alias $_.Alias -UserPrincipalName $_.UPN -OrganizationalUnit "lab.sumatra.local/resources" -DisplayName $_.Name -Database "Striper\First Storage Group\FSG_MBX_DB2" -Password $Temp -ResetPasswordOnNextLogon $false -Room:$True}

Here’s a screen shot of the results:

Finally, please note that in Exchange 2007 resources are disabled by default. Remember to enable the accounts. Also, for the test lab (and for the insertion), you should NOT set automatic processing of meeting requests (i.e. -automateprocessing:AutoAccept).

THANKS to sharp-eyed reader Vince for pointing out an error in one of our lines (corrected Oct. 4, 2007)!

No comments: