Stuck with Powershell export from AD to compare and update Sharepoint online list - help needed

Question

Hi,
I'm stuck with powershell script and need some idea to push me forward, please.

Scenario: This is all about managing information of service accounts in domain. There is a Sharepoint list containing various fileds imported from AD, like AccountName, PasswordLastSet, Enabled. The list also contains manually updated fields like department, admin, comments.

Task: To keep this list up to date, I need periodically update it with fresh export from AD (some columns only, not full record)

Method:
1 export the list from AD - done - get-ADuser
2 export the list from Powershell. - done - Get-PnPList
3 compare the lists and for each account from AD, check against Sharepoint list - Struggling here

• if exists, check fields like PasswordLastSet if different update sharepoint list - Done - Set-PnPListItem
• if doesn't exist in sharepoint (new account) create record - Done - Add-PnPListItem
• if exists in Sharepoint but not in AD (deleted account) delete record in SP - Done - Remove-PnPListItem

Problem: I'm stuck with the most important part - compare lists and match records. Tried compare-object, as the most promising but couldn't pick the record right. Any idea how can I get it? with compare-object or any other way...
Any hint appreciated.

Thanks
Kuba

Answer 1

Hi @Jakub Juszczyk ,

As per my test, I cannot compare the return value between Get-ADuser and Get-PnPListItem because the return fields(ObjectId, DisplayName...) of Get-ADuser are different form the return fields(Key, Value) of Get-PnPListItem. It seems to be impossible to achieve your requirement.

---

If an Answer is helpful, please click "Accept Answer" and upvote it.
Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.

Answer 2

Hi Allen,

Thank you for looking into it. Yes, the records are different but they have common element - name. Service account name. See attached screenshot. I got export from both AD and SP. Same account name, different password age I need to get this new password age updated on sharepoint. so this seems to be possible to match the records.
I'm stuck with logic how to for each element in AD export, find a matching record from sharepoint. Once this match is found, compare attributes. Secondary. If this bit is not possible I can update all attributes, regardless if they are the same or different. This will be much slower though.

Answer 3

Hi,

I've eventually got it working so this is just to close the question. and for future searches. If you need to export the list from AD, compare with existing sharepoint records and update records if changed in AD, here is the solution. (in my case, parameter "Interactive" was based on membership of either of two security groups.)

Question closed

#Import the Sharepoint List
Install-Module -Name SharePointPnPPowerShellOnline # Elevated/admin required
$SiteUrl = "https://"
$ListName = ""
Connect-PnPOnline $SiteUrl
$SPlist = (Get-PnPListItem -List $ListName -Fields 'ServiceAccount', 'LastPasswordSet', 'Enabled', 'Interactive', 'GUID') 

#converting to Sharepoint Array
$SParray =
foreach ($listItem in $SPlist) {
    [PSCustomObject]@{
        ServiceAccount  = $listItem['ServiceAccount'].ToString()
        PasswordLastSet = $listItem['LastPasswordSet'].ToString('yyyy-MM-dd')
        Enabled         = $listItem['Enabled'] 
        Interactive     = $listItem['Interactive']
        ID              = $listItem['ID']
    }
}
$SParray | Format-Table

#Import AD list
$Intgroup1 = '...ACCOUNTS-INT' # current group to be phased out
$Intgroup2 = '...ACCOUNTS-INTERACTIVE' # new group to be used
$Intmembers1 = Get-ADGroupMember -Identity $Intgroup1 -Recursive | Select-Object -ExpandProperty samAccountName
$Intmembers2 = Get-ADGroupMember -Identity $Intgroup2 -Recursive | Select-Object -ExpandProperty samAccountName
$saccounts = Get-ADUser -Filter * -SearchBase "OU=" -Properties enabled, PasswordLastSet

