Real world experiences of SharePoint CSS engineers from Microsoft UK

Office 365 - Retrieve User Profile Properties using CSOM with PowerShell

Office 365 - Retrieve User Profile Properties using CSOM with PowerShell

  • Comments 13
  • Likes

Update 12/04/14 - I have added an example that demonstrates how to export User Profile Properties to a CSV file at the end of this post.

The example PowerShell script below can be used to retrieve all user profile properties for all users within a Site Collection. It doesn't appear to be possible to connect to the User Profile Service Application and retrieve profile properties for all users with the tenant using CSOM, the only approach is to perform this at a Site Collection level, you could of course add a ForEach loop to iterate through all Site Collections however additional effort would be required to remove duplicate profile data from the output as it's likely that each user will have permission to multiple Site Collections therefore would be retrieved multiple times using this approach.

It requires two variables to be updated - $User which is the tenant admin and $SiteURL which is the URL for the Site Collection that you wish to retrieve User Profile data from.

#Please install the SharePoint client components SDK - prior to running this script.

#Specify tenant admin and URL
$User = ""

#Configure Site URL and User
$SiteURL = ""

#Add references to SharePoint client assemblies and authenticate to Office 365 site - required for CSOM
Add-Type -Path "C:\Program Files\Common Files\Microsoft Shared\Web Server Extensions\15\ISAPI\Microsoft.SharePoint.Client.dll"
Add-Type -Path "C:\Program Files\Common Files\Microsoft Shared\Web Server Extensions\15\ISAPI\Microsoft.SharePoint.Client.Runtime.dll"
Add-Type -Path "C:\Program Files\Common Files\Microsoft Shared\Web Server Extensions\15\ISAPI\Microsoft.SharePoint.Client.UserProfiles.dll"
$Password = Read-Host -Prompt "Please enter your password" -AsSecureString
$Creds = New-Object Microsoft.SharePoint.Client.SharePointOnlineCredentials($User,$Password)

#Bind to Site Collection
$Context = New-Object Microsoft.SharePoint.Client.ClientContext($SiteURL)
$Context.Credentials = $Creds

#Identify users in the Site Collection
$Users = $Context.Web.SiteUsers

#Create People Manager object to retrieve profile data
$PeopleManager = New-Object Microsoft.SharePoint.Client.UserProfiles.PeopleManager($Context)
Foreach ($User in $Users)
    $UserProfile = $PeopleManager.GetPropertiesFor($User.LoginName)
    If ($UserProfile.Email -ne $null)
        Write-Host "User:" $User.LoginName -ForegroundColor Green
        Write-Host ""

Here is an example of the output.

If you only need to retrieve a selection of user profile properties rather than everything, the script can be easily updated - simply replace $UserProfile.UserProfileProperties with $UserProfile | Select (Property Names). The following properties can be retrieved using this approach:

  • AccountName
  • DirectReports
  • DisplayName
  • Email
  • PersonalURL
  • PictureURL
  • Title
  • UserURL

To retrieve AccountName,Email and PictureURL the following command can be used - $UserProfile | Select AccountName,Email,PictureURL

If you need to retrieve other profile properties you will need to use the following approach - $UserProfile.UserProfileProperties.PropertyName, for example to retrieve the department for a user the following can be used $UserProfile.UserProfileProperties.Department.

Below is an example of how to export User Profile Properties to a CSV file - which you may find a little more useful than outputting to the console. Simply replace the last section of the script above with the script below, you need to update the $Output variable to specify the location to output the CSV file to.

If you need to add any additional properties, two lines in the script need to be updated (highlighted). The first line specifies the headings of the CSV file, the order of which need to match exactly the properties specified in the second line that is highlighted.

The second line that is highlighted specifies the actual properties to output for each user. The $UserProfile and $UPP variables can be used to select the properties, $UPP is $UserProfile.UserProfileProperties. My example contains both types of properties.

#Create People Manager object to retrieve profile data
$Output = "D:\Output.csv"
$Headings = "Name","Email","OneDrive URL","Phone","Job Title","Department"
$Headings -join "," | Out-File -Encoding default -FilePath $Output

