Help Iterating Through .CSV File to Update AD

This topic contains 15 replies, has 4 voices, and was last updated by Profile photo of Mike F Robbins Mike F Robbins 2 years, 3 months ago.

  • Author
    Posts
  • #18050
    Profile photo of Allen Rohl
    Allen Rohl
    Participant

    My HR department wants to update AD with some basic information including Manger and Assistant. They are providing a .csv file that contains a 10 digit employee id number to identify employess, their managers and assistants (if they have any). I need to get the users' names, managers' names and assistants' names from AD using the employee id and then set the manager and assistant name for each user.

    I'm using activeroles quest active directory management (free) to connect to get users and set information.

    I'm able to do it with a test .csv file that contains one user record with the following script code:

    $OU = "myorg.com/Users and Workstations"
    $file = Import-csv -path E:\data\powershell\Excel\Mockup_HR_EMPLID_EXP2a.csv -Delimiter "|"
    $user = $file | Foreach-Object {Get-QADUser -SearchRoot $OU -ObjectAttributes @{employeeID=$_.EmployeeID}}
    $manager = $file | Foreach-Object {Get-QADUser -SearchRoot $OU -ObjectAttributes @{employeeID=$_.manager}}
    $assistant = $file | Foreach-Object {Get-QADUser -SearchRoot $OU -ObjectAttributes @{employeeID=$_.assistant}}
    Get-QADUser -Identity $user | Set-QADUser -Manager $manager -ObjectAttributes @{Assistant=$assistant}}

    I tried it using a file with 2 user records by modifying my code:

    $OU = "myorg.com/Users and Workstations"
    $file = Import-csv -path E:\data\powershell\Excel\Mockup_HR_EMPLID_EXP2a.csv -Delimiter "|"
    $user = $file | Foreach-Object {Get-QADUser -SearchRoot $OU -Sizelimit 0 -ObjectAttributes @{employeeID=$_.EmployeeID}}
    $manager = $file | Foreach-Object {Get-QADUser -SearchRoot $OU -Sizelimit 0 -ObjectAttributes @{employeeID=$_.manager}}
    $assistant = $file | Foreach-Object {Get-QADUser -SearchRoot $OU -Sizelimit 0 -ObjectAttributes @{employeeID=$_.assistant}}
    foreach ($user in $file) {
    Get-QADUser -Identity $user | Set-QADUser -Manager $manager -ObjectAttributes @{Assistant=$assistant}}

    But I receive an error that the users in the file cannot bind to the $user identity. so, I believe, it's due to the fact that I am unsure how to do it for multiple users and the variables. Any help is greatly appreciated!

  • #18052
    Profile photo of Don Jones
    Don Jones
    Keymaster

    I think you might have an easier time building this more as a script than as a one-liner; at least, it's easier for me to approach it that way.

    $records = Import-CSV filename.csv
    foreach ($record in $records) {
      $user = Get-QADUser -SearchRoot $OU -ObjectAttributes @{employeeid=$record.employeeid}
    }

    I realize that isn't terribly different, but it gets you down to working with one file entry at a time. What you're doing with the multiple ForEach-Object calls is not, I don't think, going to get you useful data. It's entirely possible that $user is null, or that it is an array of multiple objects. Also, I'm not sure that -Manager and Assistant will accept a complete user object as their values – but that's what you're passing. I think those may just need a CN, which you could get as a property of the respective user objects.

    You didn't post the specific error message, and I don't have a sample of your file to look at. For example, in this case, I'd probably put a debug breakpoint after $user gets set. That would let me test the value of $user to make sure it's what I wanted. If it wasn't, I could execute Get-QADUser manually to see more detail about what the problem was, perhaps. Youc an set breakpoints in the ISE.

    • #18069
      Profile photo of Allen Rohl
      Allen Rohl
      Participant

      Don,

      Thanks for the assistance. I attached a sample of the file. There's other headers there but I'm not concerned about them.

      What I'm trying to do is find the username in AD, the manager and assistant from the employeeid. I then hold those names in a variable and use the set command to apply the discovered names that were once employee ids. It works with one user/record but will not apply with more than one user/record in the file.

      So, to recap, I need to take each employee in the row, find their user information based on employeeid, find their manager and assistant baed on employeeid, keed the user information linked to the proper manager name and assistant name that were found and then add the manager name and assistant name to the proper user.

  • #18058
    Profile photo of Mike F Robbins
    Mike F Robbins
    Participant

    It's been a while since I've worked with the Quest cmdlets for Active Directory so let me first start out by asking what OS your Active Directory domain controllers are running? Just want to make sure that using the Quest AD cmdlets are necessary.

    Not sure what field in AD that your putting the assistant information in so I omitted that part, but if you do indeed have to use the Quest cmdlets, something like this should work:

    Add-PSSnapin -Name Quest.ActiveRoles.ADManagement
    Import-Csv -Path c:\tmp\mockup.csv -Delimiter '|' |
    ForEach-Object {
        Set-QADUser -Identity $_.employeeID -Manager $_.manager
    }
    

    This is based on a csv file that looks like this:

    EmployeeID| Manager| Assistant
    testemployee1| testmanager1| testassistant1
    testemployee2| testmanager2| testassistant2
    
  • #18059
    Profile photo of Mike F Robbins
    Mike F Robbins
    Participant

    I have to give you credit. This was a lot more complicated than I originally thought. Either one of these should work depending on if you want to use the foreach scripting construct or the ForEach-Object cmdlet.

    Add-PSSnapin -Name Quest.ActiveRoles.ADManagement
    $OU = 'OU=Quest,OU=Users,OU=Test,DC=mikefrobbins,DC=com'
    Import-Csv -Path c:\tmp\mockup.csv -Delimiter '|' |
    ForEach-Object {
        Get-QADUser -SearchRoot $OU -Sizelimit 0 -ObjectAttributes @{employeeID=$_.EmployeeID} |
        Set-QADUser -Manager (Get-QADUser -SearchRoot $OU -Sizelimit 0 -ObjectAttributes @{employeeID=$_.manager}
        ) -ObjectAttributes @{Assistant=(Get-QADUser -SearchRoot $OU -Sizelimit 0 -ObjectAttributes @{employeeID=$_.assistant})}
    }
    
    Add-PSSnapin -Name Quest.ActiveRoles.ADManagement
    $OU = 'OU=Quest,OU=Users,OU=Test,DC=mikefrobbins,DC=com'
    $userinfo = Import-csv -path c:\tmp\mockup.csv -Delimiter '|'
    foreach ($user in $userinfo) {
        Get-QADUser -SearchRoot $OU -Sizelimit 0 -ObjectAttributes @{employeeID=$user.EmployeeID} |
        Set-QADUser -Manager (Get-QADUser -SearchRoot $OU -Sizelimit 0 -ObjectAttributes @{employeeID=$user.manager}
        ) -ObjectAttributes @{Assistant=(Get-QADUser -SearchRoot $OU -Sizelimit 0 -ObjectAttributes @{employeeID=$user.assistant})}
    }
    

    Both the manager and assistant properties require a Distinguished Name:

    [img]http://mikefrobbins.com/wp-content/uploads/2014/08/assistant.png[/img]

    Funny thing is the Assistant property doesn't even show up in PowerShell as an option until you actually set it's value which is what lead me to the Active Directory schema MMC which is where that image is from (you know you're getting deep when you start digging around in the AD schema).

    The code above is based on a csv with the following format where the numbers are the value of an AD users EmployeeID property.

    EmployeeID| Manager| Assistant
    1111| 2222| 3333
    4444| 5555| 6666
    
    • #18071
      Profile photo of Allen Rohl
      Allen Rohl
      Participant

      Mike,

      Thanks for the reply. I tried the "foreach-object" code and received the following error:

      Set-QADUser : Cannot convert 'System.Object[]' to the type 'Quest.ActiveRoles.ArsPowerShellSnapIn.Data.IdentityParameter' required by parameter 'Manager'. Specified method is not supported.
      At line:6 char:26
      + Set-QADUser -Manager <<<< (Get-QADUser -SearchRoot $OU -Sizelimit 0 -ObjectAttributes @{employeeID=$_.manager} + CategoryInfo : InvalidArgument: (:) [Set-QADUser], ParameterBindingException + FullyQualifiedErrorId : CannotConvertArgument,Quest.ActiveRoles.ArsPowerShellSnapIn.Powershell.Cmdlets.SetUserCmdlet I'm pretty sure it's due to the fact that $manager.managerid returns the numeric employeeid for the manager rather than the name. That's where $manager = $file | Foreach-Object {Get-QADUser -SearchRoot $OU -Sizelimit 0 -ObjectAttributes @{employeeID=$_.manager}} in my original attempt helps. The $manager holds the found manager name and then can be used to import that into the -Manager field. When it imports using the $manager data it does import as a DN. So, I need to do the following for each record: Find username based on employeeid Find manager based on employeeid Find assistant based on employeeid Set username (found in step one) with the manager name and assistant name found in steps 2 & 3 for each row in our file.

  • #18073
    Profile photo of Simon Wåhlin
    Simon Wåhlin
    Participant

    I created an InputFile that looks like this:

    "EmployeeID"|"Assistant"|"Manager"
    "1"|"2"|"3"
    "2"|""|"1"
    "3"|"5"|"6"
    "4"|"5"|"6"
    "5"|"6"|""
    "6"|"7"|""
    "7"|""|"6"
    

    Assuming that more than one person shares the same manager and that an assistant can assist more than one person, it is likely that the same ID will show up in the inputfile more than once.

    To minimize the number of queries to Active Directory, I first extract each unique ID in the input-file and store these in $AllUserIDs.

    For each unique ID found I query Active Directory for a distinguished name and add that value to a hashtable where EmployeeID is the key.

    Now just import the csv again and loop through each row setting the attributes assistant and manager by using references to the hashtable.

    Add-PSSnapin -Name Quest.ActiveRoles.ADManagement
    $OU = "OU=Staff,DC=simon,DC=lab,DC=lcl"
    $InputFile = '.\InputFile.csv'
    $AllUserIDs = Import-Csv -Delimiter '|' -Path $InputFile | 
        ForEach-Object {
            $_.EmployeeID
            $_.Assistant
            $_.Manager
        } | Where-Object {$_ -ne $null} | Sort-Object -Unique
    
    $AllUsers = @{}
    Foreach($UserID in $AllUserIDs)
    {
        $DN = Get-QADUser -SearchRoot $OU -Sizelimit 0 -ObjectAttributes @{employeeID=$UserID} | 
            Select-Object -ExpandProperty DN
        $AllUsers.Add($UserID,$DN)
    }
    
    Import-Csv -Delimiter '|' -Path $InputFile | Foreach {
        Set-QADUser -Identity $AllUsers["$($_.EmployeeID)"] -Manager $AllUsers["$($_.Manager)"] -ObjectAttributes @{Assistant=$AllUsers["$($_.Assistant)"]}
    }
    

    I tried to utilize the pipeline as much as possible to lessen the amount of memory used if the csv-file is huge. For smaller files it might be faster to store the imported csv in a variable and using Foreach instead of Foreach-Object.

    • #18100
      Profile photo of Allen Rohl
      Allen Rohl
      Participant

      Hi Simon,

      Thanks, so much! I modified your code to account for the path to my file (see below). When it runs, I get a "path too long" exception seemingly at the @[] location (see below):

      $InputFile = Import-csv -path E:\data\powershell\Mockup_HR_EMPLID_EXP2a.csv -Delimiter "|"
      $AllUserIDs = Import-csv -path E:\data\powershell\Mockup_HR_EMPLID_EXP2a.csv -Delimiter "|" |
      ForEach-Object {
      $_.EmployeeID
      $_.Assistant
      $_.Manager
      } | Where-Object {$_ -ne $null} | Sort-Object -Unique

      $AllUsers = @{}
      Foreach($UserID in $AllUserIDs)
      {
      $DN = Get-QADUser -SearchRoot $OU -Sizelimit 0 -ObjectAttributes @{employeeID=$UserID} |
      Select-Object -ExpandProperty DN
      $AllUsers.Add($UserID,$DN)
      }

      Import-Csv -Delimiter '|' -Path $InputFile | Foreach {
      Set-QADUser -Identity $AllUsers["$($_.UserID)"] -Manager $AllUsers["$($_.Manager)"] -ObjectAttributes @{Assistant=$AllUsers["$($_.Assistant)"]}
      }

      [b]Error:[/b]

      Import-Csv : Cannot open file "C:\WINDOWS\system32\WindowsPowerShell\v1.0\@{LastName=SMITH; FirstName=ALLEN; Office=NEW YORK; Employeeid=0005002174; BusinessTitle=Senior Engineer, Distributed Infrastructure; CoreSect
      or=#; Sector=#; CmbCorePractice=#; CmbPractice=#; Region=United States; Committees=#; SectorLeaderRole=#; PracticeLeaderRole=#; AreaCountryRole=#; FirmLeaderShipRoleOne=#; FirmLeaderShipRole=#; generationQualifier=M
      r.; givenName=Allen ; middleName=#; sn=SMITH; extensionAttribute12=#; homePostalAddress=Test1 Road United States of America; telephoneNumber=+1 (212) 711-2222; ipPhone=112222; facsmileTelephoneNumber=+1 (212) 351-21
      74; mobile=+1 (212) 351-2174; homePhone=+1 (111) 222-3333; department=Information Systems; manager =0005000561 ; assistant=0002001609 }".
      At line:16 char:11
      + Import-Csv <<<< -Delimiter '|' -Path $InputFile | Foreach { + CategoryInfo : OpenError: (:) [Import-Csv], PathTooLongException + FullyQualifiedErrorId : FileOpenFailure,Microsoft.PowerShell.Commands.ImportCsvCommand

  • #18102
    Profile photo of Simon Wåhlin
    Simon Wåhlin
    Participant

    You are not supposed to add Import-CSV to the command $InputFile = "

    Let's try again, copy this code and only edit line two and three to reflect the Organisational Unit in Active Directory to use as a searchbase and the path to your inputfile.

    # Edit the two rows below:
    $OU = ""
    $InputFile = 'E:\data\powershell\Mockup_HR_EMPLID_EXP2a.csv'
    
    # Do not edit anyghing below this line
    Add-PSSnapin -Name Quest.ActiveRoles.ADManagement
    $AllUserIDs = Import-Csv -Delimiter '|' -Path $InputFile | 
        ForEach-Object {
            $_.EmployeeID
            $_.Assistant
            $_.Manager
        } | Where-Object {$_ -ne $null} | Sort-Object -Unique
     
    $AllUsers = @{}
    Foreach($UserID in $AllUserIDs)
    {
        $DN = Get-QADUser -SearchRoot $OU -Sizelimit 0 -ObjectAttributes @{employeeID=$UserID} | 
            Select-Object -ExpandProperty DN
        $AllUsers.Add($UserID,$DN)
    }
     
    Import-Csv -Delimiter '|' -Path $InputFile | Foreach {
        Set-QADUser -Identity $AllUsers["$($_.EmployeeID)"] -Manager $AllUsers["$($_.Manager)"] -ObjectAttributes @{Assistant=$AllUsers["$($_.Assistant)"]}
    }
    
  • #18109
    Profile photo of Allen Rohl
    Allen Rohl
    Participant

    Thank you soo much! That worked in as far as the manager field updated but not the assistant field! Are you able to describe what is going one line by line or at least let me know how to set debug points in the powershell ISE so I can see it?

  • #18112
    Profile photo of Mike F Robbins
    Mike F Robbins
    Participant

    Sounds like you have a solution. I tweaked one of the ones I created based on the errors you reported and thought I would post it in case someone else comes across this thread. This version accounts for no entry in the manager and/or assistant fields:

    Add-PSSnapin -Name Quest.ActiveRoles.ADManagement
    $OU = 'OU=Quest,OU=Users,OU=Test,DC=mikefrobbins,DC=com'
    $userinfo = Import-csv -path c:\tmp\mockup.csv -Delimiter '|'
    
    foreach ($user in $userinfo) {
        $Params = @{}
    
        if ($user.manager) {
            $Params.manager = Get-QADUser -SearchRoot $OU -Sizelimit 0 -ObjectAttributes @{employeeID=$user.manager} |
                              Select-Object -ExpandProperty DN
        }
    
        if ($user.assistant) {
            $assistant = Get-QADUser -SearchRoot $OU -Sizelimit 0 -ObjectAttributes @{employeeID=$user.assistant} |
                         Select-Object -ExpandProperty DN
            $Params.ObjectAttributes = @{assistant=$assistant}
        }
    
    
        Get-QADUser -SearchRoot $OU -Sizelimit 0 -ObjectAttributes @{employeeID=$user.EmployeeID} |
        Set-QADUser @Params
    }
    

    I also tested this version of the script all the way back to PowerShell version 2 since I wasn't sure what version you were running.

    • #18188
      Profile photo of Allen Rohl
      Allen Rohl
      Participant

      Mike,

      Thanks so much! That works as well.

      What I'm trying to figure out now is how to incorporate basic ad fields that need to be set from the .csv file into this script you created. For example, ipPhone is a column in my .csv file and the information is there. I don't have to convert it to a DN, obviously, like the manager or assistant field. I'm looking to mimic the paramaters you set for $manager and $assistant to user for ipPhone or sn but it's not the same procedure.

      Also, if you don't mind, what accounts for this line: $Params.ObjectAttributes = @{assistant=$assistant}? Why is that there and there isn't a similar line for the manager parameter?

      thanks, again! This has been a great learning experience.

    • #18473
      Profile photo of Allen Rohl
      Allen Rohl
      Participant

      Mike,

      Thanks, again! Does "if ($user.manager)" check the information is present for manager in the .csv file?

  • #18121
    Profile photo of Simon Wåhlin
    Simon Wåhlin
    Participant

    Mikes script above will do the trick, I realize now that my approach might have gotten a bit complex.

    But for an educational purpose, I've commented the code and added some verbose output, hope it helps.

    [Cmdletbinding()]
    Param(
        # Set OU to the distinguished name of the search root
        $OU = "OU=Staff,DC=simonw,DC=se",
        
        # Defines the path to CSV input-file
        $InputFile = 'E:\data\powershell\Mockup_HR_EMPLID_EXP2a.csv',
        
        # Defines char used as a delimiter in the CSV
        $Delimiter = '|',
        
        # Set EmployeeID to the name of the column containing EmployeeID in CSV
        $EmployeeID = 'EmployeeID',
        
        # Set Manager to the name of the column containing Manager reference in CSV
        $Manager = 'Manager',
        
        # Set Assistant to the name of the column containing Assistant reference in CSV
        $Assistant = 'Assistant'
    )
    
    # Sets VerbosePreference to Continue to make sure verbose output is always shown
    # Remove this line once satisfied with the script
    $VerbosePreference = 'Continue'
    
    ########################################
    # Do not edit anything below this line #
    ########################################
    Add-PSSnapin -Name Quest.ActiveRoles.ADManagement
    # This just gives us a list of any employeeID referenced in the csv-file
    $AllUserIDs = Import-Csv -Delimiter $Delimiter -Path $InputFile | 
        ForEach-Object {
            $_.$EmployeeID  
            $_.$Assistant   
            $_.$Manager     
        } | Where-Object {$_ -ne $null} | Sort-Object -Unique
    
    # This creates an empty hashtable (read more on hashtables using Get-Help about_Hash_Tables
    $AllUsers = @{}
    
    # Loop through each EmployeeID found in the CSV
    Foreach($UserID in $AllUserIDs)
    {
        # Retrieve the distinguishedName for each user referenced in CSV
        $DN = Get-QADUser -SearchRoot $OU -Sizelimit 0 -ObjectAttributes @{employeeID=$UserID} | 
            Select-Object -ExpandProperty DN
        # Add an entry to the hashtable (EmployeeID is the Key and the DistinguishedName the Value)
        $AllUsers.Add($UserID,$DN)
    }
     
    # Now we can translate any EmployeeID in the CSV to a distinguished name. 
    # For example if an emploeeID is 123456 this will return that users distinguished name:
    # $AllUsers["123456"]
    
    # Import the CSV-file again and run Set-QADUser once for each row.
    Import-Csv -Delimiter $Delimiter -Path $InputFile | Foreach {
        # output some verbose messages
        Write-Verbose -Message "Processing EmployeeID $($_.EmployeeID):"
        Write-Verbose -Message "[$($_.$EmployeeID)] ID translates to DN:       $($AllUsers["$($_.$EmployeeID)"])"
        Write-Verbose -Message "[$($_.$EmployeeID)] Manager ID is:             $($_.$Manager)"
        Write-Verbose -Message "[$($_.$EmployeeID)] Manager translates to DN:  $($AllUsers["$($_.$Manager)"])"
        Write-Verbose -Message "[$($_.$EmployeeID)] Assitant ID is:            $($_.$Assistant)"
        Write-Verbose -Message "[$($_.$EmployeeID)] Assitant translates to DN: $($AllUsers["$($_.$Assistant)"])"
    
        # Update user in AD and suppress the output
        Set-QADUser -Identity $AllUsers["$($_.$EmployeeID)"] -Manager $AllUsers["$($_.$Manager)"] -ObjectAttributes @{Assistant=$AllUsers["$($_.$Assistant)"]} -Verbose:$false | 
            Out-Null
    
        # Output a Verbose message with two blank lines in the end to make it easier to read
        Write-Verbose -Message "Done processing EmployeeID $($_.$EmployeeID)`n`n"
    }
    
    • #18189
      Profile photo of Allen Rohl
      Allen Rohl
      Participant

      Thank you Simon! I appreciate all your help and effort. This has been a great learning experience for me!

  • #18475
    Profile photo of Mike F Robbins
    Mike F Robbins
    Participant

    More or less, yes. By the time that portion of the code occurs, the CSV has been imported and is converted to objects. It checks to see if that particular user's manager is populated and only executes the code in the if statement if it is (if it's true).

You must be logged in to reply to this topic.