Update AD Users from CSV, then list changes ONLY

Question

Update AD Users from CSV, then list changes ONLY

Dean Marshall 0

Hi All!

We have an HR system that exports all user data to a csv with various headers. I would like to import this csv, update AD users then list just the changes that were made, ignoring any attributes that remained the same.

The csv is like this:

Employee Id: 1000
First Name: Joe
Last Name: Bloggs
Known As: Joseph
Work Email: ******@somecompany.com
Company: Some Company Limited
Location: UK
Department: Some Department
Job Role: Some Job Role
Manager Email: ******@somecompany.com

I believe I would need to match the csv headers with the AD attribute names, and I would like to use 'Work Email' value from the csv as my referencing attribute (to userPrincipalName within AD I would assume).

Ideally, the output would be formatted listing the updated user by userPrincipalName along with just the attributes that were updated. The attributes that were not changed should show nothing.

For example, if only the 'Job Role' was changed, it would show this:

userPrincipalName	EmployeeID	Title	Department
******@somecompany.com		Some Job Role

I hope this makes sense, any help would be much appreciated.

0 comments

1 answer

Your answer

Answer 1

Rich Matheisen 48,116

This hasn't been tested! Also, I don't think the Get-MsolUser will return all the stuff you need.

# column names for input and output
$props = [ordered]@{
    "Employee Id"   = "The USER OBJECT property name"
    "First Name"    = "The USER OBJECT property name"
    "Last Name"     = "The USER OBJECT property name"
    "Known As"      = "The USER OBJECT property name"  # not sure what AD property this is
    "Work Email"    = "The USER OBJECT property name"
    "Company"       = "The USER OBJECT property name"
    "Location"      = "The USER OBJECT property name"
    "Department"    = "The USER OBJECT property name"
    "Job Role"      = "The USER OBJECT property name"
    "Manager Email" = "The USER OBJECT property name"  # I don't think this is returned by MSOLUser
}
$OutRow = [ordered]@{}
$SetProps = @{}         # used for splatting in set-msoluser/set-aduser
Import-Csv "some-file-name.csv" |
    ForEach-Object {
        $row = $_       # $_ is used in a later, nested, ForEach-Object
        $u = Get-MsolUser -UserPrincipalName $row."Work Email"    # I think you'll have to use the Get-ADUser to get some of the properties
        if ($u) {
            # check if the user was found
            $OutRow["UserPrincipalName"] = $u."UserPrincipalName"   # This wasn't a column in the inout CSV and the email may not be this users UPN
            $props.GetEnumerator() |
                ForEach-Object {
                    #    the USER          the CSV value                      
                    if ($u.($_.Value) -ne $row.($_.KEY )) {
                        $OutRow[$_.Key] = $row.($_.value)
                        $SetProps[$_.Value] = $row.($_.Value)
                    }
                    else {
                        $OutRow[$_.Key] = ""
                    }
                }
            Set-MsolUser @SetProps      # update user
            [PSObject]$OutRow
            $OutRow.Clear()                         # remove contents of hash
            $SetProps.Clear()
        }
    } | Export-Csv "some-other-file-name.csv" -NoTypeInformation

Dean Marshall 0

Thanks Rich for your answer, this is very helpful.

I have amended slightly to work on fewer attributes, and I have changed the Get-MsolUser/Set-MsolUser to Get-ADUser/Set-ADUser as these are on-premise AD user objects rather than Azure AD.

I am now receiving this message on PowerShell:

cmdlet Set-ADUser at command pipeline position 1

Supply values for the following parameters:

Identity:

I am not very good with PowerShell so perhaps I am missing something obvious. Again, any help would be hugely appreciated!

Here is the new code:

# column names for input and output
$props = [ordered]@{
    "Employee Id"   = "EmployeeID"
    "Work Email"    = "UserPrincipalName"
    "Job Role"      = "Title"
    # "Manager Email" = "The USER OBJECT property name"  # I don't think this is returned by MSOLUser
}
$OutRow = [ordered]@{}
$SetProps = @{}         # used for splatting in set-msoluser/set-aduser
Import-Csv "some-file-name.csv" |
    ForEach-Object {
        $row = $_       # $_ is used in a later, nested, ForEach-Object
        $u = Get-ADUser -Filter "UserPrincipalName -eq '$($row."Work Email")'" -Properties *  # I think you'll have to use the Get-ADUser to get some of the properties
        if ($u) {
            # check if the user was found
            $OutRow["UserPrincipalName"] = $u."UserPrincipalName"   # This wasn't a column in the inout CSV and the email may not be this users UPN
            $props.GetEnumerator() |
                ForEach-Object {
                    #    the USER          the CSV value                      
                    if ($u.($_.Value) -ne $row.($_.KEY )) {
                        $OutRow[$_.Key] = $row.($_.value)
                        $SetProps[$_.Value] = $row.($_.Value)
                    }
                    else {
                        $OutRow[$_.Key] = ""
                    }
                }
            Set-ADUser @SetProps      # update user
            [PSObject]$OutRow
            $OutRow.Clear()                         # remove contents of hash
            $SetProps.Clear()
        }
    } | Export-Csv "C:\some-other-file-name.csv" -NoTypeInformation

Rich Matheisen 48,116 Reputation points

2023-10-06T19:19:00.39+00:00
The Set-ADUser -Identity parameter takes these as acceptable values:

A distinguished name

A GUID (objectGUID)

A security identifier (objectSid)

A SAM account name (sAMAccountName)

The "splat" you're using ($SetProps) doesn't have an "Identity" key that has any of those values.

You can fix that just by changing the Set-ADUser cmdlet to look like this:

Set-ADUser -Identity $u.DistinguishedName @SetProps # update user

Share via

Update AD Users from CSV, then list changes ONLY

1 answer

Your answer