CHUVASH.eu

CHunky Universe of Vigourous Astonishing SHarepoint :)

Category Archives: sharepoint

Add a security group as hubbers using PowerShell

Today I needed to add a security group to “People who can associate sites with this hub” through PowerShell. Here is quick how-to. I usually say “hubbers” instead of the long “People who….”. By the way, if you want to know what prerequisites there are for being a hubber, read my other blog post

An example of how to add a security group is missing in the MS Docs:

This is easy, all you have to do is to find out the security group id, prepend it with “c:0t.c|tenant|” and run the command.

$url = "https://tenant.sharepoint.com/sites/hubsite1"
$groupId = "0b2d5885-158a-4aa1-bea3-cd28f71fcc37"
$securityGroup = "c:0t.c|tenant|$groupId"
Grant-SPOHubSiteRights $url -Principals $securityGroup -Rights Join

That’s it. I’ll try to provide this example to the MSDocs github.

Two workarounds for overcoming the listview threshold

These are two workarounds to see documents / list items in a view that exceeds the listview threshold of 5000 items.

Overcoming the listview threshold is as fun as succeeding in the limbo games.

This is changing all the time. When you read this, it might have changed. Today, 2021-01-05, me and my colleague found following two workarounds for listing over 5000 items in a list view in SharePoint Online:

  1. Sorting by name in a view
  2. Adding a shortcut to OneDrive

Both methods require the Modern UI in SharePoint Online.

In our case we have a migrated document library with many items. In the source, the threshold was much higher, in SharePoint Online some folders didn’t show anything. It showed only “Something went wrong”:

The classic view had a better error message, but no solution for that:

This view cannot be displayed because it exceeds the list view threshold (5000 items) enforced by the administrator.
To view items, try selecting another view or creating a new view. If you do not have sufficient permissions to create views for this list, ask your administrator to modify the view so that it conforms to the list view threshold.

Sorting by Name

We will split those big folders into smaller ones. But while trying things out, we found that having Sorting by name, suddenly showed the documents in the big folders.

As a user you have to scroll a lot to find your document (because of the infinite scroll), but still, now you are able to see your documents!

Also, you can start selecting files and re-organizing them by using “MoveTo”.

Adding shortcut to OneDrive

Another workaround (or I’d rather say trick) is to open the folder from within your personal OneDrive by adding a shortcut:

With that you’ll get the folder linked in your OneDrive.

Even with a big number of files, OneDrive will list the folder. Why is that? Maybe, Microsoft treats personal OneDrives differently, more gently, in a more forgiving way.

On your computer you’ll see the linked OneDrive folder, too.

Listing all renamed sites in SharePoint Online

When you rename a site, a new site is REDIRECTSITE#0, you can get all the sites of that type by running

Get-SPOSite -Template REDIRECTSITE#0

Please consider some caveats with renaming a site url

Automatically detect new sites in SharePoint Online

Original image by William Warby. https://www.flickr.com/photos/wwarby/16414155179/in/photostream/

Sites in SharePoint are created all the time, not only for SharePoint, but also as storage for Yammer, Teams, Planner and other services in Microsoft 365. There are ways to keep track of them, but the ability to automatically detect a new site creation is quite appealing. Automatic detection means a trigger of a Power Automate (Flow) or a Logic App.

There are a few blog posts that exactly describe how you can detect when a new site is created in SharePoint Online:

The provided blog posts are great how-tos, I am not giving you a new how-to for that, I’d like to reason about that solution.

The solution for automatic detection of new sites

Power Automate and Logic Apps can listen to new items in SharePoint. There is a list in the admin site (tenant-admin.sharepoint.com) that has SharePoint Sites as list items, its name is DO_NOT_DELETE_SPLIST_TENANTADMIN_ALL_SITES_AGGREGATED_SITECOLLECTIONS.

That’s it, in essence, it’s just setting up a new flow with “When an item is created in SharePoint” as a trigger, and you have thousands business scenarios you could implement, but let’s dig a little bit deeper.

