每当用户的OneDrive超过95%的存储配额时，我都会收到电子邮件，以了解存储需求。我已经完成了第一部分(获取列表，计算使用百分比，并导出为.xlsx(，并试图通过Power Automate完成有条件的电子邮件(如果用户使用了95%以上，请给自己发电子邮件(，但似乎无法完成。我仍在学习编码，所以这将是一个很大的帮助！
我当前的代码：
$adminUPN=""
$orgName=""
$userCredential = Get-Credential -UserName $adminUPN
Connect-SPOService -Url https://$orgName-admin.sharepoint.com -Credential $userCredential
#Get a list of OneDrive for Business sites in the tenant sorted by the biggest consumer of quota
$ODFBSites = Get-SPOSite -IncludePersonalSite $True -Limit All -Filter "Url -like '-my.sharepoint.com/personal/'" | Select Owner, Title, URL, StorageQuota, StorageUsageCurrent | Sort
StorageUsageCurrent -Desc
$TotalODFBGBUsed = [Math]::Round(($ODFBSites.StorageUsageCurrent | Measure-Object -Sum).Sum /1024,2)
$Report = [System.Collections.Generic.List[Object]]::new()
ForEach ($Site in $ODFBSites) {
$ReportLine = [PSCustomObject]@{
Owner = $Site.Title
Email = $Site.Owner
PercentUsed = [Math]::Round(($Site.StorageUsageCurrent/$Site.StorageQuota * 100),4)
QuotaGB = [Math]::Round($Site.StorageQuota/1024,2)
UsedGB = [Math]::Round($Site.StorageUsageCurrent/1024,4)}
$Report.Add($ReportLine) }
$Report | Export-Excel C:usersxxxxdesktopOneDriveUsage.xlsx
如有任何协助，我们将不胜感激！
您可以使用相同的$Report数组向自己发送一封包含超过阈值的用户表的电子邮件：
$over95 = @($Report | Where-Object { $_.PercentUsed -gt 95 })
if ($over95.Count) {
# create a Here-String template to use for mailing yourself
$mailTemplate = @"
<html><head>
<style>
body, table {font-family: sans-serif; font-size: 10pt; color: #000000;}
table {border: 1px solid black; border-collapse: collapse;}
th {border: 1px solid black; background: #dddddd; padding: 3px;}
td {border: 1px solid black; padding: 3px;}
</style>
</head><body>
These users have more than 95% of storage used.<br />
@@TABLE@@
<br />
</body></html>
"@
# create a nice HTML table from the data in $over95
$table = ($over95 | ConvertTo-Html -As Table -Fragment) -join [environment]::NewLine
# create a Hashtable for splatting the parameters to the Send-MailMessage cmdlet
$mailParams = @{
To = 'YOU@yourdomain.com'
From = 'YOU@yourdomain.com'
SmtpServer = 'smtp.yourdomain.com'
Subject = 'Users that exceed 95% storage in OneDrive'
Body = $mailTemplate -replace '@@TABLE@@', $table
BodyAsHtml = $true
# more parameters can go here
}
# send the email
Send-MailMessage @mailParams
}