Solved

Create Login from Fields in CSV

Posted on 2010-09-06
15
727 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
  • 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
 

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 500 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

Join & Write a Comment

How to sign a powershell script so you can prevent tampering, and only allow users to run authorised Powershell scripts
Disabling the Directory Sync Service Account in Office 365 will stop directory synchronization from working.
This tutorial will walk an individual through the process of transferring the five major, necessary Active Directory Roles, commonly referred to as the FSMO roles from a Windows Server 2008 domain controller to a Windows Server 2012 domain controlle…
This tutorial will walk an individual through the process of configuring their Windows Server 2012 domain controller to synchronize its time with a trusted, external resource. Use Google, Bing, or other preferred search engine to locate trusted NTP …

759 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

20 Experts available now in Live!

Get 1:1 Help Now