Powershell Script – One Drive Usage Report

Below is customized pwoershell script , which we use to send weekly report of Office365 One-Drive Usage. It helped us having a bird eye view inside email body. It sorts by UsedGb column.

---

# POWERSHELL SCRIPT TO FETCH ONE-DRIVE USGE
# Created by Syed.Jahanzaib / aacable at hotmail dot com
# 31-May-2024
# Create c:\temp folder where all holders will be saved
$HOLDER1 = "C:\Temp\OneDriveStats_1.csv"
$HOLDER2 = "C:\Temp\OneDriveStats_2.txt"
$ADMINMAIL1 = "xyz@xyz.com"
$FROM = "xyz.onedrive.report@xyz.com"
# Use any SMTP Server , we have Domino based local smtp relay server
$SMPTSERVER = "10.10.10.1"
$ONEDRIVEADMINURL = "https://xyz-admin.sharepoint.com"
#Connect to One Drive admin tenant / sjz
Connect-SPOService -Url $ONEDRIVEADMINURL
function Get-TimeStamp1 {
return "[{0:MM/dd/yyyy} {0:HH:mm:ss}]" -f (Get-Date)
}
$SUBJECT = "xyz One-Drive Usage Weekly Customized Report Generation Start Time - $(Get-TimeStamp1)"
echo "$SUBJECT" > $HOLDER2
echo `.` >> $HOLDER2
Write-Host "Getting OneDrive sites..."
$OneDrives = Get-SPOSite -IncludePersonalSite $True -Limit All -Filter "Url -like '-my.sharepoint.com/personal/'"
$Result = @()
ForEach ($OneDrive in $OneDrives) {
$OneDrive = [PSCustomObject]@{
Email = $OneDrive.Owner
QuotaGB = [Math]::Round($OneDrive.StorageQuota / 1024, 3)
UsedGB = [Math]::Round($OneDrive.StorageUsageCurrent / 1024, 3)
PercentUsed = [Math]::Round(($OneDrive.StorageUsageCurrent / $OneDrive.StorageQuota * 100), 3)
LastUsed = $OneDrive.LastContentModifiedDate
}
$Result += $OneDrive
}
# SHOW RESULT IN CMD
# $Result | ft Email,UsedGB,QuotaGB,PercentUsed,LastUsed  -AutoSize |more
# SHOW PERCENTAGE USED ONLY
# $Result | % { Write-Host OneDrive usage of $_.Email is $_.PercentUsed percent}
# Export Result into CSV
$Result | Export-Csv $HOLDER1 -NoTypeInformation
$OneDriveUser = Import-Csv -Path "$HOLDER1"
$SortedOneDriveUser = $OneDriveUser | Sort-Object -Descending -Property { $_ -as [decimal] }
Import-Csv $HOLDER1 | Sort-Object {[int]$_.UsedGB} -Descending  | Ft -AutoSize >> $HOLDER2
echo `.` >> $HOLDER2
function Get-TimeStamp2 {
return "[{0:MM/dd/yyyy} {0:HH:mm:ss}]" -f (Get-Date)
}
echo "End of Script at $(Get-TimeStamp2) ...." >> $HOLDER2
# SEND EMAIL
echo "Sending Email to $ADMINMAIL1 ... using SMTP Server $SMPTSERVER ..."
type $HOLDER2
Send-MailMessage -To $ADMINMAIL1 -From $FROM -Subject "xyz One-Drive  Weekly Customized Usage Report - $(Get-TimeStamp2)" -Body (Get-Content $HOLDER2 | Out-String) -SmtpServer $SMPTSERVER