none
Update user's AD properties according to CSV given by ADP?

    Question

  • Hello,

    I have to write a PS script to update AD users' managers, job titles and description based on the employeeID value because that is the only unique value from ADP data.

    The CSV comes in this format. "File Number" is employeeID, employee First name/Last Name, Job Title, Reports to Email is their manager's email
























    This is basically my script

    Import-Module ActiveDirectory
    $import = import-csv "$env:C:\ADPADupdate\HRData.csv"
    
    foreach ($user in $import) {
        $manager = get-aduser -filter "emailaddress -eq '$($user.'Reports to Email')'"
        get-aduser -Filter "enabled -eq 'true' -and employeeID -eq '$($user.'File Number')'" -Properties employeeID |
        set-aduser -Manager $manager.DistinguishedName -description $user.'Job Title Description' -title $user.'Job Title Description'
    }

    The PROBLEM is that. The data in the CSV are not perfect, some miss employeeID, some miss manager's email. When I clean out the incomplete rows, basically just delet them, then my script works perfectly. If I leave the incomplete rows in there, the script will error out and I get unexpected results, like employees get wrong job titles or managers.

    How can I use the IF else statement to skip the incomplete rows? For example, tell it to update if the employeeID matches the one in CSV, SKIP if employeeID or manager fields are empty??

    Thanks so much in advance for your feedback!
























    Friday, November 23, 2018 8:49 PM

All replies

  • The following tests for the existence of manager and employeeID for each user.

    ForEach ($user in $import)
    {
        $Manager = get-aduser -filter "emailaddress -eq '$($user.'Reports to Email')'"
        $ID = $($User.'File Number')
        If ($Manager -And $ID)
        {
            get-aduser -Filter "enabled -eq 'true' -and employeeID -eq $ID" -Properties employeeID |
            set-aduser -Manager $manager.DistinguishedName -description $user.'Job Title Description' -title $user.'Job Title Description'
        }
    }
    


    Richard Mueller - MVP Enterprise Mobility (Identity and Access)

    Friday, November 23, 2018 9:10 PM
  • Hi Richard,

    Thank you so much for your response. I used your modified script and put -verbose so I could see what was happening. I discovered what the script was doing. This is a sample of the data from the CSV

    When the script hit Steven J, instead of skipping this row, it used the manager in the row above, so Steven J now reported to Brian S (who is not his manager).

    How can I stop it from doing that? If an employee has no manager, I want the script to skip that row. Is it even possible? This is where I have been struggling.

    Thank you so much in advance for your help/input!

    This is the error it output

    get-aduser : The search filter cannot be recognized
    At C:\ADPADupdate\UpdateManagerandJobtitle-Canada.ps1:8 char:16
    + ...  $manager = get-aduser -filter "emailaddress -eq '$($user.'Reports to ...
    +                 ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
        + CategoryInfo          : NotSpecified: (:) [Get-ADUser], ADException
        + FullyQualifiedErrorId : The search filter cannot be recognized,Microsoft.ActiveDirectory.Management.Commands.GetADUser


    • Edited by mtplasma Monday, November 26, 2018 4:20 PM
    Monday, November 26, 2018 4:18 PM
  • The combination of single and double quotes seems to cause problems. This worked better for me:

    ForEach ($user in $import)
    {
        $Mgr = $($user.'Reports to Email')
        $ID = $($User.'File Number')
        $Manager = get-aduser -filter "emailaddress -eq $Mgr"
        If ($Manager -And $ID)
        {
            get-aduser -Filter "enabled -eq 'true' -and employeeID -eq $ID" -Properties employeeID |
            set-aduser -Manager $manager.DistinguishedName -description $user.'Job Title Description' -title $user.'Job Title Description'
        }
    


    Richard Mueller - MVP Enterprise Mobility (Identity and Access)

    Monday, November 26, 2018 4:46 PM
  • For the record:

            get-aduser -Filter "enabled -eq $true -and employeeID -eq '$ID'" -Properties employeeID

    ID is a string and must be single quoted.  Double quotes are required to expand the variables.  Booleans are tested as Booleans.

    To do this:

    $($user.'Reports to Email')

    I would use escaped single quotes.

    get-aduser -filter "emailaddress -eq '$($user.''Reports to Email'')'"


    \_(ツ)_/

    Monday, November 26, 2018 4:53 PM
    Moderator
  • The combination of single and double quotes seems to cause problems. This worked better for me:

    ForEach ($user in $import)
    {
        $Mgr = $($user.'Reports to Email')
        $ID = $($User.'File Number')
        $Manager = get-aduser -filter "emailaddress -eq $Mgr"
        If ($Manager -And $ID)
        {
            get-aduser -Filter "enabled -eq 'true' -and employeeID -eq $ID" -Properties employeeID |
            set-aduser -Manager $manager.DistinguishedName -description $user.'Job Title Description' -title $user.'Job Title Description'
        }


    Richard Mueller - MVP Enterprise Mobility (Identity and Access)

    Hi Richard,

    The results I get are still the same (ie: Steven J still gets Brian S as his manager). The script does not skip over that row as I would like it to.

    Here is the complete script, with your and jrv help

    Import-Module ActiveDirectory
    $import = import-csv "$env:C:\ADPADupdate\ADP.csv"
    
    foreach ($user in $import) {
        $Mgr = $($user.'Reports to Email')
        $ID = $($user.'File Number')
        $manager = get-aduser -filter "emailaddress -eq '$Mgr'"
        
        If ($manager -and $ID)
        {
          get-aduser -Filter "enabled -eq 'true' -and employeeID -eq '$ID'" -Properties employeeID |
          set-aduser -Manager $manager.DistinguishedName -description $user.'Job Title Description' -title $user.'Job Title Description' -Verbose }
    }


    I am trying to understand the If statement

     If ($manager -and $ID)

    Should it be a comparison of some type, like "If manager and ID equal something" ?

    Thank you again and again for your help.

    ------

    I've searched thru the internet and read many different scripts. The syntax looks fine with my script, just not sure why it is not working as expected. Could it be my powershell version? I am using win7, the server is 2012.

    • Edited by mtplasma Tuesday, November 27, 2018 10:23 PM update
    Tuesday, November 27, 2018 7:48 PM
  • Hi,

    Thanks for your question.

    If ($manager -and $ID)

    The If statement means the properties of Reports to Email and File Number all exit.

    I think you need to change it to If ($Mgr -and $ID).

    As my example:

    Best Regards,

    Lee


    Just do it.

    Tuesday, December 4, 2018 7:46 AM
    Moderator
  • Hi,

    Thanks for your question.

    If ($manager -and $ID)

    The If statement means the properties of Reports to Email and File Number all exit.

    I think you need to change it to If ($Mgr -and $ID).

    As my example:

    Best Regards,

    Lee


    Just do it.

    Thank you so much for your explanation and example. I see it now. I am still working on the script and trying to figure it out.
    Friday, December 7, 2018 2:33 PM
  • The reason I used $Manager rather than $Mgr is because I wanted to ensure the corresponding manager object was found. If $Mgr has a value in the CSV, that does not necessarily mean the manager will be found in AD. Perhaps a minor point.

    Richard Mueller - MVP Enterprise Mobility (Identity and Access)

    Friday, December 7, 2018 3:53 PM