Viewing, Restoring and Removing Items from the SharePoint Recycle Bin – The attempted operation is prohibited because it exceeds the list view threshold enforced by the administrator.


I’ve had a script for a while that allows you to view all the items in the Recycle Bin for a Site Collection and prints out to a CSV file. Recently the environment I’ve been running this in has been throwing an error saying;

The attempted operation is prohibited because it exceeds the list view threshold enforced by the administrator“.

Getting all items out of the recycle bin.

Originally, I used the PNP Powershell command Get-PnPRecycleBinItem and it was only when I did a Google search for this issue, I found that other people were also having this problem. The PnP team have solved this issue now by adding -RowLimit parameter. If you set the RowLimit high enough you can return all items, as internally, it seems to implement a paging mechanism.

I now use the below script to export the result to a CSV file.

Once I have the CSV file, I’m able to filter further in excel and save back to CSV to use to either Restore / Delete the items out of the recycle bin.

Restoring Deleted Items using a csv file.

It seemed that now that I can use RowLimit with Get-PnPRecycleBinItem I should be able to call Restore-PnpRecycleBinItem to restore the item. However, this isn’t the case. Even just passing the Identity of one item within the Recycle Bin, you get the same error message.

The attempted operation is prohibited because it exceeds the list view threshold enforced by the administrator“.

There is no RowLimit option on the Restore-PnpRecycleBinItem. The code must internally make a call to get all RecycleBin Items first without using RowLimit. Interestingly though, a user could go to a recycle bin, see items, and restore them if they wanted to. By looking through the network traffic, I was able to see that the GUI uses the following API to Restore Items.

POST /_api/site/RecycleBin/RestoreByIds

Passing in the following JSON body.

There can be one or many Ids.

The trouble with using REST API you need an accessToken. Using Connect-PnPOnline using just your Username and Password, you are unable to call back the AccessToken value. The easiest way to do this is using the PNPO365ManagementShell. When called a browser window will open, paste in the code that is showing in your PowerShell window (It is copied to the clipboard already) and then sign in with your account. This will allow you to grab an accesstoken using Get-PNPAccessToken.

This is what I do in the below code. I grab an access token, then loop through every item in a CSV file to restore.

Deleting Deleted Items using a csv file.

I discovered that I also get the error message when using Clear-PnpRecycleBinItem.

Again, I was able to do this in the GUI, and looking at the network traffic there is an API to delete the items.

POST /_api/site/RecycleBin/DeleteByIds

The JSON body is same format as the RestoreByIds, where it passes in one or many Ids.

The code below is almost identical to the Restore-RecycleBinItems.ps1. Passing in a CSV file with the IDs of files to delete permanently.

Fixing a Document Content Type that I could not change in SharePoint


I have come across a problem today, that initially had me stumped. A word document had a custom content type assigned to it, but it was the wrong one. The user was unable to change the content type. First, I thought it might be permissions, but I also couldn’t change the content type. The version number continued to go up, which indicates something was being saved, but the content type just wasn’t changing.

Steps to attempt to change the Content Type

  • On the library select the document you wish to change the content type for.
  • Go to the Information Panel and scroll down to Properties.

  • I first tried to change the Content type directly in the Information Panel, but it just flicked back. Next, I tried to click Edit all.

  • I clicked on the Content Type, and from the drop down I change the Content Type to Document.

  • The screen shot above, shows the document after 2 attempts of changing the Content Type. Notice how the version number has changed, but the Content Type still stuck.

How to fix

  • Open the document in the desktop version.

  • In the client application click File.
  • Note: You won’t be able to do the following if Protect Document is enabled, and you don’t have the password.
  • In the Info section, where is says Inspect Document click the Check for Issues button, then Inspect Document

  • On the Document Inspector dialog, Click Inspect

  • Once Inspected, click Remove All button under the Document Properties and Personal Information section.

  • Close the Document Inspector dialog.
  • Save the file (If Autosave isn’t on)
  • Close the Client Application
  • The SharePoint list will show that the file is now of Content Type “Document” (Or whatever is the first/default Content Type in your library) and the version number has gone up once more.

