Get and Export User Profile Properties using PowerShell in SharePoint Server
Do you need to export user profile properties from SharePoint for reporting or other purposes? It’s a common requirement in SharePoint to prepare reports from user profile properties. Here are my PowerShell scripts to get user profile properties in SharePoint.
Important: Make sure User Profile Service Application is created, up and running before running these scripts! And run as Farm Administrator!!
Get user profile Properties in SharePoint 2013/2016 using PowerShell
PowerShell is a versatile tool that can be used to automate many tasks in SharePoint. In this blog post, let’s get user profile property value for a given user:
#Configuration Variables
$SiteURL = "https://mysite.crescent.com"
$UserLogin="Crescent\Salaudeen"
#Get Objects
$ServiceContext = Get-SPServiceContext -site $SiteURL
$UserProfileManager = New-Object Microsoft.Office.Server.UserProfiles.UserProfileManager($ServiceContext)
#Get the User Profile
$UserProfile = $UserProfileManager.GetUserProfile($UserLogin)
#Retrieve User Profile Properties
Write-host $UserProfile["PreferredName"]
Write-host $UserProfile["WorkEmail"]
You are not limited to these fields, But there are plenty more fields in User Profiles. To get all available user profile properties, use:
$UserProfile.Properties | Select DisplayName, Name
PowerShell to Query & Export user profile properties of all users in SharePoint:
Let’s use PowerShell to get all user profile properties and export them to CSV.
Add-PSSnapin Microsoft.SharePoint.PowerShell -ErrorAction SilentlyContinue
#Configuration Variables
$SiteURL = "https://portal.crescent.com"
$outputReport = "c:\user_profiles.csv"
#Get Objects
$ServiceContext = Get-SPServiceContext -site $SiteURL
$UPM = New-Object Microsoft.Office.Server.UserProfiles.UserProfileManager($ServiceContext)
#Get All User Profiles
$UserProfiles = $UPM.GetEnumerator()
Write-host "Total Number of User Profiles found:"$UserProfiles.Count
#Array to hold Profiles
$ProfileDataCollection = @()
#Iterate through each profile
foreach ($Profile in $UserProfiles)
{
$ProfileData = New-Object PSObject
#Retrieve User profile Properties
$ProfileData | Add-Member -MemberType NoteProperty -name "Account" -value $Profile["AccountName"]
$ProfileData | Add-Member -MemberType NoteProperty -name "Display Name" -value $Profile["PreferredName"]
$ProfileData | Add-Member -MemberType NoteProperty -name "E-Mail" -value $Profile["WorkEmail"]
$ProfileData | Add-Member -MemberType NoteProperty -name "Manager" -value $Profile["Manager"]
$ProfileData | Add-Member -MemberType NoteProperty -name "Department" -value $Profile["Department"]
#Add to Array
$ProfileDataCollection+=$ProfileData
write-host "Processed Profile:"$profile["PreferredName"]
}
#Export User Profile data to CSV
$ProfileDataCollection | Export-Csv $outputReport -NoType
This script exports the given user profile properties of all users as CSV file:
Same for me
Thanks for this. Another question How can I show empty value in double quotes? Currently it shows comma separated by no quotes.
Hi, I am new to powershell – can you please advise how can I add an exclusion filter to this script? e.g. If I want to exclude results that have no employee numbers in their user profile.
In Line#19, Just Replace: foreach ($Profile in $UserProfiles) with foreach ($Profile in $UserProfiles | Where {$_[“EmployeeNumber”] -ne $NULL}) – as a note, the EmployeeNumber field must be mapped to import from AD!
I just tried to run this on my on-prem 2013 farm. The Total Number of Profiles found output as 1 1 1 1 1 1 1 1 1 1…
It created the CSV file but didn’t populate it with any profile information. Any ideas?
I have the same issue
Same for me. Was there any solution
In my case the code section counting the profiles didn’t work for me. I commented it out:
#Get All User Profiles
$UserProfiles = $UPM.GetEnumerator()
# Write-host “Total Number of User Profiles found:”$UserProfiles.Count
How to get list of “User Profiles Missing from Import”
Should this work in SP2016? I have run the script and am getting a blank CSV
if i want the output in a sharepoint list with 5 columns (firstname, lastname, workemail, jobtitle, workphone), where i need to make changes
Replace the below lines between Line#: 23 to 27.
$ProfileData | Add-Member -MemberType NoteProperty -name “Account” -value $Profile[“FirstName”]
$ProfileData | Add-Member -MemberType NoteProperty -name “Display Name” -value $Profile[“LastName”]
$ProfileData | Add-Member -MemberType NoteProperty -name “E-Mail” -value $Profile[“WorkEmail”]
$ProfileData | Add-Member -MemberType NoteProperty -name “Manager” -value $Profile[“Title”]
$ProfileData | Add-Member -MemberType NoteProperty -name “Department” -value $Profile[“Department”]