AD User Provisioning from CSV Got Easier

Creating new AD user accounts from a csv-file data has become even easier. In versions prior to 1.0.6 you could import a csv file, and then use ForEach-Object and manual column mapping to assign the values to the attributes (for example, see this post on populating test environments):

Import-CSV c:\users.csv | ForEach-Object { New-QADUser -Name $_.Name -SamAccountName $_.Name -Department $_.Department -ParentContainer mydoman.local/demo }

This works but is somewhat redundant. If the CSV file already has a column named Name, why wouldn’t PowerShell assign that column to the Name property automatically?

This is exactly what we have added in AD cmdlets 1.0.6. Now you no longer have to use ForEach-Object (pipe import- directly into new-!) and don’t need to specify the parameters which are already in the CSV file!

This means that if I have a csv file like this:

Name,samAccountName,Title
"Aaron Nelson",anelson,Engineer
"Justin Starin",jstarin,Janitor

I can create these two new user accounts with the simple command below:

Import-Csv users.csv | New-QADUser -ParentContainer mydomain.local/test Import

Notice how much simpler it has become compared to the one used in the beginning of the post!

But wait, it becomes even better! You can mix and match the approaches by using some of the parameters from the CSV and adding others in the command-line. Let’s use the same CSV but set the City property to the accounts we create:

Import-Csv users.csv | New-QADUser -ParentContainer mydomain.local/test -City Melbourne Import

This takes the Name, SamAccountName, and Title from CSV, and adds the City from our command:

Get-QADUser -SearchRoot mydomain.local/test | Format-Table Name,samAccountName,Title,City

Name          samaccountname Title    City
----          -------------- -----    ----
Aaron Nelson  anelson        Engineer Melbourne
Justin Starin jstarin        Janitor  Melbourne

The whole purpose of PowerShell and AD cmdlets is to make AD management easier and more intuitive, and it is good to see another step in that direction.

By the way, this feature was influenced by the requests we were getting from the community: for example, from Jonathan Walz and his comments here. Thanks Jonathan and everyone on the AD PowerShell discussion forum!

Tags: , , , , , , ,

