Compare export from AD with Sharepoint list and if different, update sharepoint

Hi,

I’m stuck with PS 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 list in Sharepoint that contains various fileds imported from AD, like AccountName, PasswordLastSet, Enabled etc. 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)

My 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.
Then:

  • if Username matches, compare fields like PasswordLastSet if different update sharepoint list - work in progress.
  • if username 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 to match records. Tried compare-object, as the most promising but couldn’t pick the rights record . Any idea how can I get it? with compare-object or any other way…
Any hint appreciated.

Thanks
Kuba

Kuba,
Welcome to the forum.

I’m unsure what kind of help do you expect. On most of your steps you have a “done”!? :wink:

I think you will have to be more specific about what is not working for you. Most of the time Compare-Object is the way to go if you want to compare a big amount of objects.

Ideally you may share some sample data and the relevant part of your code to explain where exactly you need help.

Hi Olaf,

You are absolutely right. I missed the important bit. I have two lists that I don’t really know how to compare. I was thinking VLOOKUP style but in powershell. Like
foreach ($ADaccount in $ADaccounts) {
Compare-Object -ReferenceObject $ADAccount.name -DifferenceObject $PSList[“ServiceAccount”] -IncludeEqual

I can compare two individual objects. See attached. Not sure how to make script going through the list.

Please do not post pictures of code as this is pretty much useless for people willing to help you. It’s impossible to copy the code this way. We would have to type out the code.

When you post code, error messages, console ouput or sample data please format it as code using the “preformatted text” button ( </> ).

You can edit your existing post to correct the formatting and exchange the picture for the actual code. You’re not supposed to create a new one.

Thanks in advance.

Apologies. Silly me.
here is the bit I’m working on but got stuck:

foreach ($ADUser in $array) {
    foreach($SPAccount in $SPList) {
$result = Compare-Object -ReferenceObject $ADuser.name -DifferenceObject $SPAccount[“ServiceAccount”] -IncludeEqual
If ($result.Sideindicator -eq "==") |  Write-Host $ADuser.name "matches ID - "  $SPAccount["ID"]
Compare-Object -ReferenceObject $ADuser.PasswordLastSet -DifferenceObject $SPAccount["LastPasswordSet"]
if (($result.Sideindicator -ne "==") | Set-PnPField...
    }
}

Still work in progress and very inefficient - yelds multiple results for looping it over and over again. That’s why I’m seeking help… :frowning:

I’ve cleaned it a bit (work in progress) but still not working as expected. I"m not even sure if I’m barking the right tree…

foreach ($ADUser in $array) {
    foreach($SPAccount in $SPList) {
$result = Compare-Object -ReferenceObject $ADuser.name -DifferenceObject $SPAccount[“ServiceAccount”] -IncludeEqual
If ($result.Sideindicator -eq "==") {
Write-Host $ADuser.name "matches ID - "  $SPAccount["ID"]
Compare-Object -ReferenceObject $ADuser.PasswordLastSet -DifferenceObject $SPAccount["LastPasswordSet"]
}
Else
{
Set-PnPField -List $SPList -Identity $SPAccount["ID"] -Values  @{"LastPasswordSet"=$ADuser.PasswordLastSet}
}
}
}

I suspect you have a missconception about how to use Compare-Object. Usually you don’t compare single objects. Instead you take arrays of objects provide them completely and choose which property should be used to compare.

Using one of your code lines from above it could be something similar to this:

Compare-Object -ReferenceObject $ADAccount -DifferenceObject $PSList -Property sAMAccountName -IncludeEqual -PassThru

You may (re) read the help for

… especially the example #5 shows a little what I meant

1 Like

Oh dear, Indeed it works like a treat. Single liner does the job. but this is where I was not sure as compare-object gives a result list, so how do I action individual item. I.e. Account xyz password age is different, how do I pick it for update?

If you’re looking for differences I’d omit the parameter -IncludeEqual. Then when you have the output from Compare-Object you can filter with Where-Object for the SideIndicator either to be “<=” or “=>”. And the result of this step is a perfect source for a loop to process for the next step. :wink:

1 Like

Thank you very much Olaf. Very professional advice. I’ll try to take it from here. Really appreciate your help. :slightly_smiling_face:

Thank you.
Kuba

I’m glad if it’s been helpful. :+1:t4: :slightly_smiling_face:

Hi Olaf,

First of all, thank you again for your help. I’ve completed this part, compare lists and got some updates working. All good but…

I had to put this script on hold for a few days, being stuck with current workload. Now back to the task and I hit another wall. I’m wondering if you could be so kind and push me a bit further.

I have two lists - one export from AD, second export from Sharepoint. I matched their properties, like AccountName, PasswordLastSet etc, by creating bespoke arrays, but Sharepoint list has unique ID (GUID), that doesn’t exists in AD.

So far I compare lists and if there is missing or surplus record in Sharepoint list I can delete or add it. But to update particular attribute for particular record I need to provide GUID of this record. So I need to match these two records from two arrays somehow. I’m stuck. Look at the code below. First attempt is in line 43:

$RA | Add-Member -type NoteProperty -name "ID" -value ($listItem["ID"] |Where-Object $listItem["ServiceAccount"] -EQ ($saccount).SamAccountName)

Then I tried built separate loop for it but again got stuck - line 108

#Adding sharepoint ID to the AD list/array
foreach ($MatchingAccount in $MatchingAccounts) {
$MatchingAccount.ID = ($listItem["ID"] |Where-Object $listItem["ServiceAccount"] -EQ ($saccount).SamAccountName)
}

I’m probably barking the wrong tree again.

Thanks in advance
Kuba

Full code for reference



function Get-SPList {
#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") 

#building comparable array
    $array2 = @()
    foreach ($listItem in $SPlist) {
    $RA2 = New-Object PSobject
    $RA2 | Add-Member -MemberType NoteProperty -name "ServiceAccount" -value $listItem["ServiceAccount"].ToString()
    $RA2 | Add-Member -MemberType NoteProperty -name "LastPasswordSet" -value $listItem["LastPasswordSet"].ToString('yyyy-MM-dd')
    $RA2 | Add-Member -type NoteProperty -name "Enabled" -value $listItem["Enabled"] 
    $RA2 | Add-Member -type NoteProperty -name "Interactive" -value $listItem["Interactive"]
    $RA2 | Add-Member -type NoteProperty -name "ID" -value $listItem["ID"]

    $array2 +=$RA2
    }
    $array2
}
Get-SPList

Function Get-adlist {
#Import AD list
    $Intgroup1 = "GLB-APPS-ACCOUNTS-INT" # current group to be phased out
    $Intgroup2 = "GLB-APPS-ACCOUNTS-INTERACTIVE" # new group to be used
    $Intmembers1 = Get-ADGroupMember -Identity $Intgroup1 -Recursive | Select -ExpandProperty samAccountName
    $Intmembers2 = Get-ADGroupMember -Identity $Intgroup2 -Recursive | Select -ExpandProperty samAccountName
    $saccounts = Get-ADUser -Filter * -SearchBase "OU=DisabledDoNotDelete,OU=Service Accounts,OU=..." -Properties enabled,passwordLastSet #test OU
    
#building output array
    $array = @()
    foreach ($saccount in $saccounts) {
        $RA = New-Object PSObject
        $RA | Add-Member -type NoteProperty -name "ServiceAccount" -value ($saccount).SamAccountName
        $RA | Add-Member -type NoteProperty -name "LastPasswordSet" -value ($saccount).passwordLastSet.ToString('yyyy-MM-dd')
        $RA | Add-Member -type NoteProperty -name "Enabled" -value ($saccount).enabled
        
        #this is my first attempt but didn't know how
        $RA | Add-Member -type NoteProperty -name "ID" -value ($listItem["ID"] |Where-Object $listItem["ServiceAccount"] -EQ ($saccount).SamAccountName)

       If ($Intmembers1 -contains $saccount.SamAccountName) {
        $RA | Add-Member -type NoteProperty -name "Interactive" -value "yes"
        } 
       Elseif ($Intmembers2 -contains $saccount.SamAccountName) {
        $RA | Add-Member -type NoteProperty -name "Interactive" -value "yes"
        } 
       Else {
        $RA | Add-Member -type NoteProperty -name "Interactive" -value "no"
        } 
         
        $array +=$RA
        }
        $array #| Export-Csv .\output.csv -NoTypeInformation
    }
Get-adlist

Function Compare-Lists {
#Comparing the list
$MatchingAccounts = Compare-Object -ReferenceObject $array2 -DifferenceObject $array  -Property ServiceAccount -IncludeEqual -PassThru| # |   
Where-Object {$_.SideIndicator -eq "=="} 
$DeletedAccounts = Compare-Object -ReferenceObject $array -DifferenceObject $array2  -Property ServiceAccount -PassThru |   
Where-Object {$_.SideIndicator -eq "=>"} 
$NewAccounts = Compare-Object -ReferenceObject $array -DifferenceObject $array2  -Property ServiceAccount -PassThru |
Where-Object {$_.SideIndicator -eq "<="} 

$UpdatedPasswords = Compare-Object -ReferenceObject $array -DifferenceObject $array2  -Property LastPasswordSet -PassThru |
Where-Object {$_.SideIndicator -eq "<="} 
$UpdatedEnableds = Compare-Object -ReferenceObject $array -DifferenceObject $array2  -Property Enabled -PassThru |
Where-Object {$_.SideIndicator -eq "<="} 
$UpdatedInteractives = Compare-Object -ReferenceObject $array -DifferenceObject $array2  -Property Interactive -PassThru |
Where-Object {$_.SideIndicator -eq "<="} 

$DeletedAccounts
$NewAccounts
$UpdatedPasswords
$UpdatedEnableds
$UpdatedInteractives
$MatchingAccounts

}
Compare-Lists

Function Update-list {
#Remove deleted accounts
foreach ($DeletedAccount in $DeletedAccounts){
write-host Deleting $DeletedAccount.ServiceAccount ID = $DeletedAccount.ID
Remove-PnPListItem -List $ListName -Identity ($DeletedAccount.ID) -Force
}

#Add created account
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";
            }
}


#Adding sharepoint ID to the AD list/array
foreach ($MatchingAccount in $MatchingAccounts) {
$MatchingAccount.ID = ($listItem["ID"] |Where-Object $listItem["ServiceAccount"] -EQ ($saccount).SamAccountName)
}



#Update changed password
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" = "updated" #-Force
    }
}

}
Update-list

