Posts Under Tag: Powershell

Office 365 Advent Calendar – 03 Programatically accessing a SharePoint Online Recycle Bin



You’ve got a big amount of items in the recycle bin of a site which you want to access. For example, a user accidentally deleted hundreds of files, and now you want to restore them back easily. Or you have a thousand items in the recycle bin and you want to export some information (which items are in the recycle bin, who deleted them and when) to Excel


Once again, we’re using the PnP PowerShell cmdlets here as a basis, but then also make use of the underlying CSOM calls to access the recycle bin.

$cred = Get-Credential
#Set URL to the web for which you want to retrieve the recycle bin
Connect-PnPOnline -Url -Credentials $cred

$web = Get-PnPWeb
$ctx = Get-PnPContext

#Loading the Recycle Bin Items
$recycleBin = $web.RecycleBin

#We are now narrowing down the items. We only want to restore all Excel spreadsheets
$spreadsheets = $recycleBin | Where {$_.Title -like "*.xlsx"}

#Another example: select all items deleted by a specific user
#$deletedByUser = $recycleBin | Where {$_.DeletedByEmail -eq ""}
#Restoring the spreadsheets
$spreadsheets | %{ $_.Restore() }

#Exporting all items to a CSV file
$recycleBin | Select Title, DeletedByEmail, DeletedDate | Export-Csv C:\RecycleBinItems.csv

Example of a CSV export:

Project_Template.pptxrene@mytenant.onmicrosoft.com12/3/2016 1:19
Azure_Information_Protection_licensing_datasheet_EN-US.pdfrene@mytenant.onmicrosoft.com12/3/2016 1:20
Security in Office 365 Whitepaper.docxrene@mytenant.onmicrosoft.com12/3/2016 1:20

If you are curious which methods and properties are available for the recycle bin in general as well as individual items, please review RecycleBinItemCollection and RecycleBinItem.

Office 365 Advent Calendar – 02 Retrieving SharePoint Online Site Collection Sizes


You want to get an overview of the storage usage of all site collections in your tenant, similar to what you can see in the “SharePoint Administration” section in Office 365. But as you may have hundreds of site collections, or as you want to pass on that information to someone else, you want to extract this information into a spreadsheet


$cred = Get-Credential
Connect-PnPOnline -Url "" -Credentials $cred
$sites = Get-PnPTenantSite -Detailed | select Url, StorageUsage, StorageMaximumLevel, @{label="Usage in %";Expression={[math]::Round($_.StorageUsage*100/$_.StorageMaximumLevel,2)}}
$sites | Export-Csv C:\SiteStorage.csv


Here is a sample output:

UrlStorageUsageStorageMaximumLevelUsage in %

Note: StorageUsage and StorageMaximumLevel are shown in MB

Office 365 Advent Calendar – 01 Getting all Permissions Levels in SharePoint Online



You want to get an overview of all Permission Levels that are set up in your tenant’s SharePoint Online site collections. Not only the out-of-the-box permission such as “Full Control” or “Read”, but also any custom permission levels which may have been defined by your site collection’s administrators (“Add Only” is a common example).


A prerequisite for this code are the OfficeDev PnP PowerShell cmdlets. Ensure that you have a current version installed before proceeding.
The following code connects to your tenant and fetches all site collections (with a small where query, as I wanted to limit it to all sites in the /sites/ or /teams/ paths). It then loops through all site collections, retrieves the permission levels, and writes that information into a CSV file.