22 Responses to “AD User Provisioning from CSV Got Easier”


  1. 1 George K January 21, 2008 at 1:40 pm

    Great job! A cmdlet which works the administrator way (as opposed to the developer way).

  2. 2 Murray Wall January 24, 2008 at 4:29 am

    We are using the Quest commandlets and would like to use them to generate SMTP addresses for Exchange 2003 AD users. Using the command get-qaduser -searchroot ‘domain.com/’ |select-object DisplayName,Mail,ProxyAddresses

    refuses to return ProxyAddresses but
    get-qaduser administrator@domain.com | select-object DisplayName,Mail,ProxyAddresses

    works fine. Is this a feature or a bug? What we want to do is add a new smtp address, leaving the old one there and making the new one primary. can you help?
    I see one potential issue that may exist is that this may create and assign a duplicate smtp address already assigned in the directory, is that true? if so can we do a check for it? we need to do this on multiple in an OU.

    Ie dump all the users in an OU, current SMTP address is FLASTNAME@Domain.com we want to add a new one, called Firstname.lastname@domain.com and after we add it, make it primary

  3. 3 dmitrysotnikov January 24, 2008 at 1:46 pm

    Murray,

    When you are querying more than one account, the cmdlets don’t retrieve all attributes. Instead they only go with a subset you specify. You can find the default subset by running:

    Get-QADPSSnapinSettings -DefaultExcludedProperties

    As you can see neither mail, nor proxyAddresses are in the set. To get them you can either modufy the default set or simply use Included Properties parameter:

    Get-QADUser -IncludedProperties Mail,ProxyAddresses | Select-Object DisplayName,Mail,ProxyAddresses

    You can also ask questions like this one in the PowerShell AD discussion forum at: http://powergui.org/forum.jspa?forumID=173 – there are a lot of knowledgeable guys there to help, so there’s a chance to get a quicker turnaround than with the blog comments.

    Dmitry

  4. 4 Aleksandr May 21, 2008 at 7:32 pm

    Hello Dmitry,
    For some reason import-csv and new-qaduser do not work for me. Thew new account appears in AD; however, all values are missing except for Name. SamAccountName, FirstName, LastName specified in CSV are ignored.
    Thanks for your help,
    AK

  5. 5 dmitrysotnikov May 22, 2008 at 5:01 am

    Aleksandr,

    Unfortunately, there has been a regression in the import functionality. So with version 1.0.7 and current 1.1 beta you have to use foreach-object between the import and the new-qaduser and list all the parameters even if they have the same names as the csv columns. E.g.

    Import-CSV c:\users.csv | ForEach-Object {
    New-QADUser -Name $_.Name -SamAccountName $_.Name -Department $_.Department -ParentContainer mydoman.local/demo
    }

    Dmitry

  6. 6 Dmitry Sotnikov August 27, 2008 at 1:36 pm

    The functionality got back in 1.1. So if you are on 1.1.1 or later – you can use direct pipelining again – just make sure that you use the -Import parameter.

  7. 7 Ambers December 8, 2008 at 10:57 pm

    Dmitry,

    When I create users from a csv file the email address field is not populated. The row header I have is “email”, but I’m unsure why it isn’t working. I don’t want the accounts email enabled with Exchange, but I do want to list the SMTP addresses.

    Any help would be appreciated.

    Thanks.

  8. 8 Dmitry Sotnikov December 15, 2008 at 2:49 pm

    Ambers,

    The reason why this does not work is that New-QADUser does not have -Email parameter. Hence, you need to either use Set-QADUser to apply this to each account, or -ObjectAttributes in New-QADUser.

    Sorry for not responding right away. Next time please consider asking at the Active Directory PowerShell forum at http://powergui.org/forum.jspa?forumID=173 – that’s the best way to get someone help you out with tasks like this.

    Dmitry

  9. 9 Azat September 8, 2009 at 2:13 pm

    Здравствуйте! Есть необходимость ввести в домен учетные записи из CSV файла (users.csv), где собраны данные такие как:
    ФИО, Логин, Должность.
    Name,samAccountName,Title
    “Иванов”,Ivanov,инженер
    “Петров”,Petrov,инженер
    Выполняю команду:
    Import-Csv c:\users.csv | New-QADUser -ParentContainer domain.loc/users
    Ошибка указывает на 38 знак тесть на –ParentContainer где тут неправильный синтаксис непонятно.

  10. 10 Dmitry Sotnikov September 14, 2009 at 12:46 pm

    Азат,

    Попробуйте взять путь к контейнеру в одинарные кавычки: ‘domain.loc/users’ или использовать вместо канонического пути, distinguished name: ‘CN=Users,DC=domain,DC=loc’.

    Если не поможет, попробуйте, все тоже самое, но с ForEach-Object:

    Import-Csv c:\users.csv | ForEach-Object {
    New-QADUser -Name $_.Name -SamAccountName $_.samAccountName -Title $_.Title -UserPassword ‘P@ssw0rd’ -ParentContainer ‘domain.loc/users’
    }

    Извините за задержку в ответе, для большей оперативности, советую в таких случаях стараться пользоваться форумами на http://powergui.org – правда, они только на английском языке.

    Дмитрий

  11. 11 Johan Pieterse March 14, 2011 at 11:45 am

    Or …. (no QAD required)

    import-csv .\newuser.csv | %{new-aduser -name $_.Name -DisplayName $_.DisplayName -GivenName $_.GivenName -SamAccountName $_.SamAccountName -UserPrincipalName
    $_.UserPrincipalName -Description $_.Description -Path ‘CN=Users,DC=contoso,DC=com -CannotChangePassword $true -ChangePasswordAtLogon $false ; Set- ADAccountPassword -identity $_.name -NewPassword (ConvertTo-SecureString -AsPlainText $_.AccountPassword -Force) -Reset ; Enable-ADAccount -identity $_.name}

    Make sure the newuser.csv file contains….

    Name,DisplayName,GivenName,SamAccountName,UserPrincipalName,Description,AccountPassword

  12. 13 lacemi May 4, 2011 at 6:55 pm

    I have this problem:
    [PS] C:\Users\Administrador>Import-Csv c:\usuarios.csv | New-QADUser -ParentContainer contoso.c
    om/UsuariosNuevos -Import
    New-QADUser : No se puede procesar la entrada de la canalización porque no se puede recuperar
    el valor predeterminado del parámetro ‘Name’. No se puede obtener el valor de propiedad porque
    “Name” es de solo escritura.
    En línea: 1 Carácter: 41
    + Import-Csv c:\usuarios.csv | New-QADUser <<<< -ParentContainer contoso.com/UsuariosNuevos –
    Import
    + CategoryInfo : ReadError: (@{Name=Maxi; Fi…sword=Acad3mia}:PSObject) [New-QA
    DUser], ParameterBindin…tValueException
    + FullyQualifiedErrorId : GetDefaultValueFailed,Quest.ActiveRoles.ArsPowerShellSnapIn.Pow
    ershell.Cmdlets.NewUserCmdlet

    Mi csv:
    UTF-8
    Name,FirstName,LastName,SamAccountName,UPN,Password
    "Mou",a,a,Mou,Mou@contoso.com,Acad3mia
    "Burt",a,a,Burt,Burt@contoso.com,Acad3mia
    "Homer",a,a,Homer,Homer@contoso.com,Acad3mia
    "Lisa",a,a,Lisa,Lisa@contoso.com,Acad3mia

    Thanks

    • 14 Dmitry Sotnikov May 4, 2011 at 7:09 pm

      See if this syntax works then:

      Import-CSV c:\users.csv | ForEach-Object { New-QADUser -Name $_.Name -FirstName $_.FirstName -LastName $_.LastName -UserPrincipalName $_.UPN -SamAccountName -UserPassword $_.Password $_.SamAccountName -ParentContainer contoso.com/UsuariosNuevos }

      • 15 lacemi May 5, 2011 at 6:12 pm

        Very Thanks, Dmitry.
        This syntax works bad, it is my first syntax. It send me: ‘Name´ is null. All time I recived this mesagge So I did the test with your before syntax.
        My Quest Software is Activate Roles Management Shell 1.4.0, a part of Quest ActiveRoles Server 6.5 and S.O. is Windows Server 2008.
        Thanks for your blog´s

  13. 16 Dmitry Sotnikov May 5, 2011 at 6:18 pm

    To make things a bit easier – let’s move the troubleshooting to our support forums. Please post the script, error message, and CSV sample to the AD and PowerShell forum here: http://powergui.org/forum.jspa?forumID=173 – and we will try to help troubleshoot further.

  14. 17 lacemi May 6, 2011 at 10:42 am

    Newww Thanks Dmitri for the link and you help and Dmitry’s PowerBlog, is normal to give you a microsoft mvp.
    Bye.

  15. 18 Andy January 2, 2012 at 3:33 pm

    Hi Dimitri,

    can i use the DistinguishedName for the import too? It doesn’t running.

    Import-Csv “D:\Temp\list.csv” | foreach {Add-ADGroupMember -Identity “ADS-Group” -Member $_.DistinguishedName}

    I have only in my csv file the DistinguishedName of the users:
    DistinguishedName
    CN=Musermann\, Max,OU=Administrators,OU=Locations,DC=ads,DC=company,DC=com
    CN=Price\, Tom,OU=Administrators,OU=Locations,DC=ads,DC=company,DC=com

    Do you have a idea?


  1. 1 Update AD from CSV « Dmitry’s PowerBlog: PowerShell and beyond Trackback on October 3, 2008 at 8:01 am
  2. 2 Brandon on AD cmdlets « Dmitry’s PowerBlog: PowerShell and beyond Trackback on October 17, 2008 at 12:26 pm
  3. 3 Overview of SQL 2008 » The IT Repository Trackback on September 28, 2011 at 5:16 am
  4. 4 Blogs for Technological Boniks - Update the information of domain users by CLI or Powershell Trackback on July 12, 2012 at 7:09 am

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s




My Recent Tweets

Legal

The posts on this blog are provided “as is” with no warranties and confer no rights. The opinions expressed on this site are mine and mine alone, and do not necessarily represent those of my employer - WSO2 or anyone else for that matter. All trademarks acknowledged.

© 2007-2014 Dmitry Sotnikov

January 2008
M T W T F S S
« Dec   Feb »
 123456
78910111213
14151617181920
21222324252627
28293031  

%d bloggers like this: