none
How to Read OU details from CSV while creating BULK AD users RRS feed

  • General discussion

  • Hello, 

    I have created script to create new mailboxes from csv. 

    $ou=[ADSI]'LDAP://OU=UserAccountsAndGroups,DC=resource,DC=com'

    $UserDetails=Import-Csv 'C:\Users\admin\Desktop\Migration\sing1.csv' #—–importing bulkusers data

    foreach($UD in $UserDetails){
        
        $user = $ou.Create('User', 'CN=' + $UD.cn)

        $user.Put('givenName',$UD.givenName)
        $user.Put('sAMAccountName',$UD.sAMAccountName)
        $user.Put('userPrincipalName',$UD.userPrincipalName)
        $user.Put('title',$UD.title)
        $user.Put('sn',$UD.sn)
        $user.Put('displayName',$UD.displayName)
        $user.Put('department',$UD.department)
        $user.Put('telephoneNumber',$UD.telephoneNumber)
      $user.Put('co',$UD.co)
    $user.Put('physicalDeliveryOffice',$UD.physicalDeliveryOfficeName)
    $user.Put('st',$UD.st)
        $user.Put('l',$UD.l)
    $user.Put('postalCode',$UD.postalCode)
        $user.Put('company',$UD.company)
    $user.put('mail',$UD.mail)

       Try
       {
             $user.commitchanges()
             $user.SetPassword('P@ssW0Rd')
        }
        Catch
    {
             # re-throw exception
             Throw $_ 
    }
    Start-Sleep -Second 40 
    Enable-Mailbox -identity $ud.displayName –Database 'Mailbox Database 1" -primarysmtpaddress $ud.mail -Alias $ud.alias | get-mailbox | select name,windowsemailaddress,database,alias

    }

    As you see, it has OU hard-coded. I want to read OU from CSV as well. Tried so many things but not working. Any idea? 


    Hasan

    • Changed type Bill_Stewart Sunday, July 29, 2018 11:13 PM
    • Moved by Bill_Stewart Sunday, July 29, 2018 11:14 PM This is not "teach me how to program in tiny incremental steps" forum (help vampire)
    Friday, June 1, 2018 2:30 PM