$cred = Get-Credential
Connect-PnPOnline -Url -Credentials $cred
$sites = Get-PnPTenantSite | where {$_.Url -like "**" -or $_.Url -like "**"}
foreach($site in $sites) {
	Connect-PnPOnline -Url $site.Url -Credentials $cred
	$ctx = Get-PnPContext
	$roleDefs = (Get-PnPWeb).RoleDefinitions
	foreach($rd in $roleDefs) {
		Add-Content ".\permissionlevels.csv" "$($site.url), $($rd.Name)"

Getting your Office 365 tenant’s SharePoint site structure with PowerShell


From time to time you may want to get an overview of the structure of your SharePoint Online environment. That is, you want to know the number of site collections and subsites, and know how they are organised.

While you could review the existing site collections either in the UI (not very convenient once you have more than 20) or via PowerShell, both approaches don’t provide you with details about a site collection’s structure itself. How many subsites are there, how are they organised? How many levels deep do they go, or are there dozens of subsites located directly underneath a specific site collection?


Just like in my recent post, what you need are the OfficeDev PnP PowerShell cmdlets available at (review the installation instructions on that page if you haven’t installed those awesome cmdlets yet) and an account which has been assigned either the “SharePoint administrator” or “Global administrator” role in your tenant. Additionally, this account needs to have access to all SharePoint Online site collections – this is where the script to set site collection administrators on all site collections comes in very handy.

The Script

What the script does is the following: it first fetches the list of all site collections (apply a filter if necessary) and then collects the structure for each site collection by going through all subsites. Lastly, it writes the collected information into a CSV file for further manual analysis, e.g. in Excel.

#Config Variables - update these as required
$tenant = "mytenant"
$ReportPath = "SPOStructure.csv"

# Note: If you run this script regularly, please have a look at
# the following site to see how you can store credentials securely in Windows
$cred = Get-Credential
Connect-SPOnline -Url "https://$($tenant)" -Credentials $cred

write-host "Getting all sites"
#Note: we do not make use of the IncludeOneDriveSites parameter here,
# which would include personal sites as well
#You could also filter here to get only specific sites, or use
# the -Url parameter for the Get-SPOTenantSite to get the structure of a single site collection only
$tenantSites = Get-SPOTenantSite

function Get-SPOSubWebs($Context, $RootWeb){ 
	$arrWebs = @()
        $Webs = $RootWeb.Webs 
        ForEach ($sWeb in $Webs) 
	    $arrWebs  += $sWeb.Url
            $arrWebs += Get-SPOSubWebs -RootWeb $sWeb -Context $Context 
	return $arrWebs

$allWebs = @()
foreach($site in $tenantSites) {
    write-host "Connecting to $($site.Url)"
    Connect-SPOnline -Url $site.Url -Credentials $cred
    $allWebs += $site.Url
    $allWebs += Get-SPOSubWebs (Get-SPOContext) (Get-SPOWeb)
write-host "Finished"

$allWebs | Out-File -FilePath $ReportPath

Here’s the script in action:

Supply values for the following parameters:
Getting all sites
Connecting to 
Connecting to 
Connecting to 
Connecting to 
Connecting to 
Connecting to 
Connecting to 


And the result written to the CSV file:

Setting Administrators on all SharePoint Online Site Collections


Imagine the following scenario: A user or a group of people need full access to all site collections in your Office 365 tenant. It could be a service account that gathers some statistics regularly, or a group of users who provide regular detailed support to your organization. How can you ensure that these users have access to all site collections, even newly created ones? What is the best of all to manage this group of users?

Currently, there is no way how you automatically assign users or groups as site collection administrators in your tenant. And while you can manage the settings per site collection in the SharePoint Online Administration area of the Office 365 portal, doing so for dozens or hundreds of sites is not a productive use of time.


But there’s a way to automate this process, and as usual PowerShell comes to the rescue. What you need are the OfficeDev PnP PowerShell cmdlets available at (review the installation instructions on that page if you haven’t installed those awesome cmdlets yet) and an account which has been assigned either the “SharePoint administrator” or “Global administrator” role in your tenant.

The cmdlet we are interested in is called Set-SPOTenantSite. It allows you to manage some selected properties of a SharePoint Online site collection, among them the owners. The ‘Owners’ parameter expected a list of accounts, for example: ‘’,  ‘’,  ‘’. As you can see, it requires a user’s login name. But how about groups? If I create an Office 365 group, how can I determine its login name? While you can also achieve the same thing via PowerShell, this is one way to do it via the browser:

  1. Go to a SharePoint site and grant the group access
  2. While still on the permissions view, click on the Group name so that you access it’s Personal Settings page
  3. On that page, it will list something like “Account c:0-.f|rolemanager|s-1-5-21-784567607-4288704409-1262486537-2161342”. “c:0-.f|rolemanager|s-1-5-21-784567607-4288704409-1262486537-2161342” is the login name which you need
  4. You can then remove the permissions for the group again

Next, you also need to think about which users you want to add as site collection administrators. If you have a group of users that should be added to all site collections, it makes sense to add all those users either to an Active Directory group (if you’re synchronising your Active Directory with Office 365) or an Office 365 group. That way, you can manage the group of users fairly easily, and add or remove users simply by managing the group – without having to do anything on the site collections directly.

The Script

Here’s the script that helps you set the site collection administrators on a filtered set of site collections (I’m skipping any personal OneDrive for Business sites in the *mytenant-my.sharepoint/* path, e.g. Update: I just realised that OneDrive sites aren’t returned by default, the IncludeOneDriveSites parameter has to be set to $true for this. Either way, we’re skipping sites in the* path in this example). Please note that in its current form below, it is meant to be run directly by someone who has account credentials for a “SharePoint administrator” or “Global administrator”. It can be adapted to use stored credentials, e.g. when you want to run it as a daily scheduled task on a server.

#comma separated list of users and groups to be added
$adminAccounts = "",""

#Specify the tenant here
$tenant = "mytenant"

# Note: If you run this script regularly, please have a look at the following site to see how you can store credentials securely in Windows
$cred = Get-Credential

write-host "Connecting to https://$($tenant)"
Connect-SPOnline -Url "https://$($tenant)" -Credentials $cred
write-host "Getting list of site collections"

#Note: we are only fetching the root site collection and any site collection in the /sites/ path
#Update filters here accordingly to match your requirements
$sitecollections = Get-SPOTenantSite | where {($_.Url -like "*$($tenant)") -or ($_.Url -like "*$($tenant)*")}

foreach($sitecollection in $sitecollections) {
	write-host "Adding administrators to $($sitecollection.Url)"
	Set-SPOTenantSite -Url $sitecollection.Url -Owners $adminAccounts

That’s it, just a couple of lines of PowerShell which can save you a lot of time and help you with your support processes.

Lastly, if you want to run this script regularly as you want to ensure that your users/groups are also added to new site collections and added to existing ones (if they have been removed), I would recommend to follow the instructions given on the OfficeDev PnP PowerShell page for setting up credentials in Windows’ credentials manager and running the script as a scheduled task.


Script to download the SharePoint Online DLLs

Over at the SharePoint Community, Balamurugan Kailasam posted that he was able to download the DLLs from SharePoint Online. While it is unclear at the moment why this is possible (maybe required by some tools?), or if this might be deactivated at some point, I decided nevertheless to write a small PowerShell script to download all DLLs that I’m aware of.

The code below create a subfolder with the current time as the name (in case you want to run it regularly and keep the older versions), and then downloads all the files into it. Additionally, it creates a file called info.txt with the version information of all DLLs

[Environment]::CurrentDirectory=(Get-Location -PSProvider FileSystem).ProviderPath
#replace contoso with your own tenant
$spUrl = ""

$dllsVTI = @("Microsoft.BusinessData.dll", "Microsoft.Office.Client.Policy.dll", "Microsoft.Office.Client.TranslationServices.dll", 
		"Microsoft.Office.DocumentManagement.dll", "Microsoft.Office.Excel.Server.Udf.dll", "Microsoft.Office.Excel.Server.WebServices.dll", 
		"Microsoft.Office.Policy.dll", "Microsoft.Office.SecureStoreService.Server.Security.dll", "Microsoft.Office.Server.dll", 
		"Microsoft.Office.Server.Search.Applications.dll", "Microsoft.Office.Server.Search.Connector.dll", "Microsoft.Office.Server.Search.dll", 
		"Microsoft.Office.Server.Search.ExchangeAdapter.dll", "Microsoft.Office.Server.UserProfiles.dll", 
		"", "Microsoft.Office.Word.Server.dll", "Microsoft.Office.Workflow.Actions.dll", 
		"", "Microsoft.SharePoint.Client.dll", "Microsoft.SharePoint.Client.DocumentManagement.dll", 
		"Microsoft.SharePoint.Client.Publishing.dll", "Microsoft.SharePoint.Client.Runtime.dll", "Microsoft.SharePoint.Client.Search.Applications.dll", 
		"Microsoft.SharePoint.Client.Search.dll", "Microsoft.SharePoint.Client.ServerRuntime.dll", "Microsoft.SharePoint.Client.Taxonomy.dll", 
		"Microsoft.SharePoint.Client.UserProfiles.dll", "Microsoft.SharePoint.Client.WorkflowServices.dll", "Microsoft.SharePoint.dll", 
		"Microsoft.SharePoint.Linq.dll", "Microsoft.SharePoint.Portal.dll", "Microsoft.SharePoint.Publishing.dll", "Microsoft.SharePoint.Search.dll", 
		"Microsoft.SharePoint.Search.Extended.Administration.Common.dll", "Microsoft.SharePoint.Search.Extended.Administration.dll", 
		"Microsoft.SharePoint.Search.Extended.Administration.ResourceStorage.dll", "Microsoft.SharePoint.Security.dll", "Microsoft.SharePoint.Taxonomy.dll", 
		"Microsoft.SharePoint.Taxonomy.Intl.dll", "microsoft.sharepoint.WorkflowActions.dll", "Microsoft.SharePoint.WorkManagement.Client.dll", 
		"Microsoft.Web.CommandUI.dll", "SHTML.dll", "spnativerequestmodule.dll")
$dllsAPP= @("Microsoft.Office.Discovery.Soap.dll", "Microsoft.Office.DocumentManagement.Pages.dll", 
 "Microsoft.Office.officialfileSoap.dll", "Microsoft.Office.Policy.Pages.dll", 
 "Microsoft.Office.Server.Search.Applications.ServerProxy.dll", "Microsoft.Office.Server.Search.ServerProxy.dll", 
 "Microsoft.Office.Server.UserProfiles.ServerStub.dll", "Microsoft.Office.Server.WorkManagement.ServerProxy.dll", "Microsoft.Office.SlideLibrarySoap.dll", 
 "Microsoft.Office.TranslationServices.ServerStub.dll", "Microsoft.Office.Workflow.Pages.dll", "Microsoft.Office.WorkflowSoap.dll", 
 "Microsoft.SharePoint.ApplicationPages.dll", "Microsoft.SharePoint.AppMonitoring.ApplicationPages.dll", "Microsoft.SharePoint.OfficeExtension.ApplicationPages.dll", 
 "Microsoft.SharePoint.Portal.Proxy.dll", "Microsoft.SharePoint.Taxonomy.ServerStub.dll", "Microsoft.SharePoint.WorkflowServices.ApplicationPages.dll", 
 "Microsoft.SharePoint.WorkflowServices.ServerProxy.dll", "STSSOAP.DLL")

$folder = new-item -type directory $(get-date -f yyyy-MM-dd_HH_mm_ss) 

function GetDll([string]$dll, [string]$path) {
	$file = $folder.FullName+"\"+$dll
	$wc = (New-Object System.Net.WebClient)
	write-host "Downloading ",$spUrl,$path,$dll
    	$wc.DownloadFile($spUrl + $path + $dll, $file)
	$item = get-item $file
	if($item) {
		add-content "$($folder)\info.txt" "$($item.Name) - $($Item.VersionInfo.ProductVersion)"

foreach($dll in $dllsVTI) {
	GetDll $dll "_vti_bin/"

foreach($dll in $dllsAPP) {
	GetDll $dll "_app_bin/"


New options in the SharePoint Online Management Shell PowerShell cmdlets

A few days ago, Microsoft released a new version of their SharePoint Online Management Shell (the PowerShell cmdlets that can be used to manage SharePoint Online). In this blog post, I will briefly explain what was changed and what you can do now with the new options. Note: The new internal version number for the cmdlets is : 15.0.4569.1506


The first new attribute that is available is for Site Collections and is called SharingCapability. Basically, this attribute tells you if sharing has been enabled for a site collection or not. The three possible values are Disabled, ExternalUserSharingOnly, and ExternalUserAndGuestSharing (see also

When you run Get-SPOSite  | select Url, SharingCapability you’ll get a list of all site collections and the corresponding Sharing Capability.

You can also set the values, so if you want to enable sharing for a site collection for authenticated users only, run Set-SPOSite -Identity <YourSiteUrl> -SharingCapability ExternalUserSharingOnly:


For tenants, there are three new attributes: DisplayStartASiteOptionSharingCapabilityStartASiteFormUrl

You can get the current settings by simply running Get-SPOTenant:

SharingCapability simply shows you which settings have been set for your tenant in general.

DisplayStartASiteOption and StartASiteFormUrl can help you with allowing users to create new site collections, and which form to use. For example, there’s a sample app from Richard diZerega from Microsoft that you could customise for this purpose.

DisplayStartASiteOption controls if the “new site” link is available when you click on Websites in the Ribbon. If it’s deactivated, you don’t get to see such an option:

Turning it on (Set-SPOTenant -DisplayStartASiteOption $true) will provide you with the link:

If you don’t want to use the default form, you can then also specifiy your custom form via Set-SPOTenant -StartASiteFormUrl  <FormUrl>.

Please note that these two options are also available through the UI.  These two options can be found in the SharePoint Admin Center under Settings:

If DisplayStartASiteOption  is set to false, “Hide the link” is activated for Start a Site:


Setting it to true, “Show the link” becomes active:


Lastly, if you set a URL for StartASiteFormUrl, the corresponding field is set:


 External Users

The last changes in the new version of the cmdlets offer a few new attributes for external users: InvitedByWhenCreatedAcceptedAs

When you run Get-SPOExternalUser, you can see who invited the external user, when he accepted, and with which email address:

Additionally, you also have the option to get the external users of a single site collection only, and not the whole tenant, by running Get-SPOExternalUser with the -SiteURL parameter:


PowerShell Script to download SharePoint Conference 2014 sessions

All sessions of the SharePoint Conference 2014 have been posted already on Channel 9! If you plan to watch a lot or even all of them, here’s a little PowerShell script that will help to download them. The original is from 2012 and was created by Vlad Catrinescu, with some very minor modifications (basically added one [xml] and changed the RSS feed) it’s also working this. year.

[Environment]::CurrentDirectory=(Get-Location -PSProvider FileSystem).ProviderPath
$rss = (new-object net.webclient)

$a = [xml]($rss.downloadstring(""))

$ | foreach{
 $code = $_.comments.split("/") | select -last 1

 $url = New-Object System.Uri($_.enclosure.url)

 $file = $code + "-" + $_.creator + "-" + $_.title.Replace(":", "-").Replace("?", "").Replace("/", "-").Replace("<", "") + ".mp4"

 if (!(test-path $file))
    $wc = (New-Object System.Net.WebClient)
    $wc.DownloadFile($url, $file)

A few new SharePoint & Office 365 presentations

During the past few months, I presented a few times online, at conferences, and at user groups. All those presentations were uploaded to my SlideShare account recently.

I uploaded multiple presentations on the topic of SharePoint Online management with PowerShell due to the fact that, depending on audience, I sometimes included infos on Exchange Online / Lync Online as well. Also, I constantly update that presentation with new information, such as newly available cmdlets.

Presenting at SharePoint Connections and European Office 365 connect

As an Office 365 MVP, I’m an active member of the SharePoint and Office 365 community. I blog, I write articles and books, I hold webinars, and sometimes I also present at conferences. I’m happy to announce that I’ll be speaking twice in the Netherlands, once in November at SharePoint Connections 2013, and in January at the first European Office 365 connect.

SharePoint Connections Amsterdam 2013 – 19.-21.November 2013

I’ll be presenting alongside a whole lot of other great people, among them some MVPs and even MCSMs. If you intend to come as well, you can get a 10% discount with thede SP288 when you register!

My presentations are:
Hybrid SharePoint 2013 and Office 365 Environments for Decision Makers
What value does hybrid SharePoint 2013 and Office 365 environments offer, which drawbacks do they have? Why do companies use them? How do you plan them, and which governance aspects need to be considered? And which resource requirements do they impose? These and many more questions will be answered in this session, targeted at IT Managers and other decision makers who want to learn more about hybrid environments. Come see!

Automating Office 365 with PowerShell
With the new Office 365, there are many more possibilities for administrators to manage Office 365 with PowerShell, especially regarding SharePoint Online. In this session, you will get an overview of the existing cmdlets and learn how common tasks such as creating a mailbox and granting access to a SharePoint site can be automated.


European Office 365 connect – 28.-30.January 2014

I’ll be back in the Netherlands in January, when I’ll present at the very first European Office 365 connect. The code for a 10% discount during registration is GB274.
My 2 presentations are:

Real-Life Use Cases for SharePoint Online
You’ve got SharePoint Online, but you think you’re not really leveraging it yet to get maximum value out of it? Or you’re looking generally for ideas on how it could be used? In this session, you will get to see how other companies use SharePoint Online, how they integrate it effectively into their IT architecture, and what kind of solutions they build on it.

Migrating to SharePoint Online
Companies that start using Office 365 usually have a lot of information and solutions already in their organisation. How can they move their data to SharePoint Online? Can all solutions be easily moved as well, or are there restrictions to consider? Who needs to be involved in a migration, which resources are required, and which steps need to be undertaken at which time? Get answers to these questions in this session!


This announcement is also available in German / Diese Ankündigung ist auch auf Deutsch verfügbar