Creating a new user using CSV after checking existing

I have a CSV file, below is an example:
FirstName, LastName, SamAccountName, Email
Peter, Ivanov, ivanov, ivanov@scripttest.ru
Yana, Budnya, budnya, budnya@scripttest.ru
I want to check SamAccountName, and if it already exists, then add the number 1, repeat the check, if this name is there, then add not 1 but 2.
For example, ivanov exists (validation is required via Get-mailbox, since we would like to use only 1 Exchange module due to Exchange integration with AD, for example, Get-mailbox -Filter “samaccountname -eq ‘ivanov’” ", add 1, get ivanov1, repeat the check, we get that ivanov1 is there, then we add 2 and we get ivanov2, we repeat the check, we get the answer that ivanov2 is unique.
Create a new CSV file in the same folder and add ivanov2 to CSV along with the rest of the data, we get:
FirstName, LastName, SamAccountName, Email
Peter, Ivanov, ivanov2, ivanov@scripttest.ru
Yana, Budnya, budnya, budnya@scripttest.ru
We create a user through the New-Mailbox, pause for synchronization, set the required Set-Mailbox limits.
At the output we have 2 files, the first one sent and the second new one with the changes.

This is part of the code I tried, but it doesn’t work:

$Logon = “ivanov”
$NTName = $Logon
$Found = $False
For ($i=1; $i ++) {

Check for existence of object with sAMAccountName $NTName.

$Check = Get-mailbox -Filter “samaccountname -eq ‘$NTName’”
If ($Check) {$NTName = “$Logon$i”}
Else {$Found = $True;Break}
}
If ($Found -eq $True)
{

Create user with sAMAccountName $NTName.

Write-Host $Logon “found” $NTName -ForegroundColor Red
}
Else
{
Write-Host $Logon “not found” -ForegroundColor Green
}

Below is an example of the original code, but there is no SamAccountName re-check that is an error:

clear screen

clear

import Exchange module

Add-pssnapin Microsoft.Exchange.Management.PowerShell.SnapIn

translate function

$csv = Read-Host Please input file path and name

check file extension, if not CSV - message and exit after 3 seconds

$exten = [IO.Path]::GetExtension(’$csv’) -ne ‘csv’
if ($exten -eq $false){
write-host “This is not a CSV file” -ForegroundColor Red
Start-Sleep -s 3
exit
}

for replace emty string in CSV if found

creation temp file *_data.csv in the same folder

$csvfile = $csv.TrimEnd(".csv") + "data.csv"
$CSVData = Import-Csv $csv
$CSVData | ForEach-Object{
if($
.FirstName -eq “”) {
Write-Host found empty string CSV for $.LastName -ForegroundColor Red
$CSVDatanew = Read-Host please input 2 letters for replace
$
.FirstName=$CSVDatanew
$CSVData | Export-Csv $csvfile -NoTypeInformation -Encoding UTF8
}
elseif($.LastName -eq “”) {
Write-Host found empty string CSV for $
.FirstName -ForegroundColor Red
$_.LastName=$CSVDatanew
$CSVData | Export-Csv $csvfile -NoTypeInformation -Encoding UTF8
}
}

request organization name example: test.ru

$org = Read-Host please input tenant name

request single password for accounts

$Password = Read-Host please input password for users account

set quota

$IWQ = “0.5gb”
$PSQ = “0.7gb”
$PSRQ = “1gb”

change the original file name (add “_changes”) to export it to the same location as the original

$csvfilepath = $csv.TrimEnd(".csv") + “_changes.csv”

checking for the presence of the file, which is created when there are empty stringes in the CSV

removing temp file - $csvfile

$WantFile = $csv.TrimEnd(".csv") + “_data.csv”
$FileExists = Test-Path $WantFile
If ($FileExists -eq $False) {
$csvfile = $csv
Write-Host In CSV no empty string -ForegroundColor Green
}
else
{
$csvfile = $csvfile
Remove-Item -Path $csvfile
Write-Host In CSV found empty string and replaced -ForegroundColor Yellow
}

check exchange alias=sam account name

if exist then replace sam account name

and export to new csv file

$checkSAM = Import-Csv $csvfile
$out = $checkSAM | ForEach{$exist = [bool](Get-mailbox $.SamAccountName -erroraction SilentlyContinue);
if ($Exist){
Write-Host $
.SamAccountName “found, will be change” -ForegroundColor Red

translate first name

$transFirstName=Translit($.FirstName)
$transFirstNamel=$transFirstName.ToLower()
$FirstName = $
.FirstName
$LastName = $_.LastName

replace sam Account Name

$SAM = $.samAccountName + $transFirstNamel
$Email = $
.Email

creating an object with values for export

[PSCustomObject]@{
FirstName = $FirstName
LastName = $LastName
SamAccountName = $SAM
Email = $Email

export new values

} | Export-Csv $csvfilepath -NoTypeInformation -Append -Force -Encoding UTF8
}else{
Write-Host $.SamAccountName “not found, no changes” -ForegroundColor Green
$FirstName = $
.FirstName
$LastName = $.LastName
$SAM = $
.samAccountName
$Email = $_.Email

creating an object with values for export

[PSCustomObject]@{
FirstName = $FirstName
LastName = $LastName
SamAccountName = $SAM
Email = $Email

export new values

} | Export-Csv $csvfilepath -NoTypeInformation -Append -Force -Encoding UTF8
}
}

message about export changes in CSV

Write-Host “Changes saved in file” $csvfilepath -ForegroundColor Yellow

creating an account and setting quotas

Import-Csv $csvfilepath | ForEach-Object {
$samAccountName = $.SamAccountName
$upn = $
.SamAccountName + “@" + $org
$uname = $.LastName + " " + $.FirstName
$OUpath = “OU=” + $org + “,OU=tenants,DC=ec,DC=mts,DC=ru”
$Email = $_.Email

translate first name + last name

$transLastName=Translit($.LastName)
$transFirstName=Translit($
.FirstName)
$transInitials=Translit($_.Initials)
$transuname = $transLastName + " " + $transFirstName

creating mailbox-account

New-Mailbox -Name $uname `
-UserPrincipalName $upn `
-Password (ConvertTo-SecureString $Password -AsPlainText -force) `
-Alias $samAccountName `
-FirstName $.FirstName `
-LastName $
.LastName `
-DisplayName $uname `
-OrganizationalUnit $OUpath `
-ResetPasswordOnNextLogon $true `
-erroraction silentlycontinue | Out-Null

pause for sync Exchange-AD changes

Start-Sleep -s 5

set Warning Quota, Prohibit Send Quota, Prohibit Send Receive Quota, Use Database Quota Defaults

set correct email address (reason: default policy)

need only for policy, but there is a question about the direction of the parameters:

-CustomAttribute1 @{‘extensionAttribute1’=$org} `

policy disabled -EmailAddressPolicyEnabled

Set-Mailbox -Identity $upn `
-IssueWarningQuota $IWQ `
-ProhibitSendQuota $PSQ `
-ProhibitSendReceiveQuota $PSRQ `
-EmailAddressPolicyEnabled $false `
-EmailAddresses $Email `
-UseDatabaseQuotaDefaults $false
}

Here is the method that I came up with. It minimizes overly repetitive [pre]Get-Mailbox[/pre] queries in favor of doing a single like/regex query per account. This should speed things up for a long input list since it pulls one time then evaluates in memory. You’ll need to retrofit your logging/account creation/export logic to this.

$Items = Import-Csv '.\data.csv'

$Items | ForEach-Object {
#Init our flag for a unique username
$Unique = $false
#Init our target SamAccountName
$Target_SamAccountName = $.SamAccountName
#Init Regex pattern
$Pattern = “^$Target_SamAccountName\d*”
#Get all existing accounts that have the same SamAccountName regardless of numeric suffix
$ExistingItems = @(Get-Mailbox -Filter “SamAccountName -like ‘$Target_SamAccountName*’” | Where-Object {$
.SamAccountName -match $Pattern} | Select-Object -ExpandProperty SamAccountName)
if ($ExistingItems -notcontains $Target_SamAccountName) { #Found unique on first try
$Unique = $true
}
else { #Have to search for next available unique numeric suffix
#Init our numeric suffix
$Numeric = 1
while (-not $Unique) {
if($ExistingItems -notcontains “$Target_SamAccountName$Numeric”) { #Found next available numeric suffix
$Unique = $true
$Target_SamAccountName = “$Target_SamAccountName$Numeric”
}
else { #Try next
$Numeric++
}
}
}

# Log the info/create an account, etc...
Write-Host $Target_SamAccountName

}

Here is my take on it. I reduced the number of [pre]Get-Mailbox[/pre] calls to one-per input item by doing a like/regex query and then processing in memory. You’ll have to retrofit the logging/export/creation activities you want to accomplish.

https://gist.github.com/jeremytbrun/04c41fe0d7aad01de291430cefb2593b

Below code might do the same.

#Without looping
$ExistingAccount = Get-AdUser -Filter "SamAccountName -like '$SamAccountName*'" | Sort-Object -Property SamAccountName | Select-Object -Last 1
if($ExistingAccount -match '^.(\d)'){
    $NewName = $ExistingAccount -replace $Matches[1],(1 + $Matches[1])
}
$NewName

@kvprasoon, I agree that your sample should achieve the same result in most cases, but if I understand it correctly (and let me know if I missed something), it won’t make use of/recycle SamAccountName’s that may have been deleted/cleaned up and therefore available to use again. For instance, if accounts with ivanov, ivanov1, ivanov2, and ivanov3 exist, but then ivanov1 is deleted so it is no longer reserved. Your code sample still seems to return ivanov4 even though it should reuse ivanov1 to be most efficient. Ultimately you’d end up with large gaps of unused account names over time due to natural turnover.

Also I had to change the Regex pattern on line 3 of your sample to include a ‘+’ character to look for 1 or more characters.

[pre]'^.+(\d)'[/pre]
OP - this is just something to consider depending on your long term goal in SamAccountName reservation management.

Another way using Compare-Object. Basically, generate a array with possible names and then search AD with a wildcard to return what exists, then compare the two:

$First = 'John'
$Last = 'Smith'

#Generate Root Account. This example is first initial and last name up to 4 char
$samFilter = '{0}{1}' -f $First.Substring(0,1), $Last.Substring(0,4)

#Possible names
$possibleNames = @()
$possibleNames += $samFilter #Add Root
$possibleNames += 1..25 | foreach{'{0}{1:D2}' -f $samFilter, $_} #Generate 25 possible names

#Search AD for Root Account with wildcard filter
$adUsers = Get-ADUser -Filter "SamAccountName -like '$samFilter*'" | Select -ExpandProperty SamAccountName

#Get first available samaccountname with Compare-Object
$uniqueSam = Compare-Object -ReferenceObject $possibleNames -DifferenceObject $adUsers | Where{$_.SideIndicator -eq '<='} | Select-Object -ExpandProperty InputObject -First 1

Output:

PS H:\> $possibleNames
JSmit
JSmit01
JSmit02
JSmit03
JSmit04
JSmit05
JSmit06
JSmit07
JSmit08
JSmit09
JSmit10
JSmit11
JSmit12
JSmit13
JSmit14
JSmit15
JSmit16
JSmit17
JSmit18
JSmit19
JSmit20
JSmit21
JSmit22
JSmit23
JSmit24
JSmit25

PS H:\> $adUsers
jsmit
jsmit01
jsmit02
jsmit05
jsmit06

PS H:\> $uniqueSam
JSmit03

Yes ,that was a code snippet on top of my head that point in time with the focus of reducing loops. I am happy that @Soldier000 has three different approaches to achieve the task.

Absolutely! I, for sure, learned something myself from working through all 3!

Thank you all for the answers, too long post moderation and work led me to my own decision.

Below is the script code:

# clear screen
clear

# import Exchange module
Add-pssnapin Microsoft.Exchange.Management.PowerShell.SnapIn

# requestCSV file path
# if problem Get-Content users.csv >> users1.csv
# example: C:\temp\orgusers.csv
# should strictly observe the capitalization of CSV header otherwise will duplicate titles
# below is a valid example:
# FirstName,LastName,SamAccountName,Email
$csv = Read-Host Enter the full path with the file name without quotes

# check file extension, if not CSV - message and exit after 3 seconds
$exten = [IO.Path]::GetExtension($csv) -ne '.csv'
if ($exten -eq $true){
write-host This file is not a CSV -ForegroundColor Red
Start-Sleep -s 3
exit
}

# request organization name example: test.com
$org = Read-Host Enter Tennant Name

# request single password for accounts
$Password = Read-Host Please enter password

# set quota according answer
# 5 plans installed with a limit 1 Gb, 2 Gb, 5 GB, 10 Gb, 50 Gb and custom
$answer= read-host "Enter a number from 1 to 6 `n1. Plan 1 Gb `n2. Plan 2 Gb `n3. Plan 5 Gb `n4. Plan 10 Gb `n5. Plan 50 Gb `n6. Custom `n"

switch ($answer) {
  1 {
  $IWQ = "0.5gb"
  $PSQ = "0.7gb"
  $PSRQ = "1gb"
  }
  2 {
  $IWQ = "1.5gb"
  $PSQ = "1.7gb"
  $PSRQ = "2gb"
  }
  3 {
  $IWQ = "4.5gb"
  $PSQ = "4.7gb"
  $PSRQ = "5gb"
  }
  4 {
  $IWQ = "9gb"
  $PSQ = "9.5gb"
  $PSRQ = "10gb"
  }
  5 {
  $IWQ = "45gb"
  $PSQ = "48gb"
  $PSRQ = "50gb"
  }
  6 {
  $IWQi = Read-Host "Enter warning threshold (example: 0.5)"
  $IWQ = $IWQi + gb"
  $PSQi = Read-Host "Enter threshold prohibit send (example: 0.7)"
  $PSQ = $PSQi + “gb"
  $PSRQi = Read-Host "Enter the threshold of the prohibition of sending and receiving (example: 1)"
  $PSRQ = $PSRQi + gb"
  }
  default {"Your choice is wrong"}
}

# if SamAccountName already exists, then a number will be added
Import-Csv $csv | ForEach{
$i=1
$SAM = $_.SamAccountName
$csvfileunique = $csv.TrimEnd(".csv") + "_data.csv"
  If (Get-mailbox -Filter "SamAccountName -ne '$SAM'")
  {
    $FirstName = $_.FirstName
    $LastName = $_.LastName
    $SAM = $_.samAccountName
    $Email = $_.Email
    # creating an object with values for export
      [PSCustomObject]@{
      FirstName = $FirstName
      LastName = $LastName
      SamAccountName = $SAM
      Email = $Email
      } | Export-Csv $csvfileunique -NoTypeInformation -Append -Force -Encoding UTF8
  }
  If (Get-mailbox -Filter "SamAccountName -eq '$SAM'")
  {
  $UniqueSAM = $SAM
    while (Get-mailbox -Filter "SamAccountName -eq '$UniqueSAM'"){
    $UniqueSAM = $SAM + ++$i
    Write-Host For $_.SamAccountName there are matches that will be replaced by $UniqueSAM -ForegroundColor Red
    $FirstName = $_.FirstName
    $LastName = $_.LastName
    $Email = $_.Email
    # creating an object with values for export
      [PSCustomObject]@{
      FirstName = $FirstName
      LastName = $LastName
      SamAccountName = $UniqueSAM
      Email = $Email
      } | Export-Csv $csvfileunique -NoTypeInformation -Append -Force -Encoding UTF8
  }
}
}
# creating file with changes and merge 2 files into 1 with duplicate removal
$csvfile = $csv.TrimEnd(".csv") + "_changes.csv"
$unique = import-csv $csvfileunique | Sort Email -Unique
$unique | Export-Csv $csvfile -NoTypeInformation -Append -Force -Encoding UTF8

# $csvfileunique - unique name validation result
# removing temp file - $csvfileunique if found
$WantFile = $csv.TrimEnd(".csv") + "_changes.csv"
$FileExists = Test-Path $WantFile
If ($FileExists -eq $False) {
  $csvfile = $csv
  Write-Host All SamAccountName are unique -ForegroundColor Green
}
else
{
  $csvfile = $csvfile
  Remove-Item -Path $csvfileunique
  Write-Host Non-unique SamAccountName values were found and replaced `nAll data is saved in $csvfile -ForegroundColor Yellow
}

# creating an account and set quotas
Import-Csv $csvfile | ForEach-Object {
if($_.FirstName -eq "") {
  $FirstName = "Shared"
  Write-Host For $_.LastName found null and FirstName will be set $FirstName -ForegroundColor Red
}
else{
  $FirstName = $_.FirstName
}
  $samAccountName = $_.SamAccountName
  $upn = $_.SamAccountName + “@" + $org
  $uname = $_.LastName + " " + $FirstName
  $OUpath = "OU=" + $org + ",OU=tenants,DC=ec,DC=contonso,DC=com"
  $Email = $_.Email

# creating mailbox-account
New-Mailbox -Name $uname 
-UserPrincipalName $upn 
-Password (ConvertTo-SecureString $Password -AsPlainText -force) 
-Alias $samAccountName 
-FirstName $_.FirstName 
-LastName $_.LastName 
-DisplayName $uname 
-OrganizationalUnit $OUpath 
-ResetPasswordOnNextLogon $true 
-erroraction silentlycontinue | Out-Null

# pause for sync Exchange-AD changes
Start-Sleep -s 5

# set Warning Quota, Prohibit Send Quota, Prohibit Send Receive Quota, Use Database Quota Defaults
# set correct email address (reason: default policy)
# need only for policy, but there is a question about the direction of the parameters:
# -CustomAttribute1 @{'extensionAttribute1'=$org} `
# policy disabled -EmailAddressPolicyEnabled
Set-Mailbox -Identity $upn 
-IssueWarningQuota $IWQ 
-ProhibitSendQuota $PSQ 
-ProhibitSendReceiveQuota $PSRQ 
-EmailAddressPolicyEnabled $false 
-EmailAddresses $Email 
-UseDatabaseQuotaDefaults $false
}