Generate a report based on allocated storage and current usage of SharePoint online site collection

This is a common requirement often we come across to generate a report on how much storage allocated to each SharePoint online site collection and currently how much it consumed. This helps to in organizing the site collection, allocating storage and planning for the near future growth of the site collection size.

I had explained how to export Title and Url details of all SharePoint Online site collections present your tenant in my previous post Export SharePoint online site collection lists to file using PowerShell.

So you can execute till Connect-SPOService command as per my previous post. After that we would do a small modifications in the "select" to include StorageQuota, StorageQuotaWarningLevel,
StorageUsageCurrent as below.

$siteCollList = Get-SPOSite | select Title,Url,StorageQuota,StorageQuotaWarningLevel,StorageUsageCurrent
$siteCollList > D:\SPOSiteCollExport\SPOSiteCollDetails.txt

After successful execution it would generate the file with all properties mentioned in select.

We can also extend our scripting to export it to CSV so that it can be used in excel to prepare further representation in graph or so. Or even we can generate a html file with some formatting and color coding based on StorageQuota or StorageUsageCurrent etc.

Export SharePoint online site collection lists to file using PowerShell

Sometimes we come across this requirement to provide a list with all SharePoint online site collection details with name and url. This is basically used for report or information purpose.

So we need to extract this information to a file so that can be sent through an email or uploaded to a SharePoint library and shared easily with someone.

To do this we will use SharePoint Online Management Shell. I have mentioned how you can open SharePoint Online Management Shell in this post.

Now open SharePoint Online Management Shell and do the following:

• Type below command replacing the url with your SharePoint admin center url. Please note word -admin in the url, you can place it by typing or copy the url directly from browser by navigating to SharePoint admin center. To know how to navigate to SharePoint admin center you can check here.

          Connect-SPOService -Url https://companyname-admin.sharepoint.com

          

         After hitting enter it will ask for credentials. Provide the credentials and click OK. This user

        must be a member of the tenant’s Global Admin or SharePoint Administrator role.

• Then type below 2 lines of script by replacing the folder path with the one where you want to generate the file "SPOSiteCollDetails.txt"

        $siteCollList = Get-SPOSite | select Title,Url 

        $siteCollList > D:\SPOSiteCollExport\SPOSiteCollDetails.txt

After successful execution it would generate the file with Title and Url that looks like:

In my next post we will see how to generate a report based on allocated storage and current usage of SharePoint online site collection.

List of SharePoint online Time Zone Id

Below are the list of SharePoint online TimeZoneId to corresponding time zone across the world. To set time zone for SharePoint site/site collection programmatically using PowerShell(for parameter -TimeZoneId) or CSOM TimeZoneId is required.

