AD Attribute changes from CSV file

Welcome Forums General PowerShell Q&A AD Attribute changes from CSV file

This topic contains 19 replies, has 5 voices, and was last updated by

 
Participant
8 months, 2 weeks ago.

  • Author
    Posts
  • #94156

    Participant
    Points: 0
    Rank: Member

    I know this about powershell and CSV files.

    When you import a CSV file you use Import-csv c:\scripts\file.csv
    When you need to work with the columns you use Select-Object to work with the columns headlines.
    Select-Object Headline1,Headline2.

    And the syntax would be like: Import-csv c:\scripts\file.csv | Select-Object Name,Company
    When you create user some attributes are empty, and I need to fill those out automatically since it is being done manually for the time being.

    In my scenario I have a csv file containing 4 rows.
    The first being "Department" This is my unique identifier, that is selected from a dropdownlist, during usercreation.
    This is fine.
    But the other 3 rows needs to be updatet depending on whats in Column1.

    If department is = to HR in column A row 9, then it must look update departmentnumber which is in column B and so on I also have an extra attribute that needs to be updated in Column C, called ExtraAttribute2 in AD.

  • #94179

    Participant
    Points: 14
    Rank: Member

    well you have parts of this correct. there is no reason to use the select-object once you've imported the csv.
    here is a REALLY simple example, assuming you have a column in your csv titled "networkid" which contains the samaccountname of the user to update, and a column titled "company" which has the value you want to set in the company attribute of AD.

    $csv = import-csv c:\scripts\file.csv
    foreach ($user in $csv)
    {
        try
        {
            set-aduser $user.networkid -company $user.company    
        }
        catch
        {
            write-error "ERROR SETTING $($user.networkid)"    
        }
        
    }
    

    we import the csv, then loop through each entry using set-aduser to apply the value.

  • #94183

    Participant
    Points: 0
    Rank: Member

    Hi David
    Thanks for helping.

    The main issue is, that the Username is not present in my csv file.
    Company attribute is already set on the user, but it depends on what value the Department attribute has, in order to update from Column B,C and D.

    I properly need help along the way to make it perfect.
    But lets assume that I have a Username SamSmith – not in the CSV file.
    Then I need to update Departmentnumber based on Department which is pre-filled in AD.
    Department is also Column A in my CSV file.

    Would it then look like I would like to test it on 1 user at a time just so I don't make too many erros 😉

    set-aduser -Identity username $User.Company -Companynumber $user.companynumber

  • #94185

    Participant
    Points: 14
    Rank: Member

    if you don't have the network id in your file, this is going to be very difficult, you would need to try to search active directory with filters.

    you have to provide one of the allowed items for identity. as well, companynumber is not a valid ad attribute.

    so you should break it down into multiple parts, find an ad filter that works to return the appropriate user account, you can then put logic in for your use cases to update companyname.

    until you can identify a single specific user to associate with each entry in your file you won't be able to proceed.

  • #94212

    Participant
    Points: 0
    Rank: Member

    I have thought of the filter process and how to do it.

    My thought is this:
    Find AD user created today:

    $date = (Get-Date ).AddDays(-1)
    Get-ADUser -Filter {whencreated -gt $date} -Properties * | Select-Object Name,WhenCreated

    Yes you you told already I dont't need select-object. But I like to see the result on-screen before proceeding. So please bare with me. 🙂

    1. You take the users created today and put them in a variable.
    2. Import-csv file.
    3. Then you somehow go through the users created today and compare column A TO Department attribute in AD for the users created today
    4. Based on the information in Column A, add departmentnumber from Column B and Extra-attribute from Column C

    I hope you can see where I am going with this?

  • #94216

    Participant
    Points: 14
    Rank: Member

    it is going to be step 3 that will be painful for you.
    unless you have a specific attribute that is 100% unique to each user, you will not really be able to accomplish your task.

  • #94228

    Participant
    Points: -12
    Rank: Member

    Hi

    If you do have something in your CSV file that could identify the correct user then you could use Get-ADUser -Filter {} and find the user and then set the values, but without it I believe it's no can do task.

    Regards

    Jake

  • #94236

    Participant
    Points: 0
    Rank: Member

    So unless I have a username in a column INSIDE the csv file my task can not be accomplished or at least very difficult?

    Even if I as in above example get users created today, and from there get the username?

    Because then I do have the unique id.
    And powershell does treat the content of a csv file as objects? Or did I misunderstand?

    • #94242

      Participant
      Points: -12
      Rank: Member

      Hi

      No you don't need to have username on the csv file, but you'll need something that you can use to find the user. Powershell is using the info/rows from csv as an object.

      Regards

      Jake

  • #94243

    Participant
    Points: 0
    Rank: Member

    So basically I need a unique identifier as in 100% unique IN the csv file in order to continue?

  • #94248

    Participant
    Points: 14
    Rank: Member

    in general yes, you really do need a 100% unique identifier.

    you could write some queries to combine first/last names and other things, but thats not the best idea as names tend to not be unique.

    even something as simple as an email address. but without knowing your user configs or whats actually present in your csv file, its impossible to provide more information

  • #94333

    Participant
    Points: 0
    Rank: Member

    A little update:
    I have just found out that the user will be created before this script is running
    So I do know the username
    For now I am testing with a user called TestUser1.
    The csv file contains the following data:
    Department,Company,Manager
    IT,Blue-42,Awesome
    HR,RED-48,Phantom

    and based on username which I have
    I would like to update company and Manager depending on which department that has already been filled out.
    So AD knows by now, username and Department, but not Company and manager.

    Could you use import-path -ashashtable ?

    And then do an IF Department -eq IT, Company should be Blue-42 and Manager should be Awesome.
    I hope there is an easier method.

    • #94335
      Jon

      Participant
      Points: 25
      Rank: Member

      You will want to organize your csv file like this,

      username, Department,Company,Manager
      testuser1,it, blue-42, awesome
      testuser2, HR,RED-48,Phantom

      then

      import-csv C:\user.csv | % {set-aduser -identity $_.username -department $_.department -company $_.company -manager $_.manager}
  • #94341

    Participant
    Points: 0
    Rank: Member

    Am I back to step 1, if I say that the username will not be a part of the imported file.
    But resides outside the imported file?
    Meaning I won't have have a column called Username.

    • #94344
      Jon

      Participant
      Points: 25
      Rank: Member

      You don't want it outside the imported file. Why can't you put it in the file?

  • #94347

    Participant
    Points: 13
    Rank: Member

    If the department only has one manager and the department is unique and everyone in that department reports to this manager you could do something like this. The last bit just outputs to the console what you have just changed so you can sanity check it. You could always set the country as well.

    Get-Aduser -filter "Department -eq 'My Department'" | Set-ADuser -Manager DaBoss -passthru | Get-Aduser -Properties Title,Manager | Select Name,Title,Manager

  • #94348

    Participant
    Points: 0
    Rank: Member

    @Jon
    I cant because the csv file is predefined. Where Department is the only Unique parametre to look up.

    And my script must be based on Department somehow to fill out the rest.

    @simon B
    I like the way you think I will need to check the Manager attribute.
    I always lije to sanity check my changes.
    But I still need the file to go from.
    Could I go the Long way around and type in all departments and there managers?
    In some hash table?

  • #94351

    Participant
    Points: 13
    Rank: Member

    Does this get you any closer ??

    $Departments = import-csv C:\HR\HR.csv
    foreach ($Department in $Departments){
    Get-Aduser -filter "Department -eq $($Department.Department)" | Set-ADuser -Manager $($Department.Manager) -passthru | Get-Aduser -Properties Title,Manager | Select Name,Title,Manager
    }

  • #94353

    Participant
    Points: 0
    Rank: Member

    I will test it later this evening and update you on the progress.

  • #95092

    Participant
    Points: 0
    Rank: Member

    @ Simon B
    When runnning you suggested command I got the following error message:

    Get-Aduser : Error parsing query: 'Department -eq ' Error Message: 'syntax error' at position: '12'.
    At line:3 char:1
    + Get-Aduser -filter "Department -eq $($Department.Department)" | Set-A ...
    + ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
    + CategoryInfo : ParserError: (:) [Get-ADUser], ADFilterParsingException
    + FullyQualifiedErrorId : ActiveDirectoryCmdlet:Microsoft.ActiveDirectory.Management.ADFilterParsingException,Microsoft.ActiveDirectory.Management.Commands.GetADUser

    Get-Aduser : Error parsing query: 'Department -eq ' Error Message: 'syntax error' at position: '12'.
    At line:3 char:1
    + Get-Aduser -filter "Department -eq $($Department.Department)" | Set-A ...
    + ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
    + CategoryInfo : ParserError: (:) [Get-ADUser], ADFilterParsingException
    + FullyQualifiedErrorId : ActiveDirectoryCmdlet:Microsoft.ActiveDirectory.Management.ADFilterParsingException,Microsoft.ActiveDirectory.Management.Commands.GetADUser

The topic ‘AD Attribute changes from CSV file’ is closed to new replies.