Want to win a PS4? Go Premium and enter to win our High-Tech Treats giveaway. Enter to Win

x
?
Solved

Create Login from Fields in CSV

Posted on 2010-09-06
15
Medium Priority
?
742 Views
Last Modified: 2012-05-10
Guys,

I'm working on a script to populate AD from a CSV file, using the Quest AD Snap In. I've got the basics of the script sorted, for example:
Import-CSV c:\users.csv | ForEach-Object { New-QADUser -Name $_.Name - -Department $_.Department -ParentContainer OU=Users,DC=test,DC=local }

However I want to do some “magic” with the SamAccountName setting. In the CSV I have three fields I want to meld into a SamAccountName; GivenName, Initial, Surname and then a single digit suffix number.

So if I had a user John H Smith, the script would make a SamAccountName jhs1, moreover if the account jhs1 exists it would increase the suffix number by one to make jhs2, its this point that is really got me puzzled on how to check AD for a pre-existing account in order to enact the increase on the suffix.

Any ideas / help on this would be gratefully appreciated.

Cheers,

Nick
0
Comment
Question by:SCC-EE
[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
  • 8
  • 7
15 Comments
 
LVL 42

Expert Comment

by:Meir Rivkin
ID: 33610498
i doubt that this scenario can happend cause if either Name or samAccountName are already being used, the New-QADUser will fail.
which means that the script should change check both Name and samAccountName, and if exists, add suffux to both of them (not just samAccountName).

so assume the following csv file:

Name,Initial,Surname,Department
Jonny,H,Bee,IT

first, i check if there's a user which Name equals Jonny OR samAccountName equals JonnyHBee.
if true, add suffix (say counter start from 1) to both Name and samAccountName, and check again:
Name - Jonny1
samAccountName- JonnyHBee1
if exists, increase counter and check again till search result false.
then run the following:

$username = $_.Name + $counter
$userSamAccountName = $_.Name + $_.Initial + $_.Surname + $counter
New-QADUser -Name $username -samAccountName $userSamAccountName -Department $_.Department -ParentContainer OU=Users,DC=test,DC=local

i'll post the script in a minute
0
 

Author Comment

by:SCC-EE
ID: 33610549
Sedqwick,

Thanks for that, it was actually what I was starting to think may be the way forward, IE pre-populate the CSV with SamAccountName prior to doing the New-QADUser. Pleased we are on the same page, look forward to seeing your script.

Cheers,

Nick
0
 
LVL 42

Expert Comment

by:Meir Rivkin
ID: 33610602
change the ldap path of the domain.
the csv file format should be as posted above.
cls

Import-CSV c:\temp\users.csv | ForEach-Object { 
	$counter=0
	$samaccount = $_.Name+$_.Initial+$_.Surname
	$name=$_.Name
	$filter = "(samaccountname=$samaccount)(name=$name)"
	$result = get-QADUser -SearchRoot 'soap.com/Users' -LdapFilter $filter
	while($result -ne $null){ 
		$counter=$counter+1
		$samaccount = $_.Name+$_.Initial+$_.Surname+$counter
		$name=$_.Name+$counter
		$filter = "(samaccountname=$samaccount)(name=$name)"
		$result = get-QADUser -SearchRoot 'soap.com/Users' -LdapFilter $filter
	}
	New-QADUser -Name $name -SamAccountName $samaccount -Department $_.Department -ParentContainer "cn=Users,DC=soap,DC=com"
}

Open in new window

0
Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

 

Author Comment

by:SCC-EE
ID: 33610835
Sort of works.... for some reason I don't get the number suffix.
Also looking to have just the first letter of the Given Name and Surname; John H Smith = jhs1
0
 
LVL 42

Expert Comment

by:Meir Rivkin
ID: 33610881
i updated the script to get the first letters of name and surname like posted above.

>>for some reason I don't get the number suffix.
if user doesn't exist, the suffix number is not being used (in the first time).
if you wish to always add suffix then let me know and i'll update the script.

cls

Import-CSV c:\temp\users.csv | ForEach-Object { 
	$counter=0
	$samaccount = $_.Name.Substring(0,1)+$_.Initial.Substring(0,1)+$_.Surname.Substring(0,1)
	$name=$_.Name
	$filter = "(samaccountname=$samaccount)(name=$name)"
	$result = get-QADUser -SearchRoot 'soap.com/Users' -LdapFilter $filter
	while($result -ne $null){ 
		$counter=$counter+1
		$samaccount = $_.Name.Substring(0,1)+$_.Initial.Substring(0,1)+$_.Surname.Substring(0,1)+$counter
		$name=$_.Name+$counter
		$filter = "(samaccountname=$samaccount)(name=$name)"
		$result = get-QADUser -SearchRoot 'soap.com/Users' -LdapFilter $filter
	}
	New-QADUser -Name $name -SamAccountName $samaccount -Department $_.Department -ParentContainer "cn=Users,DC=soap,DC=com"
}

Open in new window

0
 

Author Comment

by:SCC-EE
ID: 33610922
Sedqwick.

Yeah just to be anal, I'd like to suffix every account with a 1 or greater if required. At the moment I have multiple John and Jane accounts, it throws an error when it hits the additional instances of these in the CSV.
0
 
LVL 42

Accepted Solution

by:
Meir Rivkin earned 2000 total points
ID: 33610940
here you go, suffix from first encountered user, starting from 1 onward
cls

Import-CSV c:\temp\users.csv | ForEach-Object { 
	$counter=1
	$samaccount = $_.Name.Substring(0,1)+$_.Initial.Substring(0,1)+$_.Surname.Substring(0,1)+$counter
	$name=$_.Name+$counter
	$filter = "(samaccountname=$samaccount)(name=$name)"
	$result = get-QADUser -SearchRoot 'soap.com/Users' -LdapFilter $filter
	while($result -ne $null){ 
		$counter=$counter+1
		$samaccount = $_.Name.Substring(0,1)+$_.Initial.Substring(0,1)+$_.Surname.Substring(0,1)+$counter
		$name=$_.Name+$counter
		$filter = "(samaccountname=$samaccount)(name=$name)"
		$result = get-QADUser -SearchRoot 'soap.com/Users' -LdapFilter $filter
	}
	New-QADUser -Name $name -SamAccountName $samaccount -Department $_.Department -ParentContainer "cn=Users,DC=soap,DC=com"
}

Open in new window

0
 

Author Comment

by:SCC-EE
ID: 33611021
Fine work mate, still have a drama with multiple users with samename, however I am now getting the suffix for everyone and the first letter of each field.

Can you post your test CSV so I can make sure its not mine that is screwing things up? When it hits the second line of Jane in my CSV it fails and dosn't attempt to call it Jane2.
0
 
LVL 42

Expert Comment

by:Meir Rivkin
ID: 33611032
this is my testing csv file:

Name,Initial,Surname,Department
Jonny,H,Bee,R&D
Ziggy,Z,Rivkin,IT
Jonny,H,Bee,R&D
Ziggy,Z,Rivkin,IT

post your csv file and i'll check it...
0
 

Author Comment

by:SCC-EE
ID: 33611095
Hmm, even using your CSV I get the following error on the second objects:
Output.jpg
0
 

Author Comment

by:SCC-EE
ID: 33615976
Attached are the CSV and the PS Files.
users.csv
0
 

Author Comment

by:SCC-EE
ID: 33615989
PS File:

cls

Import-CSV c:\temp\users.csv | ForEach-Object {
      $counter=1
      $samaccount = $_.Name.Substring(0,1)+$_.Initial.Substring(0,1)+$_.Surname.Substring(0,1)+$counter
      $name=$_.Name+$counter
      $filter = "(samaccountname=$samaccount)(name=$name)"
      $result = get-QADUser -SearchRoot 'test.local' -LdapFilter $filter
      while($result -ne $null){
            $counter=$counter+1
            $samaccount = $_.Name.Substring(0,1)+$_.Initial.Substring(0,1)+$_.Surname.Substring(0,1)+$counter
            $name=$_.Name+$counter
            $filter = "(samaccountname=$samaccount)(name=$name)"
            $result = get-QADUser -SearchRoot 'test.local' -LdapFilter $filter
      }
      New-QADUser -Name $name -SamAccountName $samaccount -Department $_.Department -ParentContainer "OU=Companies,DC=test,DC=local"
}
0
 
LVL 42

Expert Comment

by:Meir Rivkin
ID: 33616110
change this line:

 $result = get-QADUser -SearchRoot 'test.local' -LdapFilter $filter

with this line:

 $result = get-QADUser -SearchRoot 'test.local/Companies' -LdapFilter $filter
0
 
LVL 42

Expert Comment

by:Meir Rivkin
ID: 33616120
this might not fix the bug you have, but it should be in the script anyways cause the search root should  match the parent container
0
 

Author Comment

by:SCC-EE
ID: 33616155
Thanks mate, major fail on my part there.
0

Featured Post

Get your Conversational Ransomware Defense e‑book

This e-book gives you an insight into the ransomware threat and reviews the fundamentals of top-notch ransomware preparedness and recovery. To help you protect yourself and your organization. The initial infection may be inevitable, so the best protection is to be fully prepared.

Question has a verified solution.

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

In this post we will be converting StringData saved within a text file into a hash table. This can be further used in a PowerShell script for replacing settings that are dynamic in nature from environment to environment.
It’s time for spooky stories and consuming way too much sugar, including the many treats we’ve whipped for you in the world of tech. Check it out!
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.
There are cases when e.g. an IT administrator wants to have full access and view into selected mailboxes on Exchange server, directly from his own email account in Outlook or Outlook Web Access. This proves useful when for example administrator want…

609 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