#building AD Array
$ADlist = 
foreach ($saccount in $saccounts) {
    [PSCustomObject]@{
        ServiceAccount  = ($saccount).SamAccountName
        PasswordlastSet = ($saccount).passwordLastSet.ToString('yyyy-MM-dd')
        Enabled         = ($saccount).enabled
        Interactive     = If ($Intmembers1 -contains $saccount.SamAccountName) { 
                                'yes'
                            }
                            Elseif ($Intmembers2 -contains $saccount.SamAccountName) { 
                                'yes'
                            }
                            Else {
                                'no'
                            }
        ID              = ($SParray | Where-Object -Property 'ServiceAccount' -EQ -Value $saccount.SamAccountname).ID
    }
}
$ADlist #| Export-Csv .\output.csv -NoTypeInformation
$ADlist | Format-Table

#Remove deleted accounts
$DeletedAccounts = Compare-Object -ReferenceObject $ADlist -DifferenceObject $SParray  -Property ServiceAccount -PassThru |   
Where-Object { $_.SideIndicator -eq '=>' } 
$DeletedAccounts | Format-Table
foreach ($DeletedAccount in $DeletedAccounts) {
    write-host Deleting $DeletedAccount.ServiceAccount ID = $DeletedAccount.ID
    Remove-PnPListItem -List $ListName -Identity ($DeletedAccount.ID) -Force
}

#Add created accounts
$NewAccounts = Compare-Object -ReferenceObject $ADlist -DifferenceObject $SParray  -Property ServiceAccount -PassThru |
Where-Object { $_.SideIndicator -eq '<=' } 
$NewAccounts | Format-Table

foreach ($newAccount in $NewAccounts) {
    write-host Adding $NewAccount.ServiceAccount
    Add-PnPListItem -List $ListName  -Values @{
        ServiceAccount  = $($newAccount.ServiceAccount)
        LastPasswordSet = $($newAccount.PasswordLastSet)
        Enabled         = $($newAccount.Enabled)
        Interactive     = $($newAccount.Interactive)
        comments        = 'account added'
    }
}

#Update changed password
$UpdatedPasswords = Compare-Object -ReferenceObject $ADlist -DifferenceObject $SParray  -Property PasswordlastSet -PassThru |
Where-Object { $_.SideIndicator -eq '<=' } 
$UpdatedPasswords | Format-Table
foreach ($UpdatedPassword in $UpdatedPasswords) {
    write-host Updating password for $UpdatedPassword.ServiceAccount ID = $UpdatedPassword.ID
    Set-PnPListItem -List $ListName -Identity ($UpdatedPassword.ID) -Values @{
        LastPasswordSet = $($UpdatedPassword.PasswordLastSet);
        comments        = 'password updated'
    }
}

#Update changed Enabled
$UpdatedEnableds = Compare-Object -ReferenceObject $ADlist -DifferenceObject $SParray  -Property Enabled -PassThru |
Where-Object { $_.SideIndicator -eq '<=' } 
$UpdatedEnableds | Format-Table
foreach ($UpdatedEnabled in $UpdatedEnableds) {
    write-host Updating password for $UpdatedEnabled.ServiceAccount ID = $UpdatedEnabled.ID
    Set-PnPListItem -List $ListName -Identity ($UpdatedEnabled.ID) -Values @{
        Enabled  = $($UpdatedEnabled.Enabled);
        comments = 'Enabled updated'
    }
}

#Update changed Interactive
$UpdatedInteractives = Compare-Object -ReferenceObject $ADlist -DifferenceObject $SParray  -Property Interactive -PassThru |
Where-Object { $_.SideIndicator -eq '<=' } 
$UpdatedInteractives | Format-Table
foreach ($UpdatedInteractive in $UpdatedInteractives) {
    write-host Updating password for $UpdatedInteractive.ServiceAccount ID = $UpdatedInteractive.ID
    Set-PnPListItem -List $ListName -Identity ($UpdatedInteractive.ID) -Values @{
        Interactive = $($UpdatedInteractive.Interactive);
        comments    = 'Interactive updated'
    }
}