Actually I’m still not completely sure what exactly your issue is.
If you want to update one list with a property from another list you need to have a unique identifier in both lists. Usually the sAMAccountName is a neat choice in such cases.

Here is a small example:

$ADData = @'
"sAMAccountName","DisplayName"
"jlennon","John Lennon"
"gharrison","George Harrison"
"pmccartney","Paul McCartney"
"rstarr","Ringo Starr"
'@ |
    ConvertFrom-Csv
$SPData = @'
"sAMAccountName","UniqueID"
"jlennon","1"
"gharrison","2"
"pmccartney","3"
"rstarr","4"
'@ |
    ConvertFrom-Csv

$NewList = 
foreach($User in $ADData){
    [PSCustomObject]@{
        sAMAccountName = $User.sAMAccountName
        DisplayName = $User.DisplayName
        UniqueID = ($SPData | Where-Object -Property 'sAMAccountName' -EQ -Value $User.sAMAccountName).UniqueID
    }
}
$NewList

And the result would be the sAMAccountname you have in both lists, the DisplayName from the ADData and the UniqueID from the SPData:

sAMAccountName DisplayName     UniqueID
-------------- -----------     --------
jlennon        John Lennon     1
gharrison      George Harrison 2
pmccartney     Paul McCartney  3
rstarr         Ringo Starr     4

