Get and Export User Profile Properties using PowerShell in SharePoint 2013
Its a common requirement in SharePoint to prepare reports from user profile properties. Here is 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 using PowerShell
Let’s get user profile property value for a given user.
#Configuration Variables
$SiteURL = "http://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 of 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 to CSV.
Add-PSSnapin Microsoft.SharePoint.PowerShell -ErrorAction SilentlyContinue
#Configuration Variables
$SiteURL = "http://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 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
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”]