Office 365 - User Profiles in Hybrid Model (SharePoint Online & On-Premises)

A - OBJECTIVE: 

My client has a need to manage User Profiles at SharePoint Online (Office365) with least maintenance effort. This includes:

1. Synchronize all default user properties from SharePoint On-premises to SharePoint Online.

2. Create custom properties of user profiles (defined by business needs) at SharePoint Online and sync these custom ones with Active Directory or SharePoint On-Premises.

B - PROBLEMS :

SharePoint Online is designed to synchronize only some default properties from Active Directory (AD) to User Profiles at the main tenant (i.e. no custom properties are supported in this task), such as (reference link):

1. SPS-DistinguishedName [AD property: dn]
2. SID [AD property: objectSid]
3. Manager [AD property: manager]
4. PreferredName [AD property: displayName]
5. FirstName [AD property: givenName]
6. LastName [AD property: sn]
7. SPS-PhoneticDisplayName [AD property: msDS-PhoneticDisplayName]
8. SPS-PhoneticFirstName [AD property: msDS-PhoneticFirstName]
9. SPS-PhoneticLastName [AD property: msDS-PhoneticLastName]
10. WorkPhone [AD property: telephoneNumber]
11. WorkEmail [AD property: Mail/proxyAddress]
12. Office [AD property: physicalDeliveryOfficeName]
13. SPS-JobTitle [AD property: title]
14. Department [AD property: department]
15. UserName [AD property: sAMAccountName]
16. PublicSiteRedirect [AD property: wWWHomePage]
17. SPS-ProxyAddresses [AD property: proxyAddresses]
18. SPS-SourceObjectDN [AD property: msDS-SourceObjectDN]
19. SPS-ClaimID [AD property: <specific to connection>]
20. SPS-ClaimProviderID [AD property: <specific to connection>]
21. SPS-ClaimProviderType [AD property: <specific to connection>]

C - SOLUTION:

From a certain source of user profiles (Active Directory, Exchange Online, SharePoint On-premises), we can implement a synchronization job to replicate all users to SharePoint Online (Office 365)

In this specific task, the single source is designed at the local SharePoint On-premises farm and a scheduled job will be implemented to copy custom properties:

Step 1: using SharePoint 2013 Management Shell to extract all relevant information into a text file.

Step 2:  using SharePoint Online Management Shell to parse the input text to update the properties at User Profiles (Office365) accordingly.

D - SOURCE CODE:

Step 1: to export user properties from the local SharePoint On-Prem

$SPonpremURL = "http://my-SP-On-Premises-URL/"
$date = Get-Date
$FilePath = "C:\Ouput_" + $date.Year + $date.Month + $date.Day + "_Log.txt"

$Site = Get-SPSite -Identity $SPonpremURL
$context = Get-SPServiceContext -Site $site
$upm = New-Object Microsoft.Office.Server.UserProfiles.UserProfileManager($context)
$SPProfile  = $upm.GetEnumerator()
foreach ($SPUser in $SPProfile){
add-content -Path $FilePath "$($SPUser['AccountName']),$($SPUser['EmployeeID']),$($SPUser['PictureURL']),$($SPUser['ReportingManager'])"
}

Step 2: to import user properties into SharePoint Online.

# important to add this DLL to be used in the script
Add-Type -Path "D:\SharePoint\PowerShell\SPO-DLL\Microsoft.SharePoint.Client.dll"
Add-Type -Path "D:\SharePoint\PowerShell\SPO-DLL\Microsoft.SharePoint.Client.Runtime.dll"
Add-Type -Path "D:\SharePoint\PowerShell\SPO-DLL\Microsoft.SharePoint.Client.UserProfiles.dll"

# Source File to be synced from SharePoint On-Prem to Online
$sourcefile = "D:\SharePoint\PowerShell\SPO-DLL\Output_20160825.txt"

#variables that needs to be set before starting the script
$siteURL = "https://sp.sharepoint.com/"
$adminUrl = "https://sp-admin.sharepoint.com"
$userName = "admin@sp.onmicrosoft.com"
# Let the user fill in their password in the PowerShell window
$password = Read-Host "Please enter the password for $($userName)" -AsSecureString

 
# set SharePoint Online credentials
$SPOCredentials = New-Object Microsoft.SharePoint.Client.SharePointOnlineCredentials($userName, $password)  
# Creating client context object
$context = New-Object Microsoft.SharePoint.Client.ClientContext($adminUrl)
$context.credentials = $SPOCredentials
$context.ExecuteQuery() 

$contextLocal = New-Object Microsoft.SharePoint.Client.ClientContext($siteURL)
$contextLocal.credentials = $SPOCredentials
$contextLocal.ExecuteQuery() 

# get User Profiles
# reference: https://blogs.technet.microsoft.com/fromthefield/2014/02/27/office-365-retrieve-user-profile-properties-using-csom-with-powershell/
$PeopleManager = New-Object Microsoft.SharePoint.Client.UserProfiles.PeopleManager($context)
  
# get all users within a site
$Users = $contextLocal.Web.SiteUsers
$contextLocal.Load($Users)
$contextLocal.ExecuteQuery()