If you need to, you should be able to change the content type without any issues.

I’m not 100% sure, but I believe this is happening because the content type that has been saved within the document is corrupted/different from the same name content type with the one in the library. I believe this has happened in the user’s environment, where the document was originally in a library with an older version of the content type in a different site collection. Then moved to a newer library. The content type exists in the newer library (as we provision all our sites with PNP), but it has changed slightly, for example a column has the same name but different ID.

When you clear the content type from the document, when it is saved back to the SharePoint library, it grabs the information from the library and puts the new content type information back into the document. Going forward, there will be no more corruption or conflict. Although it might be possible to have the issue again if you move the document back to the other library in the other site collection with the older version of a content type.

Finding the related Site from Teams Private Channel Site


Private Channels gives the ability to restrict the membership further within a Team site. A person can create a private channel, like creating a public channel, except they can add owners/members to the channel from a subset of members from the Team site.

When a private channel is created, what is happening under the covers is a creation of another SharePoint site. A cut down version of a SharePoint site, using the Template TEAMCHANNEL#0. (ID: 69 for those that want to know)

As this is my first blog post about Private Channels, let me demonstrate quickly how to create a Private Channel.

How to add a private channel

  • From MS Teams click on the ellipse next to your Team name, and select Add Channel.
  • Give the channel a name, optional description, and select “Private – Only accessible to a specific group of people within the team”
  • Click Next. On the next page you can add people from the Team to have access to the Private Channel. They can be an Owner of the channel even if they are only a member within the Team.
  • The private channel will show up as a channel underneath your team, with a pad lock next to it, indicating that it is a private site. You will only see this channel if you are a owner/member of the channel.
  • The SharePoint site – which you can get to by clicking on files Open in SharePoint – has the URL made up of https://<tenant>.sharepoint.com/sites/<TeamName>-<ChannelName> and the home page of the site is the root of the Shared Document library.

Finding the related site

There are a couple of places I have found out where to get the related site.

Property Bag and Graph API

When I did a PNP Get-ProvisioningTemplate pointing at a private channel site, I discovered in the property bag there is a value called RelatedGroupId and it is Indexed.

  <pnp:PropertyBagEntry Key=“RelatedGroupId” Value=“d99aa865-cd55-46cc-b256-177975ad3e13” Overwrite=“false” Indexed=“true” />

With this value you can then get the SharePoint site of the MS Team using Graph API

https://graph.microsoft.com/v1.0/groups/<group-id>/sites/root?$select=webUrl

or

https://graph.microsoft.com/v1.0/groups/<group-id>/sites/root/weburl

Note: On the parent Team site, there is a property bag value called GroupId. It also has RelatedGroupId, which has the same value.

CSOM using the Site object

The related GroupID can also be obtained in CSOM via the Site object.

Site site = context.Site;
context.Load(site, s => s.RelatedGroupId);
context.ExecuteQueryRetry();

Showing all the private channels from the main SharePoint site

When I used PNP to obtain the Private Channel template and discovered the RelatedGroupId was in the property bag and that it was indexed, means that it is searchable. If you check the Manage Search Schema, you will find the managed property.

This means doing a simple search like below, will return all the private channel sites.

RelatedGroupId:<GroupID> contentclass:STS_Site