One List to rule them all

Honestly, I was not aware of that list before I started looking at that. What is that list, why is it called DO_NOT_DELETE_SPLIST_TENANTADMIN_ALL_SITES_AGGREGATED_SITECOLLECTIONS.

The name is hillarious. Why name something to “DO_NOT_DELETE…” and all capslock🤣. But I suppose, there were support cases.

Beware, that list is not documented, that means you’re on your own when Microsoft changes the name or moves the list to somewhere else. So don’t build business critical solutions with that.

From what I can see, that list keeps information about all sites (site collections) in SharePoint Online, even those that are deleted and permanently deleted (?). This might be a source for deeper troubleshooting in some scenarios. It is like an old card index in a library you might have seen long time ago. It is hidden nowadays, but it is still there.

Image by LisaJasminAdams from Pixabay

First, that list is in the SharePoint Admin Site Collection, you need to be at least a SharePoint Administrator to access it. Okay, I’d like to know what’s more in its Site Contents (_layouts/15/viewlsts.aspx):

Well, the UI of that page has not been focused on, but nevermind, the lists are there. But you cannot navigate to that list in the browser directly:

It doesn’t matter since we can use it as a trigger but also the SharePoint REST API to get the items, e.g.:

https://tenant-admin.sharepoint.com/_api/Web/Lists/GetByTitle('DO_NOT_DELETE_SPLIST_TENANTADMIN_AGGREGATED_SITECOLLECTIONS')/Items

You can see more examples of listing the sites in the linked posts. Unfortunately I bumped into an issue when trying to filter the results. If that list contains more than 5000 items (and it will, soon or later), you’ll have to deal with the ListView Threshold.

If you filter on Modified, you won’t able to anything because of the ListView Threshold, but filtering on Created will work.

But this is a side note, this post is automatically detecting new sites, not listing them

Alternative solutions

Using this kind of a hidden list mentioned above is a bit of a hack. I’d say it’s okay as long as it works, and it serves an complementary function, e.g. notifying IT about new sites, and the work is backed up by documented and reliable alternatives:

SharePoint Online Admin

Visiting “Active Sites” in SharePoint Online Admin gives you all the sites, you can sort by Created and see all the new sites. You cannot set up an alert or a flow directly from that, but maybe there will be some built-in functionality for that.

Office 365 Usage Reports

You can get all the sites in an Usage Report, their created, size, last activity etc. It’s not real time, but if you’re fine with 1-2 days delay, you can get this report, extract the new ones and do whatever you wanted to do in your original scenario/need.

SharePoint PowerShell Module

It’s worth mentioning, too, although it’s “heavy”. In a tenant with many sites, the scripts for getting all the sites and connected groups may take hours. I am refering to those scripts that start with Connect-SPOService.

Permissions, Licenses, and Security

The SharePoint connection that listens to the DO_NOT_DELETE_SPLIST_TENANTADMIN_ALL_SITES_AGGREGATED_SITECOLLECTIONS list in the Admin Site Collection needs to be set up with a SharePoint Administrator role account. Beware of who has access to that solution (Power Automate or Logic App), this SPO Admin connection in wrong hands can be disastrous. Especially in Azure, pay attention to who has access to the resource, but also to the resource group and the azure subscription.

The account who sets up a Power Automate needs obviously an appropriate license and also Power Automate activated. In my scenario, I don’t need any premium connectors, but depending on your solution, you might need to license your account appopriately.

Scenarios

In “my” scenario, I want to be notified of all new sites in my business unit within a shared tenant, so that we can contact the site owners, provide guidance and also provision important parts (initial folder structure, some spfx solutions etc).

What is your sceanario?

The code

When I am done developing my proof-of-concept, I’ll try to share more details on the actual implementation. It might be an idea to submit the template to the Microsoft Power Community, but I am not sure it will be accepted, given the fact that it uses undocumented and hidden parts of SharePoint Online that soon or later will be subject to change.

