Category Archives: SharePoint

Search through Microsoft 365 SharePoint from code

Below is how do I search Microsoft 365 content programmatically from PowerShell using MS Graph API, PowerShell PnP, Microsoft Graph module, MSAL library being authenticated as user or daemon application. Let me focus on SharePoint content here but you can use the same technique to search through other Microsoft 365 services. Also, I’ll be using PowerShell but same ideas should work for other platforms/languages – Python, C#, node.js etc.

First, we need to be authenticated

Here is how to authenticate to Microsoft 365 Graph API.

Second, we need to be authorized

To search on behalf of currently authenticated user we need delegated “Sites.Read.All” API permissions. I recommend you to add both Graph API and SharePoint API permissions as different libraries might use different API’s under the hood. Ensure you add delegated “Sites.Read.All” even if you already have “Sites.FullControl.All” as by some reason “Sites.FullControl.All” does not always work for search.

Here is how an app API permissions to search as current user should look like:

app API permissions to search as current user

Actually, for interactive authentication app ownership is not required, so we can surely use our own registered app, but also it is possible to use any other app registered in Azure and properly configured, e.g. Enterprise “PnP Management Shell” app id: “31359c7f-bd7e-475c-86db-fdb8c937548e”
How do I create and configure Azure App with delegated permissions to SharePoint for PowerShel usage

For unattended search – e.g. search on behalf of daemon app – we need application “Sites.Read.All” API permissions. Again, I suggest both Graph API and SharePoint API permissions added. Here is how an app API permissions to search as daemon app should look like:

Ensure you got admin consent for API permissions.

In case you have incorrect permissions in your app – Microsoft Graph will be kind enough to inform you exactly what you need. Example:

“Access to ChatMessage in Graph API requires the following permissions: Chat.Read or Chat.ReadWrite, ChannelMessage.Read.All. However, the application only has the following permissions granted: Sites.Read.All, User.Read”

Assuming we have configured apps – let us get started with

Microsoft Graph API

Microsoft Graph API allows search through all the Microsoft 365 content – including Exchange e-mail messages, Yammer (Viva Engage) and Teams chat messages and surely OneDrive and SharePoint content (please refer to the original doc).

Authenticate as current user to Search with Graph API

I use MSAL.PS PowerShell module to get token, then I build a headers variable

# Prerequisites
Get-Module MSAL.PS -ListAvailable | ft name, Version, Path 
# Install-Module MSAL.PS -Force -Scope CurrentUser -AcceptLicense
Import-Module MSAL.PS

# Interactive Authentication
$clientid = 'd82858e0-ed99-424f-a00f-cef64125e49c'
$TenantId = '7ddc7314-9f01-45d5-b012-71665bb1c544'
$token = Get-MsalToken -TenantId $TenantId -ClientId $clientid -Interactive
$headers = @{Authorization = "Bearer $($token.AccessToken)" }

Authenticate as service/daemon app

You’d need to update the script providing Tenant id, client (app) id and client (app) secret:

# App Authentication
$clientID = ""
$clientSc = ""
$TenantId = ""

# Construct URI and body needed for authentication
$uri = "https://login.microsoftonline.com/$tenantId/oauth2/v2.0/token"
$body = @{
    client_id     = $clientID
    client_secret = $clientSc
    scope         = "https://graph.microsoft.com/.default"
    grant_type    = "client_credentials" 
}

# Get OAuth 2.0 Token
$tokenRequest = Invoke-WebRequest -Method Post -Uri $uri -ContentType "application/x-www-form-urlencoded" -Body $body -UseBasicParsing
$token = ($tokenRequest.Content | ConvertFrom-Json).access_token
$headers = @{Authorization = "Bearer $token" }
$headers

Search m365 SharePoint and OD content with Microsoft Graph API

In this sample I limited search scope to list items only ($entityTypes = “[‘listItem’]”). Check other entity types here.

Code:

# Search
$entityTypes = "['listItem']"
$apiUrl = "https://graph.microsoft.com/beta/search/query"
$query = "*"
# body for interactive search
$body = @"
{ 
  "requests": [
    {
      "entityTypes": $entityTypes,
      "query": {
        "queryString": "$query"
      }
    }
  ]
}
"@

