网站搜索

SharePoint Online：使用 PowerShell 将用户配置文件属性导出到 CSV

要求：将所有用户配置文件和属性从 SharePoint Online 导出到 CSV 文件。

SharePoint Online：使用 PowerShell 将用户配置文件导出到 CSV

用户个人资料包含有关个人的广泛信息，包括职位、部门、联系信息等。有时我们需要从 SharePoint Online 环境导出用户配置文件属性。使用 PowerShell，这非常容易，在这篇博文中，我将向您展示如何将用户配置文件属性值从 SharePoint Online 导出为 CSV 格式。

以下是如何使用 PowerShell 在 SharePoint Online 中导出用户配置文件：


#Load SharePoint CSOM Assemblies
Add-Type -Path "C:\Program Files\Common Files\Microsoft Shared\Web Server Extensions\16\ISAPI\Microsoft.SharePoint.Client.dll"
Add-Type -Path "C:\Program Files\Common Files\Microsoft Shared\Web Server Extensions\16\ISAPI\Microsoft.SharePoint.Client.Runtime.dll"
Add-Type -Path "C:\Program Files\Common Files\microsoft shared\Web Server Extensions\16\ISAPI\Microsoft.SharePoint.Client.UserProfiles.dll"

Function Export-SPOUserProfileProperties()
{
    param
    (
        [Parameter(Mandatory=$true)] [string] $SiteURL,
        [Parameter(Mandatory=$true)] [string] $CSVPath
    )    
    Try {
        #Setup Credentials to connect
        $Cred= Get-Credential
        $Credentials = New-Object Microsoft.SharePoint.Client.SharePointOnlineCredentials($Cred.Username, $Cred.Password)

        #Setup the context
        $Ctx = New-Object Microsoft.SharePoint.Client.ClientContext($SiteURL)
        $Ctx.Credentials = $Credentials
        
        #Delete the CSV report file if exists
        if (Test-Path $CSVPath) { Remove-Item $CSVPath }

        #Get all Users
        $Users = $Ctx.Web.SiteUsers
        $Ctx.Load($Users)
        $Ctx.ExecuteQuery()
        
        Write-host "Total Number of Profiles Found:"$Users.count -f Yellow
        #Get User Profile Manager
        $PeopleManager = New-Object Microsoft.SharePoint.Client.UserProfiles.PeopleManager($Ctx)
        #Array to hold result
        $UserProfileData = @()

        Foreach ($User in $Users)
        {
            Write-host "Processing User Name:"$User.LoginName
            #Get the User Profile
            $UserProfile = $PeopleManager.GetPropertiesFor($User.LoginName)
            $Ctx.Load($UserProfile)
            $Ctx.ExecuteQuery()
            if($UserProfile.Email -ne $Null)
            {
            #Send Data to object array
            $UserProfileData += New-Object PSObject -Property @{
            'User Account' = $UserProfile.UserProfileProperties["UserName"]
            'Full Name' = $UserProfile.UserProfileProperties["PreferredName"]
            'E-mail' =  $UserProfile.UserProfileProperties["WorkEmail"]
            'Department' = $UserProfile.UserProfileProperties["Department"]
            'Location' = $UserProfile.UserProfileProperties["Office"]
            'Phone' = $UserProfile.UserProfileProperties["WorkPhone"]
            'Job Title' = $UserProfile.UserProfileProperties["Title"]
            }
            }
        }
        #Export the data to CSV
        $UserProfileData | Export-Csv $CSVPath -Append -NoTypeInformation

        write-host -f Green "User Profiles Data Exported Successfully to:" $CSVPath
    }
    Catch {
        write-host -f Red "Error Exporting User Profile Properties!" $_.Exception.Message
    }
}

#Call the function
$SiteURL="https://crescent-my.sharepoint.com"
$CSVPath="C:\Temp\UserProfiles.csv"

Export-SPOUserProfileProperties -SiteURL $SiteURL -CSVPath $CSVPath

此 PowerShell 脚本从网站集中获取用户配置文件并将其导出到 Excel 文件 (CSV)。那么，我们如何提取所有网站集的用户配置文件？没有直接的方法可以连接到 SharePoint Online 中的 User Profile Service 应用程序并使用 CSOM 获取租户中所有用户的所有用户配置文件属性。您必须遍历所有网站集（并删除其中的重复项！），或者使用 Web 服务连接到 SharePoint Online 并检索所有用户配置文件。

但是，存在更好的方法：让我们使用 SharePoint Online CSOM 和 Azure AD Connect 的组合！

Pr-Requisites：确保您有 SharePoint Online 客户端 SDK (https://www.microsoft.com/en-us/download/details.aspx?id=42038) 和 Azure Active Directory 模块 (在使用此脚本之前，请先将 https://technet.microsoft.com/en-us/library/dn975125.aspx）安装在您的客户端计算机上！

PowerShell 用于导出 SharePoint Online 中的所有用户配置文件属性

让我们获取租户的所有用户配置文件并将用户配置文件属性导出到 CSV 文件，而不是获取属于特定网站集的用户配置文件。


#Load SharePoint CSOM Assemblies
Add-Type -Path "C:\Program Files\Common Files\Microsoft Shared\Web Server Extensions\16\ISAPI\Microsoft.SharePoint.Client.dll"
Add-Type -Path "C:\Program Files\Common Files\Microsoft Shared\Web Server Extensions\16\ISAPI\Microsoft.SharePoint.Client.Runtime.dll"
Add-Type -Path "C:\Program Files\Common Files\microsoft shared\Web Server Extensions\16\ISAPI\Microsoft.SharePoint.Client.UserProfiles.dll"

#Import Azure AD Module
Import-Module MSOnline

Function Export-AllUserProfiles()
{
    param
    (
        [Parameter(Mandatory=$true)] [string] $TenantURL,
        [Parameter(Mandatory=$true)] [string] $CSVPath
    )    
    Try {
        #Setup Credentials to connect
        $Cred= Get-Credential
        $Credentials = New-Object Microsoft.SharePoint.Client.SharePointOnlineCredentials($Cred.Username, $Cred.Password)

        #Setup the context
        $Ctx = New-Object Microsoft.SharePoint.Client.ClientContext($TenantURL)
        $Ctx.Credentials = $Credentials
        
        #Delete the CSV report file if exists
        if (Test-Path $CSVPath) { Remove-Item $CSVPath }

        #Get all Users
        Connect-MsolService -Credential $Cred
        $Users = Get-MsolUser -All |  Select-Object -ExpandProperty UserPrincipalName
        
        Write-host "Total Number of Profiles Found:"$Users.count -f Yellow
        #Get User Profile Manager
        $PeopleManager = New-Object Microsoft.SharePoint.Client.UserProfiles.PeopleManager($Ctx)
        #Array to hold result
        $UserProfileData = @()

        Foreach ($User in $Users)
        {
            Write-host "Processing User Name:"$User
            #Get the User Profile
            $UserLoginName = "i:0#.f|membership|" + $User  #format to claims
            $UserProfile = $PeopleManager.GetPropertiesFor($UserLoginName)
            $Ctx.Load($UserProfile)
            $Ctx.ExecuteQuery()
            if($UserProfile.Email -ne $Null)
            {
            #Send Data to object array
            $UserProfileData += New-Object PSObject -Property @{
            'User Account' = $UserProfile.UserProfileProperties["UserName"]
            'Full Name' = $UserProfile.UserProfileProperties["PreferredName"]
            'E-mail' =  $UserProfile.UserProfileProperties["WorkEmail"]
            'Department' = $UserProfile.UserProfileProperties["Department"]
            'Location' = $UserProfile.UserProfileProperties["Office"]
            'Phone' = $UserProfile.UserProfileProperties["WorkPhone"]
            'Job Title' = $UserProfile.UserProfileProperties["Title"]
            }
            }
        }
        #Export the data to CSV
        $UserProfileData | Export-Csv $CSVPath -Append -NoTypeInformation

        write-host -f Green "User Profiles Data Exported Successfully to:" $CSVPath
  }
    Catch {
        write-host -f Red "Error Exporting User Profile Properties!" $_.Exception.Message
    } 
}

#Call the function
$TenantURL="https://crescent.sharepoint.com"
$CSVPath="C:\Temp\UserProfiles.csv"

Export-AllUserProfiles -TenantURL $TenantURL -CSVPath $CSVPath

此脚本将所有用户配置文件从 SharePoint Online 租户提取到具有以下属性的 CSV 文件：

  • 帐户
  • 全名
  • 电子邮件
  • 部门
  • 地点，
  • 职称

您可以添加其他属性。以下是 PowerShell 脚本创建的报告输出：

PnP PowerShell 用于导出所有用户配置文件属性

以下是 PnP PowerShell，用于将每个用户配置文件的所有属性提取到 CSV 报告：


#Config Variables
$AdminSiteURL = "https://crescent-admin.sharepoint.com"
$CSVPath = "C:\Temp\UserProfiles.csv"
 
Try {
    #Connect to AzureAD
    Connect-AzureAD | Out-Null
 
    #Get All Users of the Domain from AzureAD
    $AllUsers = Get-AzureADUser -All:$True -Filter "UserType eq 'Member'"
    Write-host "Total Number of User Profiles Found:"$AllUsers.Count 
 
    #Connect to PnP Online
    Connect-PnPOnline -Url $AdminSiteURL -UseWebLogin
 
    #Iterate through All Users
    $Counter = 1
    $UserProfileData = @()
    ForEach($User in $AllUsers)
    {
        Write-host "`nGetting User Profile Property for: $($User.UserPrincipalName)" -f Yellow
  
        #Get the User Property value from SharePoint  
        $UserProfile = Get-PnPUserProfileProperty -Account ($User.UserPrincipalName)  
    
        #Get User Profile Data
        $UserData = New-Object PSObject
        ForEach($Key in $UserProfile.UserProfileProperties.Keys)
        {  
            $UserData | Add-Member NoteProperty $Key($UserProfile.UserProfileProperties[$Key])
        }
        $UserProfileData += $UserData
        $Counter++
        Write-Progress -Activity "Getting User Profile Data..." -Status "Getting User Profile $Counter of $($AllUsers.Count)" -PercentComplete (($Counter / $AllUsers.Count)  * 100)
    }
    #Export the data to CSV
     $UserProfileData | Export-Csv $CSVPath -NoTypeInformation
  
     write-host -f Green "User Profiles Data Exported Successfully to:" $CSVPath
}
Catch {
    write-host -f Red "Error Getting User Profile Property!" $_.Exception.Message
}

从 SharePoint Online 导出用户配置文件属性对于管理和操作用户数据非常有用。通过执行本文中概述的步骤，您可以导出用户配置文件属性并查看用户配置文件数据。

相关文章