SharePoint Online: Get a list of site users and their group memberships
Introduction
This posting consolidates notes presenting options for obtaining a list of users and their group memberships for a SharePoint Online site collection. Two scripts are presented. Both can be run within the SharePoint Online Management Shell using standard copy and past methods. Scripts and output formats are shown below.
Credits
Thanks to Vasil L. Michev and Salaudeen Rajack for these scripts.
Scripts
Site Users and Groups Script 1
Get-SPOUser -Site "https://www.contoso.com" -Limit "All" | select DisplayName , LoginName , IsSiteAdmin , IsGroup , @{n = "Groups" ;e = { $_.Groups -join "; " }} | Export-Csv -nti -Path "Users.csv"
Output 1
DisplayName LoginName IsSiteAdmin IsGroup Groups First1 Last1 username1 FALSE FALSE Group1; Group2; Group3 First2 Last2 username2 FALSE FALSE Group1 First3 Last3 username3 FALSE FALSE Group1; Group2; Group6; Group9 etc...
Site Users and Groups Script 2
$SiteURL = "https://www.contoso.com"
$OutputFile = "users.csv"
$objectCollection=@()
$SiteGroups = Get-SPOSiteGroup -Site $SiteURL
foreach($Group in $SiteGroups)
{
$users = Get-SPOUser -Site $SiteURL -Group $Group.Title;
foreach ($user in $users)
{
$email = $user.LoginName.Split('#')[0];
$object = New-Object PSObject;$object | Add-Member -type NoteProperty -Name "Group Name" -Value $Group.Title;
$object | Add-Member -type NoteProperty -Name "Name" -Value $user.DisplayName;
$object | Add-Member -type NoteProperty -Name "Work Email" -Value $email;
$object | Add-Member -type NoteProperty -Name "Account" -Value $user.LoginName;
$objectCollection += $object
}
}
$objectCollection | Export-Csv -Path $OutputFile -NoTypeInformation
Output 2
Group Name Name Work Email Account Group1 First1 Last1 email1 username1 Group1 First2 Last2 email2 username2 Group1 First3 Last3 email3 username3 Group2 First1 Last1 email1 username1 Group2 First3 Last3 email3 username3 Group3 First1 Last1 email1 username1 Group6 First3 Last3 email3 username3 Group9 First3 Last3 email3 username3 etc...
References
- Get-SPOSite
- Get-SPOSiteGroup
- New-Object
- Add-Member
- Select-Object
- Export-csv
- Sharepoint Online Powershell command to get list of users exported without cutting off the groups
- Performing Joins and Splits in PowerShell
- Join Me in a Few String Methods Using PowerShell
- SharePoint Online Management Shell
- Intro to SharePoint Online Management Shell
Notes
- Thanks to Vasil L. Michev for the first script. The second one was provided by a colleague and I believe it is attributable to Salaudeen Rajack but I don't have a URL for it.
- Script 2 was modified significantly from its original so as to enable it to be pasted into the shell and executed from the shell prompt.