Now … your code … assumed your code really starts in line 1 …

Line 3: I think it’s a bad idea to install a module inside of a function while you actually just want to use this module. Do you really need to install it every single time you run this code?

Line 4 and 5: To make function really re-usable you should not hard code values in it that could change. Make it a parameter of the function

Line 10 to 21: This syntax to create an object is very old. Nowadays we use PSCustomObjects.

Your function could look like this:

function Get-SPList {
    param (
        $SiteUrl,
        $ListName
    )
    Connect-PnPOnline $SiteUrl
    $SPlist = Get-PnPListItem -List $ListName -Fields 'ServiceAccount', 'LastPasswordSet', 'Enabled', 'Interactive', 'GUID'

    foreach ($listItem in $SPlist) {
        [PSCustomObject]@{
            ServiceAccount  = $listItem['ServiceAccount'].ToString()
            LastPasswordSet = $listItem['LastPasswordSet'].ToString('yyyy-MM-dd')
            Enabled         = $listItem['Enabled'] 
            Interactive     = $listItem['Interactive']
            ID              = $listItem['ID']
        }
    }
}

Now if you want to have the result of this function in an array with the name $array2 you can call it like this:

$array2 = Get-SPList -SiteUrl 'Https://...' -ListName 'Whatever'

In your function Compare-Lists you use the variables $array and $array2 you defined in other functions. And you do the same with the variable $listItem in your line 43. That cannot work because variables you define in the scope of a function only exist in the scope of this very function. You could circumvent this by using global variables but that is considered very bad style and should be avoided. The proper approach would be to provide everything a function needs as input parameters of the function.

