O365租户报表脚本性能优化求助：大租户速度及磁盘IO问题

架构老林

2026-8-18

O365大型租户PowerShell报表脚本优化建议

我开发了一款适用于多租户合并迁移场景的PowerShell脚本，可从O365租户生成包含用户、组等多类数据集的CSV文件。但在10000+用户的大型租户中运行时，存在耗时过长、磁盘IO占用过高的问题，求优化建议。脚本代码如下：

# PS 7.2 Compatible

# Module Installations (required once)
Install-Module MicrosoftTeams
Install-Module ExchangeOnlineManagement
Install-Module Microsoft.Online.SharePoint.PowerShell

# Module Imports (required everytime a new terminal is ran)
Import-Module ExchangeOnlineManagement -UseWindowsPowerShell
Import-Module MicrosoftTeams
Import-Module Microsoft.Online.SharePoint.PowerShell -UseWindowsPowerShell

# Configurable Vars
$tenantName = "M365x51125269"
$OutputFolder = "C:\Users\admin\Documents\Scripts\O365 Reporting\Output"

# Connecting to Services
Connect-MgGraph -Scopes DeviceManagementApps.Read.All, DeviceManagementApps.ReadWrite.All, DeviceManagementManagedDevices.Read.All, DeviceManagementManagedDevices.ReadWrite.All, DeviceManagementServiceConfig.Read.All, DeviceManagementServiceConfig.ReadWrite.All, Directory.Read.All, Directory.ReadWrite.All, User.Read.All, User.ReadBasic.All, User.ReadWrite.All
Connect-ExchangeOnline 
Connect-SPOService -Url https://$tenantName-admin.sharepoint.com

# Generate a list of all users with the given properties in a variable
$users = Get-MgUser -Property 'accountEnabled, userPrincipalName, displayName, givenName, surname, department,id' -All
$OneDriveUsage = Get-SPOSite -IncludePersonalSite $true -Limit all -Filter "Url -like '-my.sharepoint.com/personal/'"
$MailboxInfo = Get-EXOMailbox

# Iterate through the variable to pull individual details out
foreach ($user in $users) {

    # License 
    $license = Get-MgUserLicenseDetail -UserId $user.Id

    $CurrentOneDrive = $OneDriveUsage| Where-Object{$_.Owner -eq $user.UserPrincipalName}

    $CurrentMailboxInfo = $MailboxInfo | Where-Object{$_.PrimarySmtpAddress -eq $user.UserPrincipalName}
    $sortedArray = ($CurrentMailboxInfo.EmailAddresses | Select-String '(?<=smtp:)\S+(?!=\S)' -AllMatches).Matches.Value
    $stringSMTP = $sortedArray -join ";"
    
    [PSCustomObject]@{
        'Enabled Credentials' = $user.AccountEnabled        
        'Unique ID' = $user.UserPrincipalName
        'Display Name' = $user.DisplayName
        'Assigned Products' = $license.SkuPartNumber -join " ; "
        'First Name' = $user.GivenName
        'Last Name' = $user.Surname
        'Department' = $user.Department
        'Proxy Addresses' = $stringSMTP
        'OneDrive Storage Usage (MB)' = $CurrentOneDrive.StorageUsageCurrent
    } | Export-Csv -path "$OutputFolder\UserInfo.csv" -Append -NoTypeInformation
}   

# Generate list of all sites in a variable
$siteList = Get-SPOSite -Limit ALL
# Iterate through the variable to pull individual details out
foreach($site in $siteList){
    # Checking the value result to determine a connection
    if ($site.IsTeamsConnected){
        $connectionWrite = 'TRUE'
    }
    else{
        $connectionWrite = 'FALSE'
    }
    # Generate a custom object with our desired properties for export
    [PSCustomObject]@{
        'Unique ID' = $site.Url
        'Display Name' = $site.Title
        'Teams Connected' = $connectionWrite
        'Sharepoint Storage (MB)' = $site.StorageUsageCurrent
        'SharePoint Last Activity Date' = $site.LastContentModifiedDate
    } | Export-Csv -path "$OutputFolder\Sites.csv" -Append -NoTypeInformation
}

$teamList = Get-Team

foreach($team in $teamlist){

    $teamInfo = $team
    $teamReport = Import-CSV "$InputFolder\TeamReport.csv"
    $teamDetails = $teamReport | Where-Object{$_."Team Name" -eq $team.DisplayName}

    $Sites = Get-SPOSite | Where-Object{$_.Title -eq $team.DisplayName}


    [PSCustomObject]@{
        'Display Name' = $teamInfo.DisplayName
        'Teams Last Activity Date' = $teamDetails."Last Activity Date"
        'Sharepoint Storage (MB)' = $Sites.StorageUsageCurrent
    } | Export-Csv -path "$OutputFolder\TeamReport.csv" -Append -NoTypeInformation
}

$Groups = Get-MgGroup -Property "Members" -All
foreach($group in $Groups){
    if ($group.SecurityEnabled -And -not $group.MailEnabled){
        $members = Get-MgGroupMember -GroupId $group.Id
        [PSCustomObject]@{
            'Display Name' = $group.DisplayName
            'Members' = $members.AdditionalProperties.displayName -join ";"
        } | Export-Csv -path "$OutputFolder\SecurityGroups.csv" -Append -NoTypeInformation
    }
    continue
}

$Groups = Get-MgGroup -Property "Members" -All
foreach($group in $Groups){
    if ($group.GroupTypes -eq 'Unified'){
        $members = Get-MgGroupMember -GroupId $group.Id
        [PSCustomObject]@{
            'Display Name' = $group.DisplayName
            'Members' = $members.AdditionalProperties.displayName -join ";"
        } | Export-Csv -path "$OutputFolder\UnifiedGroups.csv" -Append -NoTypeInformation
    }
    continue
}

$Groups = Get-MgGroup -Property "Members" -All
foreach($group in $Groups){
    if ( ( ($group.SecurityEnabled) -and ($group.MailEnabled) ) -and ('Unified' -notin $group.GroupTypes) ){
        $members = Get-MgGroupMember -GroupId $group.Id
        [PSCustomObject]@{
            'Display Name' = $group.DisplayName
            'Members' = $members.AdditionalProperties.displayName -join ";"
        } | Export-Csv -path "$OutputFolder\MailEnabledSecurityGroups.csv" -Append -NoTypeInformation
    }
    continue
}

$Groups = Get-MgGroup -Property "Members" -All
foreach($group in $Groups){
    if  ( (($group.MailEnabled) -and (-not $group.SecurityEnabled)) -and ('Unified' -notin $group.GroupTypes))
    {
            $members = Get-MgGroupMember -GroupId $group.Id
            [PSCustomObject]@{
                'Display Name' = $group.DisplayName
                'Members' = $members.AdditionalProperties.displayName -join ";"
            } | Export-Csv -path "$OutputFolder\DistributionGroups.csv" -Append -NoTypeInformation
    }
    continue
}

$sharedmailList = Get-EXOMailbox
$filteredList = $sharedmailList | Where-Object {$_.RecipientTypeDetails -notcontains "UserMailbox" -and "DiscoveryMailbox"} 
foreach($sharedmail in $filteredList){
    if ($sharedmail.RecipientTypeDetails -eq 'DiscoveryMailBox'){
        continue
    }
    else{
        [PSCustomObject]@{
            'Unique ID' = $sharedmail.PrimarySmtpAddress
            'Display Name' = $sharedmail.DisplayName
        } | Export-Csv -path "$OutputFolder\SharedMailbox.csv" -Append -NoTypeInformation
    }
}


# Disconnect from services to avoid error before finalising script
Disconnect-Graph
Disconnect-ExchangeOnline
Disconnect-SPOService

优化建议

1. 彻底解决磁盘IO过高问题：批量导出CSV

原脚本在每个循环迭代中调用Export-Csv -Append，会反复打开、写入、关闭文件，10000+用户场景下磁盘IO会被严重消耗。

优化方案：先将所有对象存入内存数组，最后一次性导出：

# 初始化空数组
$userOutput = @()
foreach ($user in $users) {
    # 生成用户对象的逻辑保持不变
    $userObj = [PSCustomObject]@{
        'Enabled Credentials' = $user.AccountEnabled        
        'Unique ID' = $user.UserPrincipalName
        # 其他属性...
    }
    $userOutput += $userObj
}
# 一次性导出所有数据
$userOutput | Export-Csv -Path "$OutputFolder\UserInfo.csv" -NoTypeInformation

所有循环导出的模块（用户、站点、组、共享邮箱等）都要改成这种方式。

2. 消除重复API调用：一次性获取数据后内存分类

原脚本重复调用Get-MgGroup -Property "Members" -All四次，完全可以只调用一次，再在内存中分类处理：

$allGroups = Get-MgGroup -Property "Members" -All

