SharePoint Online forecast storage requirements

Background

Office 365 users get quite a bit of storage on SharePoint Online for content, be it files, metadata, etc. But still to manage the storage and forecast as to when additional storage has to be added becomes a challenge with very limited analytics available in SharePoint Online. Since adding more storage cost money so adding before you actually require or a bit too late would not be ideal.

SharePoint Online provides two ways to track the storage one from the admin center and other one from within the site collection using Storage metrics.

In SharePoint Online tenant’s admin center it gives us a bar matrix with details about the total storage, used storage and storage available. Here customers can manage the total amount of space allocated to each site collection, the total amount of space utilized and available.

To check Storage Metrics to the Site collection under site settings we can find the Storage metrics link, storage metrics provides us with the space utilization breakdown by each sub site, library and list in that Site Collection.

As shown in the image below sub sites, libraries, lists etc. are listed with size, % of parent. This can help in finding resources which are consuming most resources.

There are some third party storage monitoring utilities available to connect to SharePoint online to determine usage patterns and trends.

But if we need to forecast storage usage, find out the site collection which is growing the most over a period of time in a tenant, with more than hundreds of site collection then it becomes a challenge to keep that track.

Solution

We can use PowerShell script and get the storage information from the tenant, store it in a SharePoint list on a periodic basis. Once we have this data available then on this data Power BI can be used to build a report which shows the growth % for each site collection over a period and get some insight to the storage.

By using PowerShell, we get heaps of information from SharePoint tenant using the command Get-SPOSite but we will here use it to get site collection list, storage information.

Get-SPOSite -Detailed -Limit All | select *

Then once the information is retrieved from SPO, use PowerShell to iterate over the data and save the relevant data to a SharePoint list.

SharePoint list Columns: Site Name/Title, Site Url, Storage, Report Run Date.

Data in SharePoint list can be filtered, grouped and sorted in list views to make it more usable for business to analyze and predict storage growth and requirements.

In order to get detailed analytics on the data, use Power BI reports which can help in putting data together in form of reports and predict the growth percentage for each site collection. PowerBI provides many connectors one of them is for SharePoint Online to easily connect with lists. To set up connector in Power BI check this Kloud Blog.

Below is the Power BI report that was built on the SharePoint list data which was pulled in using PowerShell, Data in the report below is sorted by sites with maximum storage increase within a selected interval.

Using Power BI more smarts can be added like filters, graphs and make the data more usable.

Report details

  • x-axis : List of site collection
  • y-axis : Storage value’s in MB

As we hover the curser over the graph it shows the Max and Min value for the storage over the selected period of time. The graph captures the sites in decreasing order of growth value by storage.

Tabular view for the report.

PowerShell script can be scheduled to run periodically and gather the data by weekly or fortnightly we can calculate the average weekly or fortnightly storage requirement for the tenant.

Secondly data captured is used to forecast weekly storage requirement. From SPO tenant we get details about total storage, used storage, storage available.

Average weekly storage requirement can be taken from list view grouped by week and aggregate of the storage as shown below in the image.

Once we have the above information then we get the average requirement for week is 24,349 MB i.e. 24 GB (difference in storage growth in two weeks) in this case, this can be used to predict the weekly storage requirement which can help forecast as to when the tenant storage is going to run out and additional storage would be required.

SharePoint Online external user access error “User Not in directory”

Background

Organization wants to share their SharePoint online site collection, documents and collaborate with external partners, vendors or customers. By default site collection are shared to internal user’s only. But this can be extended to authenticated external users or also with limited sharing to anonymous users. External users do not have a license to office 365 subscription, they are limited to basic collaboration tasks.

I had recently enabled external access for site collection on the SPO tenant only to selected domains and authenticated external users. External sharing in SharePoint online works well in most scenario’s but there are few issues which pop up while enabling access for external user’s and with limited error details it becomes a bit challenging to understand the cause.

Problem

Error: “User Not in directory”

Error message which user’s get as they try to login to external SharePoint site is quiet generic ” User not in directory “it is not that descriptive and did not points to the cause for the issue.

Solution

To troubleshoot the access for the user. Clear browser cache or open Incognito or Private session, then try below steps.

First check to make sure the account which is used to accept the email invitation to the site is the same account which is being used to login later.

In Office 365 login screen if below screen is popping up prompting “Which account do you want to use?” when you sign in, it means that two different accounts have been configured with Microsoft using the same email address:

A “Work or school” account, which probably was created by your IT department

A “personal” account, which you probably created later on by the user.   

            

Personal account can be renamed which means using a different email address to sign in to it. To fix it follow this KB article (https://support.microsoft.com/en-us/help/11545/microsoft-account-rename-your-personal-account)

If external user’s accepted the invite using the personal account and later on try to connect by selecting the work account and is getting the error “User not in directory”. This is the most common cause for the error. Make sure the user is using the same account to accept the invite and log-on to the site.

Secondly if the account used for accepting the invite and login are same and still the error screen pop’s up, then user account has to be set up again, but before we need to do the clean up on the existing references for user profile and remove the user from SharePoint and then send fresh invite. To remove the user and all references follow the below steps.

External users are managed from a site-collection–by–site-collection basis. An external user account must be removed from each site collection that the user was granted access to.

Browse to each site collection that the user previously had access to, and then follow below steps:

  • In the site collection, edit the URL in the browser by appending the following string to the site address:
    _layouts/15/people.aspx/membershipGroupId=0

  • Select the user from the list and Click Delete. Then once user is removed next

  • Start the SharePoint Online Management Shell.
  • Type the following cmdlet:
    $cred = Get-Credential
    In the Windows PowerShell Credential required dialog box, type your site collection admin account and password, and then click OK.
  • Connect to SharePoint Online, and then type the following cmdlet:
    Connect-SPOService -Url https://tenant-admin.sharepoint.com -Credential $cred
  • Remove the user from each site collection by using the following cmdlet:
    $ExtUser = Get-SPOExternalUser -filter someone@example.com
  • Type the following cmdlet:
    Remove-SPOExternalUser -UniqueIDs @($ExtUser.UniqueId)

Then we can add back the user and resend the invite. It should fix up the issue.

Last thing to check is that user has a proper role assigned to user account under user profile in the office portal.

  • To check the role assigned to the user, Go to office 365 admin center.
  • Sign in with global administrator’s account.
  • Check the external user in Users>Active users, then, check the roles of the external user and change it to User (no admin access).