The new permission in Graph API – Sites.Selected – is a step in the right direction. Since long we have been looking for ways of scoping the accesses to live up to the least privilege principle. It was either nothing or everything. I have tried out the new Sites.Selected permission and here are my findings.
First of all, if you haven’t heard about Sites.Selected, please visit these pages to find out more. I am skipping the introduction, since there are already good resources on that out there.
Once you have your Azure AD App and the admin consent for Graph Sites.Selected, all you need is the Azure AD Application Id and Site Collection Administrator on a particular site. The simplest way is to use PnP.PowerShell:
The only way to the application permissions is PowerShell or Graph, there is no indication on the site.
What about governance
A site collection administrator can grant Read or Write permissions on a site. It gives the desired granularity for application access. But on the other side, there is no way (as of writing) to get all the sites that an Azure AD Application has permissions to.
Which leads me to the biggest weakness of the today’s implementation. Of course, we can traverse through all the sites using powershell and get the summary of all application permissions. The problem is that it can be time consuming in a bigger where you have plenty of sites. Also, it requires that your account that runs the script is a Site Collection Administrator on every site, which is a complete opposite of the granularity goal that Sites.Selected permission tries to achieve.
With that you might end up with several applications that have Write permissions to many sites and you might not have any clue wether it is used or not, who has access to those applications and if they need it.
My wish is that:
There will be an api (graph) or azure cli (or similar) that can list all the sites that an application with Sites.Selected has access to, without me being a Site Collection Admin on every site.
There will be transparency in the user interface, so that users and site owners can see which applications can read and write content on their sites, the same way as we can see the members of a site.
Have you also got a legacy powershell script that loads SharePoint dlls and runs CSOM code directly? It’s quite easy to convert to PnP PowerShell. But if you run out of time and just need to execute the script, then I have a quick tip for you.
First of all, a CSOM script can be recognized by Add-Type commands (or Import-Module) plus the SharePoint dll paths.
The odds are high that you don’t have those directories and files, unless you run it on a SharePoint Server (who would do that at all?) or you have installed the SharePoint SDK.
SharePoint SDK can be downloaded and installed (as suggested here), but why would you want to do that? An easier way is just to locate the files that are distributed with the PnP.PowerShell module, let me show how to do that.
All the dlls are available from the PnP.PowerShell module directory:
So the only thing you need to do is to re-point the path from the original (the “GAC”) folder to the PnP.PowerShell folder. You don’t need to guess the folder. It’s easy.
Thanks to the PowerTip: Find the Path to a PowerShell Module (Scripting Guy) I could find a way to read the information dynamically, so it doesn’t matter where your folder actually is. The fact what version number the module has, what OS you run on, and whether or not you installed it for your user account only or for all users on your computer – allt that has impact on the folder location. So we need to read the right path and then use it in the Add-Type command.
The PnP.PowerShell is built on top of.NET Core and it works cross plattform, that’s better.
If your legacy script does not work with the newer PnP.PowerShell, you might need to install the older PnP PowerShell and adjust the module name in the script above accordingly.
The SharePoint SDK is built on top of .NET Framework (as of my understanding) and it can only be installed on a Windows machine.
The SharePoint SDK requires local administrator rights to be installed. The PnP.PowerShell can be installed for a user without beeing an administrator by adding -Scope CurrentUser (to the Install-Module), which makes the work much smoother.
If you have two or more versions of the PnP.PowerShell module installed, you have to adjust the script a little by loading only the latest version of the module:
$pnpModule=Get-Module PnP.PowerShell –ListAvailable |Sort-Object Version –Descending |Select-Object–First 1
That was a quick tip on how you can use the types from the original CSOM libraries when you don’t have time to convert a script to a PnP code or if there is some functionality that is not covered in PnP yet (not quite sure if there is something you cannot do with PnP that you can do with CSOM).
The good sides of that approach:
it can be a step towards rewriting a legacy script to a newer PnP.PowerShell
the dlls are up-to-date thanks to an easy way to update the PowerShell Module (Update-Module)
it is cross platform, meaning you can execute your legacy script on a linux or on a Mac as well, good for automation!
The python script checks the service status every five minutes and shows it with colors on the unicorn phat.
Since the unicorn phat is just a grid of 8×4 rgb leds, I needed to color code the different service statuses (more on the statuses later in this post). I came up with these color combinations. It doesn’t matter what combinations they are as long as they mean something to you (or as long as you can decode them).
🟩 🟩 🟩 🟩 ServiceOperational
🟩 🟩 🟩 🟨 ServiceRestored
🟪 🟪 🟪 🟪 Investigating
🟩 🟩 🟩 🟪 FalsePositive
⬜️ ⬜️ ⬜️ ⬜️ InformationUnavailable
🟥 🟥 🟥 🟥 ServiceInterruption
🟥 🟥 🟥 🟨 ExtendedRecovery
🟥 🟥 🟨 🟩 ServiceDegradation
🟩 🟩 🟩 🟦 PIRPublished
🟥 🟨 🟨 🟩 RestoringService
There is a list of all possible statuses you can get for Microsoft 365 Services, and it is here:
Install the cli-microsoft365 npm package globally.
sudo npm i -g @pnp/cli-microsoft365
You have to log in, admin consent (if you run this for the first time) and then you can get the status of the Microsoft 365 Services by running:
m365 tenant status list
There are many services in Microsoft 365. I choose the 8 most important ones (from my point of view), because there are only 8 rows on the unicorn phat, you can choose your services and order them as you prefer of course. Beware the spelling and the casing:
Assembling the hardware
I had my raspberry pi zero w, with raspberry pi os already installed. I attached the unicorn phat using solderless pogo pins. I found a little white cardboard box, cut out a rectangular hole for the unicorn phat and glued the raspberry pi with unicorn inside the box. On the front side I put a sticker with the actual service names for every led row. I connected it to the power, ran the script.
Other tips and tricks
The pogo pins were to loose and the leds did not work. I had to shorten the plastic holders a little to tighten the the pins.
Login to cli-microsoft365 as sudo
When I explored the m365 commands, it worked perfectly. My login was cached. Then I needed to run my scripts as sudo, since it requires communication with GPIO pins and the unicorn phat. It didn’t work. The login cache is in different place if you run as sudo. Obvious, when I look at it afterwards, but it took some time to realize that. So, if you are going to do the same, just make sure you log in to m365 as sudo as well, before running the script:
sudo m365 login
This web resource is gold, it shows the pinout and connections to many hats etc:
There is confusion around how the storage is calculated in SharePoint Online. I believe, in SharePoint Online 1 TB is 1024 GB (based on powers of two), although the SI Prefix is for numbers based on powers of 10 (1TB = 1000GB, Wikipedia). In this post I would like to summarize the results of my investigations and I hope Microsoft or the community can confirm or disconfirm this.
First, let me explain why we care about it. The storage in SharePont is limited and we need to keep an eye on it. Especially in our case, where we need to track storage utilization across different parts of the organization/our tenant. The storage in SharePoint is calculated like so:
1 TB + 10GB * E-licensed users
The tricky part, though, is how to convert it into TB correctly.
Why I believe Microsoft treats 1 TB as 1024 GB
First of all, I can see it clearly in my dev tenant with exactly 25 licenses.
That would give 1TB + 10GB*25 = 1,25 TB if it would be based on powers of 10. But it isn’t because the storage I get is 1,24 TB, or 1,244 to be precise.
That means, for every E-license you get 10 GB or 10/1024 TB.
That also means you need more licenses to get the desired storeage. E.g. 10 TB more storage requires 1024 licenses and not 1000, 10 TB = 10240 GB, 10240 GB / 10 = 1024 E-licenses.
Also in OneDrive, the initial space I get, is 1024 GB (or 1TB). If 1TB = 1024GB in OneDrive, why should SPO be different?
Further, the MSDocs page reveals that the 25 TB are 25600 GB (which is exactly the product of 25 and 1024):
One contradictory page, though is the news about storage increase:
The calculations there are based on the decimal system:
Calculation of MB and GB
Just to verify how the storage is calculated in KB, MB and GB, I looked at the Storage of a SharePoint site. Luckily, I can get the storage used in Bytes, MB and GB (from different sources) and compare them to each other.
When I calculate back and forth I can defnitely see, it is multipled/divided by 1024, hence powers of 2:
The values in GB are exactly the same, the Bytes, KB and MB differ a bit due to rounding
Det här är en enkel guide på svenska om hur du kan ladda in data från två eller fler listor i SharePoint och lägga ihop dem till en.
Scenariot är följande. Du har två eller fler sajter i SharePoint Online som har var sin lista (med samma kolumner). Du vill ladda in data från båda och se en aggregerad/summerad version. Alternativet är att ha en delad lista, men ibland (av behörighetskäl eller av behovet för smärre anpassningar av enskilda listor), ligger det i separata listor/sajter.
För enkelhetens skull, har jag följande demouppsättning:
Två enkla listor i samma sajt:
Listorna innehåller två exakt likadana kolumner
Målet är att addera raderna ifrån två listorna till en större lista.
Att läsa in SharePoint-listor in i Power BI är ganska enkelt. Starta Power BI Desktop på din Windows-dator.
Nästa steg är det vikigaste i den här guiden: Kombinera de här två listorna: Append Queries
Den kombinerade datan ligger i “Append1”
Resten är “bara” visualisering. “Bara”, eftersom det hårdaste jobbet (Tranformation, kombinering) är redan bakom oss. Testa olika alternativ.
Ett exempel är Treemap:
Summering och reflektion
Den här guiden visar hur man kombinerar (lägger på) två listor och jobbar med dem som om de vore en lista. Hör av dig om det har varit till hjälp eller om du har frågor eller funderingar.
Själva behovet är verkligt. I SharePoint har det alltid varit en utmaning att samla ihop datan från olika sajter och listor. Vi har använt oss av söken (med Sökcenter och DisplayTemplates) och andra tekniker. Visst vore det enklare att ha det samlat i en central databas eller en lista. Det är dock en viss frihet att låta olika organisatoriska enheter “äga” sina delar (användare kan justera sina vyer, ordna formatering, lägga till extrakolumner och annat trevligt). Aggregeringsbehovet kan nu, med Power BI, lösas på ett relativt smidigt och användarvänligt sätt.
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
These are two workarounds to see documents / list items in a view that exceeds the listview threshold of 5000 items.
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:
Sorting by name in a view
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.