SharePoint Online: Export User Profile Properties to CSV using PowerShell

Requirement: Export All User Profiles and Properties from SharePoint Online to a CSV File.

SharePoint Online: Export User Profiles to CSV using PowerShell
Here is how to export user profiles in SharePoint Online using PowerShell
#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
This PowerShell script gets user profiles from given site collection and exports them to Excel file (CSV). Well, How do we extract user profiles for all site collections? There is no direct way to connect to User Profile Service Application in SharePoint Online and get all user profile properties for all users in the tenant using CSOM. Either you'll have iterate through all Site Collections (and remove duplicates among them!) or use Web Services to connect to SharePoint Online and retrieve all user profiles.

However, There is a better way: Lets use the Combination of SharePoint Online CSOM and Azure AD Connect!

Pr-Requisites: Make sure you have SharePoint Online Client SDK (https://www.microsoft.com/en-us/download/details.aspx?id=42038) and Azure Active Directory Module (https://technet.microsoft.com/en-us/library/dn975125.aspx) installed on your client machine, before using this script!

PowerShell to Export All User Profile Properties in SharePoint Online
Rather getting user profiles belong to a particular site collection, lets get all user profiles of the tenant and export user profile properties to CSV file.
#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
This script extracts all user profiles from SharePoint online Tenant to a CSV File with following properties:
  • Account
  • Full Name
  • E-Mail
  • Department
  • Location,
  • Job Title
You can add additional properties. Here is the Report output created by the PowerShell Script:
sharepoint online powershell export user profile properties
SharePoint Online: Export User Profile Properties to CSV using PowerShell SharePoint Online: Export User Profile Properties to CSV using PowerShell Reviewed by Salaudeen Rajack on August 17, 2017 Rating: 5

No comments:

Please Login and comment to get your questions answered!

Powered by Blogger.