Description	Time Zone ID
(UTC-12:00) International Date Line West	39
(UTC-11:00) Coordinated Universal Time-11	95
(UTC-10:00) Hawaii	15
(UTC-09:00) Alaska	14
(UTC-08:00) Baja California	78
(UTC-08:00) Pacific Time (US and Canada)	13
(UTC-07:00) Arizona	38
(UTC-07:00) Chihuahua, La Paz, Mazatlan	77
(UTC-07:00) Mountain Time (US and Canada)	12
(UTC-06:00) Central America	55
(UTC-06:00) Central Time (US and Canada)	11
(UTC-06:00) Guadalajara, Mexico City, Monterrey	37
(UTC-06:00) Saskatchewan	36
(UTC-05:00) Bogota, Lima, Quito	35
(UTC-05:00) Eastern Time (US and Canada)	10
(UTC-05:00) Indiana (East)	34
(UTC-04:30) Caracas	88
(UTC-04:00) Asuncion	91
(UTC-04:00) Atlantic Time (Canada)	9
(UTC-04:00) Cuiaba	81
(UTC-04:00) Georgetown, La Paz, Manaus, San Juan	33
(UTC-03:30) Newfoundland	28
(UTC-03:00) Brasilia	8
(UTC-03:00) Buenos Aires	85
(UTC-03:00) Cayenne, Fortaleza	32
(UTC-03:00) Greenland	60
(UTC-03:00) Montevideo	90
(UTC-03:00) Salvador	103
(UTC-03:00) Santiago	65
(UTC-02:00) Coordinated Universal Time-02	96
(UTC-02:00) Mid-Atlantic	30
(UTC-01:00) Azores	29
(UTC-01:00) Cabo Verde	53
(UTC) Casablanca	86
(UTC) Coordinated Universal Time	93
(UTC) Dublin, Edinburgh, Lisbon, London	2
(UTC) Monrovia, Reykjavik	31
(UTC+01:00) Amsterdam, Berlin, Bern, Rome, Stockholm, Vienna	4
(UTC+01:00) Belgrade, Bratislava, Budapest, Ljubljana, Prague	6
(UTC+01:00) Brussels, Copenhagen, Madrid, Paris	3
(UTC+01:00) Sarajevo, Skopje, Warsaw, Zagreb	57
(UTC+01:00) West Central Africa	69
(UTC+01:00) Windhoek	83
(UTC+02:00) Amman	79
(UTC+02:00) Athens, Bucharest	5
(UTC+02:00) Beirut	80
(UTC+02:00) Cairo	49
(UTC+02:00) Damascus	98
(UTC+02:00) Harare, Pretoria	50
(UTC+02:00) Helsinki, Kyiv, Riga, Sofia, Tallinn, Vilnius	59
(UTC+02:00) Jerusalem	27
(UTC+02:00) Minsk (old)	7
(UTC+02:00) E. Europe	104
(UTC+02:00) Kaliningrad (RTZ 1)	100
(UTC+03:00) Baghdad	26
(UTC+03:00) Istanbul	101
(UTC+03:00) Kuwait, Riyadh	74
(UTC+03:00) Minsk	109
(UTC+03:00) Moscow, St. Petersburg, Volgograd (RTZ 2)	51
(UTC+03:00) Nairobi	56
(UTC+03:30) Tehran	25
(UTC+04:00) Abu Dhabi, Muscat	24
(UTC+04:00) Baku	54
(UTC+04:00) Izhevsk, Samara (RTZ 3)	106
(UTC+04:00) Port Louis	89
(UTC+04:00) Tbilisi	82
(UTC+04:00) Yerevan	84
(UTC+04:30) Kabul	48
(UTC+05:00) Ekaterinburg (RTZ 4)	58
(UTC+05:00) Islamabad, Karachi	87
(UTC+05:00) Tashkent	47
(UTC+05:30) Chennai, Kolkata, Mumbai, New Delhi	23
(UTC+05:30) Sri Jayawardenepura	66
(UTC+05:45) Kathmandu	62
(UTC+06:00) Astana	71
(UTC+06:00) Dhaka	102
(UTC+06:00) Novosibirsk (RTZ 5)	46
(UTC+06:30) Yangon (Rangoon)	61
(UTC+07:00) Bangkok, Hanoi, Jakarta	22
(UTC+07:00) Krasnoyarsk (RTZ 6)	64
(UTC+08:00) Beijing, Chongqing, Hong Kong, Urumqi	45
(UTC+08:00) Irkutsk (RTZ 7)	63
(UTC+08:00) Kuala Lumpur, Singapore	21
(UTC+08:00) Perth	73
(UTC+08:00) Taipei	75
(UTC+08:00) Ulaanbaatar	94
(UTC+09:00) Osaka, Sapporo, Tokyo	20
(UTC+09:00) Seoul	72
(UTC+09:00) Yakutsk (RTZ 8)	70
(UTC+09:30) Adelaide	19
(UTC+09:30) Darwin	44
(UTC+10:00) Brisbane	18
(UTC+10:00) Canberra, Melbourne, Sydney	76
(UTC+10:00) Guam, Port Moresby	43
(UTC+10:00) Hobart	42
(UTC+10:00) Magadan	99
(UTC+10:00) Vladivostok, Magadan (RTZ 9)	68
(UTC+11:00) Chokurdakh (RTZ 10)	107
(UTC+11:00) Solomon Is., New Caledonia	41
(UTC+12:00) Anadyr, Petropavlovsk-Kamchatsky (RTZ 11)	108
(UTC+12:00) Auckland, Wellington	17
(UTC+12:00) Coordinated Universal Time+12	97
(UTC+12:00) Fiji	40
(UTC+12:00) Petropavlovsk-Kamchatsky - Old	92
(UTC+13:00) Nuku'alofa	67
(UTC+13:00) Samoa	16

