Solved

Create Primary SMTP from CSV and hash-table

Posted on 2013-05-30
6
610 Views
Last Modified: 2013-05-31
CSV with example how to should build the smtp
This time i like to build from a CSV the primary SMTP-Adress and add it to an existing
Exchange 2010 mailaccount.

The prefix like e.g. @emc.com should be taken from an external hash table. The SMTP should be build from CSV like firstname.lastname@hashtable.com. The value of the prefix is working so far. The value of the table depends on $entry.company in csv

First i took the firstname.lastname from Active directory value. Now this will be deliver also
from the csv. Its $entry.name.

Unfortunately the names will deliver in a lot of complicate forms. Here some examples with
the smtp should be build from it.
JONES, MIKE (EXTERN)      mike.jones.extern@emc.com
VON RAND, MONEESHA      moneesha.von.rand@lap.com
GRUB, SANDRA-KIM      sandra.kim.grub@ltag.com
PHILIPPSEN-SMITH, KAT        kat.philippsen.smith@emc.com
DURMAZA, FUNDA             funda.durmaza@trm.com
BLAIR, BILGE OENDER (Z110093)  oender.bilge.blair.z110093@lap.com


This commands below should be use to create the correct with the names from CSV:

$newSMTP = '{0}.{1}@{2}' -f $ADUser.GivenName, $ADUser.Surname.Replace(' ', '.'), $co


The script is still in this form to read the company and names from AD. Now
everything should read from the CSV.

What i exactly want:
1.Rebuild the code to read the data from CSV
2.Ensure that with all complicate names will create the correct SMTP
3.If a smtp already in AD exists write-host to the screen


Appreciate so much for your help
Mandy

$data = @()
gc c:\hash.txt | %{
	$tokens = $_ -split '[''="]' | where{$_.Trim() -ne ''}
	$item = New-Object System.Object
	$item | Add-Member -type NoteProperty -name Key -value $tokens[0]
	$item | Add-Member -type NoteProperty -name Value -value $tokens[1]
	$data +=  $item
}

#$data

external hash.txt for example

"EMC" = 'emc.com'
"LAP" = 'lap.com'
"LTAG" = 'ltag.com'
"TRM" = 'trm.com'


ForEach ($Entry in Import-Csv 'c:\import1.csv') { 

    $ADUser = Get-ADUser -identity  -Properties Company  
    $mbox   = Get-Mailbox $Entry.UserID
	 
    if ($co = $data[$ADUser.Company])
    {
      $newSMTP = '{0}.{1}@{2}' -f $ADUser.GivenName, $ADUser.Surname.Replace(' ', '.'), $co
      If ($mbox.PrimarySMTPAddress -ne $NewSMTP)
      {
        Write-Host  -ForegroundColor Green "Setting primary SMTP $newsmtp address for $mbox "
	  "$newsmtp" | %{write-host $_; out-file -filepath c:\smtp.csv -inputobject $_ -append}
       set-mailbox -identity $Entry.UserID -PrimarySmtpAddress $newsmtp -EmailAddressPolicyEnabled $false
		
      } else {
        Write-Host -ForegroundColor red -backgroundcolor yellow "$mbox has an SMTP-Adress already exists in system"
		
      }
    } else {
      Write-Host -ForegroundColor yellow -backgroundcolor blue "$mbox is not in company list!"	  
	  }
   
 
	}

Open in new window


the csv is attached
import.csv
0
Comment
Question by:Mandy_
  • 3
  • 3
6 Comments
 
LVL 39

Accepted Solution

by:
footech earned 500 total points
ID: 39209785
What you were creating in your code above for $data was an object array.  Though it's possible to use that, since you mentioned a hash table I changed the code to do that.  The format of your hash.txt file is a bit of pain to work with.  It'd be better if you just used a CSV.
$data = $null
$data = @{}
Select-String c:\hash.txt -Pattern "([a-z]+).+?=.+?([a-z.]+)" |
 ForEach { $_.matches} |
 ForEach { $tokens = @($_.groups[1].value,$_.groups[2].value); $data.Add($tokens[0],$tokens[1])}

