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.
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.
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.:
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.
But this is a side note, this post is automatically detecting new sites, not listing them
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.
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?
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.
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”
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:
# AllowBasic as Admin, perhaps in a separate window