$res = Invoke-RestMethod -Headers $Headers -Uri $apiUrl -Body $Body -Method Post -ContentType 'application/json'
$res.value[0].hitsContainers[0].hits

If you are getting error message “SearchRequest Invalid (Region is required when request with application permission.)”:

that’s OK, just modify your body to include region like this (“region”: “NAM” for North America or “GBR” or …). Also, I can modify body with from/size for paging (technique used to iterate through search results if there are many) and return just specific fields to decrease traffic and improve performance:

# Search
$entityTypes = "['driveItem','listItem','list','drive','site']"
$entityTypes = "['driveItem','listItem']"

$query = "LastModifiedTimeForRetention<2021-01-01"
$apiUrl = "https://graph.microsoft.com/beta/search/query"
$query = "test*"
$body = @"
{ 
  "requests": [
    {
      "entityTypes": $entityTypes,
      "query": {
        "queryString": "$query"
      },
      "from" : 0,
      "size" : 5,
      "fields": ["WebUrl","lastModifiedBy","name" ],
      "region": "NAM"
    }
  ]
}
"@

$res = Invoke-RestMethod -Headers $Headers -Uri $apiUrl -Body $Body -Method Post -ContentType 'application/json'
$res.value[0].searchTerms
$res.value[0].hitsContainers[0].hits
$res.value[0].hitsContainers[0].hits.Count

We’d not use region for interactive calls or we’ll get “Region is not supported when request with delegated permission.”.

Microsoft.Graph PowerShell module

There is a Microsoft.Graph PowerShell module provided by Microsoft which simplifies authentication and search operations.

Interactive authentication code sample:

# Prerequisites
Get-Module Microsoft.Graph.Authentication -ListAvailable 
Get-Module Microsoft.Graph.Search -ListAvailable 

# Interactive Authentication
$clientid = '31359c7f-bd7e-475c-86db-fdb8c937548e'
$clientid = 'd82858e0-ed99-424f-a00f-cef64125e49c'
$TenantId = '7ddc7314-9f01-45d5-b012-71665bb1c544'
Connect-MgGraph -ClientId $clientid -TenantId $TenantId