The same issue you have with your compared lists and your update function.

Then something general … you have 4 functions but you actually use every function only once. That does not make that much sense. We use functions for code we need more than once. :wink:

I think you’d be better off not using functions in you script in this case.

I hope it helps a little bit.

1 Like

Hi,

First, thank you for taking time to review and advise… Your help allowed me to finish it. The script works fine now. I’m in heaven. :slight_smile: Thank you.

Let me reply in points as you raised a few things.

  1. Where the issue were…
    Basically I got stuck with matching records from two lists to copy ID from one to another. Your suggestion
UniqueID = ($SPData | Where-Object -Property 'sAMAccountName' -EQ -Value $User.sAMAccountName).UniqueID

was all I needed to push this forward. Now it works fine and I can update all fields. Thank you very much.
2. I didn’t really intend use function in this script in production. I put them in for convenience during dev phase to collapse part of the code for clarity, plus I needed to run some parts over and over with slight modification. I agree functions have no use in this script.
3. As for

$array2 = Get-SPList -SiteUrl 'Https://...' -ListName 'Whatever'

Get-SPList is part of SPO module - on a tenant level permissions. I’m not a tenant admin, so need to stick to PnP module that runs on site level. Hence the need to create the array. Unless there is a simple way in PnP as well. I’d love to learn this. There is always a scope for improvement. :slight_smile:
4. Old syntax. Thank you. That is really simple as you wrote it. I used it. As you can see I’m not a programmer and PS scripts is just part of the admin job. Appreciate your comments and patience.

Lastly, working script, although probably not final, for reference below. Maybe you have some suggestions how to optimise it? Although i don’t expect you invest your valuable time in this. You’ve been kind enough already. Thank you.

#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") 
    
#building Sharepoint array
    $SParray = @()
    foreach ($listItem in $SPlist) {
    $RA2 = New-Object PSobject
    $RA2 | Add-Member -Type NoteProperty -name "ServiceAccount" -value $listItem["ServiceAccount"].ToString()
    $RA2 | Add-Member -Type NoteProperty -name "PasswordLastSet" -value $listItem["LastPasswordSet"].ToString('yyyy-MM-dd')
    $RA2 | Add-Member -type NoteProperty -name "Enabled" -value $listItem["Enabled"] 
    $RA2 | Add-Member -type NoteProperty -name "Interactive" -value $listItem["Interactive"]
    $RA2 | Add-Member -type NoteProperty -name "ID" -value $listItem["ID"]

    $SParray +=$RA2
    }
    $SParray |ft

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

#building interim AD output array
    $ADTemparray = @()
    foreach ($saccount in $saccounts) {
        $RA = New-Object PSObject
        $RA | Add-Member -type NoteProperty -name "ServiceAccount" -value ($saccount).SamAccountName
        $RA | Add-Member -type NoteProperty -name "PasswordlastSet" -value ($saccount).passwordLastSet.ToString('yyyy-MM-dd')
        $RA | Add-Member -type NoteProperty -name "Enabled" -value ($saccount).enabled
        
       If ($Intmembers1 -contains $saccount.SamAccountName) {
        $RA | Add-Member -type NoteProperty -name "Interactive" -value "yes"
        } 
       Elseif ($Intmembers2 -contains $saccount.SamAccountName) {
        $RA | Add-Member -type NoteProperty -name "Interactive" -value "yes"
        } 
       Else {
        $RA | Add-Member -type NoteProperty -name "Interactive" -value "no"
        } 
         
        $ADTemparray +=$RA
        }
        $ADTemparray #| Export-Csv .\output.csv -NoTypeInformation

