Monday, July 8, 2013

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:

[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:
Export SharePoint Users and Groups to Excel using PowerShell
 
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
export users from sharepoint group powershell

Related Posts:



You might also like:
SharePoint Usage Reports
Usage reports, collaboration and audit for SharePoint.
Document SharePoint Farm
Automatically generate SharePoint documentation.
*Sponsored


Check out these SharePoint products:

6 comments :

  1. Worked like a charm. Thanks much!

    ReplyDelete
  2. Here the script for getting the list only fr site owner on the web application

    [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"

    ReplyDelete
  3. Nice Post...Worked fine... Thank u..

    ReplyDelete
  4. power shell script for getting unique users list to site collection in share point

    ReplyDelete
  5. Nice post. Thanks a lot.

    How will I get user department.
    Tried with $($user.Department) , but not fetching.

    ReplyDelete

Please Login and comment to get your questions answered!


You might also like:

Related Posts Plugin for WordPress, Blogger...