# Connect to SharePoint Online
Connect-SPOService -Url "https://xxxxxx-admin.sharepoint.com"
# Connect to Microsoft Graph (for department filtering)
Connect-MgGraph -Scopes "User.Read.All"
# Get all users where department contains "*"
$UsersInLangDept = Get-MgUser -All -Property Id, UserPrincipalName, Department |
Where-Object { $_.Department -like "*XYZ*" }
# Get their OneDrive sites
$UsersInLangDept | ForEach-Object {
# Convert UPN to correct URL format
$OneDriveUrl = "https://xxxxx-my.sharepoint.com/personal/$($_.UserPrincipalName.Replace("@","_").Replace(".","_"))"
# Get OneDrive site (silently skip errors if no site exists)
Get-SPOSite -Identity $OneDriveUrl -ErrorAction SilentlyContinue |
Select-Object Title, Owner, Department, StorageUsageCurrent
} | Export-Csv "D:\temp\OneDriveUsage_XYZ.csv" -NoTypeInformation
Write-Host "Exported to D:\temp\OneDriveUsage_XYZ.csv"