Solved

Create Primary SMTP from CSV and hash-table

Posted on 2013-05-30
6
637 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_
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 3
  • 3
6 Comments
 
LVL 40

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 40

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
Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
LVL 40

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

Has Powershell sent you back into the Stone Age?

If managing Active Directory using Windows Powershell® is making you feel like you stepped back in time, you are not alone.  For nearly 20 years, AD admins around the world have used one tool for day-to-day AD management: Hyena. Discover why.

Question has a verified solution.

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

Recently we ran in to an issue while running some SQL jobs where we were trying to process the cubes.  We got an error saying failure stating 'NT SERVICE\SQLSERVERAGENT does not have access to Analysis Services. So this is a way to automate that wit…
Unified and professional email signatures help maintain a consistent company brand image to the outside world. This article shows how to create an email signature in Exchange Server 2010 using a transport rule and how to overcome native limitations …
This video shows how to use Hyena, from SystemTools Software, to bulk import 100 user accounts from an external text file. View in 1080p for best video quality.
This video shows how to use Hyena, from SystemTools Software, to update 100 user accounts from an external text file. View in 1080p for best video quality.

729 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