Hey everyone! I'm new to coding and trying to learn as I go. I'm currently working on a script that gathers all SharePoint sites and their corresponding members. While exporting the user information to a CSV file, I realized that the site URL isn't included in the output. Here's the relevant code I'm working with:
```powershell
Import-Module Microsoft.Online.Sharepoint.PowerShell
$AdminSiteURL="https://insertsitehere-admin.sharepoint.com"
Write-host "Connecting to Admin Center..." -f Yellow
Connect-SPOService -url $AdminSiteURL
Write-host "Getting All Site collections..." -f Yellow
$Sites = Get-SPOSite -Limit ALL
Foreach($Site in $Sites)
{
Write-host "Getting Users from Site collection:"$Site.Url -f Yellow
Get-SPOUser -Limit ALL -Site $Site.Url | Select DisplayName, LoginName, Site | Export-CSV -Path C:SharePointTest.CSV -Append
}
```
I'm aware I'm a Site Collection Admin for all the sites, but I'm not sure how to add the `$Sites.URL` to the CSV. Any help would be much appreciated!
5 Answers
Just a heads-up, is the "site" property actually pulling any values? I came across a guide that talks about using a custom object during this process. You might want to check it out for better clarity!
You can add the site URL to the output by modifying the `Select-Object`. Use a calculated property like this:
```powershell
Get-SPOUser -Limit ALL -Site $Site.Url | Select-Object DisplayName, LoginName, Site, @{n='SiteURL';e={$Site.URL}}
```
You can do that by using calculated properties. However, I've noticed a performance issue with your approach since `Export-Csv` inside a loop with `-Append` is quite slow. Here's a more efficient way to achieve what you're looking for:
```powershell
$siteUsers = foreach ($Site in $Sites) {
Write-host "Getting Users from Site collection:"$Site.Url -f Yellow
Get-SPOUser -Limit ALL -Site $Site.Url | Select-Object DisplayName, LoginName, Site, @{Name="SiteURL"; Expression={$Site.URL}}
}
$siteUsers | Export-CSV -Path C:SharePointTest.CSV
```
This approach accumulates all the user data in one go and avoids multiple file access operations, which should help speed things up!
I'll test this out! Thanks for the quick response and for explaining how it works; it really helps me understand better.
Oh, and make sure to update the last line to reference `$siteUsers` when exporting.
To add to previous points, I prefer using a PSCustomObject for clearer code structure. Here's how I would streamline it:
```powershell
$SiteUsers = foreach ($Site in $Sites) {
Write-host "Getting Users from Site collection:"$Site.Url -f Yellow
$SPOUser = Get-SPOUser -Limit ALL -Site $Site.Url
[pscustomobject]@{
DisplayName = $SPOUser.DisplayName
Login = $SPOUser.LoginName
SiteUrl = $Site.URL
TimeStamp = Get-Date -f 'yyyy-MM-dd-HHmm'
}
}
$SiteUsers | Export-CSV -Path C:SharePointTest.CSV
```
This method enhances readability, especially when the loop becomes more complex, and you can easily manage what data to include or exclude.
The "site" property doesn't pull anything; I just included it for consistency in the code. Appreciate your suggestion, I'll look into it!