Create SharePoint online site collection using PowerShell

Before we start with any PowerShell scripting for SharePoint online it needs to be ensured that SharePoint Online Management Shell is installed. If not we can download and install SharePoint Online Management Shell from here.

System requirements:

• Supported Operating System: Windows 10 , Windows 7 Service Pack 1, Windows 8, Windows Server 2008 R2 SP1, Windows Server 2008 Service Pack 2, Windows Server 2012, Windows Server 2016
• PowerShell 3.0 installed.

Now go "Start" menu and search for "SharePoint Online Management Shell" and open it.

• Type below command replacing the url with your SharePoint admin center url. Please note word -admin in the url, you can place it by typing or copy the url directly from browser by navigating to SharePoint admin center. To know how to navigate to SharePoint admin center you can check here.

          Connect-SPOService -Url https://companyname-admin.sharepoint.com

          

         After hitting enter it will ask for credentials. Provide the credentials and click OK. This user

        must be a member of the tenant’s Global Admin or SharePoint Administrator role.

• Then type below command by placing correct value for parameters -Title, -Template, -TimeZoneId and hit enter.

        New-SPOSite -Url "https://companyname.sharepoint.com/sites/mydailysp" -Owner 

        "administrator@companyname.onmicrosoft.com" -Title "My Daily SharePoint PS" -Template

        "STS#0" -TimeZoneId 23

        STS#0 is template name for team site. For other templates title and name are listed here.

        TimeZoneId 23 is for UTC+5:30 Indian time. For other SharePoint online TimeZoneId you 

        can check here.

Once execution successfully completed, site collection would be listed in the SharePoint admin center and here you go. Cheers !!!

List of SharePoint online template name

Below are the list of OOTB templates for SharePoint online. To create a SharePoint site/site collection programmatically using PowerShell(for parameter -Template) or CSOM, template name needs to be provided.

Title	Name
Team Site	STS#0
Blog	BLOG#0
Document Center	BDR#0
Developer Site	DEV#0
Records Center	OFFILE#1
Team Site - SharePoint Online configuration	EHS#1
Business Intelligence Center	BICenterSite#0
Enterprise Search Center	SRCHCEN#0
Publishing Portal	BLANKINTERNETCONTAINER#0
Enterprise Wiki	ENTERWIKI#0
Project Site	PROJECTSITE#0
Product Catalog	PRODUCTCATALOG#0
Community Site	COMMUNITY#0
Community Portal	COMMUNITYPORTAL#0
Basic Search Center	SRCHCENTERLITE#0
Visio Process Repository	visprus#0

Create site collection using SharePoint admin center in Office 365

Step 1:

Using tenant administration account login to Microsoft portal at: https://login.microsoftonline.com

Step 2:

You would be able to see different tiles as below. This might look different in your case based on license and your role in the tenant. Go to Office 365 admin center by clicking on the "Admin" tiles as below.











You can also navigate to the admin center using the app launcher and clicking on "Admin" tiles.

Step 3:
Now you need to navigate to SharePoint admin center by mouse hovering on the "Admin" tiles in left navigation menu and clicking on the SharePoint link as shown below.

