Solved

Create Primary SMTP from CSV and hash-table

Posted on 2013-05-30
6
602 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
 
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

Join & Write a Comment

Not sure what the best email signature size is? Are you worried about email signature image size? Follow this best practice guide.
Following basic email etiquette rules will help you write a professional email and achieve a good, lasting impression with your contacts.
In this video we show how to create an Accepted Domain in Exchange 2013. We show this process by using the Exchange Admin Center. Log into Exchange Admin Center.: First we need to log into the Exchange Admin Center. Navigate to the Mail Flow >> Ac…
The video tutorial explains the basics of the Exchange server Database Availability groups. The components of this video include: 1. Automatic Failover 2. Failover Clustering 3. Active Manager

746 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

13 Experts available now in Live!

Get 1:1 Help Now