# 安全组
$securityGroups = $allGroups | Where-Object { $_.SecurityEnabled -and -not $_.MailEnabled }
# 统一组
$unifiedGroups = $allGroups | Where-Object { $_.GroupTypes -contains 'Unified' }
# 邮件启用安全组
$mailEnabledSecurityGroups = $allGroups | Where-Object { $_.SecurityEnabled -and $_.MailEnabled -and 'Unified' -notin $_.GroupTypes }
# 通讯组
$distributionGroups = $allGroups | Where-Object { $_.MailEnabled -and -not $_.SecurityEnabled -and 'Unified' -notin $_.GroupTypes }

之后针对每类组单独循环处理，避免四次重复拉取全量组数据。

3. 提升内存查询效率：用哈希表替代Where-Object遍历

原脚本在用户循环中用Where-Object遍历$OneDriveUsage和$MailboxInfo，时间复杂度为O(n)，用户量越大越慢。改为哈希表后查询效率为O(1)：

# 转换OneDrive数据为哈希表，键为用户UPN
$oneDriveHash = @{}
foreach ($od in $OneDriveUsage) {
    $oneDriveHash[$od.Owner] = $od
}
# 用户循环中直接取值
$CurrentOneDrive = $oneDriveHash[$user.UserPrincipalName]

# 转换邮箱数据为哈希表，键为主SMTP地址
$mailboxHash = @{}
foreach ($mb in $MailboxInfo) {
    $mailboxHash[$mb.PrimarySmtpAddress] = $mb
}
$CurrentMailboxInfo = $mailboxHash[$user.UserPrincipalName]

同理，站点数据也可以转为哈希表，避免Teams循环中反复调用Get-SPOSite：

$allSites = Get-SPOSite -Limit ALL
$siteHash = @{}
foreach ($site in $allSites) {
    $siteHash[$site.Title] = $site
}
# Teams循环中直接取值
$Sites = $siteHash[$team.DisplayName]

4. 简化字符串处理：替代复杂正则

原脚本提取SMTP地址的正则可以简化，改用更高效的管道处理：

$stringSMTP = $CurrentMailboxInfo.EmailAddresses | Where-Object { $_ -cmatch '^smtp:' } | ForEach-Object { $_ -replace '^smtp:' } -join ";"

避免Select-String的全量匹配开销，提升处理速度。

5. 修复过滤逻辑：提前减少数据集大小

原共享邮箱过滤逻辑存在语法错误，导致无效数据进入循环：

# 错误写法
$filteredList = $sharedmailList | Where-Object {$_.RecipientTypeDetails -notcontains "UserMailbox" -and "DiscoveryMailbox"} 
# 正确写法：排除用户邮箱和发现邮箱
$filteredList = $sharedmailList | Where-Object { $_.RecipientTypeDetails -notin @("UserMailbox", "DiscoveryMailbox") }

提前过滤无效数据，减少后续循环迭代次数。

6. 优化API权限与模块导入

原Connect-MgGraph请求了大量不必要的权限（如DeviceManagement系列），只保留所需权限：
```
Connect-MgGraph -Scopes Directory.Read.All, User.Read.All, Group.Read.All, Sites.Read.All
```
移除不必要的模块导入，确保只加载需要的模块。

7. 避免循环内重复文件操作

原Teams循环中，$teamReport = Import-CSV "$InputFolder\TeamReport.csv"放在循环内，会重复导入文件，应移到循环外：

$teamReport = Import-CSV "$InputFolder\TeamReport.csv"
foreach($team in $teamlist){
    $teamDetails = $teamReport | Where-Object{$_."Team Name" -eq $team.DisplayName}
    # 其他逻辑...
}

8. 可选：并行处理加速

对于超大量数据，可使用ForEach-Object -Parallel并行处理，但需注意O365 API的速率限制，避免触发限流：

$userOutput = $users | ForEach-Object -Parallel {
    # 这里需要确保会话可用，或在并行块内重新建立必要连接
    $user = $_
    $license = Get-MgUserLicenseDetail -UserId $user.Id
    # 其他逻辑...
    return [PSCustomObject]@{/* 属性 */}
} -ThrottleLimit 10

注意：并行处理需谨慎，需根据租户API配额调整并发数。

内容的提问来源于stack exchange，提问作者Alistair Trout

O365租户报表脚本性能优化求助：大租户速度及磁盘IO问题

优化建议 #

1. 彻底解决磁盘IO过高问题：批量导出CSV #

2. 消除重复API调用：一次性获取数据后内存分类 #

3. 提升内存查询效率：用哈希表替代Where-Object遍历 #

4. 简化字符串处理：替代复杂正则 #

5. 修复过滤逻辑：提前减少数据集大小 #

6. 优化API权限与模块导入 #

7. 避免循环内重复文件操作 #

8. 可选：并行处理加速 #