共用方式為

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 - https://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

  • Anonymous
    January 01, 2003
    When I get chance I will update the example to include details on how to do this? How soon do you need this?
  • Anonymous
    January 01, 2003
    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 :)
  • Anonymous
    January 01, 2003
    401 looks like an authentication issue, are your credentials correct?
  • Anonymous
    April 08, 2014
    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?
  • Anonymous
    April 09, 2014
    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.
  • Anonymous
    May 15, 2014
    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
  • Anonymous
    May 27, 2014
    The comment has been removed
  • Anonymous
    August 20, 2014
    The comment has been removed
  • Anonymous
    October 07, 2014
    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
  • Anonymous
    October 07, 2014
    Great. Thanks!
  • Anonymous
    October 08, 2014
    @VReyes - that's correct.
  • Anonymous
    October 28, 2014
    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.
  • Anonymous
    November 19, 2014
    Hi excellent post , however to retrieve all users in system we can Use the MySiteURL instead of individual Site Collections.
  • Anonymous
    October 07, 2015
    The comment has been removed
  • Anonymous
    October 08, 2015
    Can you confirm that you are a Site Collection Admin for the site?
  • Anonymous
    January 04, 2016
    Hi,
    Many thanks for the blog. I have a question for you, how can I retrieve this information through the REST API?
  • Anonymous
    January 04, 2016
    I am using Sharepoint Online (Office 365).
  • Anonymous
    January 12, 2016
    Great post! Thank you
  • Anonymous
    June 13, 2016
    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 ?
    • Anonymous
      June 21, 2016
      Whilst you could change this, changing the actual e-mail addresses needs to be performed from Exchange Online rather than SharePoint.
  • Anonymous
    June 13, 2016
    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 ?
    • Anonymous
      June 21, 2016
      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.
  • Anonymous
    June 15, 2016
    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.
    • Anonymous
      June 21, 2016
      Whilst you could change this, changing the actual e-mail addresses needs to be performed from Exchange Online rather than SharePoint.
  • Anonymous
    June 15, 2016
    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 CSOMAdd-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 } }
    • Anonymous
      June 21, 2016
      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).
      • Anonymous
        September 12, 2016
        Hi Brendan,how can i get site permission of all user also.NameEmailOneDrive URLPhoneJob TitleDepartmentpermission grantedsite ownersite admin namesite created date
        • Anonymous
          September 14, 2016
          You can't use this to obtain permissions or information on Site Collections you would need to use Get-SPOSite to do this, depending on the granularity of permissions reporting you need this may not be the best approach. For the remaining properties these are all output by the script in the post.
  • Anonymous
    September 15, 2016
    Thanks for the script , it works well, but there is a catch.As you rightly mentioned, it queries against a site collection, and not against user profile service as such. ( i think the query goes agains user info table in each site collection, correct me if i am wrong here)So there is no way of querying the user profile service to collect properties for all users.Is there any other way ?
  • Anonymous
    November 19, 2016
    Great article, but can you share script to delete the user profile.
    • Anonymous
      November 22, 2016
      User profiles are automatically removed after a user has been deleted, there should be no reason to do this manually.