Import-Csv c:\import.csv | ForEach `
{
    $_.name.ToLower() -match "^(?<last>[a-z -]+),( (?<middle>[a-z]+))? (?<first>[a-z-]+)( (?<code>[a-z0-9()]+))?$" | Out-Null
    $first = $matches["first"] -replace "[^a-z]","."
    $middle = $matches["middle"] -replace "[^a-z]","."
    $last = $matches["last"] -replace "[^a-z]","."
    $code = $matches["code"] -replace "[()]"
    $company = $data[$_.company]
    If ($middle)
    { $middle = "." + $middle }
    If ($code)
    { $code = "." + $code }
    
    $newSMTP = "{0}{1}.{2}{3}@{4}" -f $first,$middle,$last,$code,$company
    $ADUser = Get-ADUser -identity $_.UserID -Properties Company  
    $mbox = Get-Mailbox $_.UserID

    If ($_.company -eq $ADUser.company)
    {
        If ($mbox.PrimarySMTPAddress -ne $newSMTP)
        {
            Write-Host  -ForegroundColor Green "Setting primary SMTP $newsmtp address for $mbox "
	        $newsmtp | out-file -filepath c:\smtp.csv -append
            set-mailbox -identity $_.UserID -PrimarySmtpAddress $newsmtp -EmailAddressPolicyEnabled $false
		} else {
            Write-Host -ForegroundColor red -backgroundcolor yellow "$mbox has an SMTP-Address already exists in system"
		}
    } else {
        Write-Host -ForegroundColor yellow -backgroundcolor blue "$mbox is not in company list!"	  
	}
}

Open in new window

I tested the hash table creation and the email address construction, but nothing further.  I'll have to leave that up to you.
0
 
LVL 2

Author Comment

by:Mandy_
ID: 39209886
hi !

thank you for your hard work!  The first tests show me that sth should be wrong with
the form of my hashtable. The script reads only the first line and create only
one smtp.

You should know that always the 3/4 Letter code of
the company in the CSV isn't equal to the prefix of the smtp. If so i dont need the
hashtable or array. It could be like that:

company  prefix
"EMC" = 'emc.com'
"LTA" = 'contoso.com'
"LSAG" = 'contoso2.com'
"TST" = 'contoso4.com'
"FTT" = 'contoso5.com'

That means the company 3 letter code in the CSV has to be verify in the hashtable
and then append the right @prefix to the firstname.lastname result.

The company field in AD has the fullname of the company not the 3/4 letter code
so it makes no sense to look in the AD and compare it with the CSV company entry.
It's not important to verify that.

More important is to verify the created smtp with AD because there always people with the same names. But that part working already.

I hope you could give me an advice how we should create the hash and the script.

thank you so much
mandy
0
 
LVL 39

Expert Comment

by:footech
ID: 39210286
I don't understand the problem.  From everything I can see it is working as you desire.  Given a file likehash.txt the code
$data = $null
$data = @{}
Select-String hash.txt -Pattern "([a-z]+).+?=.+?([a-z.]+)" |
 ForEach { $_.matches} |
 ForEach { $tokens = $_.groups[1,2].value; $data.Add($tokens[0],$tokens[1])}

Open in new window

will create a hash table like in the screenshot below.hash table resultsAs you can see every key-value pair from the file is accounted for.

What you refer to as the "company prefix" is a little misleading.  "Pre" means "before" or "at the beginning".  It would be more accurate to call it the company post-fix., or even better the "email domain", which is what I'll use from now on.
The email domain is determined by the hash table according to the letter code that is read from the CSV, and then appended to create the email address.  Given the input.csv that you supplied, and the hash.txt file, the following email addresses were generated as shown in the screenshot.email addresses
OK, if there's no need to verify the 3/4 letter code from the CSV against AD then you can remove that bit of code.  Since you said the comparison of the created smtp address is working, I won't worry about it.
0
Is Your AD Toolbox Looking More Like a Toybox?

Managing Active Directory can get complicated.  Often, the native tools for managing AD are just not up to the task.  The largest Active Directory installations in the world have relied on one tool to manage their day-to-day administration tasks: Hyena. Start your trial today.

 
LVL 39

Expert Comment

by:footech
ID: 39210310
I was pretty sure there was an easier way to create the hash table, and I found it.
$data = (Get-Content hash.txt) -replace "[`'`"]" | Out-String | ConvertFrom-StringData

Open in new window

0
 
LVL 2

Author Comment

by:Mandy_
ID: 39211162
thank you so much. i will check everything now.
0
 
LVL 2

Author Closing Comment

by:Mandy_
ID: 39211874
nice... thank you so much
0

Featured Post

Netscaler Common Configuration How To guides

If you use NetScaler you will want to see these guides. The NetScaler How To Guides show administrators how to get NetScaler up and configured by providing instructions for common scenarios and some not so common ones.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

This process describes the steps required to Import and Export data from and to .pst files using Exchange 2010. We can use these steps to export data from a user to a .pst file, import data back to the same or a different user, or even import data t…
This article aims to explain the working of CircularLogArchiver. This tool was designed to solve the buildup of log file in cases where systems do not support circular logging or where circular logging is not enabled
This tutorial will walk an individual through the process of transferring the five major, necessary Active Directory Roles, commonly referred to as the FSMO roles from a Windows Server 2008 domain controller to a Windows Server 2012 domain controlle…
To add imagery to an HTML email signature, you have two options available to you. You can either add a logo/image by embedding it directly into the signature or hosting it externally and linking to it. The vast majority of email clients display l…

920 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

12 Experts available now in Live!

Get 1:1 Help Now