Foreach ($User in $Users)
 {
 # Properties MUST be loaded to use the function SetSingleValueProfileProperty
 $UserProfile = $PeopleManager.GetPropertiesFor($User.LoginName)
 $context.Load($UserProfile)
 $context.ExecuteQuery()

 If ($UserProfile.Email -ne $null)
  {
  Write-Host "User:" $User.LoginName -ForegroundColor Green
  
  $employeeID = ""
  $pictureURL = ""
  # to replace a special character : put it into the square bracket []
  $userLoginName = $User.LoginName -replace 'i:0#.f[|]membership[|]', ''
  $userLoginName = $userLoginName -replace '@domain.com.sg', ''
  
  Write-Host "User:" $userLoginName -ForegroundColor Blue
  
  # Read User Profiles from FEOnet (in TXT format)
  Foreach ($line in [System.IO.File]::ReadLines($sourcefile)) {
   $userInfo = $line -split ',';
   #userInfo[0] must be trimmed to remove "DOMAIN\"
   If ($userInfo[0] -ne $null -and $userInfo[0] -eq $userLoginName){
    $employeeID = $userInfo[1]
    $pictureURL = $userInfo[2] -replace 'https://localURL/User%20Photos', 'https://sp.sharepoint.com/UserPhotos'
    $reportingManager = $userInfo[4] + " " + $userInfo[3]
    Write-Host "User [" $userInfo[0] "] with EmployeeID [" $userInfo[1] "] with PictureURL [" $pictureURL "] under Manager [" $reportingManager "]";
    break
   }   
  }
  
  If ($employeeID -ne ""){
   $PeopleManager.SetSingleValueProfileProperty($User.LoginName, "EmployeeID", $employeeID)
  }
  
  If ($pictureURL -ne ""){
   $PeopleManager.SetSingleValueProfileProperty($User.LoginName, "PictureURL", $pictureURL)
  } 
  
  $context.ExecuteQuery()  
  
  # Get Properties of a User Profile
  $UserProfile.UserProfileProperties
  } 
}

 

Office 365 - Manipulate Items Programmatically (using Powershell)

A - OBJECTIVE: 

Developers and administers are expected to manipulate objects at SharePoint Online (aka Office 365), such as:

• compare files
• manage permissions
• etc

This post is to elaborate the steps to do administrative work programmatically by Powershell.

B - PROBLEMS :

Manual work at Office365 is not a good idea. Using Powershell, you can improve your productivity (speed) to deliver the work faster.

You can run the script from your own PC (e.g. Windows 7) with the following prerequisites:

1. Download & Install "Powershell 3.0": https://www.microsoft.com/en-us/download/details.aspx?id=34595
    
2. Download & Install “SharePoint Online Management Shell”
    
3. Download & Install “SharePoint Server 2013 Client Components SDK” https://www.microsoft.com/en-gb/download/details.aspx?id=35585
4. Download & Install "SharePoint Online Client Components SDK" at https://www.microsoft.com/en-us/download/details.aspx?id=42038 

C - SOLUTION:

The required API reference is Microsoft.SharePoint.Client (Microsoft details) which is integrated into the SharePoint Online Management Shell. This will allow you to manipulate objects at SharePoint Online sites.

D - SOURCE CODE:

An example to list items (level 1) of a document library at SharePoint Online site:

Step 1: provide the information of your sites & the credential 



Step 2: setup the connection to SharePoint Online



Step 3: access to the specific library in a web



Step 4: design a CAML query to extract relevant items




Step 5: load the items from the current context and display them





Powershell Script:

#variables that needs to be set before starting the script
$siteURL = "https://tenant.sharepoint.com/sites/git"
$adminUrl = "https://tenant-ADMIN.sharepoint.com"
$userName = "admin@tenant.onmicrosoft.com"

# Let the user fill in their password in the PowerShell window
$password = Read-Host "Please enter the password for $($userName)" -AsSecureString
 
# set SharePoint Online credentials
$SPOCredentials = New-Object Microsoft.SharePoint.Client.SharePointOnlineCredentials($userName, $password)  
# Creating client context object
$context = New-Object Microsoft.SharePoint.Client.ClientContext($siteURL)
$context.credentials = $SPOCredentials
 
$web = $context.Web
$list = $web.Lists.GetByTitle("4. IT Capabilities and Knowledge Base")

# CAML QUERY REFERENCE: https://msdn.microsoft.com/en-us/library/microsoft.sharepoint.client.list.getitems.aspx
$query = New-Object Microsoft.SharePoint.Client.CamlQuery
$query.ViewXML = ""

# LIST ITEMS
[Microsoft.SharePoint.Client.ListItemCollection]$items = $list.GetItems($query)
$context.Load($items)
try{
 $context.ExecuteQuery()
}
catch{
 write-host "info: $($_.Exception.Message)" -foregroundcolor red
}  

foreach ($item in $items) { Write-Host $item.Id " - " $item["Title"] }

Note: 

(1) all required DLLs are loaded at C:\Program Files\Common Files\Microsoft Shared\Web Server Extensions\16\ISAPI

(2) you can use SharePoint Online Management Shell to connect to your on-premises SharePoint farm as well 

$siteUrl = "http://yourOnPremSP/site"
$loginname = "spadmin@yourdomain.com"
$pwd = Read-Host -AsSecureString

# initialize the environment
$ctx = New-Object Microsoft.SharePoint.Client.ClientContext($siteUrl)

# IMPORTANT NOTE: NetworkCredential Class must be used
$ctx.Credentials = New-Object System.Net.NetworkCredential($loginname, $pwd)

$web = $ctx.Web 
$ctx.Load($web) 
$ctx.ExecuteQuery() 

Write-Host " Current web title is '$($web.Title)', $($web.Url)"