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
        } 
 } 

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:

4 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

Please Login and comment to get your questions answered!

You might also like:

Related Posts Plugin for WordPress, Blogger...