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

Jakub Juszczyk 1 Reputation point
2021-06-24T00:21:11.1+00:00

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

SharePoint
SharePoint
A group of Microsoft Products and technologies used for sharing and managing content, knowledge, and applications.
9,614 questions
Active Directory
Active Directory
A set of directory-based technologies included in Windows Server.
5,848 questions
Windows Server PowerShell
Windows Server PowerShell
Windows Server: A family of Microsoft server operating systems that support enterprise-level management, data storage, applications, and communications.PowerShell: A family of Microsoft task automation and configuration management frameworks consisting of a command-line shell and associated scripting language.
5,362 questions
0 comments No comments
{count} votes

3 answers

Sort by: Most helpful
  1. Allen Xu_MSFT 13,776 Reputation points
    2021-06-24T07:25:46.853+00:00

    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.
    108846-1.png


    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.

    0 comments No comments

  2. Jakub Juszczyk 1 Reputation point
    2021-06-24T12:33:43.547+00:00

    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.
    109007-capture.png

    0 comments No comments

  3. Jakub Juszczyk 1 Reputation point
    2021-07-05T14:48:19.99+00:00

    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'
        }
    }