For daemon app authentication we need a certificate configured in Azure App and installed on the user machine. Daemon app authentication code sample (please specify your tenant id, app (client) id and certificate thumbprint:

# App Authentication
$clientID = ""
$certThumbprint = ""
$TenantId = ""
Connect-MgGraph -ClientId $clientid -TenantId $TenantId -CertificateThumbprint $certThumbprint

Code sample for SharePoint search with Microsoft.Graph PowerShell module

As currently authenticated user

# Search in the current user context
$params = @{
	requests = @(
		@{
			entityTypes = @(
				"driveItem"
			)
			query = @{
				queryString = "lorem"
			}
			from = 0
			size = 25
			fields = @(
				"title"
				"description"
			)
		}
	)
}

$res = Invoke-MgQuerySearch -Body $params
$res.HitsContainers[0].Hits
   

Again, in case with app authentication – an additional parameter – region – is required:

# Search
$params = @{
	requests = @(
		@{
			entityTypes = @(
				"driveItem"
			)
			query = @{
				queryString = "lorem"
			}
			from = 0
			size = 25
			fields = @(
				"title"
				"description"
			)
                        region = "NAM"
		}
	)
}

$res = Invoke-MgQuerySearch -Body $params
$res.HitsContainers[0].Hits

It’s a good idea to explore returning object.

PnP.PowerShell module

PnP.PowerShell allows you to search through Microsoft 365 SharePoint content with PowerShell style – using command and options.

Let us authenticate interactively:

# Interactive Authentication
$clientid = 'd82858e0-ed99-424f-a00f-cef64125e49c'
$TenantId = '7ddc7314-9f01-45d5-b012-71665bb1c544'
$siteUrl = "https://s5dz3.sharepoint.com"
Connect-PnPOnline -ClientId $clientid -Tenant $TenantId -Url $siteUrl -Interactive

Authentication on behalf of service/daemon app would require certificate installed on the machine and configured in the app and look like:

# Application (daemon) Authentication
$clientID = ""
$certThumbprint = ""
$TenantId = ""
$siteUrl = "https://contoso.sharepoint.com"
Connect-PnPOnline -ClientId $clientid -Tenant $TenantId -Url $siteUrl -Thumbprint $certThumbprint

There are no differences in Microsoft 365 SharePoint Search with PowerShell code samples for interactive and daemon apps (no region parameter).

Examples:

# search
$query = "test*"
$res = Submit-PnPSearchQuery -Query $query 

# examples of query:
$query = "test*"
$query = "* contentclass:STS_ListItem_DocumentLibrary"
$query = "* author:Patti"
$query = "test* site:https://s5dz3.sharepoint.com/teams/sxc"

# examples of submitting request
$res = Submit-PnPSearchQuery -Query $query 
Submit-PnPSearchQuery -Query $query -All 
Submit-PnPSearchQuery -Query $query -MaxResults 5
Submit-PnPSearchQuery -Query $query -SortList @{"LastModifiedTime" = "ascending"} 

# exploring result object:
$res.ResultRows.Count
$res.ResultRows[0]
$res.ResultRows.Title
$res.ResultRows.OriginalPath
$res.ResultRows.LastModifiedTime

Video tutorials

Video tutorials (playlist) on how to authenticate to Microsoft 365 and Search through Microsoft 365 content from code

References

SharePoint Add-Ins and ASC retirement

Microsoft announced retirement for SharePoint Add-Ins and ASC-based app permissions (SharePoint app-only principals). Let me summarize here:

  • SharePoint Add-Ins, ASC-based app permissions and SharePoint app-only principals
  • Timeline of retirement and future plans
  • How to detect if there are legacy service principals used in the environment
  • Migration guidance

Timeline

Known key retirement milestone dates:

  • Mar 2024 – Microsoft will stop accepting new Add-In submission to app store
  • Jul 2024 – Microsoft will stop ability for customers to acquire add-ins from App Store (*)
  • using ACS and Add-ins will be blocked for new tenants since Nov 2024
  • using ACS and Add-ins will be blocked for all tenants since Apr 2026

(*) SPFx based solutions will continue to be available

So timeline is generous, and we have plenty of time to

Detect if there are any legacy service principals used in the environment

WIP…

Microsoft 365 admin center: Manage ownerless Microsoft 365 groups and teams

There is a new feature published at Microsoft roadmap site:

Microsoft 365 admin center: Manage ownerless Microsoft 365 groups and teams

Teams, Outlook groups, Team Sites etc. powered by Microsoft 365 Groups supports two roles: members and owners. Members can collaborate with others in the group through files, emails, messages etc. Owners manage the group membership and monitor content and conversations. When employees leave an organization or switch projects internally, it results in their existing user accounts getting deleted. If such employees were group owners, keeping track of their groups becomes critical to ensure accountability within the organization. We have introduced a new ownership governance policy to help automate the management of ownerless groups by requesting active members to become owners of the group. Admins can define who is eligible for these notifications and configure what notifications and how often these notifications are sent to active group members. Users, who are members of the ownerless groups can simply accept or decline request via the actionable email message.

  • Feature ID: 180749
  • Added to roadmap: 10/10/2023
  • Last modified: 10/10/2023
  • Product(s): Microsoft 365 Admin Center
  • Cloud instance(s): GCC
  • Platform(s): Web
  • Release phase(s): General Availability


But based on the feature description – all looks exactly as what we already have for years as “Microsoft 365 ownerless groups policy” which you can configure under Microsoft 365 Admin Center -> Settings -> Org settings -> Microsoft 365 groups

More on Microsoft 365 ownerless groups

Massive Microsoft 365 groups update with PowerShell

What if you need to update thousands of m365 groups? E.g. membership – to add a group owner or member.

In my case, it was Microsoft 365 ownerless groups policy… Details are here but in short, I needed to update ownership for 10,000 m365 groups adding 10 accounts to group owners, 1,000 groups per account.

And I figured out for myself that the fastest way is to use Microsoft Graph API with PowerShell parallel trick. Here is the PowerShell code:

$groups | ForEach-Object -Parallel {
    $owner = "user2del27@vladslab.onmicrosoft.com"
    Add-PnPMicrosoft365GroupOwner  -Identity $_.Id -Users $owner
} -ThrottleLimit 50

In my environment, it took ~ 8 seconds per 1,000 groups.

Sites.Selected permissions provisioning automation

Scenario

You administer Microsoft 365 SharePoint Online. Part of your daily activities is providing Microsoft Graph and SharePoint Sites.Selected API permissions to other users (developers).

In Aug/Sep 2023 Microsoft pushed an update that prevents site collection admins to create or update an Azure Access Control (ACS) principal (that was the way most of developers used to get Client Id and Client secret to access SharePoint site). So your users are probably getting something like Your SharePoint tenant admin doesn’t allow site collection admins to create or update an Azure Access Control (ACS) principal message attempting to create or update SharePoint App-only principal at AppRegNew.aspx or AppInv.aspx pages. Here are more details on the issue.

Microsoft and MVPs shared some technique how to provide Sites.Selected API permissions, but dealing with scripts manually, elevating individual permissions every time you need to run the script – it all takes time and not very efficient. More and more devs are reaching you on the app. So you want to automate this process.

Solution

Solution architecture

My way to automate it includes:

  • SharePoint list as a frontend
    here you can accept intake requests, organize approval workflow and display automation results
  • Azure Function App as a backend
    here will be your PowerShell script hosted that runs on scheduled basis and takes care of actual permissions provisioning

Solution details

High-level, getting application permissions to some specific SharePoint site is a two-step process:

  1. get application registration in Azure and properly configure it
  2. get permissions for this application to a specific SharePoint site

For the first step – check this and this articles. I’ll focus on the second step below.

You can provide Sites.Selected permissions for the app to a site with

I will be using second one one. Also PnP.PowerShell will be used to get access to SharePoint intake site and read/update requests from SharePoint list and so on.

Azure App Registration

I registered an admin Application in Azure – “SharePoint Automation App”, added Graph Sites.FullControl.All and SharePoint Sites.FullControl.All permissions, then added Microsoft Graph Directory.Read.All permissions and got tenant admin consent:

I generated a self-signed certificate and added it to the app:

This app will be used to call provide permissions, and to connect to the SharePoint front-end.

Users will register their applications in Azure, add Graph Sites.Selected and SharePoint Sites.Selected permissions, got tenant admin consent, then request permissions to the specific site by creating an intake request – new list item.

Front-End SharePoint Site

I created a SharePoint site for automation. This site will play a front-end role for users. I created a list “Sites.Selected” and updated list columns so I have the following fields:

  • Target Site Url
  • Application Id
  • Permissions (read/write)
  • Automation Output

In real-world (Prod) – You can (should) also implement approval workflow as you’d provide permissions for the application to the site only with this site owner approval. The PowerShell code behind should also validate site owner’s consent with app access to site. But for the sake of simplicity I’ll skip this in my demo.

Azure Function App

I created an Azure Function App with the following parameters:
– Runtime stack: PowerShell Core
– Version: 7.2.
– OS: Windows
– Hosting plan: Consumption

And then PowerShell timer-triggered function in Visual Studio Code.

Function requirements.psd1 (it takes a few hours for Azure to install modules; while modules are installing – you might see “[Warning] The first managed dependency download is in progress, function execution will continue when it’s done. Depending on the content of requirements.psd1, this can take a few minutes. Subsequent function executions will not block and updates will be performed in the background.”):

@{
    'Az' = '10.*'
    'PnP.PowerShell' = '2.*'
}

Azure Az module to access other Azure resources. PnP.PowerShell module will be used to access SharePoint.

I will keep my admin Azure registered app in a key vault, so need somehow to let the key vault know that this specific app can access this specific credentials. So I enabled system assigned managed Identity for the Function App:

MS: “This resource is registered with Azure Active Directory. The managed identity can be configured to allow access to other resources…”.
I’m going to use an object (principal) Id of this function to grant access to keyvault.

Azure key vault

Surely we do not hard-code app secrets. So we need a key vault o store app credentials.

I created a key vault under the same resource group in Azure and named it “SharePointAutomationDemo”. Then I added a roles assignment – “Key Vault Secret User” and “Key vault Reader” to the Function App via it’s managed identity:

I also assigned “Key Vault Administrator” role to the user (developer) who will add certificates/secrets to this key vault and develop Azure function code.

Code repository

https://github.com/VladilenK/Sites.Selected-Automation

Videos

Part 1: Getting Azure App Registration with Sites.Selected API Permissions

Part 2: SharePoint and Microsoft Graph API Sites.Selected permissions provisioning automation

Find Old Content on Your OneDrive or SharePoint Site

Below I’m sharing some options – how to find files or documents in SharePoint, Teams or OneDrive that are older than some specific date. Why you might need that? To avoid content deletion as a result of retention policies in action.

If an organization is mature enough – it has data lifecycle policies established. If so – these polices must be applied to information stored in Microsoft 365 via retention policies. Retention policies are configured under Compliance center, but in particular applied to documents stored in SharePoint and OneDrive. Policies might dictate to retain documents or delete documents. Let say your organization is implementing retention policy that is configured to delete documents if 5 years passed after the file was last modified. That literally means all your files modified more than 5 years ago will be deleted and you will not even notice it. So – what if you want to know – which documents in your OneDrive or SharePoint site are older than 5 years?

Search in SharePoint with query parameters (GUI)

At any level of your site hierarchy – root level, library, folder etc. – you can refine your search results specifying properties values, e.g. document author or document created date or document last modified date. For last modified date the property is “LastModifiedTime”, e.g. here I’m in the SharePoint site document library:

If I put in search box query “LastModifiedTime<2023-07-15” I’ll get only documents older than July 15 2023:

There is property “LastModifiedTime”, and there is also property “LastModifiedTimeForRetention” you can use to detect documents your retention policy works against.

When you issue query with just “LastModifiedTimeForRetention<2023-06-15” you get as results all kind of SharePoint content – including pages, libraries, folders etc. If your concern is to avoid specific documents deletion as a result of retention policy – you’d probably be interested in finding documents only and do not want folders (as retention policy applies to all files in all document libraries), e.g.

  • If you need only Microsoft Word documents older than some specific date (e.g. June 15, 2021), you might use query: “*.docx LastModifiedTimeForRetention<2021-06-15”
  • For Microsoft Word and Excel documents older than June 15, 2021 – you’ might ‘d use query: “(*.docx OR *.xlsx) LastModifiedTimeForRetention<2021-06-15”

If you need only Microsoft Word documents authored by some specific User and older than some specific date, you might use query:
“*.docx author:Patti LastModifiedTimeForRetention<2021-01-01”

Search in Teams

You can successfully use refinements to search for the same in Teams. But you’d select “Files” tab for better experience:

Microsoft is constantly updating this product, so your experience might be different. Note also that when you search in teams – you search through all sites you have access to.

Search for old documents in OneDrive

You can use the same technique – putting “LastModifiedTime<2023-07-15” in search bar in OneDrive. In some ways it’s even better, as you can

  • search for files in all sites (not only your personal OD site)
  • select multiple file types you are interested in

Search with Graph API

The same query you can use to search content with Microsoft Graph API. Here is the code example:

$query = "LastModifiedTimeForRetention<2021-01-01"
$apiUrl = "https://graph.microsoft.com/beta/search/query"
$body = @"
{ 
  "requests": [
    {
      "entityTypes": [
        "driveItem"
      ],
      "query": {
        "queryString": "$query"
      }
    }
  ]
}
"@

$res = Invoke-RestMethod -Headers $Headers -Uri $apiUrl -Body $Body -Method Post -ContentType 'application/json'
$res.value[0].searchTerms
$res.value[0].hitsContainers[0].hits

Video tutorial

How to fetch all SharePoint documents older than some amount of time:

References

Ownerless groups and dependent channels

Here is the scenario:

There is a team (including SharePoint site) under Microsoft Teams. There are multiple channels under this team with types:
– standard channel
– shared channel
– private channel

A single group owner (team owner) leaves company and the team (group) becomes ownerless. Question: what will happen with private and shared channels?

A group (team) gets a new owner. Question: what will happen with private and shared channels?

“Microsoft Teams – Teams And Channels Service made you an owner of a channel”

TBP

Archiving SharePoint Sites

WIP

What is archiving SharePoint sites and why we’d need it?

Disclaimer: Archival that was announced at Microsoft Inspire 2023 (Introducing Microsoft 365 Backup and Microsoft 365 Archive) is not what we are discussing here.

Scenario

(Work in progress)

You are in the process of cleaning-up large Microsoft 365 environment. You need to delete SharePoint sites (e.g. due to inactivity) but you cannot get confirmation from site owners (e.g. sites or groups are ownerless).

Deleted sites could be restored within 93 days of deletion if somebody rise a hand, but there is still a risk of possible loosing of important information, e.g. in case site is needed one a year. So you need to do clean-up but at the same time you want to decrease risks of loosing information.

So, you might want to do something with sites to engage users to volunteer to be site owner if they want to keep this site – e.g. prevents using the site the regular way and let users know that the site will be deleted etc., but do not actually delete site until it will be fully clear that site is not needed for anyone and can be safely deleted.

Let us call it “Staging” period. Depending on your org culture/rules/licensing etc. it might be 6 months, or 1 year or 5 years or more.

Approach options

generally, the options are (random order):

  • Set site to Read-Only mode
  • Set site to No-Access mode
  • Convert group from Public to Private
  • Remove access to the site (remove users from group)
  • Rename the site
  • Put a banner on a top bar with a message
  • Message to Teams or Yammer chat
  • Send e-mail to site members
  • Implement a Microsoft 365 ownerless groups policy

You might choose to set sites to read-only mode or even no-access mode. If so – users that are still need this site are loosing ability to work with site, but site is not deleted. Consider archiving as kind of scream-test phase before actual sites deletion.

If a user who needs this site would scream (rise a ticket to restore site) – you can trigger processes of
a) finding new owner for the site
b) excluding the site from clean-up process
c) actual restoring site to normal mode

There are some options to setup a site to Read-Only or NoAccess mode. Here is the PowerShell command:


$siteurl = "https://contoso.sharepoint.com/teams/Team-SO-B"
Get-PnPTenantSite -Identity $siteurl   | ft -a Url, LockState
Set-PnPTenantSite -Identity $siteurl -LockState ReadOnly
Get-PnPTenantSite -Identity $siteurl   | ft -a Url, LockState
Set-PnPTenantSite -Identity $siteurl -LockState NoAccess
Get-PnPTenantSite -Identity $siteurl   | ft -a Url, LockState
Set-PnPTenantSite -Identity $siteurl -LockState Unlock

The problem is what if the site is teams-connected or yammer-connected or just group-based. Here are some test results:

Services SharePoint site is connected to/Site StateRead-OnlyNoAccess
Outlook onlyN/AN/A
SharePoint and OutlookOutlook emails: OK
Outlook files: read-only experience; No options to upload or create document; Documents are open in read-only mode. “The file couldn`t be saved to group” error message when trying to save file to a group library.
Outlook emails: OK
Outlook files: empty screen; No error messages; Documents are not visible; “The file couldn`t be saved to group” error message when trying to save file to a group library.
SharePoint and Yammer
SharePoint, Teams and OutlookTeams chats: OK
Teams files: documents are open as read-only; No options to upload or create a new document
SharePoint: “This site is read-only at the administrator’s request.”
Teams chats: OK
Teams files: “403 FORBIDDEN” error message
SharePoint: “
This site can’t be reached
The webpage at https://contoso.sharepoint.com/teams/Team-STO-B might be temporarily down or it may have moved permanently to a new web address.
ERR_INVALID_RESPONSE”

So you can see – behavior is inconsistent – users can still chat in Teams and Yammer and consume SharePoint content (in case the site in read-only) or get error messages or not very meaningful results (in case the site is in NoAccess mode) – so it would be not clear for users that the site is gong to be decommissioned.

to be continued…