Using the Microsoft Search PnP Modern Search SPFX (https://github.com/microsoft-search/pnp-modern-search/releases/tag/3.8.0), very quickly I was able to display links.

For someone who only has access to one of the Private Channels, they will only see one in search.

Updating an expired Client Secret of a SharePoint Add-in using Azure-AD/Az Cli


Back in May 2016, I wrote a post to show you how to update the Client Secret of a SharePoint add-in. This used the PowerShell module MSOL. https://cann0nf0dder.wordpress.com/2016/05/18/updating-an-expired-client-secret-of-sharepoint-add-in/

The MSOL module is now (or going to be) deprecated. Therefore, I needed to find a different way of doing this, and ideally something that could be done with Azure Dev-ops pipelines. I originally started with AZ CLI. Although the Client Secret is tied to a Service Principal in Azure AD, I was unable to change the Key Credentials or Password Credentials for it.

Due to the issues I was getting with AZ CLI, I used Azure-AD module instead. See at end of blog post, how I got Az CLI to work with a workaround.

Updating Client Secret

Connect to Azure-AD

First you will need to connect to Azure-AD

The above code ensures that you have Azure AD installed on your machine, and logs you in.

Getting the Add-in as a Service Principal

Once you have logged in, you will be able to call back your SharePoint Add-in. The SharePoint Add-in is actually a Service Principal within Azure AD, and we will grab this using Get-AzureADServicePrincipal. You can do this by using the AppId, or the AppName. The AppId is fine to use, but when you want to use the same script across multiple environments, you will need to ensure you are passing in the different AppId for each environment. THis is why I have used the Name of the Add-In. (Assuming you have given your App the same name in each environment)

Create a new Secret

Next you need to be able to create a new secret. This is done by creating random bytes and converting to a Base64String. I ensure the password is valid for an additional 2 years.

Create Key Credentials and Password Credential for the App

The SharePoint Add-In requires 2 Key Credentials (One Sign and one Verify) and 1 Password Credentials. The following script creates new ones, this allows both the old password and the new password to continue working at the same time, until you are able to update the code that uses the ClientID and ClientSecret.

Removing the original Key and Password Credential for the App

The following code shows how to loop round the Key and Password credentials and remove the original ones. It does this by looking for any Credentials that were created before the start date of the new ones. I would only run this part of my code, once I know I have updated my application to use the new password. Not in my example here, but where I’m using it in the real world, my Client Secret is stored within a Keyvault. I would update the keyvault value (ensuring to disable the previous version).

Connecting with AZ Cli Workaround

Using Azure Dev-Ops Pipeline, I really wanted to use AZ cli to be able to update the Client Secret of a SharePoint Add-in. Due to the error messages when I attempted to update the Service Principal Key Credential and Password Credentials, I was forced to use Azure-AD instead. So how can I uses AZ Cli.

My Dev-Ops Pipeline uses a service account, and I have ensured this service account has permissions to update the directory.

Then I can connect from Az Cli to Azure-AD doing the following:

I add the above code in the full script just after the parameter, as the pipeline will already be signed in as the Pipeline Service Principal. It will then grab the Access token to sign in with Azure AD, and then able to run the rest of the script.

Full Script

Unable to change Office 365 Group Membership


Was recently having a problem trying to change the group membership of a 365 Group. I was trying to add external users to the group, and through SharePoint it always redirects you to Outlook to do this.

  • Click on members top right of the screen.
  • Click Add members
  • Click go to Outlook to add Guests.
  • This should redirect you to the group information for the group, where you can edit; about this group, change membership, see emails, and files related to the group.

The problem I was getting, was that as soon as it hit the above page, it was redirecting to https://outlook.office365.com/people/. I also couldn’t see the Groups part, as highlighted below.

It made no sense that I couldn’t see it, I was a global administrator, I created the site, I was an owner of the site, I had a E5 license.

It turned out, it was a simple thing that took Microsoft Support, and several engineers a while to help me solve. Somehow my account mailbox had been converted to a Shared Mailbox. How or why this happened doesn’t matter.

By going to the Exchange admin centre, clicking on Recipients and Shared it displays all the Shared Mailboxes.

In the example above, David Mamam (a made-up person in my demo tenant) has a Shared Mailbox. If David attempted to click on the ‘go to outlook’ link in the SharePoint site, he would be re-directed to https://outlook.offic365.com/people. To fix this problem, David’s mailbox needs to be converted back to a regular mailbox.

To do this, click on the ‘convert’ link underneath the ‘Convert to Regular Mailbox’ within the Exchange admin center, as show above. The conversion takes a few moments.

Once complete, the user will be able to click the link to modify the Office 365 Group that they were an owner of.

Setting up a O365 Dev Tenant – Part 6 – Set up SharePoint Tenant


Introduction

In this series of posts, I will explain how you can set up a Development O365 Tenant quickly. Using PowerShell scripts, at the end of this process, you will have:

  • A O365 Development Tenant with 25 “DEVELOPERPACK” Licenses.
  • 25 Users assigned with license
  • A total of 274 Users added to the Tenant
    • Set up for multiple offices
    • Organisational structured
  • All users will be MFA enabled
  • All users will have photos added to their accounts
  • Enabling Office 365 Auditing
  • Setting up the SharePoint Tenant Settings and enabling Public CDN for SPFX

Unfortunately, I have found it impossible to do the following via PowerShell scripts, and these would need to be done manually. I haven’t included this information within these blog post.

  • Create a Tenant App Catalog
  • Set Organisation Details
  • Set Login Branding
  • Set Tenant Branding

Obtaining the code

I have stored my code on GitHub at the following URL for cloning. https://github.com/pmatthews05/SetupDevTenant.git

Setting up the SharePoint Tenant

SharePoint Tenant has many different settings. Here my script is just calling the PNP powershell command Set-PnPTenant. More information about each setting can be found here. https://docs.microsoft.com/en-us/powershell/module/sharepoint-pnp/set-pnptenant?view=sharepoint-ps

If you already have a SharePoint tenant that you want to have exact same settings on your environment, you just need to go to that tenant, sign into the SharePoint admin URL with PNP, and then run

Get-PnPTenant | ConvertTo-Json > .\othertenant.json

This will output a json file that will read in with my script, but you will need to add the following to “PublicCdnOrigins” and sets PublicCdnEnabled to True. as it never reads this in with this command.

“PublicCdnEnabled”: true,
“PublicCdnOrigins”: [
   “*/MASTERPAGE”,
   “*/STYLE LIBRARY”,
   “*/CLIENTSIDEASSETS”
]

Inside the Settings folder, you will find a pre-configured SPTenantSettings.json file. I have put all the parameters in alphabetical order to make it easier to read. This is a typical setup I use, but it probably isn’t what you want. Especially around Sharing. I don’t allow sharing to anonymous users (SharingCapability). I also set Direct default sharing link (DefaultSharingLinkType).

To run this script you first need to connect to the admin site, using Connect-PnPOnline.

Connect-PnPOnline -url:https://[tenant]-admin.sharepoint.com -useweblogin

Now you are connected, you can call the Set-SharePointTenant.ps1 file.

.\Set-SharePointTenant.ps1 -SettingsPath:'.\settings\SPTenantSettings.json'

There are two settings that will require a confirmation that for some reason cannot be pre applied in PowerShell. These settings are OneDriveForGuestsEnabled and OrphanedPersonalSitesRetentionPeriod.



If you are running the template I provided, once the script has finished running your Public CDN will also be turned on.

Using PowerShell, type the following and you will see that the configuration is pending. This takes up to 15 minutes before it is fully enabled. You can keep calling the below command until it no longer says Configuration Pending.

Get-PnPTenantCdnOrigin –CdnType Public

Intune and Azure Directory Premium

In lines 84-86 of Set-SharePointTenant.ps1 there are 3 settings that I have commented out (ConditionalAccessPolicy, AllowDownloadingNonWebViewableFiles and AllowEditing). These settings require Intune and Azure Active Directory Premium subscription. As this is a development tenant, there is no need to set these settings.

I hope you have found this series useful and are able to setup within a couple of days due to Microsoft back end processes a SharePoint Development tenant that you can work with. I am more than happy for anyone to help expand/improve on my GitHub project.

Setting up a O365 Dev Tenant – Part 5 – Turning on O365 Auditing


Introduction

In this series of posts, I will explain how you can set up a Development O365 Tenant quickly. Using PowerShell scripts, at the end of this process, you will have:

  • A O365 Development Tenant with 25 “DEVELOPERPACK” Licenses.
  • 25 Users assigned with license
  • A total of 274 Users added to the Tenant
    • Set up for multiple offices
    • Organisational structured
  • All users will be MFA enabled
  • All users will have photos added to their accounts
  • Enabling Office 365 Auditing
  • Setting up the SharePoint Tenant Settings and enabling Public CDN for SPFX

Unfortunately, I have found it impossible to do the following via PowerShell scripts, and these would need to be done manually. I haven’t included this information within these blog post.

  • Create a Tenant App Catalog
  • Set Organisation Details
  • Set Login Branding
  • Set Tenant Branding

Obtaining the code

I have stored my code on GitHub at the following URL for cloning. https://github.com/pmatthews05/SetupDevTenant.git

Turning on and setting permissions for Office 365 Auditing

At the URL https://protection.office.com you have access to the Security & Compliance Center. There is lots you can do in this area of O365, but I am just going to talk about Auditing in this post.

In the left hand navigation of Office 365 Security & Compliance, expand Search and select Audit Log Search. Yours should be like the screen shot above and has a yellow banner that states you need to turn auditing on. There is a button at the end, and this allows you to turn it on.

My script, Set-Office365Auditing.ps1 not only turns on the Auditing, but it assigns a group of users to have view-only access to the Audit logs.

Enable-OrganizationCustomization

Before you can run my script, the above command needs to be run first, and you need to wait a while before you can run my script below. Due to the time I was working on this blog, I ended up waiting 24 hours before I ran my next script. I’m not sure how long you have to wait.

You need to use the Microsoft Exchange Online PowerShell Module. If you haven’t already downloaded this from part 3 of this series, please follow my previous blog about how to do this correctly. https://cann0nf0dder.wordpress.com/2019/04/14/unable-to-download-the-exchange-online-powershell-module-deployment-and-application-do-not-have-matching-security-zones/

Open Microsoft Exchange Online PowerShell Module. You will need to connect first, before running the script. It allows you to control the signing in.

Connect-EXOPSSession -userPrincipalName [user.name]@[tenant].onmicrosoft.com

Now you can run

Enable-OrganizationCustomization 

Running Set-UserAccountsOnline.ps1

This script uses the Microsoft Exchange Online Powershell Module, and ViewAuditUsers.csv file. It will:

  • Create a new RoleGroup called “View Audits Only
  • Add Users from the CSV file to the RoleGroup
  • Lastly it will turn on Auditing for O365.

Before you run any code, you will need to replace [User.Name] on line 2 of the ViewAuditUsers.csv to your User Name.

Open Microsoft Exchange Online PowerShell Module. You will need to connect first, before running the script. It allows you to control the signing in.

Connect-EXOPSSession -userPrincipalName [user.name]@[tenant].onmicrosoft.com

Now you are connected, you can call the Set-Office365Auditing.ps1 file.

.\Set-Office365Auditing.ps1 -Path:'.\data\ViewAuditUsers.csv' -TenantDomain:'[mytenant].onmicrosoft.com' 

Replace [mytenant] with your tenant name.

If you now head to the Exchange admin center.

Viewing Audit Logs

At the URL https://protection.office.com you have access to the Security & Compliance Center. In the left hand navigation of Office 365 Security & Compliance, expand Search and select Audit Log Search. As you have just turned on Auditing, you might see the yellow/orange message that I have on my screen shot below.

Because you started recording user and admin activities within the last 24 hours, some activities might not show up in search results yet.

After a little while, (at least an hour) you will start receiving results when you click Search.

For a user that doesn’t have access, as you haven’t given them View Audits permission, they will get an error message when going directly to the URL.

In this blog post we have turned on the Office 365 Auditing and assigned a few users to have access. In my next and last blog post in this series, I will be setting up my SharePoint tenant admin properties and ensuring public CDN is turned on for SPFX.