?
Solved

Create Login from Fields in CSV

Posted on 2010-09-06
15
Medium Priority
?
740 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:sedgwick
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:sedgwick
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
 [eBook] Windows Nano Server

Download this FREE eBook and learn all you need to get started with Windows Nano Server, including deployment options, remote management
and troubleshooting tips and tricks

 

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:sedgwick
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:
sedgwick 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:sedgwick
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:sedgwick
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:sedgwick
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

Office 365 Training for Admins - 7 Day Trial

Learn how to provision tenants, synchronize on-premise Active Directory, implement Single Sign-On, customize Office deployment, and protect your organization with eDiscovery and DLP policies.  Only from Platform Scholar.

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…
Active Directory can easily get cluttered with unused service, user and computer accounts. In this article, I will show you the way I like to implement ADCleanup..
Exchange organizations may use the Journaling Agent of the Transport Service to archive messages going through Exchange. However, if the Transport Service is integrated with some email content management application (such as an antispam), the admini…
Are you ready to implement Active Directory best practices without reading 300+ pages? You're in luck. In this webinar hosted by Skyport Systems, you gain insight into Microsoft's latest comprehensive guide, with tips on the best and easiest way…
Suggested Courses
Course of the Month10 days, 14 hours left to enroll

770 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