Link to home
Start Free TrialLog in
Avatar of SCC-EE
SCC-EE

asked on

Create Login from Fields in CSV

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
Avatar of Meir Rivkin
Meir Rivkin
Flag of Israel image

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
Avatar of SCC-EE
SCC-EE

ASKER

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

Avatar of SCC-EE

ASKER

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

Avatar of SCC-EE

ASKER

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.
ASKER CERTIFIED SOLUTION
Avatar of Meir Rivkin
Meir Rivkin
Flag of Israel image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Avatar of SCC-EE

ASKER

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.
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...
Avatar of SCC-EE

ASKER

Hmm, even using your CSV I get the following error on the second objects:
Output.jpg
Avatar of SCC-EE

ASKER

Attached are the CSV and the PS Files.
users.csv
Avatar of SCC-EE

ASKER

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"
}
change this line:

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

with this line:

 $result = get-QADUser -SearchRoot 'test.local/Companies' -LdapFilter $filter
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
Avatar of SCC-EE

ASKER

Thanks mate, major fail on my part there.