$PeopleManager = New-Object Microsoft.SharePoint.Client.UserProfiles.PeopleManager($Context)
Foreach ($User in $Users)
    $UserProfile = $PeopleManager.GetPropertiesFor($User.LoginName)
    If ($UserProfile.Email -ne $null)
        $UPP = $UserProfile.UserProfileProperties
        $Properties = $UserProfile.DisplayName,$UserProfile.Email,$UserProfile.PersonalUrl, $UPP.WorkPhone,$UPP.'SPS-JobTitle',$UPP.Department
        $Properties -join "," | Out-File -Encoding default -Append -FilePath $Output

 The example outputs the following properties:

  • Name
  • Email
  • OneDrive URL
  • Phone
  • Job Title
  • Department

Here is the output file that is created:

Hopefully this gives you enough basic information to make a start using PowerShell to retrieve user profile information.

Brendan Griffin - @brendankarl

  • Thanks for posting this. We've been looking for a solution to output profile data for ages. We've been asked to output users profiles to a file and have managed to output to a csv by adding "$UserProfile.UserProfileProperties | Out-File C:\psoutputs\users.csv -append" to your code.

    We are now having trouble manipulating that data into something readable as it outputs into rows instead of columns. We think we might need to define an array but have limited knowledge of PowerShell. Are you or any of your readers able to offer any advice or assistance?

  • When I get chance I will update the example to include details on how to do this? How soon do you need this?

  • Brilliant, many thanks - any additional information would be greatly received. With regard to the urgency, we would like to report the information as soon as possible but completely understand that you may have other priorities.

  • Hi Peter, I've added an example that demonstrates how to output to a CSV file, if you have any questions feel free to DM me on Twitter or e-mail me at Microsoft - I'm sure you can guess my email address :)

  • This is wonderful :) I'm attempting this in c# rather than PS, but the principle idea is the same and EXACTLY what we needed for a client. If you take this a step further and open a client context object pointed at the Personal or OneDrive URL, and get the documents library, you can see who is actually USING their one drive, and who hasn't visited their profile to update their properties. This is a great tool for gauging user adoption. Cheers! - Baltimore, MD

  • when I'm trying to execute this script i receive following error:

    Exception calling "ExecuteQuery" with "0" argument(s): "The remote server returned an error: (401) Unauthorized."
    At line:22 char:1
    + $Context.ExecuteQuery()
    + ~~~~~~~~~~~~~~~~~~~~~~~
    + CategoryInfo : NotSpecified: (:) [], MethodInvocationException
    + FullyQualifiedErrorId : WebException

    The collection has not been initialized. It has not been requested or the request has not been executed. It may need to be explicitly requested.
    At line:26 char:10
    + Foreach ($User in $Users)
    + ~~~~~
    + CategoryInfo : OperationStopped: (:) [], CollectionNotInitializedException
    + FullyQualifiedErrorId : Microsoft.SharePoint.Client.CollectionNotInitializedException

    I'm admin in office 365 and adapted the values to ours. any ideas?

  • 401 looks like an authentication issue, are your credentials correct?

  • Getting following error on this line: $PeopleManager = New-Object Microsoft.SharePoint.Client.UserProfiles.PeopleManager($Context)

    New-Object : Cannot find an overload for "PeopleManager" and the argument count: "1".
    + $PeopleManager = New-Object Microsoft.SharePoint.Client.UserProfiles.PeopleManag ...
    + ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
    + CategoryInfo : InvalidOperation: (:) [New-Object], MethodException
    + FullyQualifiedErrorId : ConstructorInvokedThrowException,Microsoft.PowerShell.Commands.NewObjectCommand

  • Hi Brendan, if the OneDrive URL is blank, does that mean the user has not setup their Personal Site? I am trying to find powershell script that will give me an output of users who have yet to setup OneDrive and this could be my answer. Many thanks in advance

  • Great. Thanks!

  • @VReyes - that's correct.

  • Is it possible to bulk delete the OneDrive account (Personal site) of all the users using PowerShell. If possible what command should I use. Kindly advise.

  • Hi excellent post , however to retrieve all users in system we can Use the MySiteURL instead of individual Site Collections.

Your comment has been posted.   Close
Thank you, your comment requires moderation so it may take a while to appear.   Close
Leave a Comment