locked
How to write a script to update AD Attribute " otherTelephone " with CSV file? RRS feed

  • Question

  • I have a CSV file contains the information below. I just take a few of examples.

    EMPLOYEEID WORKLANDLINE
    138 +1 (512) 7284071
    147 +1 (512) 7287269
    182 +1 (512) 7285688
    187 +1 (512) 7283613
    202 +1 (512) 5132257
    233 +1 (512) 7285549
    235 +1 (512) 7288711
    259 +1 (512) 7283881
    264 +1 (512) 7283684

    Where " WORKLANDLINE " going to update at AD Attribute " otherTelephone "

    But all these data including 4 different domains. So should I ask user to create 4 different CSV file for different domain?


    Friday, October 12, 2018 3:20 AM

All replies

  • So to answer your direct question you could either split them into 4 csv's or you could add another column that includes a servername on the domain that you want to hit. So long as the account you are running the script as can update users in all domains then you should be good. If not then split them up or save 4 sets of credentials.

    Also if you already have items in the otherTelephone field please make sure you grab, add, replace. e.g.

    Import-Csv -Path "<<Some path.csv>>" | % {
    $ADaccount = Get-ADUser -filter {EmployeeID -eq $_.EMPLOYEEID} -Property othertelephone -server $_.SERVER
    $ot = @( $ADaccount.othertelephone)
    $ot += $_.WORKLANDLINE
    Set-ADUser $ADaccount.Samaccountname -replace @{othertelephone=$ot} -Server $_.SERVER
    }

    Otherwise you will end up wiping what's already there.

    Also I would highly suggest extensive error handling, particularly if you are doing 4 domains in one script.


    Friday, October 12, 2018 5:00 AM
  • Thanks for your script!

    I have a question, the -server at the script there is for 4 individual set, correct?

    If I would like to add another column that includes a servername on the domain, that script how should I write to add it at where?

    Friday, October 12, 2018 6:06 AM
  • Thanks for your information!
    Friday, October 12, 2018 6:08 AM
  • Yeah you use the -server switch to jump domains if you need to.

    With any script you need a logical hook to perform a programmatic action. If the format of the employeeid or the phone numbers is very different and you can identify the domain by that then you can have the script do it. If you just have a long list of employeeIDs and phone numbers and even you don't know which domain each employee is in then it's hard.

    Are all the employeeIDs unique across the domains? In that case you could grab all the users form all the domains and then check which domain the account with EmployeeID X came from.

    Friday, October 12, 2018 6:12 AM
  • So to answer your direct question you could either split them into 4 csv's or you could add another column that includes a servername on the domain that you want to hit. So long as the account you are running the script as can update users in all domains then you should be good. If not then split them up or save 4 sets of credentials.

    Also if you already have items in the otherTelephone field please make sure you grab, add, replace. e.g.

    Import-Csv -Path "<<Some path.csv>>" | % {
    $ADaccount = Get-ADUser -filter {EmployeeID -eq $_.EMPLOYEEID} -Property othertelephone -server $_.SERVER
    $ot = @( $ADaccount.othertelephone)
    $ot += $_.WORKLANDLINE
    Set-ADUser $ADaccount.Samaccountname -replace @{othertelephone=$ot} -Server $_.SERVER
    }

    Otherwise you will end up wiping what's already there.

    Also I would highly suggest extensive error handling, particularly if you are doing 4 domains in one script.


    Thanks for your script!

    I have a question, the -server at the script there is for 4 individual set, correct?

    If I would like to add another column that includes a servername on the domain, that script how should I write to add it at where?


    Friday, October 12, 2018 6:13 AM
  • Yeah you use the -server switch to jump domains if you need to.

    With any script you need a logical hook to perform a programmatic action. If the format of the employeeid or the phone numbers is very different and you can identify the domain by that then you can have the script do it. If you just have a long list of employeeIDs and phone numbers and even you don't know which domain each employee is in then it's hard.

    Are all the employeeIDs unique across the domains? In that case you could grab all the users form all the domains and then check which domain the account with EmployeeID X came from.

    Employee number kind of standard for all domains. The CSV file came from another department so I think I would suggest him to create 4 sets of CSV file for each individual domain is better, I think.
    Friday, October 12, 2018 6:18 AM
  • Yeah or ask for the username (SAMAccountName) or some other unique info in another column and then you can just use that to work out where they are.
    Friday, October 12, 2018 6:20 AM
  • Saw your script include Attribute " SamAccountName " so the CSV file they have to provide this detail at another column. Am I right?

    Another question at your script :-

    $ADaccount = Get-ADUser -filter {EmployeeID -eq $_.EMPLOYEEID} -Property othertelephone -server $_.SERVER

    For example I have 4 domains ( APAC, Europe, Japan and Americas ) Below I take APAC for my example :-

    $ADaccount = Get-ADUser -filter {EmployeeID -eq $_.EMPLOYEEID} -Property othertelephone -apac.dell.com $_.SERVER

    Am I right?

    If the CSV file create another column named DOMAIN, then how should I write the script?


    • Edited by Donny Yeoh Friday, October 12, 2018 6:38 AM
    Friday, October 12, 2018 6:37 AM
  • When I tried to run this and want to test show $ADaccount and $ot but I bump into this error. Means cannot find Attribute " otherTelephone " or what?

    PS C:\WINDOWS\system32> Import-Csv -Path "C:\testing.xlsx" | % {
    $ADaccount = Get-ADUser -filter {EmployeeID -eq $_.EMPLOYEEID} -Property otherTelephone -server apac.dell.com $_.SERVER
    $ot = @( $ADaccount.otherTelephone)
    $ot += $_.WORKLANDLINE }

    Get-ADUser : A positional parameter cannot be found that accepts argument '$null'.
    At line:2 char:14
    + $ADaccount = Get-ADUser -filter {EmployeeID -eq $_.EMPLOYEEID} -Property otherTe ...
    + ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
        + CategoryInfo          : InvalidArgument: (:) [Get-ADUser], ParameterBindingException
        + FullyQualifiedErrorId : PositionalParameterNotFound,Microsoft.ActiveDirectory.Management 
       .Commands.GetADUser

     
    Friday, October 12, 2018 8:28 AM
  • The SamAccountName would be useful to have in the csv but in the code snippet above I am referencing the SamAccountName from the object returned from the get-aduser -filter {employeeid -eq $_.employeeid}. If that get command works then the ADAccount object will have the SamAccountName as a property.

    $ADaccount = Get-ADUser -filter {EmployeeID -eq $_.EMPLOYEEID} -Property othertelephone -apac.dell.com $_.SERVER

    Not quite :). It would be;

    $ADaccount = Get-ADUser -filter {EmployeeID -eq $_.EMPLOYEEID} -Property othertelephone -Server apac.dell.com

    Friday, October 12, 2018 9:18 PM
  • If your csv file does not have a SERVER column then you will get this;

    A positional parameter cannot be found that accepts argument '$null'. because you still have the $_.SERVER item on the line above. Remove just the "$_.SERVER" and try that command again.

    Friday, October 12, 2018 9:22 PM