Thursday, 7 May 2015

PowerShell: Get all sharepoint groups and users in Excel sheet

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:
?
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
[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:
?
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
[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:
?
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
[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 read user Profile Properties:
?
1
2
3
4
5
6
#Get Profile Properties from User Profile
$ServerContext=[Microsoft.Office.Server.ServerContext]::GetContext($site)
 $UPM = New-Object Microsoft.Office.Server.UserProfiles.UserProfileManager($ServerContext)
 $UserProfile=$upm.GetUserProfile($user.LoginName )
 $Title=$UserProfile["Title"].Value
 $Department=$UserProfile["Department"].Value


Read more: http://www.sharepointdiary.com/2013/07/export-sharepoint-users-and-groups-to-excel-using-powershell.html#ixzz3ZWb7m6JM



REF:
http://www.sharepointdiary.com/2013/07/export-sharepoint-users-and-groups-to-excel-using-powershell.html
Posted by at

No comments:

Post a Comment

Subscribe to: Post Comments (Atom)