Forum Discussion

Baron164's avatar
Baron164
Brass Contributor
Jun 24, 2022

Need help importing user data from csv

I need to fill in the Manager Attribute for employee accounts using a CSV export from HR. I have the added bonus of the fact that SamAccountNames do not follow a consistent naming convention. So I need to use the EmployeeID attribute for finding accounts.

Here is an example of the CSV. 

 

"LAST_NAME","FIRST_NAME","EmployeeID","SupervisorID"
"Doe","John","12345","13324"
"Jane","Laura","13456","3455"
"Mclane","John","12351","11331"

 

In this case, John Doe's Manager has an EmployeeID of 13324.
So I need to search AD for a User object with the EmployeeID attribute of 13324.

Then I need to pull the DistinguishedName attribute from that User Object, for example "CN=LSkywalker,OU=Department,DC=contoso,DC=com"
Then I'd have to copy that data into the Manager attribute of John Doe's account.

I've been trying to outline the script and so far this is what I have.

Variables
$User
$SupervisorID
$EmployeeID
$DistinguishedName

For each $User in CSV find $EmployeeID and $SupervisorID
Search AD for Supervisor Account where EmployeeID attribute = $SupervisorID
Pull $DistinguishedName Attribute value from Supervisor's account
Input $DistinguishedName value into original $EmployeeID's Manager Attribute
Proceed to next line of CSV and repeat

Hopefully I've explained the situation well and someone can help me flesh this out more, any help would be greatly appreciated, thank you.


  • LainRobertson's avatar
    LainRobertson
    Jun 28, 2022

    Harm_Veenstra 

     

    Hey, Harm.

     

    I'd strongly recommend using the -Filter parameter in the Get-AD* calls, as you're current pulling every single user in Active Directory client-side before filtering anything - twice.

     

    In smaller environments, that won't be an issue, but it'll run into scalability issues rather quickly.

     

    Using your original example, something like this would be more efficient in medium to large directories:

     

    $csv = import-csv c:\scripts\accounts.csv
    foreach ($line in $csv) {
        $user = Get-ADUser -Filter "employeeID -eq '$($line.EmployeeID)'";          # Here, we're now leveraging server-side filtering.
        $manager = Get-ADUser -Filter "employeeID -eq '$($line.SupervisorID)'";     # And again here.
    
        if ($user -is [Microsoft.ActiveDirectory.Management.ADUser] -and $manager -is [Microsoft.ActiveDirectory.Management.ADUser])
        {
            # We only want to be in here where we got precisely one user and one manager, otherwise we have no way to arbitrate who gets updated with what.
            Set-ADUser $user -Manager $manager
        }
    }

     

    Cheers,

    Lain

  • Baron164 I've just tested this in my test DC and this should work:

     

    $csv = import-csv c:\scripts\accounts.csv
    foreach ($line in $csv) {
        $user = Get-ADUser -Filter * -Properties EmployeeID | Where-Object EmployeeID -eq $line.EmployeeID
        $manager = Get-ADUser -Filter * -Properties EmployeeID | Where-Object EmployeeID -eq $line.SupervisorID
        Set-ADUser $user -Manager $manager
    }

     

    together with this CSV file

    "LAST_NAME","FIRST_NAME","EmployeeID","SupervisorID"
    "User1","Test","1","3"
    "User2","Test","2","3"

     

    Which results into this (Test User2 as example having Test user 3 as manager now )

     

     

    • LainRobertson's avatar
      LainRobertson
      Silver Contributor

      Harm_Veenstra 

       

      Hey, Harm.

       

      I'd strongly recommend using the -Filter parameter in the Get-AD* calls, as you're current pulling every single user in Active Directory client-side before filtering anything - twice.

       

      In smaller environments, that won't be an issue, but it'll run into scalability issues rather quickly.

       

      Using your original example, something like this would be more efficient in medium to large directories:

       

      $csv = import-csv c:\scripts\accounts.csv
      foreach ($line in $csv) {
          $user = Get-ADUser -Filter "employeeID -eq '$($line.EmployeeID)'";          # Here, we're now leveraging server-side filtering.
          $manager = Get-ADUser -Filter "employeeID -eq '$($line.SupervisorID)'";     # And again here.
      
          if ($user -is [Microsoft.ActiveDirectory.Management.ADUser] -and $manager -is [Microsoft.ActiveDirectory.Management.ADUser])
          {
              # We only want to be in here where we got precisely one user and one manager, otherwise we have no way to arbitrate who gets updated with what.
              Set-ADUser $user -Manager $manager
          }
      }

       

      Cheers,

      Lain

      • Harm_Veenstra's avatar
        Harm_Veenstra
        MVP
        I did think about that, but thought that I couldn't use the Filter command on that 🙂 But this is better for larger environments with more objects of course
    • Baron164's avatar
      Baron164
      Brass Contributor

      Harm_Veenstra Thank you, that is very helpful. It's looks to be working, I see the manager information being filled in. The only issue I see is that the script spits out an error each time it runs the "Set-ADUser" line.


      I tried changing the line to "Set-ADUser -Identity $user but that did not make a difference.

      • LainRobertson's avatar
        LainRobertson
        Silver Contributor

        Baron164 

         

        Noting the error and cross-referencing it with the commandlet help (I don't work much with these commandlets in preference for either ADSI-native or Get-ADObject if I'm doing something ad hoc), you could try the following.

         

        Set-ADUser (ActiveDirectory) | Microsoft Docs

         

        The commandlet help is confusing me a bit because the type is [ADUser] - which is precisely what Harm has provided, yet the help on the -Identity and -Manager parameters speaks to things like objectGUID (my go-to attribute for almost everything), distinguished name, SID or sAMAccountName - clearly none of which are the full [ADUser] type.

         

        I guess I could test to remove the ambiguity but I'm taking the lazy approach in the example below and just using the objectGUID from the $user and $manager objects.

         

        Set-ADUser -Identity ($user.objectGUID) -Manager ($manager.objectGUID)

         

        Hopefully something here or from the replies above resolves your issue.

         

        Cheers,

        Lain

         

        Edited to include the commandlet link, which I forgot first time around.

Resources