Office 365 – Monitor site collection storage (PS1 daily email)
I wrote the below PowerShell script to gather site collection storage data for any Office 365 tenant. This PS1 could be scheduled daily for a quick email to keep an eye on sites approaching storage limits.
Hope this helps! 
# Plugin
Import-Module Microsoft.Online.SharePoint.PowerShell -ErrorAction SilentlyContinue
# Config -- get URL from "Tenant Admin" page in Office 365
$url = "https://company-admin.sharepoint.com"
$user = "admin@company.com"
$smtp = "mailrelay"
# Connect
Connect-SPOService -url $url -credential $user
# Gather data
$sites = Get-SPOSite -Detailed
$coll = @()
foreach ($s in $sites) {
$curr = $s.StorageUsageCurrent
$quota = $s.StorageQuota
if ($quota -eq 0) {
$prct = [math]::Round($curr, 2) * 100
} else {
$prct = [math]::Round($curr/$quota, 2) * 100
}
$surl = $s.Url
$coll += New-Object -TypeName PSObject -Prop (@{'curr'=$curr;'quota'=$quota;'prct'=$prct;'surl'=$surl})
}
$coll = $coll | sort prct -Desc
# Format HTML
$html = ""
foreach ($c in $coll) {
$row = "" -f $c.surl, $c.curr, $c.quota, $c.prct
$html += $row
}
$html += "
| URL | Current (MB) | Quota (MB) | Percent | |
| {0} | {1} | {2} | {3} % |
|
Thanks, Jeff. This works well when I have an SMTP server available, but what if I don’t? Is there another way I can send the email?
Excellent question! A few ideas come to mind …
1) $html|Out-File to write HTML locally.
2) Add SPList item with SPDesigner workflow to send email from server backend. Bonus = historical “growth curve” list.
3) Secure SMTP over TLS. GMail, Hotmail, and others enable desktop mail clients (i.e. Outlook) with special server names and ports. Maybe that could be used here, haven’t tested.
Cool, thanks. Seems obvious now. 🙂
I had an issue with a divideByZero when the quota has not been set and wanted to parameterize this, so I made some minor updates and posted on GitHub: https://github.com/skaggej/powershell/blob/master/SPO-GetSiteCollectionStorageInfo.ps1.
Thanks! I updated quota block to have if(){}else{} now for better zero handling. Awesome stuff to have a code jam session. =)
Awesome Script Jeff.
I am having issues in Powershelgl with this part: $row = “{0}{1}{2}{3} % ” -f $c.surl, $c.curr, $c.quota, $c.pact
Getting the following error: Any Ideas? I was able to work around it some by removing the div tags, but i really like the percentage that shows on the report.
At line:8 char:30
+ $row = “{0}{1}</ …
+ ~
Unexpected token '{' in expression or statement.
At line:8 char:34
+ $row = "{0}{1}</ …
+ ~
You must provide a value expression on the right-hand side of the '/' operator.
At line:8 char:34
+ $row = "{0}{1}{0}{1}{2}{3}
%<div style="background-color:' in expression or statement.
At line:2 char:1
+ {
+ ~
Missing closing '}' in statement block.
+ CategoryInfo : ParserError: (:) [], ParentContainsErrorRecordEx
ception
+ FullyQualifiedErrorId : UnexpectedToken
Thanks Jose! Looks like you need to escape out the PowerShell double quote for “”background-color”” Give this a whirl …
$row = “{0}{1}{2}{3} % ” -f $c.surl, $c.curr, $c.quota, $c.pact
Thanks Jeff for the lightning response… 🙂
One last question “for now” he he
I see after the you have a I don’t see the corresponding for the at the front of the row. Should this be changed to ?
sure thing! thanks for helping proofread and double check HTML. I updated the original blog post with (1) double quotes on “”background-color”” and (2) ending correctly for each row.
Jeff,
I did a little more troubleshooting on the scipt and I made the following changes on mine version.
Original: $row = “{0}{1}{2}{3} % ” -f $c.surl, $c.curr, $c.quota, $c.prct
New Version: $row = “{0}{1}{2}{3} % ” -f $c.surl, $c.curr, $c.quota, $c.prct
I removed the quotes around the href section as it was still causing issues in running without an error, Also I removed an extra at the end of the row as it was one too many. I compared it vs. the header section of the table
My last piece that is weird for me is that I do not get a row filled in color depending on the {3} variable properly. You must have something else in your code that does that right as per the screenshot you added in your original blog post. I tried to replace the with {3}% and it filled in but then I got the value in the filled in section and that is not my goal. One thing i did is change the color from Blue to lime so as to indicate it is “good” if above a certain threshold.
here is what I did that changes the colors depending on how much of the quota is consumed.
$threshold = 70
If($c.prct -gt $threshold)
{
$row = “{0}{1}{2}{3} % ” -f $c.surl, $c.curr, $c.quota, $c.prct
#Logic to send an email or create a service ticket here to the Engineers to add space.
}
Else
{
$row = “{0}{1}{2}{3} % ” -f $c.surl, $c.curr, $c.quota, $c.prct
}
$html += $row
Excellent coding Jose! Really appreciate the testing here to improve. I believe needs a “width” property for the percentage bar to display. Check out line 31 now. I also fixed up to match your PowerShell.