Home

PowerShell script to export OneDrive Usage in CSV format used to Audit an Office 365 Tenant

Highlighted
Fabrice Romelard
MVP

After the previous scripts published to audit an Office 365 Tenant:

This script will focus the OneDrive for Business case and reuse a part of the previous SharePoint script.

 

[string]$username = "Admin@yourtenant.onmicrosoft.com"
[string]$PwdTXTPath = "C:\SECUREDPWD\ExportedPWD-$($username).txt"

[string]$ReportPath = ".\Reports\"
[string]$TenantUrl = "https://YourTenant-admin.sharepoint.com"

function Load-DLLandAssemblies
{
	[string]$defaultDLLPath = ""

	# Load assemblies to PowerShell session 

	$defaultDLLPath = "C:\Program Files\SharePoint Online Management Shell\Microsoft.Online.SharePoint.PowerShell\Microsoft.SharePoint.Client.dll"
	[System.Reflection.Assembly]::LoadFile($defaultDLLPath)

	$defaultDLLPath = "C:\Program Files\SharePoint Online Management Shell\Microsoft.Online.SharePoint.PowerShell\Microsoft.SharePoint.Client.Runtime.dll"
	[System.Reflection.Assembly]::LoadFile($defaultDLLPath)

	$defaultDLLPath = "C:\Program Files\SharePoint Online Management Shell\Microsoft.Online.SharePoint.PowerShell\Microsoft.Online.SharePoint.Client.Tenant.dll"
	[System.Reflection.Assembly]::LoadFile($defaultDLLPath)
}

cls
Write-Host " ---------------------------------------------- "
Load-DLLandAssemblies
Write-Host " ---------------------------------------------- "

$secureStringPwd = ConvertTo-SecureString -string (Get-Content $PwdTXTPath)
$adminCreds = New-Object System.Management.Automation.PSCredential $username, $secureStringPwd
#Connect-SPOService -Url $TenantUrl -credential $adminCreds -ErrorAction SilentlyContinue -ErrorVariable Err

Connect-SPOService -Url $TenantUrl -ErrorAction SilentlyContinue -ErrorVariable Err

#Retrieve all site collection infos
#$sitesInfo = Get-SPOSite -IncludePersonalSite $true -Limit 100 -Filter "Url -like '-my.sharepoint.com/personal/" | Sort-Object -Property url | Select *

$sitesInfo = Get-SPOSite -IncludePersonalSite $true -Limit All -Filter "Url -like '-my.sharepoint.com/personal/" | Sort-Object -Property url | Select *

[int]$i = 1;
$data = @()

Write-Host "--------------------------------------------------------------------------------------------"
#Retrieve and print all sites
foreach ($site in $sitesInfo)
{
	Write-Host "SiteColl Number:", $i, "- of:", $sitesInfo.Count;
    $i += 1;
		
	Write-Host "SPO Site collection:", $site.Url, "- Title:", $site.Title
	Write-Host "   => Creation Date:", $RootSiteCreatedDate, "- LastItemModifiedDate", $site.LastContentModifiedDate
	Write-Host "   => External Sharing:", $site.SharingCapability
	Write-Host "   => Site Template Used:", $site.Template
	Write-Host "   => Storage Quota:", $site.StorageQuota
	Write-Host "   => Storage used:", $site.StorageUsageCurrent
	Write-Host "   => Storage Warning Level:", $site.StorageQuotaWarningLevel
	Write-Host "   => Resource Quota:", $site.ResourceQuota, "- Resource used:", $site.ResourceUsageCurrent

	$SuborRootSite = "RootSite"
	$data += @(
		[pscustomobject]@{
		UPN = $site.Owner
		SiteCollectionURL = $site.Url
		SiteName = $site.Title
		WebTemplate = $site.Template
		LastItemModifiedDate = $site.LastContentModifiedDate
		ExternalSharingCapability = $site.SharingCapability
		StorageQuotaMB = $site.StorageQuota
		StorageUsageCurrentMB = $site.StorageUsageCurrent
		}
	)
} 

#Write-Host $data

$datestring = (get-date).ToString("yyyyMMdd-hhmm")
$fileName = Join-Path -Path $ReportPath  -ChildPath $("O365-OneDriveDetails_"+ $datestring + ".csv")
	
Write-host " -----------------------------------------" -ForegroundColor Green
Write-Host (" >>> writing to file {0}" -f $fileName) -ForegroundColor Green
$data | Export-csv $fileName -NoTypeInformation -enc utf8 
Write-host " -----------------------------------------" -ForegroundColor Green

 

You can adapt that script as you need, based on your own requirements

 

Fabrice Romelard

 

French version:

Source used: