PowerShell, extracting a list of SharePoint sites with owners and users, and making a CSV

Question

I am trying to write a PowerShell scripts that accomplishes the following tasks with partial results :

1. Retrieving a list of SharePoint sites (including OneDrive sites) that contain a certain word in the url (done) ;
2. Using a ForEach-Object to retrieve the list of owners and users of each site (done);
3. Exporting these informations in a CSV file (partially done);

My problem is the number 3, I'm trying to make it with one column for the sites URLs, one column for the owner, and the 3rd column with all the users inside, but unfortunately I'm only able to make the csv with the users list inside, here is the code that brought me to this point :

---

$username = "[email protected]"
$password = "password" | ConvertTo-SecureString -asPlainText -Force
$credential = New-Object System.Management.Automation.PSCredential($username,$password)
$Tenanturl = "https://domain-admin.sharepoint.com/"

# Connessione all'account Admin SharePoint
Connect-SPOService -Url $TenantUrl -Credential $credential
Get-SPOSite -IncludePersonalSite $true -Limit all -Filter "Owner -like 'WordIWantToFilter'" | 

ForEach-Object {
Get-SPOUser -Site $_.Url
} | 

Select-Object -Property LoginName,UserType | 

Export-Csv -Path "C:\Users\User\Desktop\SharePoint_Sites_List.csv" -NoTypeInformation

---

The result is a CSV file with LoginName,"UserType" in the cell A1, and the related info in the other rows.

---

What I am trying to accomplish : First column for the sites URLs, Second column for the owner of the sites, and the 3rd column with all the users of each site inside.

---

I know I am missing a lot of stuff, but I'm not a developer whatsoever :), these are some of the links I used to make this code,

https://docs.microsoft.com/en-us/powershell/module/microsoft.powershell.utility/export-csv?view=powershell-7 https://techcommunity.microsoft.com/t5/sharepoint/list-all-site-collection-admins-powershell/m-p/264135

what should I look for ? I'm looking for tips or even just little pieces of code, thanks

Answer 1

I predict you'll run into woes with this structure here:

Get-SPOSite -IncludePersonalSite $true -Limit all -Filter "Owner -like 'WordIWantToFilter'" | 

ForEach-Object {
Get-SPOUser -Site $_.Url
} | 

Select-Object -Property LoginName,UserType | 

Export-Csv -Path "C:\Users\User\Desktop\SharePoint_Sites_List.csv" -NoTypeInformation

These pipes will make things confusing. As an Automation Consultant and Engineer for a variety of firms for five years, I would avoid over-reliance on pipes, as it makes debugging code tricky and error prone.

I would rewrite it like this, minimizing those piped statements:

$filteredSites = Get-SPOSite -IncludePersonalSite $true -Limit all -Filter "Owner -like 'WordIWantToFilter'"

"Found $($filteresSites.Counts) from previous line"

$sitesArray=@() #make empty array to hold results
ForEach($site in $filteredSites){
   $sitesArray += Get-SPOUser -Site $_.Url
}
"Have $($sitesArray.Count) sites from previous line"

#good place to debug the output, btw :)
#prepare to export
"Run in the PowerShell ISE, we will have a left-over object `$sitesArray` we can use to test exporting "

$sitesArray | Select-Object -Property LoginName,UserType | 
Export-Csv -Path "C:\Users\User\Desktop\SharePoint_Sites_List.csv" -NoTypeInformation

Then to use it, open this all in the PowerShell ISE and run it. You'll get some helpful logging to the console and the ISE leaves some state in time variables behind which makes it really easy to troubleshoot.

These changes will make it easier to determine where you're losing data. For instance, if you run this and see an empty file, and also have an empty $sitesArray, then your filtered in $filteredSites was too exclusive.

If you're still stuck, post an update.