Many customers with a SharePoint Online intranet also have a page based on people search. Many default SharePoint User Profile properties are synced from Azure AD automatically. But what if you added your own custom properties that you would like to keep in sync based on information in Azure?
Overview scenario
I have many SharePoint User Profiles for different kind of accounts in my Azure AD: I have FTE, temporarily hired people, interns, etc… In Azure AD I have a distribution group with only FTE members. On my SharePoint page I have a people search component that I only want to show the FTE profiles.
For my solution I need a SharePoint User Profile property and a Search Managed property. For the sync component I use a timer triggered Azure Function that reads the members from the Azure AD Group and updates the SharePoint User Profile property accordingly. For the script I use PowerShell, to have it accessible and maintainable for admins.
Still with me? Let’s gooooooooo!
SharePoint User Profile Property
From the SharePoint Admin site, navigate to More Features and open the User Profiles page.
On the User Profiles page, click on the link Manage User Properties
Click on the link New Property
Give both Name and Display Name the value : isFullTimeEmployee and set the Type to boolean
Set the Policy setting to Optional and the Privacy setting to Everyone. This last one is important because this makes it available for SharePoint Search.
Last but not least, make sure the Search setting indexed is checked. By default it is, but check it anyway.
Click on the button [OK] to save the new property.
Before we can configure SharePoint Search, you need to update one user profile to set this new property. It can take a few hours (!) before the Search crawler picks it up.
SharePoint Search Managed Property
Once the search crawler has picked up the new user profile property, a crawled property called people:isFullTimeEmployee now exists. We need this one to create a managed property and map it to this crawled property. The managed property will be required when we do a search query to get all required user profiles.
From the SharePoint Admin site, navigate to More Features and open the Search page.
On the Search page, click on the link Manage Search Schema
Click on the link New Managed Property
Give the property the name IsFullTimeEmployee and set its type to Yes/No
At Main Characteristics, make the property Searchable, Queryable and Retrievable.
Last but not least, add the mapping to the crawled property People:isFullTimeEmployee
Click on the button [OK] to save the Managed Property.
Azure AD Group
In my Azure AD I have created a security group called RBG-FullTimeEmployees. Maybe in your case you have a Distribution List of Microsoft 365 Group. All fine.
What we need here is the Object Id. We are going to use this later with a Microsoft Graph call from our Azure Function.
Azure Function
The Azure Function is the heart of this solution. Basically, it will query both the Azure AD group and the SharePoint User Profiles using Search. Then it will update the SharePoint User Profile. For all group members set the User Profile property IsFullTimeEmployee to True. For all user profiles found and not a member of the Azure AD group set the property to False.
Now, for the Azure Function do all this, we need quite some configuration and permissions. I want to thank Luise Freese with her post that helped me very much! 👏
Create your Azure Function the way you like best. Whether it’s manually in the Azure Portal or by using Azure CLI, it’s all okay as long as the runtime stack is PowerShell Core:
Once created, we can configure the Function App.
Managed Identity
For accessing Azure and Graph resources we need permissions. For this I’ll use a Managed Identity. No need now to create an additional App Registration (although we’ll need one later. Hang on…)
Navigate to your created Function App.
In the Settings category, select Identity
On the tab System assigned, toggle the Status switch to On.
Save your settings.
This will create a Managed Identity. We can look this up in Azure AD in Enterprise Applications. It has the same name as your Function App.
When you select your Managed Identity and go to its Permissions, you’ll see it is still empty. And more important, there is no option in the Azure Portal to set permissions! What now? Well, PowerShell or CLI to the rescue!
What the Azure Portal does provide, is the option to run CLI in the browser: Cloud Shell. Awesome, no need to install things locally and sign in.
When you start Cloud Shell, you’ll see something like this:
Now you can use CLI commands directly in the browser. How cool is that! We need to give our Managed Identity some permissions to use the Microsoft Graph. But what permissions exactly? One, we need to query Group Membership and two, we need to know the tenant’s SharePoint root site. For this we need to set Directory.Read.All and Sites.Read.All.
The script is as follows:
#Set values
$webAppName = "RED-FA-WHO-DEV-WE"
$principalId = $(az resource list -n $webAppName --query [*].identity.principalId --out tsv)
$graphResourceId = $(az ad sp list --display-name "Microsoft Graph" --query [0].objectId --out tsv)
# API permission: Directory.Read.All
$appRoleId = $(az ad sp list --display-name "Microsoft Graph" --query "[0].appRoles[?value=='Directory.Read.All' && contains(allowedMemberTypes, 'Application')].id" --out tsv)
$body = "{'principalId':'$principalId','resourceId':'$graphResourceId','appRoleId':'$appRoleId'}"
az rest --method post --uri https://graph.microsoft.com/v1.0/servicePrincipals/$principalId/appRoleAssignments --body $body --headers Content-Type=application/json
# API permission: Sites.Read.All (Graph)
$appRoleId = $(az ad sp list --display-name "Microsoft Graph" --query "[0].appRoles[?value=='Sites.Read.All' && contains(allowedMemberTypes, 'Application')].id" --out tsv)
$body = "{'principalId':'$principalId','resourceId':'$graphResourceId','appRoleId':'$appRoleId'}"
az rest --method post --uri https://graph.microsoft.com/v1.0/servicePrincipals/$principalId/appRoleAssignments --body $body --headers Content-Type=application/json
After execution, you can check the permissions for the Managed Identity again:
App Registration
Sadly (and as far as I know) we also need an App Registration. Why? Because we need to access SharePoint Search and SharePoint User Profiles using REST API and PnP PowerShell. I could not get it done with Managed Identities. That’s only for Azure and Microsoft Graph resources, I guess.
Navigate to Azure Active Directory and select App Registrations
In the App registrations blade, click on the link New registration
Give it a name and click on the button [Register]
Once created, the App Registration is shown. Navigate to Permissions
Add the following permissions:
Grant admin consent.
Almost done with the App Registration. In order to use this app and its permissions, we need to create a secret (or a certificate).
Navigate to Certificates and Secrets.
Select the tab Secrets and click on the link New Client Secret
Give it a description and when it should expire.
Click on the button [Add]
Now, this is important: only now the secret VALUE is shown. Make sure you copy it and store is safely for now. We need it later.
Trust app in SharePoint
Next, we need to tell SharePoint that is app is to be trusted.
First find the application Id of this App Registration. Navigate to the tab Overview and copy the Application (client) ID.
Navigate to the SharePoint Admin site and open the page on this url:
htps://<tenantname>-admin.sharepoint.com/_layouts/15/appinv.aspx
Copy the Application ID in the field App Id and click on the button [Lookup]
When found, add the name of your Function App in the field App Domain
And copy the XML Permission Request:
<AppPermissionRequests AllowAppOnlyPolicy="true">
<AppPermissionRequest Scope="http://sharepoint/social/tenant" Right="FullControl" />
<AppPermissionRequest Scope="http://sharepoint/content/tenant" Right="FullControl" />
</AppPermissionRequests>
Click on the button [Create] and SharePoint will ask you if the app should be trusted. Please, confirm.
Azure Key Vault
One more thing to configure before we head back to our Function App. To store your secrets and certificates safely, we need a key vault. But to access the key vault you’ll need permissions, right? That’s where the Managed Identity comes in! Voila. (and I have another Wow! for you later)
Create a Key Vault, give it a name and the correct region
When created, navigate to your Key Vault and select Access Policies
Click on the link Add Access Policy
For Secret Permissions, select Get
Select Principal : Select your Managed Identity !
Click on the button [Add]
Advantage of this, is that you do not create additional credentials and logic to your code to access the Key Vault.
Key Vault Secret
Now we only need to create a Key Vault secret to store the App Registration secret value.
Navigate to Secrets and clink on the link Generate/Import
Click on the button [Create]
Azure Function
Finally, we can create our function in the Azure Function App.
To make our script awesome, we add some configuration.
Navigate to the Function App and select Configuration
You’ll see the Application Settings and we are adding our own. This is to eliminate hard coded values from the script.
Add the following Application Settings:
Setting | Value |
Red_TENANT_ID | <<your Azure AD tenant ID>> |
Red_GROUP_ID | <<your Azure Group ID>> |
Red_APP_ID | <<Application ID of the App Registration |
Red_APP_SECRET | NOTE: see below |
Red_PROFILE_PROPERTY | isFullTimeEmployee |
Red_SEARCH_PROPERTY | IsFullTimeEmployee |
Note: the value for Red_APP_SECRET is a special one. If you store the Secret Identifier from the Key Vault secret, then the Function App automatically connects and retrieves the secret value. No need to create code for this!! (Now, you can say WOW!)
To get the Secret Identifier, navigate back to your Key Vault, select your secret and current version:
Required PowerShell Modules
Ow, one more thing (always wanted to say this). Our PowerShell script will both use the Az and Pnp.PowerShell modules. We need to tell this to our Function App. It will then install these modules in the background.
In your Function App, select App files.
From the dropdown list, select requirements.psd1
Configure it as follows:
Save it all.
Add Code to the Function
We want to have a function that runs every now and then. This would be a Timer Trigger function.
Navigate to Functions and click on the link Create
In the Create panel, leave the Development environment to Develop in portal
Select the template Timer Trigger
Give it a proper name and set the schedule once in every 4 hours:
Click on the button [Create]
In the created function, click on Code + Test
Paste the PowerShell script into the editor and save it. What PowerShell script? you say. No worries, you grab the complete script here.
Some code snippets I want to explain:
There are several functions for mainly getting access tokens and calling the graph or REST APIs:
Retrieving Azure AD Group Membership:
function Get-MembersDLGroup() {
$members = $null
$uri = "https://graph.microsoft.com/v1.0/groups/$groupId/transitiveMembers/microsoft.graph.user"
$isNextLink = $false
do {
$result = Invoke-RestMethod -Method Get -Headers $authHeader -Uri $uri
if( $result.value ) {
$members += $result.value
}
if( $result.'@odata.nextLink' ) {
$isNextLink = $true
$uri = $result.'@odata.nextLink'
}
else {
$isNextLink = $false
}
} while( $isNextLink )
$members
}
The Graph call is here to your Azure group and the transitiveMembers will give you all members of that group and of all nested groups! Since I only want user accounts, I also added the filter microsoft.graph.user.
And, as the Graph will return results in batches, I also check for that. If there are more results a #odata.nextLink is present.
Retrieving SharePoint User Profiles
function Get-SPOEmployeeProfiles( [string]$urlDefaultSite) {
$profiles = $null
$header = @{
'Accept' = 'application/json'
'odata' = 'verbose'
'Content-Type' = 'application/json'
'Authorization' = 'Bearer ' + $spoToken
}
$searchUrl = "$urlDefaultSite/_api/search/query?querytext='" + $searchProperty + ":true'&sourceid='b09a7990-05ea-4af9-81ef-edfab16c4e31'"
$result = Invoke-RestMethod -Method Get -Headers $header -Uri $searchUrl
if ( $result ) {
if( $result.PrimaryQueryResult.RelevantResults.RowCount -gt 0 ) {
$profiles = $result.PrimaryQueryResult.RelevantResults.Table.Rows
}
}
$profiles
}
I use SharePoint Search REST API to query all User Profiles (sourceId=’b09a7990-05ea-4af9-81ef-edfab16c4e31′) and having the value present (IsFullTimeEmployee) using the Search Managed Property.
Updating SharePoint User Profile
#Set New Employees
[int]$added=0
$membersDLGroup | ForEach-Object {
$userAccount = $_.userPrincipalName
$userId = $_.id.ToLower()
$userProfile = $workingProfiles | Where-Object { $_.ID -eq $userId }
if( -Not $userProfile ) {
$added++
Write-Host "ADDING - Updating profile for $userAccount"
Set-PnPUserProfileProperty `
-Connection $spoConnection `
-Account $userAccount `
-PropertyName $profileProperty `
-Value $True
}
}
Write-Host "Updated $added new employees"
I use the PnP PowerShell cmdlet Set-PnPUserProfileProperty to update the user profile. Easypeasy.
Results