One of the things PowerShell enables you to do with Office 365 (particularly SharePoint Online) is collecting bulk info. In this post I will be providing a nice little script which can be used to collect groups from site collections including the names of users in those groups.
The main reason you might want to collect this is the information takes quite some time to be collected. By the time the information would be needed It would take a long unnecessarily amount of time. If the data however is already collected the requested information can be looked up quickly. The only real downside is that your data used will be “old” data. How old depends on how often you execute the function in this post.
Before going into detail about what the script does, let me elaborate about what goes in and what comes out.
There is one mandatory parameter which must be specified: “outputFullFilePath”. This will be the path where the csv will be stored. Providing an invalid or unreachable path will result in the output being lost.
Optional parameters are:
- csvSeparator: this will be used as separator for the output csv file, by default its value is ‘;’
- internalSeparator: this will be used as separator inside csv fields (make sure it is different from the csvSeperator), by default its value = ‘,’
- selectSites: if selected you will be prompted to select of which site collections the groups will be collected (this is a switch it requires no value, if omitted its value is false).
The output will be a csv file with the following headers: SiteCollectionUrl, LoginName, Title, OwnerLoginName, OwnerTitle, GroupUsers, GroupRoles
If the output file is opened in Microsoft Excel the columns can be used for filtering and searching. Making it an east way to find out who is in which group or where a certain person has access over all selected site collections.
Important note: groups can only be collected if the account that runs the script is site collection admin. Tenant admin is not enough! The account has to be specified at each site collection as site collection admin.
Important note: before the following script can be run a connection to the Microsoft Online service and the SharePoint Online service must be established. For more information on how to achieve this, check out this previous post.
Here is the total script (further down I will highlight the main parts of the script):
function Get-SiteCollectionGroups { Param( [Parameter(Mandatory=$true)] [ValidateNotNull()] [string]$outputFullFilePath, [Parameter(Mandatory=$false)] [switch]$selectSites, [Parameter(Mandatory=$false)] [char]$csvSeparator = ';', [Parameter(Mandatory=$false)] [char]$internalSeparator = ',' ) Write-Host "Collecting site collection groups"; $SiteCollectionGroups = @(); $sites = $null; if($selectSites) { $sites = Get-SPOSite -Detailed | Out-GridView -Title "Select site collections to collect groups from" -PassThru; } else { $sites = Get-SPOSite -Detailed; } [int]$counter = 0; [int]$total = $sites.Count; [string]$counterFormat = "0" * $total.ToString().Length; foreach($site in $sites) { $counter++; Write-Host "$($counter.ToString($counterFormat))/$($total.ToString($counterFormat)) [" -ForegroundColor Yellow -NoNewline; Write-Host "$($site.Url)" -ForegroundColor Cyan -NoNewline; Write-Host "]: " -ForegroundColor Yellow -NoNewline; try { $groups = Get-SPOSiteGroup -Site $site; foreach($group in $groups) { [string]$groupUsers = ""; foreach($user in $group.Users) { $groupUsers += "$user$($internalSeparator)"; } if($groupUsers -match "$($internalSeparator)$") { $groupUsers = $groupUsers.Substring(0, $groupUsers.Length-1); } [string]$groupRoles = ""; foreach($role in $group.Roles) { $groupRoles += "$role$($internalSeparator)"; } if($groupRoles -match "$($internalSeparator)$") { $groupRoles = $groupRoles.Substring(0, $groupRoles.Length-1); } $group | Add-Member -MemberType NoteProperty -Name "SiteCollectionUrl" -Value $site.Url $group | Add-Member -MemberType NoteProperty -Name "GroupUsers" -Value $groupUsers $group | Add-Member -MemberType NoteProperty -Name "GroupRoles" -Value $groupRoles $SiteCollectionGroups += $group; } Write-Host "$($groups.Count) groups are successfully collected" -ForegroundColor Green; } catch { Write-Host "Groups could not be collected" -ForegroundColor Red; } } $SiteCollectionGroups | Select SiteCollectionUrl,LoginName,Title,OwnerLoginName,OwnerTitle,GroupUsers,GroupRoles | Export-Csv -Path $outputFullFilePath -Delimiter $csvSeparator -NoTypeInformation Write-Host "Site collection groups are collected and written to $outputFullFilePath" -ForegroundColor Green } # 2 examples, the first is a minimal call, the second is a call with all optional parameters #Get-SiteCollectionGroups -outputFullFilePath "C:\Backup\AllSiteCollectionGroups$(Get-Date -Format "yyyyMMddhhmmss").csv" #Get-SiteCollectionGroups -outputFullFilePath "C:\Backup\AllSiteCollectionGroups$(Get-Date -Format "yyyyMMddhhmmss").csv" -csvSeparator ',' -internalSeparator ';' -selectSites
At line 14 we create a generic collection (which can hold any type of object). At line 58 each group is added to this collection. At line 67 this collection is exported to the csv file which is specified at the outputFullFilePath parameter.
If the switch is set to manually select site collections a prompt will be shown. This will be in form of an Out-Gridview (line 18). You can select multiple items with Ctrl or Shift. If manual selection of sites is off (not set) then the groups of all site collections will be collected. Because of the time it takes to collect groups it is advised to only collect the most important site collections. Keep in mind that the collection of the groups is dependant on the permissions of the account that runs them. If the account is not site collection admin of one site no groups will be collected and the host will show a red line where the site collection URL is mentioned (line 64).
Because the process may take a while I added a progress indicator. It does not give an accurate estimation for the remaining time (as it only counts the amount of site collections and not the remaining groups or users). For this three variables are used. They are defined at lines 24 through 26. At line 29 the counter is raised by one for every site collection. At line 30 through 32 the count is written to the host including the URL of the current site collection. Note the switch “NoNewLine” which means that the success or error message (lines 60 and 64) are places behind it in stead of below the counter.
The main loops are quite simple. First there is a loop through all site collections (starts at line 27). Inside this loop there is a loop which loops all groups for each site collection (starts at line 35). Inside each group, all users are added to a string, also all roles of the group (these are only roles on site collection / root site level). After the users and roles are collected the site collection URL, the groups users and the group roles are added to the group object (at lines 55 through 57). Finally the group object is added to the siteCollectionGroups collection.
At the bottom of the script there are three lines commented. The first of the three provides a brief explanation of the two following examples.
The first example (second comment line) is a minimum required use of the function. It only specifies the outputFullFilePath (if this parameter is omitted you will be prompted to enter it before the script is ran.
The second example (third comment line) has all optional parameters, this includes the separators and the manual selection switch.
Save the script someplace, remove the hash (#) before one of the examples, and modify this as it suits your need. Then simply run the file and wait… After completion check the file in the location that is specified in the script and start working the numbers.
Because the file is in CSV format it is easy to load it in PowerShell and use scripting to quickly analyse data.
In my next post I will share a followup script which collects external users over all site collections using the output csv of this script as input.
Hi Andreas,
thank you for providing that script. Did you find any option to accelerate this script? In my test environment it needs a couple of minutes for only a minor amount of site collections.
I’d like to use this script for maintenance and monitoring tasks. I’d like to monitor the quotas as well as the amount of users per site collection on a daily basis. And it seems to be to slow for productive use, when a few thousand site collections exist within the tenant.
Best regards
Christian
Hello Christian,
If you only want to have the ammount of users you can replace line 35 through 59 with the following part:
foreach($group in $groups)
{
[int]$groupUsersCount = $group.Users.Count;
$group | Add-Member -MemberType NoteProperty -Name “SiteCollectionUrl” -Value $site.Url
$group | Add-Member -MemberType NoteProperty -Name “GroupUsersCount” -Value $groupUsersCount
$SiteCollectionGroups += $group;
}
#EndOfCode
Also replace line 67 with the following:
$SiteCollectionGroups | Select SiteCollectionUrl,LoginName,Title,OwnerLoginName,OwnerTitle,GroupUsersCount | Export-Csv -Path $outputFullFilePath -Delimiter $csvSeparator -NoTypeInformation
#EndOfCode
It will improve the speed of the script slightly, however it still is necessary to go through all selected sites and get all groups before it is possible to access the users (or count them).
When it comes to monitoring quotas (and also see how many sub sites there are under a site collection) there are fast ways to collect these. I will post this script shortly.
Pingback: Collect site collection information from Office 365 | AndreasBijl.com
Hello Andreas,
Thank you for sharing this information. What I found out is the script doesn’t go through the subsites, and give the permission details for the sub-sites. I accept the fact the groups are created at the root site collection level but how do we know what permissions the SP security groups have to what sites?
Hello Shine,
Unfortunately this is where PowerShell falls short for SharePoint Online, opposed to the on-premise version of SharePoint there are only a limited amount of cmdlets to be used with the Online version of SharePoint. PowerShell can collect site collection groups and webcount (number of subsites) but not the actual sub sites themselves (which would be possible on-premise). The only way those can be found is through the Client Side Object Model (CSOM) for SharePoint. This way you use JavaScript to communicate with SharePoint through the current open session. With visual studio you can create cmdlets which allow you to collect sub sites as well.. I unfortunately have no example of this available but it might be useful to see if someone else has already shared an example of this.
Hi,
Parameter is spelled wrong on line 8 & 10. Great script, thanks!
Hi Marion,
Thanks for letting me know, I have edited the post 🙂
Hi Andreas,
Thank you for sharing this script.
Is there a way to find all the users listed under UG groups used in a site collection and its subsites.
Hi Andreas,
Thanks for the script…exactly what I was looking for.
Excellent
Cheers
Andy
Hi,
I am using SharePoint online. i have a Site Collection, in that I have many Sub sites around 150. I want to create a Permission report in excel for each sub site(Site URL,Group Name,Permission Level). It will be better if this report shows permission for each sub sites as well as each sub sites list and library also.
Can you tell me the Power Shell Script for this?
Thanks
Abhi.
Great script. Has anyone try using CSOM to get permission details for the sub-sites? I have been searching and found nothing out there.
Hi, do you have Sharepoint online powershell script to get the list of owners (present in site owners group) for all site collections ?
How you get the site owner it the site template is from type group#0 ?
Hi,
I am using SharePoint online. I have a Site Collection, in that I have many Sub sites around 150. I want to create a Permission report in excel for each sub site(Site URL, Group Name, Permission Level). It will be better if this report shows permission for each sub sites as well as each sub sites list and library also.
Can you tell me the Power Shell Script for this?
Thanks
Supreet.
I was kinda looking for this script. Thanks for sharing, Andreas!