Infographics. Who can associate a site with a hub

I found this nice post and a nice decision flowchart.

Low resolution, visit the post to see more.

I also have drawed a simple chart while explaining for my colleagues, you can see it above. I hope this infographics can be useful to more people. By the way, we use the word “Hubber” (sv. Hubbare) for “People who can associate sites to hubs”

Is an M365 Group a Yammer Community

Nowadays a Yammer Community gets a corresponding Microsoft 365 Group (Office 365 Group, Unified Group). In your work as an SPO Admin, you might need to differentiate “ordinary” Modern Team Sites from those ones that were created for a Yammer Community.

They both have GROUP#0 as Template. On the actual SPO Site object, there is nothing that you can use to differentiate those. Neither you can use the Office 365 Group information. But there is a way: if you connect to Exchange Online and get the group from there, then there is something useful.

I’ll share a piece of code with you, as the rest of the posts and code snippets, it is “evergreen”, it changes all the time, maybe when you read this in future, there is a better way, but today I am using this code:

# Prerequisites
# AllowBasic as Admin, perhaps in a separate window
# Set-ItemProperty -path 'HKLM:\\SOFTWARE\\Policies\\Microsoft\\Windows\\WinRM\\Client' -Name AllowBasic -Value 1
# Connect to Exchange Online
Import-Module ExchangeOnlineManagement
Connect-ExchangeOnline
# you can get $groupId from the SPO object
$exogroup = Get-UnifiedGroup Identity $groupId
$isYammer = $exogroup.GroupSKU -eq "Yammer"
#Bonus: determine if Team is connected (if $isYammer is $false)
$hasTeam = "Team" -in $exogroup.ResourceProvisioningOptions

Estimated Completion in Write-Progress in PowerShell

Have you also got many sites in your tenant? Write-Progress is the bare minimum in a script that goes through all sites. But there is also another nice way to make easier to see the progress – estimated completion time.

Although the idea comes from another blog post (My life is a message), I thought it could be worth sharing it again, especially in the cloud context.

Here is a bit simplified scenario: Getting information for every site. The status message in Write-Progress contains also the estimated completion time.

# This is just an example for time estimations in write-progress,
# though a simplified scenario
$sitesBareMinimum = Get-SPOSite Limit All
$starttime = Get-Date
$count = 0 # kind of an index, counter
$total = $sitesBareMinimum.Count
$sites = $sitesBareMinimum | ForEach-Object {
$site = $_
$estimation = ""
$now = Get-Date
if ($count -gt 0) { # noone wants a DividedByZeroException 🙂
$elapsed = $now $starttime # how much time has been spent
$average = $elapsed.TotalSeconds / $count # how many seconds per site
$totalSecondsToGo = ($total $count) * $average # seconds left
$span = New-TimeSpan Seconds $totalSecondsToGo # time left
$estimatedCompletion = $now + $span # when it will be complete
$estimation = $estimatedCompletion.ToString() # readable estimation
}
$count++
$percent = 100 * $count / $total # percentage complete
$status = "#{0:d5} of $total. Est:d $estimation. $($site.URL)" -f $count # aggregated status message
Write-Progress Activity "Getting information for " Status $status PercentComplete $percent
$siteWithMoreInfo = Get-SPOSite Identity $site.URL # the actual time consuming operation
$siteWithMoreInfo # return the site with more information
}

I included the comments, and it should be straight forward to follow the logic in the script. Every iteration tries to estimate time, by calculating the average time of time per site, mulplying it by the remainder of the sites and adding it to the current time. The more sites are processed, the more accurate is the estimation.

Optimizing lookups in PowerShell

Have you had a PowerShell script that contains two bigger arrays and you wanted merge the information. It can become quite slow if you need to search for every item from array A through all items in array B. The solution is called a HashTable! It might be not an advanced tip for some, but I was really glad to see a huge improvement, so I decided to share it as a post.

