如何加速提取O365用户SignInActivity的PowerShell脚本执行

问题背景 #

用MSGraph PowerShell脚本提取公司O365用户信息时，前十几位用户处理正常，之后卡顿数分钟再恢复，循环往复。排查发现提取SignInActivity的环节是性能瓶颈，移除该部分后脚本速度正常。核心问题代码如下：

@{Label = "Last Sign On"; Expression = {
    #Expression variables
    $GUID = $_.Id
    $GetSignOn = Get-MgUser -UserId $GUID -Property SignInActivity | `
    Select-Object {$_.SignInActivity} -ExpandProperty SignInActivity | Select-Object LastSignInDateTime
    #Extract desired data from array
    $LastSignOn = $GetSignOn.LastSignInDateTime -split " "
    $LastSignOn[0]
    }

优化思路 #

当前脚本的问题是逐个用户调用Get-MgUser获取SignInActivity，属于串行请求，触发了Graph API的速率限制（Throttling），导致卡顿。优化核心是：减少重复API请求，批量获取所需数据。

优化1：直接从初始批量请求中读取SignInActivity #

你初始调用Get-MgUser时已经指定了SignInActivity作为要获取的属性，完全不需要再单独调用Get-MgUser！直接从已获取的用户对象中读取即可，彻底消除额外API请求。

修改后的"Last Sign On"表达式：

@{Label = "Last Sign On"; Expression = {
    if ($_.SignInActivity.LastSignInDateTime) {
        # 直接格式化日期，替代字符串拆分
        $_.SignInActivity.LastSignInDateTime.ToString("yyyy-MM-dd")
    } else {
        "无登录记录"
    }
}}

优化2：同步修复Manager和License的性能问题 #

原脚本中Manager和License部分也是逐个用户调用API，同样会触发速率限制，一起优化：

• Manager信息：初始Get-MgUser时直接-ExpandProperty Manager，无需后续单独请求
• License信息：提前定义SkuId与名称的映射，从AssignedLicenses直接读取，避免逐个调用Get-MgUserLicenseDetail

步骤1：提前定义License映射

# 替换为你的实际License名称
$LicenseMap = @{
    "f245ecc8-75af-4f8e-b61f-27d8114de5f3" = "Office 365 E3"
    "cbdc14ab-d96c-4c30-b9f4-6ada7cdc1d46" = "Office 365 E5"
}

步骤2：优化Manager和License的表达式

# Manager Name优化
@{Label = "Manager Name"; Expression = { $_.Manager.AdditionalProperties.displayName ?? "无经理" } },
# Manager Email优化
@{Label = "Manager Email"; Expression = { $_.Manager.AdditionalProperties.userPrincipalName ?? "无经理邮箱" } },
# License信息优化
@{Label = "License"; Expression = {
    $_.AssignedLicenses.SkuId | ForEach-Object { $LicenseMap[$_] } | Where-Object { $_ } -join ','
}},

优化3：修正初始Get-MgUser的参数 #

确保初始请求包含所有需要的属性和扩展，同时修正License匹配逻辑（-contains比-eq更适合多License场景）：

$UserReport = Get-MgUser -Filter "AccountEnabled eq true" -Property $Properties -ExpandProperty Manager | Where-Object {
    ($_.AssignedLicenses.SkuId -contains $StdLicense -or $_.AssignedLicenses.SkuId -contains $PremLicense) -and $_.DisplayName -ne $IT
}

完整优化后的脚本 #

#Connect to Microsoft Graph with Required Scopes
Connect-MgGraph -Scopes "User.Read.All","Directory.Read.All","AuditLog.Read.All"

#Switch to Beta version to access Last Sign On Properties
Select-MgProfile beta

#DateStamp and Path for Output file
$LogDate = Get-Date -Format "dd-MMM-yyyy"
$Path = "C:\Scripts\MSGraph\ADUsers\CTS Licensed Users_$LogDate.csv"

#Define Properties for UserReport
$Properties = @(
    'GivenName', 'DisplayName', 'JobTitle', 'UserPrincipalName', 'CompanyName', 'Department', 'MobilePhone', 'BusinessPhones', 'OfficeLocation', `
    'StreetAddress', 'City', 'State', 'OtherMails', 'EmployeeId', 'Manager', 'CreatedDateTime', 'EmployeeHireDate', 'Id', 'SignInActivity', 'AssignedLicenses'
)

#Define Filter Variables for Users with Paid O365 Subscription
$StdLicense = "f245ecc8-75af-4f8e-b61f-27d8114de5f3"
$PremLicense = "cbdc14ab-d96c-4c30-b9f4-6ada7cdc1d46"

# 提前定义License SkuId与名称的映射（替换为实际名称）
$LicenseMap = @{
    $StdLicense = "Office 365 E3"
    $PremLicense = "Office 365 E5"
}

#Define Filter Variable to exclude IT Helpdesk
$IT = "IT Helpdesk"

#Structure Filter as a Script Block - 加入ExpandProperty Manager，修正License匹配逻辑
$UserReport = Get-MgUser -Filter "AccountEnabled eq true" -Property $Properties -ExpandProperty Manager | Where-Object {
    ($_.AssignedLicenses.SkuId -contains $StdLicense -or $_.AssignedLicenses.SkuId -contains $PremLicense) -and $_.DisplayName -ne $IT
}

Write-Host -f Yellow "PowerShell is Preparing:"
Write-Host -f Yellow "CTS Licensed User Report"

#Build Hashtable and Populate Properties - 全量优化所有表达式
$UserReport | Sort-Object GivenName | Select-Object `
@{Label = "Full Name"; Expression = { $_.DisplayName } },
@{Label = "Job Title"; Expression = { $_.JobTitle } },
@{Label = "Company Email"; Expression = { $_.UserPrincipalName } },
@{Label = "Company"; Expression = { $_.CompanyName } },
@{Label = "Department"; Expression = { $_.Department } },
@{Label = "Mobile Phone"; Expression = { $_.MobilePhone } },
@{Label = "Office Phone"; Expression = { $_.BusinessPhones -join ';' } }, # 处理多号码情况
@{Label = "Office Address"; Expression = { $_.OfficeLocation } },
@{Label = "Employee Street"; Expression = { $_.StreetAddress } },
@{Label = "Employee City"; Expression =  { $_.City } },
@{Label = "Employee State"; Expression = { $_.State } },
@{Label = "Personal Email"; Expression = { $_.OtherMails -join ';' } }, # 处理多邮箱情况
@{Label = "EIN"; Expression = { $_.EmployeeId } },
@{Label = "Manager Name"; Expression = { $_.Manager.AdditionalProperties.displayName ?? "无经理" } },
@{Label = "Manager Email"; Expression = { $_.Manager.AdditionalProperties.userPrincipalName ?? "无经理邮箱" } },
@{Label = "Account Created"; Expression = { $_.CreatedDateTime.ToString("yyyy-MM-dd") } }, # 直接格式化日期
@{Label = "Hire Date"; Expression = { if ($_.EmployeeHireDate) { $_.EmployeeHireDate.ToString("yyyy-MM-dd") } else { "无入职日期" } } },
@{Label = "License"; Expression = { $_.AssignedLicenses.SkuId | ForEach-Object { $LicenseMap[$_] } | Where-Object { $_ } -join ',' } },
@{Label = "Last Sign On"; Expression = {
    if ($_.SignInActivity.LastSignInDateTime) {
        $_.SignInActivity.LastSignInDateTime.ToString("yyyy-MM-dd")
    } else {
        "无登录记录"
    }
}} | Export-Csv -Path $Path -NoTypeInformation -Encoding UTF8 # 加入UTF8编码避免乱码

Write-Host -f Yellow "Success!"
Write-Host -f Yellow "Report Saved to:" $Path

优化效果 #

1. SignInActivity部分：彻底消除逐个用户的API请求，解决卡顿问题
2. 整体性能：Manager和License部分同样减少了批量串行请求，脚本执行速度提升数倍
3. 代码可靠性：用日期格式化替代字符串拆分，处理多值字段避免CSV导出异常

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