locked
AD attribute update script RRS feed

  • Question

  • Hey all, 

    New to the forum and very new to powershell. I have an excel sheet that has names (formatted to displayname) and ID's associated to each name. What I want to be able to do is run a script to compare the displayname in the excel file to what the display name is in active directory then have it update an attribute field using the ID associated to that name. Is this possible? Do I need to convert the xlsx file to a csv as well?

    Tuesday, October 29, 2019 8:26 PM

Answers

All replies

  • Step 1 would be to export the spreadsheet file as a csv file.

    Then you can use the AD cmdlets to update based on the csv file.


    -- Bill Stewart [Bill_Stewart]

    Tuesday, October 29, 2019 8:29 PM
  • Do you know what cmdlets I would need after I export as a csv file?
    Wednesday, October 30, 2019 1:52 PM
  • It sounds like you need to start with some fundamentals.

    One way you could start is by following the Learn link at the top of this forum.

    Also read the very first post:

    This forum is for scripting questions rather than script requests


    -- Bill Stewart [Bill_Stewart]

    • Marked as answer by Bill_Stewart Monday, November 25, 2019 3:05 PM
    Wednesday, October 30, 2019 2:14 PM
  • Got it. Thank you.
    Wednesday, October 30, 2019 2:17 PM
  • First you need to export the users form AD , this will be the "Master" file , make a copy of it and work on this "Copy of master".

    Export all Users from AD

    Get-ADUser -Filter * -Properties * | export-csv D:\FullADusers.csv -Encoding UTF8 -NoTypeInformation

    Export users in a specific OU

    Get-ADUser -SearchBase "OU=HR,DC=MyComp,DC=com" -Filter * -Properties SamAccountName, GivenName, Surname, EmailAddress | export-csv D:\HRADusers.csv -Encoding UTF8 -NoTypeInformation


    Export Members of a AD Group (Users)

    Get-ADGroupMember -identity "HRUsersGroup" | Get-ADUser -Properties name, SamAccountName, GivenName, Surname, EmailAddress, Department, Title, OfficePhone, MobilePhone | export-csv t:\HRUsersGroup.csv -Encoding UTF8 -NoTypeInformation

    Lets say you made changes on the "MobilePhone" field in the CSV and you want to sync the changes to AD, you could run :

    ForEach ($user in (import-csv -path "C:\Scripts\AdUsers.csv"))
    {
      Get-AdUser -Identity $user.SamAccountName | set-aduser -replace @{MobilePhone=$user.MobilePhone}
    }

    Wednesday, October 30, 2019 3:00 PM
  • First you need to export the users form AD , this will be the "Master" file , make a copy of it and work on this "Copy of master".

    Export all Users from AD

    Get-ADUser -Filter * -Properties * | export-csv D:\FullADusers.csv -Encoding UTF8 -NoTypeInformation

    Export users in a specific OU

    Get-ADUser -SearchBase "OU=HR,DC=MyComp,DC=com" -Filter * -Properties SamAccountName, GivenName, Surname, EmailAddress | export-csv D:\HRADusers.csv -Encoding UTF8 -NoTypeInformation


    Export Members of a AD Group (Users)

    Get-ADGroupMember -identity "HRUsersGroup" | Get-ADUser -Properties name, SamAccountName, GivenName, Surname, EmailAddress, Department, Title, OfficePhone, MobilePhone | export-csv t:\HRUsersGroup.csv -Encoding UTF8 -NoTypeInformation

    Lets say you made changes on the "MobilePhone" field in the CSV and you want to sync the changes to AD, you could run :

    ForEach ($user in (import-csv -path "C:\Scripts\AdUsers.csv"))
    {
      Get-AdUser -Identity $user.SamAccountName | set-aduser -replace @{MobilePhone=$user.MobilePhone}
    }

    Unfortunately this has nothing to do with the original question.  Exporting to a Csv does nothing useful and will create a very large and likely broken Csv in larger domains.  It also doesn't obtain the new info from the OP's Excel file.

    The user wants to find users based on "DisplayName".  This is not a unique item and can cause issues.  There can be multiple matches which will cause multiple users to have the same attribute with the required ID.  I don't think this is what the user wants.  The request is also a bit vague as to the intended purpose for this.

    Learning how to use both PowerShell and AD are critical prior to attempting batch updates to AD to prevent unrepairable disaster from happening.


    \_(ツ)_/

    Wednesday, October 30, 2019 4:31 PM
  • Do you know what cmdlets I would need after I export as a csv file?

    help Import-CSv -online
    help foreach-object -online

    I recommend following Bill's suggestion that you learn basic PowerShell before continuing. It is easy and will help you to move forward with PowerShell. It can also help keep your AD safe.


    \_(ツ)_/

    Wednesday, October 30, 2019 4:37 PM
  • I entirely agree.

    Do not, under any circumstances, make any bulk changes in AD using a PowerShell script unless you have proper training and understanding. Learn PowerShell first. Set up a test AD environment where you can experiment with code and not break a production instance.


    -- Bill Stewart [Bill_Stewart]

    Wednesday, October 30, 2019 4:45 PM