Step 4:
It would take you to the SharePoint admin center and by default site collection tab would be selected.
Go to the drop-down menu in "New" and click "Private Site Collection".

Step 5:
This would bring new site collection window and you have to provide:

• Title: It's the title of your site collection that gives. In my case I have provided "My Daily SharePoint Team Site".

• Web Site Address: This defines the Url of your site collection. I have typed "mydailysharepoint". So it would look like: https://somename.sharepoint.com/sites/mydailysharepoint

• Template: Template is going to tell SharePoint what are the features and functionality needs to be included in the site. For example, if you are creating it for your team collaboration you would select "Team Site" template, if you are creating it for posting some thoughts, ideas, views, experience then you would select "Blog" template. Like this SharePoint provides number of templates and you can select as per your need.

• Time Zone: Time zone that is going to be used by this site. I have selected UTC+5:30 for maintain Indian standard time.

• Administrator: Administrator is the user who is having supreme power to change any settings, set permission and access, add restrictions, enable disable feature etc. for this site collection. So you have to provide the user account who needs to be the administrator.

• Server Resource Quota: The main purpose of resource quotas is to limit the risk that sandboxed custom code can have on available resources on a site collection. By default it's 300, if you are sure that no sandboxed code would be running on this site collection, it can be set to 0.

Step 6:
Once everything is filled up, you can click on the "OK" button. SharePoint would take a short while to create and it would be listed under site collections. Then click on your specific site collection Url, it would bring a popup with the details and you can click on the Url listed against "Web Site Address".

There it is, your site is ready !!!

We can create a site collection using PowerShell as well. In my next post we will see how to create a SharePoint online site collection using PowerShell.

About SharePoint online

SharePoint Online is one of the services in Office 365. It's is a cloud based solution. SharePoint Server which is a on-premise version also provides similar solution, but for this one has to purchase and maintain his own hardware, servers to install all required operating system, software etc. At the same time you have to hire local IT expert to do required setup, configuration and for future maintenance. But in case of SharePoint online you don't need to think about all those, Microsoft would take care.

Some years back while SharePoint online was not introduced, it was not possible to adopt SharePoint easily for small and mid level companies due to higher cost involved in purchase and setup. With SharePoint online, Microsoft takes care of those heavy lifting and sells SharePoint platform as service. So as a Microsoft customer you can connect to this managed version of SharePoint over internet and use for developing your business solution in SharePoint.

Why SharePoint Online:

Work culture has changed drastically over the years and technology now enables to work faster, more productively and with more flexibility than ever. There was time when people used to write, type, put in envelope, stamp and post to specific address. Now we type attach and send emails in clicks.

When multiple people work to achieve a common goal as a team it boosts productivity as well as brings all creativity under a single umbrella. This also helps the manager to manage and gives more ownership to groups of the work that they perform. As end result, we always get the best output. These days most of the companies works forming work team only. 

A team may include people from across the globes from different time zones. Team members are now a days often on the road, travelling across the region or might be accessing office network from different continent. This leads to collaborate virtually and it has even better benefits when team works successfully from remote locations. Because there is a less need of centralized office space resulting cost cut down on office lease. It would save daily 1-2 hours travel to office if working from home. Also saving fuel and CO₂ released by your car making earth greener. In a greater context if we see this there is a bigger impact.

All this are good, but now, this would create a big question mark in our mind if this kind of work flexibility go-anywhere, do-anything is allowed how team focus would be maintained and primary assignment would be finished without failing the deadlines !  How you would organize work, relevant documents, meet together in same space and time, keep track of the task progress !

Yes, SharePoint online is the answer to your questions. You would be able to create a shared team space, always available online, document libraries, share assets, assign task, publish announcement, initiate discussion, collaborate world wide and much more.

In my next post I will cover how to create site collection using SharePoint admin center in Office 365.