Office 365 – Retrieve User Profile Properties using CSOM with PowerShell


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 – http://www.microsoft.com/en-us/download/details.aspx?id=35585 prior to running this script.

#Specify tenant admin and URL
$User = "admin@tenant.onmicrosoft.com"

#Configure Site URL and User
$SiteURL = "https://tenant.sharepoint.com/sites/site"

#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
$Context.Load($Users)
$Context.ExecuteQuery()

#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)
    $Context.Load($UserProfile)
    $Context.ExecuteQuery()
    If ($UserProfile.Email -ne $null)
        {
        Write-Host "User:" $User.LoginName -ForegroundColor Green
        $UserProfile.UserProfileProperties
        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)
    $Context.Load($UserProfile)
    $Context.ExecuteQuery()
    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

Comments (26)

  1. Anonymous says:

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

  2. Anonymous says:

    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 🙂

  3. Anonymous says:

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

  4. Peter Fell says:

    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:psoutputsusers.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?

  5. Peter Fell says:

    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.

  6. @SparkDustJoe says:

    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

  7. @3bser says:

    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?

  8. PW says:

    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

  9. VReyes says:

    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

  10. Martin says:

    Great. Thanks!

  11. @VReyes – that’s correct.

  12. Jeff25 says:

    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.

  13. Godwin says:

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

  14. karen says:

    When I’m about to run this particular script:

    "$Users = $Context.Web.SiteUsers
    $Context.Load($Users)
    $Context.ExecuteQuery()"

    I get Exception calling "ExecuteQuery" with "0" argument(s): "The remote server returned an error: (403) Forbidden." At line:1 char:1 $Context.ExecuteQuery()

    Please help! Thanks!

  15. @Karen says:

    Can you confirm that you are a Site Collection Admin for the site?

  16. Maooz says:

    Hi,
    Many thanks for the blog. I have a question for you, how can I retrieve this information through the REST API?

  17. Maooz says:

    I am using Sharepoint Online (Office 365).

  18. Gal Anonim says:

    Great post! Thank you

  19. Anthony Balogun says:

    Thanks Guys, for making this script available even just to have a successful connection to the SharePoint online UserProfile and displaying information on the SharePoint Online Management shell is something worth cheering after over 1 week of trying. Your script above has help displayed some information about who the SharePoint Admins are although I don’t what part of the script is responsible for that ?

    I now need to set the work email of each user WorkEmail from user@company.onmicrosoft.com to user@emailaddressofcompany.org can you please help customise the script to achieve this please guys ?

    1. Whilst you could change this, changing the actual e-mail addresses needs to be performed from Exchange Online rather than SharePoint.

  20. Anthony Balogun says:

    In addition to the above set of WorkEmail properties , I need this piped using CSV with exported users: with headers of UPN | Email Addresses. There may be another smart way of achieving this without CSV perhaps querying and updating user Properties directly ?

    1. These two values can be retrieved using $UPP.’SPS-UserPrincipalName’ and $upp.WorkEmail. All you need to do is update the $Headings and $Properties variables to reflect these values.

  21. profileuser says:

    Big thanks Brendan, for your candid scripts. It has helped retrieved information the main users of our sharepoint online sites in addition to making a successful connection I’ve trying for over a week to achieve.
    Could the scripts be customised to set the WorkEmail property to anything but user@compnay.onmicrosoft.com please for all SharePoint licensed users ?. We need to change the WorkEmail of over 10,000 users.

    1. Whilst you could change this, changing the actual e-mail addresses needs to be performed from Exchange Online rather than SharePoint.

  22. profileuser says:

    Hi Brendan,

    The script exported the output file however with the Title Headings only. There were no entries below those heading in the output file. Could you please help Assist us please .?

    Scripts carried out below:
    #Specify tenant admin and URL
    $User = “user@company.com”

    #Configure Site URL and User
    $SiteURL = “https://company-admin.sharepoint.com”

    #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\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”
    Add-Type -Path “C:\Program Files\Common Files\Microsoft Shared\Web Server Extensions\16\ISAPI\Microsoft.SharePoint.Client.Search.dll”
    Add-Type -Path “C:\Program Files\Common Files\Microsoft Shared\Web Server Extensions\16\ISAPI\Microsoft.SharePoint.Client.Taxonomy.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
    $Context.Load($Users)
    $Context.ExecuteQuery()

    #create people manager object to retrieve profile data
    $Output = “C:\folder\SharePointOnlineOutput.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)
    $Context.Load($UserProfile)
    $Context.ExecuteQuery()
    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
    }
    }

    1. Hmm, were any errors returned? Also, you don’t need to specify the tenant admin site, any site collection URL will suffice. Could you try re-running using a “normal” site collection ($SiteURL variable).