#Adding Sharepoint ID to ADlist
    $ADlist = 
        foreach ($row in $ADTemparray) {
            [PSCustomObject]@{
                ServiceAccount = $row.ServiceAccount
                PasswordlastSet = $row.PasswordlastSet
                Enabled = $row.Enabled
                Interactive = $row.Interactive
                ID = ($SParray |Where-Object -Property 'ServiceAccount' -EQ -Value $row.ServiceAccount).ID
                }
            }
    $ADlist |ft

#Remove deleted accounts
    $DeletedAccounts = Compare-Object -ReferenceObject $ADlist -DifferenceObject $SParray  -Property ServiceAccount -PassThru |   
    Where-Object {$_.SideIndicator -eq "=>"} 
    $DeletedAccounts |ft
    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 |ft
    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 |ft
    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 |ft
    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 |ft
    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"
        }
    }

Great that we’ve found the solution. :+1:t4: :wink:

I’d recommend to use VSCode for script development. You can select parts of your code and hit to run only the selected code. So no need to transform your code for developement. And VSCode does much more for you. It helps you writing your code faster and even more nicely formatted. And if you want to fold your code to focus only on the part you’re working at the moment you could use regions like it is explained here:

This works in VSCode as well.

Hmmm … not as much as I whished. :wink: Of course I meant to replace all object declarations you have in your script. … not just the one I showed as example. :wink:

Me too. :slightly_smiling_face:

OK. I streamlined your script even a little bit more:

#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') 
    
#building 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 = 'GLB-APPS-ACCOUNTS-INT' # current group to be phased out
$Intgroup2 = 'GLB-APPS-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=DisabledDoNotDelete,OU=Service Accounts,...' -Properties enabled, PasswordLastSet #test OU

#building interim AD output array
$ADTemparray = 
foreach ($saccount in $saccounts) {
    [PSCustomObject]@{
        ServiceAccount  = ($saccount).SamAccountName
        PasswordlastSet = ($saccount).passwordLastSet.ToString('yyyy-MM-dd')
        Enabled         = ($saccount).enabled
        Interactive     = If ($Intmembers1 -contains $saccount.SamAccountName) { ### here you define for two different cases the same result
                                'yes'
                            }
                            Elseif ($Intmembers2 -contains $saccount.SamAccountName) { ### this might be intentional but it looks wrong to me ;-)
                                'yes'
                            }
                            Else {
                                'no'
                            }
    }
}
$ADTemparray #| Export-Csv .\output.csv -NoTypeInformation

#Adding Sharepoint ID to ADlist
$ADlist = 
foreach ($row in $ADTemparray) {
    [PSCustomObject]@{
        ServiceAccount  = $row.ServiceAccount
        PasswordlastSet = $row.PasswordlastSet
        Enabled         = $row.Enabled
        Interactive     = $row.Interactive
        ID              = ($SParray | Where-Object -Property 'ServiceAccount' -EQ -Value $row.ServiceAccount).ID
    }
}
$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'
    }
}

It should actually still work the same. But it should be easier to read.
I added a comment to your declaration of your variable $ADTemparray. There you assign the same value for two different cases. It may be intentional but looks a little weird to me. If it’s intentional you could use an -or operation in your if statement.

Regardless of all that you may continue your studies of PowerShell with reading the

Have a nice Weekend :wave:t4:

Hi,

Blimey, I never heard about regions… :frowning: and they are brilliant… I think I’d need to employ you to write my scripts… :wink:

Thanks for the best practices guide. Bit embarrassing to read, remembering my codes… :wink:

Well, there is always scope for improvement.

As for your comments - strange two condition for Interactive parameter are intentional. it’s based on security group and the account can be a member of either of them.

I’ve managed to collapse it even further combining adding ID from sharepoint into initial AD list build, so no need for second iteration. Final’ish version below but for now case closed. Again. Thank you for all your help and patience.

Kuba

#region Script header
<#Script exports Service Accounts from AD, compares with exisitng Sharepoint list and updates changes.
By Jakub Juszczyk @2021
#>
#endregion

#region 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
#endregion

#region Import AD list
$Intgroup1 = 'GLB-APPS-ACCOUNTS-INT' # current group to be phased out
$Intgroup2 = 'GLB-APPS-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
#endregion

#region update Sharepoint
#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'
    }
}
#endregion

If you can afford it … :wink: :stuck_out_tongue_winking_eye:

1 Like