Export SharePoint Users and Groups to Excel using PowerShell
Requirement:
Export SharePoint Users and Groups to Excel for analyzing SharePoint Groups and Users along with their Account Name, E-mails!We can export SharePoint User Group to excel using PowerShell. Here is how:
PowerShell Script to Export Users & Groups:
This script will Export SharePoint user group to excel. Here is the report output:
PowerShell script to Get All Groups and Members of Each group:
How to Get members of a particular group in SharePoint 2007 using PowerShell:
PowerShell script to Get Members of Each Group at Web (Sub-Site) Level:
Related Posts:
Export SharePoint Users and Groups to Excel for analyzing SharePoint Groups and Users along with their Account Name, E-mails!We can export SharePoint User Group to excel using PowerShell. Here is how:
PowerShell Script to Export Users & Groups:
[System.Reflection.Assembly]::LoadWithPartialName("Microsoft.SharePoint") #Using Get-SPSite in MOSS 2007 function global:Get-SPSite($url) { return new-Object Microsoft.SharePoint.SPSite($url) } function global:Get-SPWeb($url) { $site= New-Object Microsoft.SharePoint.SPSite($url) if($site -ne $null) { $web=$site.OpenWeb(); } return $web } $URL="http://sharepoint.crescent.com/sites/csaportal/" $site = Get-SPSite $URL #Write the Header to "Tab Separated Text File" "Site Name`t URL `t Group Name `t User Account `t User Name `t E-Mail" | out-file "d:\UsersandGroupsRpt.txt" #Iterate through all Webs foreach ($web in $site.AllWebs) { #Write the Header to "Tab Separated Text File" "$($web.title) `t $($web.URL) `t `t `t `t " | out-file "d:\UsersandGroupsRpt.txt" -append #Get all Groups and Iterate through foreach ($group in $Web.groups) { "`t `t $($Group.Name) `t `t `t " | out-file "d:\UsersandGroupsRpt.txt" -append #Iterate through Each User in the group foreach ($user in $group.users) { #Exclude Built-in User Accounts if(($User.LoginName.ToLower() -ne "nt authority\authenticated users") -and ($User.LoginName.ToLower() -ne "sharepoint\system") -and ($User.LoginName.ToLower() -ne "nt authority\local service")) { "`t `t `t $($user.LoginName) `t $($user.name) `t $($user.Email)" | out-file "d:\UsersandGroupsRpt.txt" -append } } } } write-host "Report Generated at d:\UsersandGroupsRpt.txt"
This script will Export SharePoint user group to excel. Here is the report output:
PowerShell script to Get All Groups and Members of Each group:
[System.Reflection.Assembly]::LoadWithPartialName("Microsoft.SharePoint") #Using Get-SPSite in MOSS 2007 function global:Get-SPSite($url) { return new-Object Microsoft.SharePoint.SPSite($url) } function global:Get-SPWeb($url) { $site= New-Object Microsoft.SharePoint.SPSite($url) if($site -ne $null) { $web=$site.OpenWeb(); } return $web } $URL="https://sharepoint.crescent/sites/helpdesk/us" $site = Get-SPSite $URL if (Get-SPWeb($url).HasUniqueRoleAssignments -eq $true) { $Web=Get-SPWeb($url) } else { $web= $site.RootWeb } #Get all Groups and Iterate through foreach ($group in $Web.sitegroups) { write-host " Group Name: "$group.name "`n---------------------------`n" #Iterate through Each User in the group foreach ($user in $group.users) { write-host $user.name "`t" $user.LoginName "`t" $user.Email | FT } write-host "==================================" #Group Separator }
How to Get members of a particular group in SharePoint 2007 using PowerShell:
[System.Reflection.Assembly]::LoadWithPartialName("Microsoft.SharePoint") #Using Get-SPSite in MOSS 2007 function global:Get-SPSite($url) { return new-Object Microsoft.SharePoint.SPSite($url) } Function global:Get-SPWeb($url) { $site= New-Object Microsoft.SharePoint.SPSite($url) if($site -ne $null) { $web=$site.OpenWeb() } return $web } $URL="http://sharepoint.crescent.com/sites/operations/" $site = Get-SPSite $URL $web= $site.OpenWeb() #Get the Group by its name $Group = $Web.sitegroups | Where-Object {$_.Name -eq "CSA Test All Entity Users"} #Iterate through Each User in the group foreach ($user in $group.users) { write-host $user.name "`t" $user.LoginName "`t" $user.Email }Its also possible to read user properties from User Profile.
PowerShell script to Get Members of Each Group at Web (Sub-Site) Level:
Add-PSSnapin Microsoft.SharePoint.PowerShell -ErrorAction SilentlyContinue $WebURL="http://portal.crescent.com/Teams/sales/" $Web = Get-SPWeb $WebURL #Get all Groups and Iterate through foreach ($group in $Web.groups) { #Get Permission Levels Applied to the Group $RoleAssignment = $Web.RoleAssignments.GetAssignmentByPrincipal($group) $RoleDefinitionNames="" foreach ($RoleDefinition in $RoleAssignment.RoleDefinitionBindings) { $RoleDefinitionNames+=$RoleDefinition.Name+";" } write-host "Group Name: $($Group.name) : Permissions: $($RoleDefinitionNames) ----" #Iterate through Each User in the group foreach ($user in $group.users) { write-host $user.name "`t" $user.LoginName "`t" $user.Email | FT } }and the output: SharePoint PowerShell export group members
Related Posts:
Worked like a charm. Thanks much!
ReplyDeleteHere the script for getting the list only fr site owner on the web application
ReplyDelete[System.Reflection.Assembly]::LoadWithPartialName("Microsoft.SharePoint")
#Using Get-SPSite in MOSS 2007
function global:Get-SPSite($url)
{
return new-Object Microsoft.SharePoint.SPSite($url)
}
function global:Get-SPWeb($url)
{
$site= New-Object Microsoft.SharePoint.SPSite($url)
if($site -ne $null)
{
$web=$site.OpenWeb();
}
return $web
}
$farm = [Microsoft.SharePoint.Administration.SPFarm]::Local
$farmWebServices = $farm.Services | where -FilterScript {$_.GetType() -eq [Microsoft.SharePoint.Administration.SPWebService]}
#Write the Header to "Tab Separated Text File"
"Site Name`t URL `t Group Name `t User Account `t User Name `t E-Mail" | out-file "d:\UsersandGroupsRpt.txt"
$SPWebApp = Get-SPWebApplication https://sharepoint
foreach ($site in $SPWebApp.Sites)
{
$sites = Get-SPSite $site.url
#Iterate through all Webs
foreach ($web in $sites.AllWebs)
{
#Write the Header to "Tab Separated Text File"
"$($web.title) `t $($web.URL) `t `t `t `t " | out-file "d:\UsersandGroupsRpt.txt" -append
#Get all Groups and Iterate through
$Group = $Web.sitegroups | Where-Object {$_.Name -Like "*Owners*" }
foreach ($group in $Web.groups)
{
if(($Group.Name.ToLower() -Like "*owners*"))
{
"`t `t $($Group.Name) `t `t `t " | out-file "d:\UsersandGroupsRpt.txt" -append
#Iterate through Each User in the group
foreach ($user in $group.users)
{
#Exclude Built-in User Accounts
"`t `t `t $($user.LoginName) `t $($user.name) `t $($user.Email)" | out-file "d:\UsersandGroupsRpt.txt" -append
}
} }
}
}
write-host "Report Generated at d:\UsersandGroupsRpt.txt"
Nice Post...Worked fine... Thank u..
ReplyDeletepower shell script for getting unique users list to site collection in share point
ReplyDeleteNice post. Thanks a lot.
ReplyDeleteHow will I get user department.
Tried with $($user.Department) , but not fetching.
Well, Department property is not part of SPUser object. Query either UIL or SharePoint user profile store How to Query SharePoint User Information List using PowerShell
DeleteQuery SharePoint User Profile Properties using PowerShell
Hello Admin,
ReplyDeleteExporting SP groups and Members does not work for SharePoint 2013.
Very first script.
Please check and correct.
Thanks.
Hi,I have 2 questions: first, if the user account is different than the user name for example user account is test123 but full name is john doe would the user name column display john doe. My sec question how do i get the user Telephone number to show in the report as well.
ReplyDeleteThank you for this amazing script.
If i want the user phone number to show how to do that i tried $($user.Phone). Also, are the user name and user login different or they always will be same values
ReplyDelete"Phone Number" is not available in SPUser object, But you can query user profile properties to get phone number and other fields (If they are available in AD!) How to Query User Profile Properties using PowerShell
DeleteUser.Name is the Display Name of the userf and User.LoginName is the Login ID (Domain\LoginID)
How to get created date as well in the export?
ReplyDelete