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

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

• https://techcommunity.microsoft.com/t5/SharePoint/PowerShell-script-to-export-SharePoint-Usage-in-CSV-format-used/m-p/357535#M27324
• https://techcommunity.microsoft.com/t5/Microsoft-Stream-Forum/PowerShell-script-to-audit-and-export-Channel-content-details-of/m-p/354832#M3011
• https://techcommunity.microsoft.com/t5/Office-365-Video/PowerShell-script-to-audit-and-export-all-content-details-of/m-p/352594#M830
• https://techcommunity.microsoft.com/t5/Exchange/PowerShell-script-to-export-Exchange-Usage-in-CSV-format-used-to/m-p/356287#M3063

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:

• http://blogs.developpeur.org/fabrice69/archive/2019/04/25/office-365-script-powershell-pour-auditer-l-usage-de-onedrive-for-business-de-votre-tenant.aspx

Source used:

• https://docs.microsoft.com/en-us/onedrive/list-onedrive-urls
• https://gallery.technet.microsoft.com/scriptcenter/OneDrive-for-Business-0cb45614