How do we know when the SharePoint site was last updated?
We have multiple “when the site was modified last time” properties – e.g. some we can retrieve with SharePoint CSOM:
Site LastContentModifiedDate
Web LastItemModifiedDate
Web LastItemUserModifiedDate
Also we can get MS Graph site LastModifiedDateTime property and use “Last activity” field via Admin Center GUI
And we can modify site in a multiple ways – update document or list item on the site, change library or site settings, configure site permissions, assign site sensitivity label, setup site property and so on.
Question: which site “last modified” properties reflect what events/actions?
This might be important if we think of retention policies, or any kind of clean-up processes… Let say, we are getting report on abandoned sites (inactive sites), but we are also assigning sites sensitivity labels, or we are updating site custom properties (e.g. for adaptive scopes), we have an ownerless groups policy working etc.
What if we assign site sensitivity label to an inactive (5 years old site) – would it affect site retention since site was updated?
Results
So i did some tests and based on detailed results below, it seems like
Web LastItemModifiedDate is triggered when user visited site (but not LastItemUserModifiedDate)
If a document or list Item updated by user or app – all properties are triggered
MS Graph site property LastModifiedDateTime, root web property LastItemModifiedDate and Site LastContentModifiedDate – same values
If site custom property is updated – it does not affect any site “last modified” property
The same for sensitivity label updated by app – it does not affect any site “last modified” property
The same for Microsoft ownerless groups policy – when user accept or decline group membership – no site “last modified” properties are changed (the same is true for Microsoft 365 group last modified date/time property).
Please refer to the table below
Detailed test results
Test results if the event triggers property update:
Event
Last Content Modified Date
Last Item Modified Date
Last Item User Modified Date
Graph Last Modified DateTime
GUI Last activity
Page viewed by user
Yes
Yes
No
Yes
Home Page viewed by user
Site Page viewed by user
Document or list item updated by user
Yes
Yes
Yes
Yes
Document or list item updated by app
Yes
Yes
Yes
Yes
Site config settings updated by user
Site config settings updated by app
Site custom property updated by app
No
No
No
No
Site Sensitivity label updated by user via SharePoint
Yes
No
No
No
Site/Group Sensitivity label updated by user via Teams
Site/Group Sensitivity label updated by user via Azure
No
No
No
No
Site Sensitivity label updated by app
No
No
No
No
Site collection admin updated by user
Yes
Yes
No
Yes
Site collection admin updated by app
Yes
Yes
No
Yes
SharePoint group membership updated by user
Yes
Yes
No
Yes
Standalone Site connected to a group by user
Yes
Yes
Yes
Yes
Add Microsoft Teams to Site by User
Yes
Yes
Yes
Yes
Update m365 group membership via M365 admin console by admin
Yes
Yes
No
Yes
Update m365 group membership via Azure by admin
Update m365 group membership via Teams by user
No
No
No
Yes
Update m365 group membership via App
Accept group ownership invitation sent by ownerless groups policy
No
No
No
No
Decline group ownership invitation sent by ownerless groups policy
This PowerShell script pulls all tenant sites and all sites owners. The script require app authentication with Sites.FullControl.All and Directory.Read.All permissions. PnP.PowerShell for PowerShell 7 is used.
It generates two reports
Owners report: one user per line, include: Site Url, Title, Owner e-mail, name and type
Sites report: one site per line, include: Site Url, Title, list of owners e-mails
Bert Jansen (Microsoft) revealed some details on throttling when you access Microsoft 365 programmatically – via Microsoft Graph or CSOM and guided developers on how to regulate request traffic for optimized throughput using RateLimit headers (Here).
Demystifying SharePoint throttling
Throttling is necessary to ensure that no single user or application consumes too many resources compromising the stability of the entire system, which is used by many clients.
Throttling happens at
User (there are user request limits. Microsoft counts all requests linked to user
Application (Delegated or Application permissions)
Resource units per app per minute
Resource units per app per day
Farm – Spike protection
Very common reason for throttling – when an Application (Delegated or Application permissions) reaches “Resource units per app per minute” threshold.
Usually you catch HTTP errors 429 or 503, wait for some time (respect Retry-after header) and try again.
SharePoint provides various APIs. Different APIs have different costs depending on the complexity of the API, but Microsoft favor Graph API over SharePoint REST/CSOM. The cost of APIs is normalized by SharePoint and expressed by resource units. Application’s limits are also defined using resource units.
Quota depends on tenant size.
Resource unit limits for an application in a tenant (please refer to the Microsoft article)
Predefined costs for Microsoft Graph calls:
Assuming 2 resource units per request is a safe bet.
Here I’m trying to figure out – how much PowerShell Parallel option is beneficial and how to avoid throttling…
Let us test, how long would it take to create a SharePoint site, if we use regular (sequential) loop or parallelism (I’m creation a sample set of 50 SharePoint Sites in a row):
Regular (Sequential) seconds per site
Parallel, 100 sites in batch seconds per site
Parallel, 500 sites in batch seconds per site
Regular (Sequential)
3.0
Parallel, ThrottleLimit = 2
1.60
0.91
Parallel, ThrottleLimit = 5
0.69
Parallel, ThrottleLimit = 10
0.2 – 0.3
Parallel, ThrottleLimit = 20
0.17
Interesting, but I did not get even one (throttling or any other) error during creation 500 sites.
Get sites details
Now let us test, how long it takes to get sites details with Get-PnPTenantSite (I use a sample set of 500 sites):
Test type
Regular (Sequential), seconds per site
Parallel sample = 100 sites, seconds per site
Parallel sample = 200 sites, seconds per site
Parallel sample = 500 sites, seconds per site
Regular (Sequential)
0.65
Parallel, ThrottleLimit = 2
0.40
0.33
0.31
Parallel, ThrottleLimit = 5
0.17
0.14
0.36 (errors)
Parallel, ThrottleLimit = 10
0.11 (errors)
0.11 (errors)
0.34 (errors)
Parallel, ThrottleLimit = 20
0.12 errors+
0.07 errors+
0.52 (errors)
(errors) means there were small number of errors during test… e.g.
If I get token with (Graph, MSAL, PnP) and use this token for (Graph API, SharePoint CSOM API, SharePoint REST API) matrix.
An App used in this tests has Sites.FullControl.All MS Graph API and SharePoint API permissions, as well as FullControl ACS based permissions to SharePoint (AppInv.aspx).
Sites.Selected MS Graph API permissions were introduced by Microsoft in March 2021. One year later, in 2022 they added SharePoint Sites.Selected API permissions.
Why is this so important? Because MS Graph API for SharePoint is still limited and cannot cover all possible needs. I’d estimate: 90% of applications use SharePoint CSOM, so developers have to use AppInv.aspx to provide permissions for their applications to SharePoint API.
But from this moment – having SharePoint API permissions in MS Graph – in theory – we can fully rely on permissions provided in Azure and – in theory – this should allow us disable SharePoint-Apps only principal:
Meantime I’ll test providing SharePoint Sites.Selected API permissions via Graph API call.
(wip) Test set #1: Certificate vs Secret
DisableCustomAppAuthentication: $false (SP-app-only spns are enabled). All applications have “write” access provided to a specific site only. Connecting with Connect-PnPOnline and then test access with Get-PnPSite
App / Get-PnPSite
Secret
Certificate
ACS based (Azure+AppInv)
OK
The remote server returned an error: (401) Unauthorized.
MS Graph API Sites.Selected
The remote server returned an error: (403) Forbidden.
The remote server returned an error: (401) Unauthorized.
SharePoint API Sites.Selected
OK
OK
MS Graph API + SharePoint API Sites.Selected
Access is denied. (Exception from HRESULT: 0x80070005 (E_ACCESSDENIED))
OK
App with no permissions
The remote server returned an error: (403) Forbidden
The remote server returned an error: (401) Unauthorized
(wip) Test set #2: Sites.Selected SharePoint vs MS Graph (secret)
DisableCustomAppAuthentication = $false (SP-app-only spns are enabled).
All applications have “write” access provided to a specific site only.
Using Client Secret (not a certificate)
Using PnP.PowerShell
Action/Via
SharePoint + MS Graph Sites.Selected “secret”
SharePoint Sites.Selected “secret”
MS Graph Sites.Selected “secret”
Connect-PnPOnline
WARNING: Connecting with Client Secret uses legacy authentication and provides limited functionality. We can for instance not execute requests towards the Microsoft Graph, which limits cmdlets related to Microsoft Teams, Microsoft Planner, Microsoft Flow and Microsoft 365 Groups.
WARNING: Connecting with Client Secret uses legacy authentication and provides limited functionality. We can for instance not execute requests towards the Microsoft Graph, which limits cmdlets related to Microsoft Teams, Microsoft Planner, Microsoft Flow and Microsoft 365 Groups.
WARNING: Connecting with Client Secret uses legacy authentication and provides limited functionality. We can for instance not execute requests towards the Microsoft Graph, which limits cmdlets related to Microsoft Teams, Microsoft Planner, Microsoft Flow and Microsoft 365 Groups.
Get-PnPSite
OK
OK
The remote server returned an error: (403) Forbidden.
Get-PnPList
OK
OK
Get-PnPListItem
OK
OK
Set-PnPSite
Attempted to perform an unauthorized operation.
Set-PnPList
Attempted to perform an unauthorized operation.
Set-PnPListItem
OK
OK
New-PnPList
Access is denied. (Exception from HRESULT: 0x80070005 (E_ACCESSDENIED))
Add-PnPListItem
OK
(wip) Test set #3: Read vs Write vs FullControl
DisableCustomAppAuthentication = $false (SP-app-only spns are enabled). All applications have Sites.Selected SharePoint and MS Graph API permissions. Using Client Secret (not a certificate) Using PnP.PowerShell
Read
Write
FullControl
Connect-PnPOnline
WARNING: Connecting with Client Secret uses legacy authentication and provides limited functionality. We can for instance not execute requests towards the Microsoft Graph, which limits cmdlets related to Microsoft Teams, Microsoft Planner, Microsoft Flow and Microsoft 365 Groups.
WARNING: Connecting with Client Secret uses legacy authentication and provides limited functionality. We can for instance not execute requests towards the Microsoft Graph, which limits cmdlets related to Microsoft Teams, Microsoft Planner, Microsoft Flow and Microsoft 365 Groups.
WARNING: Connecting with Client Secret uses legacy authentication and provides limited functionality. We can for instance not execute requests towards the Microsoft Graph, which limits cmdlets related to Microsoft Teams, Microsoft Planner, Microsoft Flow and Microsoft 365 Groups.
Get-PnPSite
Access is denied. (Exception from HRESULT: 0x80070005 (E_ACCESSDENIED))
Get-PnPList
Access is denied. (Exception from HRESULT: 0x80070005 (E_ACCESSDENIED))
Get-PnPListItem
Set-PnPSite
Set-PnPList
Set-PnPListItem
New-PnPList
Add-PnPListItem
(wip) Test set #5: Certificate vs Secret
C#, SharePoint CSOM, PnP.Framework
Findings
PnP.PowerShell Get-, Grant-, Set- and Revoke-PnPAzureADAppSitePermission cmdlets require Azure App with MS Graph Sites.FullControl.All app permissions (otherwise it says “Access denied”) and authentication via certificate (otherwise it says “This cmdlet does not work with a ACS based connection towards SharePoint.”)
The same actions – managing permissions for the client app to the specific site collections – could be done via Microsoft Graph Sites Permissions API using just secret-based authentication.
If an azure app does not have Sites.Selected API permissions configured – “Grant-PnPAzureADAppSitePermission” works as expected – no error messages – the output is normal – as if Sites.Selected API permissions were configured in the app. The same for Get-, -Set and Revoke-. Permissions provided for the app to the site are not effective though: Connect-PnPOnline works well, but all other commands – starting from Get-PnPSite – returns “The remote server returned an error: (403) Forbidden.”
If an app have no permissions to SharePoint – “Connect-PnPOnline” works ok, but “Get-PnPSite” return an error: “The remote server returned an error: (403) Forbidden.”
Set-PnPAzureADAppSitePermission gives an error message “code”:”generalException”,”message”:”General exception while processing” if the site is not specified.
AppInv is not working?
Error: Access is denied. (Exception from HRESULT: 0x80070005 (E_ACCESSDENIED))
How to provide permissions for an Azure registered application with MS Graph SharePoint Sites.Selected API permissions to a specific site via calling Microsoft Graph API from PowerShell.
We need an “admin” application – Azure registered application with with Sites.FullControl.All MS Graph API permissions. This method can use secret, so we need Client Id and Client Secret for this “admin” app.
We also need a Client Id and Application Display Name for an Azure application with Sites.Selected MS Graph and/or SharePoint API permissions provided.
And we need our “target” site Url.
With PowerShell scripts you can:
Get Microsoft Graph Access Token with an “admin” app
Get client (target) site Id
Get current app permissions provided to client site
Add read or write permissions for the client app to the client site
Update: Sites.Selected API MS Graph permissions was introduced by Microsoft in 2021. It was a good move towards site-level development, but still developers were limited with only what MS Graph API provides for SharePoint dev. So devs had to use AppInv.aspx at site level to provide ACS permissions to their apps to be able to use SharePoint CSOM and REST APIs. Recently Microsoft introduced Sites.Selected SharePoint API permissions for registered Azure Apps! So now devs should be fully happy without ACS-based permissions.
Scenario
You have an application that needs access to Microsoft 365 SharePoint Online site/list/documents. Application is running without interaction with users – e.g. unattended, as daemon job.
There are two options you can authenticate to Microsoft 365 – with the secret or with the certificate. Authenticating with certificate is considered more secure.
Questions
What happens if SharePoint-Apps only principal is disabled (i.e. ‘set-spotenant -DisableCustomAppAuthentication $true’ )?
Why I’m getting 401 error when authenticating to SPO?
Why I’m getting 403 error when authenticating to SPO with secret?
What permissions to I need to work with SPO?
Findings
Note: we will use PowerShell 7.2 and PnP.PowerShell 1.9 to illustrate it.
Disabled SharePoint-Apps only principal
If SharePoint-Apps only principal is disabled in your tenant (i.e. ‘Get-PnPTenant | select DisableCustomAppAuthentication’ returns $true ), then the only way you work with SPO from code is:
an App registered in Azure
API permissions provided via Azure (MS Graph, SharePoint)
Certificate is used
In all other cases (even your Connect-PnPOnline command complete successfully) – you will be getting error 401 (unauthorized) when trying Get-PnPTenant or Get-PnPTenantSite or Get-PnPSite
Enabled SharePoint-Apps only principal
If SharePoint-Apps only principals are enabled in your tenant (i.e. ‘Get-PnPTenant | select DisableCustomAppAuthentication’ returns $false ), then you have two options to work with SPO from code:
Azure App with a secret (Client Id + Client Secret) and permissions to SharePoint provided via SharePoint ( AppInv.aspx )
Azure App with a certificate (Client Id + Certificate) and permissions provided via Azure (Microsoft Graph and/or SharePoint)
Error 401 while accessing SharePoint Online with PnP
Let say you administer Microsoft 365 SharePoint Online and you want to get a list of new SharePoint sites (e.g. sites created recently – during last day/week/month).
With GUI it’s done easily: SharePoint Admin Center -> Active Sites -> sort based on “Date Created” – done.
With PowerShell – not so simple. “Get-PnPTenantSite” cmdlet returns a site object but the object does not have “Created” field. It’s a web property. But to get a web object – you have to connect separately to each site and get root web object to check when the web was created. For small environments it is possible, for large environments it can take days… And still not nice. “Get-PnPTenantSite” with “-Filter” option would help, but “…Currently, you can filter by these properties: Owner, Template, LockState, Url.”
Get-SPOSite – similar experience.
Solution
Microsoft Graph API helps. It returns result in seconds and you can sort or filter results based on created date . Below are two methods: Option 1 is based on Search and filtering and Option 2 is based on Sites Search and sorting. So there are some pros and cons for each method.
Microsoft Graph Search API allows KQL in queries. So we can form a query with something like “created>=1/1/2021” and use entity type = ‘[“site”]’. Search should return only sites created after Jan 01, 2021.
This option is also based on Microsoft Graph API, but sites entry point, which allows search too and sort results by property “createdDateTime”. So we will just search for everything and select how many results we need based on createdDateTime property.