All replies

  • You are reading from a CSV in this code already. Just add an OU column to your CSV and use it in your foreach loop.


    -- Bill Stewart [Bill_Stewart]

    Friday, June 1, 2018 2:47 PM
  • And because you use a csv, and distinguished names include commas, quote the value in the csv so it is interpreted as one value.

    Richard Mueller - MVP Enterprise Mobility (Identity and Access)

    Friday, June 1, 2018 3:24 PM
  • And because you use a csv, and distinguished names include commas, quote the value in the csv so it is interpreted as one value.

    Richard Mueller - MVP Enterprise Mobility (Identity and Access)

    I tried, but it is not working. Here is the value:

    "OU=USA,OU=North America,DC=resource,DC=com"

    Code within for loop is:

    $ou= $UD.ou
    $user = $ou.Create('User', 'CN=' + $UD.cn)

    Error is

    Method invocation failed because [System.String] does not contain a method named 'Create'.


    Hasan


    Friday, June 1, 2018 3:41 PM
  • You need to convert the string representation of the DN of the OU into an ADsPath. Similar to:

    $OU = [ADSI]"LDAP://" + $UD.OU
    ...
    $User = $OU.Create("User", "CN=" + $UD.cn)
    

    Now the $OU object has the Create method.


    Richard Mueller - MVP Enterprise Mobility (Identity and Access)

    Friday, June 1, 2018 4:21 PM
  • You need to convert the string representation of the DN of the OU into an ADsPath. Similar to:

    $OU = [ADSI]"LDAP://" + $UD.OU
    ...
    $User = $OU.Create("User", "CN=" + $UD.cn)

    Now the $OU object has the Create method.


    Richard Mueller - MVP Enterprise Mobility (Identity and Access)

    Tried. It was giving me error:

    Method invocation failed because [System.DirectoryServices.DirectoryEntry] does not contain a method named 'op_Addition'.

    So i added +

    $OU = [ADSI] + "LDAP://" + $UD.OU

    Then error is:

    Cannot convert value "LDAP://" to type "System.Int32". Error: "Input string was not in a correct format."


    Hasan

    Friday, June 1, 2018 4:44 PM
  • $OU = [adsi]('LDAP://' +$UD.OU)

    Use parens.


    \_(ツ)_/


    • Edited by jrv Friday, June 1, 2018 4:49 PM
    Friday, June 1, 2018 4:49 PM
  • The correct syntax would be:


    [ADSI] "LDAP://$($UD.OU)"

    or


    [ADSI] ("LDAP://{0}" -f $UD.OU)

    Note that the binding will fail if the OU string contains characters that need to be escaped for LDAP syntax.


    -- Bill Stewart [Bill_Stewart]

    Friday, June 1, 2018 4:50 PM
  • I tried all syntax's above and same error this time:

    The following exception occurred while retrieving member "Create": "The server is not operational.

    I tried this in CSV:

    "OU=USA\,OU=North America\,DC=resource\,DC=com"

    and also this

    "OU=USA,OU=North America,DC=resource,DC=com"


    Hasan


    Friday, June 1, 2018 5:20 PM
  • Please post the complete error message and the line that caused it.


    \_(ツ)_/

    Friday, June 1, 2018 5:28 PM
  • The commas separating components of a DN do not need to be escaped. Commas within a component do (when using ADSI). For example:

    $OU = "ou=East\, Sales,ou=USA,dc=domain,dc=com"

    Edit: Characters that need to be escaped in components of DN values are:

    , \ # + < > ; " = /

    plus any leading or trailing spaces. But PowerShell AD module cmdlets automatically escape all of these characters, except "/".


    Richard Mueller - MVP Enterprise Mobility (Identity and Access)


    Friday, June 1, 2018 5:38 PM
  • Please post the complete error message and the line that caused it.


    \_(ツ)_/

    The following exception occurred while retrieving member "Create": "The server is not operational.
    "
    At C:\Users\ddd\Desktop\Important Scripts\Create Bulk Mailboxes\CreateNLinkNew06012018.ps1:13 char:5
    +     $user = $ou.Create('User', 'CN=' + $UD.cn)
    +     ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
        + CategoryInfo          : NotSpecified: (:) [], ExtendedTypeSystemException
        + FullyQualifiedErrorId : CatchFromBaseGetMember

    You cannot call a method on a null-valued expression.
    At C:\Users\ddd\Desktop\Important Scripts\Create Bulk Mailboxes\CreateNLinkNew06012018.ps1:15 char:5
    +     $user.Put('givenName',$UD.givenName)        #First Name
    +     ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
        + CategoryInfo          : InvalidOperation: (:) [], RuntimeException
        + FullyQualifiedErrorId : InvokeMethodOnNull

    Hasan

    Friday, June 1, 2018 5:50 PM
  • The commas separating components of a DN do not need to be escaped. Commas within a component do (when using ADSI). For example:

    $OU = "ou=East\, Sales,ou=USA,dc=domain,dc=com"

    Edit: Characters that need to be escaped in components of DN values are:

    , \ # + < > ; " = /

    plus any leading or trailing spaces. But PowerShell AD module cmdlets automatically escape all of these characters, except "/".


    Richard Mueller - MVP Enterprise Mobility (Identity and Access)


    I have whtie space:

    OU=USA,OU=North America,DC=resource,DC=hearstcorp,DC=com


    Hasan

    Friday, June 1, 2018 5:51 PM
  • Embedded spaces, as in "OU=North America" do not need to be escaped.

    Edit: As pointed out, my earlier suggestion was flawed. The parentheses are needed to convert into a string so the "+" operator is available.

    $OU = [ADSI]("LDAP://" + $UD.OU)

    But your error message sounds as if you are not authenticated to AD.


    Richard Mueller - MVP Enterprise Mobility (Identity and Access)




    Friday, June 1, 2018 5:57 PM
  • Embedded spaces, as in "OU=North America" do not need to be escaped.

    Edit: As pointed out, my earlier suggestion was flawed. The parentheses are needed to convert into a string so the "+" operator is available.

    $OU = [ADSI]("LDAP://" + $UD.OU)

    But your error message sounds as if you are not authenticated to AD.


    Richard Mueller - MVP Enterprise Mobility (Identity and Access)




    I am running it onprem using EMS as it is hybrid environment. 

    If i remove OU from spreadsheet and hardcode the OU in script, it works fine so it is not authentication issue right? 


    Hasan

    Friday, June 1, 2018 6:09 PM
  • So is the OU value in the csv quoted? If not, then $UD.OU will be just "OU=USA".

    Richard Mueller - MVP Enterprise Mobility (Identity and Access)

    Friday, June 1, 2018 6:19 PM
  • So is the OU value in the csv quoted? If not, then $UD.OU will be just "OU=USA".

    Richard Mueller - MVP Enterprise Mobility (Identity and Access)

    It is quoted. 

    Hasan

    Friday, June 1, 2018 7:40 PM
  • As you are discovering, it is very difficult and cumbersome to incrementally debug and fix a script remotely from a forum. It just is not interactive enough. It seems apparent that you are making some basic mistakes in syntax or input, but it's difficult to pinpoint because we can't see your screen.

    Instead: We would encourage learning PowerShell basics first; the Learn link at the top of this forum is a helpful resource in that regard.


    -- Bill Stewart [Bill_Stewart]

    Friday, June 1, 2018 8:02 PM
  • There is an issue with adsi.  When retrieving an object using a bad DN ADSI does not throw an exception.  It throws an exception on the first access to the object.

    It is better to guard all calls to prevent run-on errors.

    Pay close attention to the DN in the error message.

    We could also use ADSISEARCHER to validate the OU's DN.

    $UserDetails = Import-Csv 'C:\Users\admin\Desktop\Migration\sing1.csv' #—–importing bulkusers data
    foreach($UD in $UserDetails){
        
        Try{
            $adsisearcher = [adsisearcher]'objectClass=organizationalUnit'
            $adsisearcher.SearchRoot = $UD.OU
            $ou = $adsisearcher.FindOne().GetDirectoryEntry()
            
            $user = $ou.Create('User', 'CN=' + $UD.cn)
    
            $user.Put('givenName',$UD.givenName)
            $user.Put('sAMAccountName',$UD.sAMAccountName)
            $user.Put('userPrincipalName',$UD.userPrincipalName)
            $user.Put('title',$UD.title)
            $user.Put('sn',$UD.sn)
            $user.Put('displayName',$UD.displayName)
            $user.Put('department',$UD.department)
            $user.Put('telephoneNumber',$UD.telephoneNumber)
            $user.Put('co',$UD.co)
            $user.Put('physicalDeliveryOffice',$UD.physicalDeliveryOfficeName)
            $user.Put('st',$UD.st)
            $user.Put('l',$UD.l)
            $user.Put('postalCode',$UD.postalCode)
            $user.Put('company',$UD.company)
            $user.put('mail',$UD.mail)
            
            $user.commitchanges()
            $user.SetPassword('P@ssW0Rd')
        }
        Catch{
             Throw $_ 
        }
        #Enable-Mailbox -identity $ud.displayName –Database 'Mailbox Database 1" -primarysmtpaddress $ud.mail -Alias $ud.alias | get-mailbox | select name,windowsemailaddress,database,alias
    }
    

    The above will throw an exception if the DN of the OU is wrong.  It will not throw a bogus exception on the "Create"  method.


    \_(ツ)_/

    Saturday, June 2, 2018 12:53 AM