My Array A ($sites) is a list of SharePoint Sites (over 10K of them). For every site I need to get information on the owner (such as UsageLocation). In order to minimize calls to the server I want to reuse the information – in my array B: $users. This array of users has also thousands of entries.

Here is my main (simplified) setup:

$users = # @() array, code ommitted for brevity
$sites = # @() array, code ommitted for brevity
$sitesAndOwners = $sites | ForEach-Object {
[PSCustomObject]@{
Site = $_
Owner = GetUserInfo($_.Owner)
}
}

Traversing the array B for the right item for every entry in array A is slow: Where-Object:

function GetUserInfoSlow($upn) {
$user = $users | Where-Object { $_.UserPrincipalName -eq $upn }
if ($user.Count -eq 0) {
$user = Get-AzureADUser SearchString $upn
$users = $users + $user
}
return $user
}

Using a hashtable is much faster:

$sersHash = @{}
function GetUserInfoFast($upn) {
# we check if there is an entry even if value is null
if ($sersHash.Contains($upn)) {
$user = $sersHash[$upn]
}
else {
$user = Get-AzureADUser SearchString $upn
$sersHash.Add($upn, $user)
}
$user
}

In my example it took hours first. Now it takes seconds. A bonus: here is how you can convert an array to a hash table:

#bonus: convert array to a hash table
$users | ForEach-Object {
$usersHash.Add($_.UserPrincipalName, $_)
}

That’s all I’ve got today. Sharing is caring… And of course, big thanks to my colleague Anton H. for his advise.

Page Diagnostics for SharePoint

While trying to set up a new Home Site, I discovered that there is a tool (browser extension) called Page Diagnostics for SharePoint.

After running this, I tried that command again and it was smart enough to detect the problem the tool discovered.

Also Network Trace is available.

Network trace

Page Diagnostics Tool is defnitely a tool to have in the troubleshooting toolbelt for SharePoint.

Setting up a Home Site

Here is the script:

# Sets up a SharePoint Home Site at Skanska
$tenant = "takana17"
Connect-SPOService https://$tenant-admin.sharepoint.com
$baseUrl = "https://$tenant.sharepoint.com"
# site swap takes 1-2 minutes. be patient
# https://docs.microsoft.com/en-us/powershell/module/sharepoint-online/invoke-spositeswap?view=sharepoint-ps
Invoke-SPOSiteSwap SourceUrl "$baseUrl/sites/futurehomesite" TargetUrl "$baseUrl" ArchiveUrl "$baseUrl/sites/oldroot-deleteit"
# Home Site. Docs: https://aka.ms/homesites, it make take some time
Set-SPOHomeSite HomeSiteUrl $baseUrl
view raw spo-home-site.ps1 hosted with ❤ by GitHub
Daniel Chronlund Cloud Tech Blog

News, tips and thoughts for Microsoft cloud fans

Вула Чăвашла

VulaCV - Чăвашла вулаттаракан сайт

Discovering SharePoint

And going crazy doing it

Bram de Jager - Architect, Speaker, Author

Microsoft 365, SharePoint and Azure

SharePoint Dragons

Nikander & Margriet on SharePoint

Mai Omar Desouki

PFE @ Microsoft

Cameron Dwyer

Office 365, SharePoint, Azure, OnePlace Solutions & Life's Other Little Wonders

paul.tavares

Me and My doings!

Share SharePoint Points !

By Mohit Vashishtha

Jimmy Janlén "Den Scrummande Konsulten"

Erfarenheter, synpunkter och raljerande om Scrum från Jimmy Janlén

Aryan Nava

DevOps, Cloud and Blockchain Consultant

SPJoel

SharePoint for everyone

SharePointRyan

Ryan Dennis is a SharePoint Solution Architect with a passion for SharePoint and PowerShell

SharePoint 2020

The Vision for a Future of Clarity

Aharoni in Unicode

Treacle tarts for great justice

... And All That JS

JavaScript, Web Apps and SharePoint

blksthl

Mostly what I know and share about...