It’s a common requirement in SharePoint to prepare reports from user profile properties. Here is a PowerShell script to get user profile properties from SharePoint exported to a csv file.
#CSOM first
[System.Reflection.Assembly]::LoadWithPartialName("Microsoft.SharePoint.Client") | Out-Null
[System.Reflection.Assembly]::LoadWithPartialName("Microsoft.SharePoint.Client.Runtime") | Out-Null
#SOM
Add-PSSnapIn Microsoft.SharePoint.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"
#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"]
'Hire Date' = $UserProfile.UserProfileProperties["SPS-HireDate"]
'Birthday' = $UserProfile.UserProfileProperties["SPS-Birthday"]
}
}
}
#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://tennant.sharepoint.com"
$CSVPath="c:\UserProfiles.csv"
Export-AllUserProfiles -TenantURL $TenantURL -CSVPath $CSVPath
This script will export the requested user profile properties of all users as a CSV file.
Questions? Please send your questions or make an appointment today for a consult via email to doctor@doctorsharepoint.com