PowerShell Script to get All Alerts of all Users from a specific SharePoint Online Site Collection, including subsites:
https://github.com/VladilenK/PowerShell/blob/main/reports/Site/Fetch-All-Alerts-from-SPO-Site.ps1
based on Salaudeen Rajack:
“SharePoint Online: Get All Alerts from a Site Collection using PowerShell“
You have a Microsoft 365 subscription with SharePoint Online. You use PowerShell, PnP.PowerShell module and MS Graph API to work with SharePoint under current user’s credential. You need to authenticate to SharePoint Online via Connect-PnPOnline and to Microsoft Graph API interactively on behalf of a current user.
Unfortunately, both “Connect-PnPOnline -Interactive -Url <siteUrl>” or “Connect-PnPOnline -UseWebLogin -Url <siteUrl>” might fail with something like “Need admin approval”, “App needs permission to access resources in your organization that only an admin can grant. Please ask an admin to grant permission to this app before you can use it.” or “Permissions requested” or similar
$siteUrl = "https://contoso.sharepoint.com/teams/myTeamsSite"
$appId = "" # Client Id
Connect-PnPOnline -ClientId $appId -Url $siteUrl -Interactive
Get-PnPSiteA pop-up window will appear to authenticate interactively. If you are already authenticated with another credentials (or single-sigh-on) – an interactive window might pop up and disappear – that prevents you enter your other id.
To ensure Connect-PnPOnline prompts you for your credentials – use ” -ForceAuthentication” option.
If you are a SharePoint tenant admin – you can connect to a tenant with:
$orgName = "yourTenantPrefix"
$adminUrl = "https://$orgName-admin.sharepoint.com"
$appId = "" # Client Id
$connection = Connect-PnPOnline -ClientId $appId -Url $adminUrl -Interactive -ReturnConnection # -ForceAuthentication
$connection Use MSAL.PS module to get an msal token then use token in Microsoft graph-based requests:
$tenantId = ""
$clientid = ""
$url = ""
$token = Get-MsalToken -ClientId $clientid -TenantId $tenantId -InteractiveBy default token expires in ~ 1 hour. But you can refresh it silently.
This helps you in long-running PowerShell scripts that takes hours to complete.
So you can include something like this in the loop:
if ($token.ExpiresOn.LocalDateTime -lt $(get-date).AddMinutes(10)) {
$token = Get-MsalToken -ClientId $clientid -TenantId $tenantId -ForceRefresh -Silent
Write-Host "Token will expire on:" $token.ExpiresOn.LocalDateTime
}Somehow using Connect-PnPOnline with AccessToken option did not work if the token was acquired with MSAL.PS interactively. But it did work when you get msal.ps token unattended (using App credentials). So…
If you can get an Application (non Delegated) permissions to your azure-registerd-app,
you can use msal token to connect to site with PnP
=========================
NB: For delegated permissions, the effective permissions of your app are the intersection of the delegated permissions the app has been granted (via consent) and the privileges of the currently signed-in user. Your app can never have more privileges than the signed-in user.
You have a list in SharePoint Online. You want list items be visible to specific users only.
You want to leverage Item-Level Permissions under List Advanced settings: “Read access: Read items that were created by the user”. But the problem is it was not users who created items. E.g. the list was imported from excel file or created programmatically or migrated.
PnP.PowerShell helps. Using “Set-PnPListItem”, you can re-write “Author” field in the list item.
Set-PnPListItem -List "Test" -Identity 1 -Values @{"Author"="testuser@domain.com"}And, of course, use Item-Level Permissions under List Advanced settings: “Read access: Read items that were created by the user”:
Add users to “Site Visitors” group for read-only access:
… more TBP
You run some PnP PowerShell code unattended e.g. daemon/service app, background job – under application permissions – with no user interaction.
Your app needs to connect to SharePoint and/or Microsoft Graph API. Your organization require authentication with a certificate (no secrets). You want certificate stored securely in Azure Key Vault.
Here is the sample PowerShell code to get certificate from Azure Key Vault and Connect to SharePoint with PnP (Connect-PnPOnline):
# ensure you use PowerShell 7
$PSVersionTable
# connect to your Azure subscription
Connect-AzAccount -Subscription "<subscription id>" -Tenant "<tenant id>"
Get-AzSubscription | fl
Get-AzContext
# Specify Key Vault Name and Certificate Name
$VaultName = "<azure key vault name>"
$certName = "certificate name as it stored in key vault"
# Get certificate stored in KeyVault (Yes, get it as SECRET)
$secret = Get-AzKeyVaultSecret -VaultName $vaultName -Name $certName
$secretValueText = ($secret.SecretValue | ConvertFrom-SecureString -AsPlainText )
# connect to PnP
$tenant = "contoso.onmicrosoft.com" # or tenant Id
$siteUrl = "https://contoso.sharepoint.com"
$clientID = "<App (client) Id>" # Azure Registered App with the same certificate and API permissions configured
Connect-PnPOnline -Url $siteUrl -ClientId $clientID -Tenant $tenant -CertificateBase64Encoded $secretValueText
Get-PnPSite
The same PowerShell code in GitHub: Connect-PnPOnline-with-certificate.ps1
References:
Can I use PowerShell 7 “-Parallel” option against SharePoint list items with PnP.PowerShell? Can I run something like:
$items | ForEach-Object -Parallel {
$listItem = Set-PnPListItem -List "LargeList" -Identity $_ -Values @{"Number" = $(Get-Random -Minimum 100 -Maximum 200 ) }
} Yes, sure… But! Since it’s a cloud operation against Microsoft 365 – you will be throttled if you start more than 2 parallel threads! Using just 2 threads does not provide significant performance improvements.
So, try PnP.PowerShell batches instead. When you use batching, number of requests to the server are much lower. Consider something like:
$batch = New-PnPBatch
1..100 | ForEach-Object{ Add-PnPListItem -List "ItemTest" -Values @{"Title"="Test Item Batched $_"} -Batch $batch }
Invoke-PnPBatch -Batch $batchAdding and setting 100 items with “Add-PnPListItem” and “Set-PnPListItem” in a large (more than 5000 items ) SharePoint list measurements:
| Add-PnPListItem Time per item, seconds | Set-PnPListItem Time per item, seconds | |
| Regular, without batching | 1.26 | 1.55 |
| Using batches (New-PnPBatch) | 0.10 | 0.80 |
| Using “Parallel” option, with ThrottleLimit 2 | 0.69 | 0.79 |
| Using “Parallel” option, with ThrottleLimit 3 | 0.44 (fails level: ~4/100) | 0.53 (fails level: ~3/100) |
Adding items with PnP.PowerShell batching is much faster than without batching.
More:
Scenario: You have a large (>5k items) list in SharePoint Online you need to clean-up, for instance:
There was a problem in SharePoint Online – you could not delete a large list – you had to remove all items first, but removing all items was also a challenge. Microsoft improved SharePoint Online, so now it takes ~1 second to delete any SharePoint list, including 5000+ items list via GUI or PowerShell:
Remove-PnPList -Identity $listcommand works very fast – ~1 second to delete entire list with >5000 items.
In this scenario we need to keep the list, but make it empty (clean it up).
GUI: You can change the list view settings “Item Limit” to <5000 and try to delete items in chunks, but (at least in my experience) when you try to select, let say, 1000 items and delete them via GUI – it says “775 items were not deleted from large list”:
so this option seems like not a good one.
ShareGate: 3-rd party tools like Sharegate, SysKit give a good results too.
PowerShell
Try this PowerShell command with ScriptBlock:
Get-PnPListItem -List $list -Fields "ID" -PageSize 100 -ScriptBlock { Param($items) $items | Sort-Object -Property Id -Descending | ForEach-Object{ $_.DeleteObject() } }
or this PowerShell with batches:
$batch = New-PnPBatch
1..12000 | Foreach-Object { Remove-PnPListItem -List $list -Identity $_ -Batch $batch }
Invoke-PnPBatch -Batch $batchfor me both methods gave same good result: ~17 items per second ( ~7 times faster than regular).
Consider the following scenario: in a large SharePoint list there are items you need to delete and the rest items must stay (typical case might be to purge old items – e.g. items created last year).
In this case you’d
# to get all list items
$listItems = Get-PnPListItem -List Tasks -PageSize 1000
# or to get some list items
$listItems = Get-PnPListItem -List Tasks -Query <query>
# select items to delete
$itemsToDelete = $listItems | ?{$_.Modified -lt $threshold}
# delete some list items
$batch = New-PnPBatch
$itemsToDelete | Foreach-Object { Remove-PnPListItem -List $list -Identity $_ -Batch $batch }
Invoke-PnPBatch -Batch $batchHow fast are PnP batches? What is better in terms of performance – ScriptBlock or Batching? Here are my measurements:
| Time elapsed, seconds | with batches | with scriptBlock | without batches |
| Add-PnPListItem (100 items) | 6-10 seconds | 60-120 seconds | |
| Add-PnPListItem (500 items) | 20-40 seconds | 230-600 seconds | |
| Add-PnPListItem (7000 items) | 314-600 seconds | ||
| Add-PnPListItem (37000 items) | 3200 seconds | ||
| Remove-PnPListItem (1000 items) | 58-103 seconds | 58 seconds | 430-1060 seconds |
| Remove-PnPListItem (7000 items) | 395-990 seconds 3000 seconds | 397-980 seconds | |
| Remove-PnPListItem (30000 items) | one big batch : 13600 seconds 30 batches 1000 items each: 3500 seconds |
both – PnP PowerShell batches and ScriptBlocks are 7-10 times faster than plain PnP PowerShell!
Can we use Microsoft Graph API to complete the same task? TBC…
Note… For the sake of history: It used to be like that for 5k+ lists:
“Remove-PnPList” fails with a message “The attempted operation is prohibited because it exceeds the list view threshold enforced by the administrator”. Deleting with GUI failed too.
References: