Microsoft Graph脚本问题：过滤授权用户后无法获取正确登录时间

问题 #

需要生成非活跃用户报表，统计最后登录时间超过90天的授权用户（授权信息通过extensionAttribute15字段标识，值以E、f、k开头）。但使用Microsoft Graph的PowerShell脚本添加extensionAttribute15的过滤条件后，所有用户的最后登录时间都显示为"Never"；移除过滤条件后能获取真实登录时间，但结果会包含未授权用户。

原因 #

Microsoft Graph Beta端点存在限制：当在$filter中使用自定义扩展属性（如extension_1fe7973b28e74213b897d62528e614c7_extensionAttribute15）时，即使在$select中指定了signInActivity，该字段也不会被返回，导致脚本中$User.SignInActivity始终为null，最终显示"Never"。

解决方案 #

方法一：分两步查询（适合用户量较大的场景） #

先获取所有符合extensionAttribute15条件的用户ID，再通过这些ID批量查询包含signInActivity在内的完整用户信息，避开过滤与投影的冲突。

修改后的脚本：

# Azure AD Enterprise App for authentication to the 84 tenant
$AppId = "X"
$TenantId = "X"
$AppSecret = 'X'

# Construct URI and body needed for authentication
$uri = "https://login.microsoftonline.com/$tenantId/oauth2/v2.0/token"
$body = @{
    client_id     = $AppId
    scope         = "https://graph.microsoft.com/.default"
    client_secret = $AppSecret
    grant_type    = "client_credentials" 
}

# Get OAuth 2.0 Token
$tokenRequest = Invoke-WebRequest -Method Post -Uri $uri -ContentType "application/x-www-form-urlencoded" -Body $body -UseBasicParsing
$token = ($tokenRequest.Content | ConvertFrom-Json).access_token
$headers = @{Authorization = "Bearer $token"}

# Step 1: 获取符合extensionAttribute15条件的用户ID列表
Write-Host "Fetching authorized user IDs..."
$userIds = @()
$nextLink = "https://graph.microsoft.com/beta/users?`$filter=startsWith(extension_1fe7973b28e74213b897d62528e614c7_extensionAttribute15,'E') or startswith(extension_1fe7973b28e74213b897d62528e614c7_extensionAttribute15,'f') or startswith(extension_1fe7973b28e74213b897d62528e614c7_extensionAttribute15,'k')&`$select=id&`$top=999"

while ($nextLink -ne $null) {
    $response = Invoke-RestMethod -Uri $nextLink -Headers $headers -Method Get
    $userIds += $response.Value.id
    $nextLink = $response.'@Odata.NextLink'
}

# 添加Split-Chunk函数用于拆分ID列表
function Split-Chunk {
    param(
        [Parameter(ValueFromPipeline=$true)]
        [array]$InputObject,
        [int]$Size
    )
    begin {
        $chunk = @()
    }
    process {
        foreach ($item in $InputObject) {
            $chunk += $item
            if ($chunk.Count -eq $Size) {
                ,$chunk
                $chunk = @()
            }
        }
    }
    end {
        if ($chunk.Count -gt 0) {
            ,$chunk
        }
    }
}

# Step 2: 批量查询用户的完整信息（包含signInActivity）
Write-Host "Fetching full user data with sign-in activity..."
$Report = [System.Collections.Generic.List[Object]]::new()
# 按每20个ID一组拆分（Graph批量查询限制）
$idChunks = $userIds | Split-Chunk -Size 20

foreach ($chunk in $idChunks) {
    $idFilter = ($chunk | ForEach-Object { "id eq '$_'" }) -join " or "
    $uri = "https://graph.microsoft.com/beta/users?`$filter=$idFilter&`$select=displayName,userPrincipalName, mail, department, jobTitle, extension_1fe7973b28e74213b897d62528e614c7_extensionAttribute14, extension_1fe7973b28e74213b897d62528e614c7_extensionAttribute15, accountEnabled, signInActivity, UserType, id&`$expand=manager(`$select=displayName,userPrincipalName)"
    
    $userData = Invoke-RestMethod -Uri $uri -Headers $headers -Method Get
    foreach ($User in $userData.Value) {
        if ($Null -ne $User.SignInActivity) {
            $LastSignIn = Get-Date($User.SignInActivity.LastSignInDateTime) -format g
            $DaysSinceSignIn = (New-TimeSpan $LastSignIn).Days
        }
        else {
            $LastSignIn = "Never" 
            $DaysSinceSignIn = "N/A"
        }

        $ReportLine = [PSCustomObject] @{
            UPN                = $User.UserPrincipalName
            DisplayName        = $User.DisplayName
            Email              = $User.Mail
            SignInStatus       = $User.accountEnabled
            Department         = $User.department
            AADLastSignIn      = $LastSignIn
            DaysInactive       = $DaysSinceSignIn  # 新增字段：直观显示未活跃天数
            JobTitle           = $User.jobTitle
            JobCode            = $User.extension_1fe7973b28e74213b897d62528e614c7_extensionAttribute14
            License            = $User.extension_1fe7973b28e74213b897d62528e614c7_extensionAttribute15
            Manager            = if($User.manager) { $User.manager.userPrincipalName } Else {$null}
        }
        $Report.Add($ReportLine)
    }
}

# 筛选最后登录超过90天或从未登录的用户
$InactiveReport = $Report | Where-Object { $_.DaysInactive -ge 90 -or $_.AADLastSignIn -eq "Never" }

# 输出结果
$InactiveReport | Sort-Object DisplayName | Out-GridView

方法二：客户端侧过滤（适合用户量较小的场景） #

先获取所有用户的完整信息（包含signInActivity），然后在PowerShell中筛选extensionAttribute15符合条件的用户，避免在Graph查询中使用扩展属性过滤。

修改后的核心逻辑：

# 获取所有用户的完整信息
$URI = "https://graph.microsoft.com/beta/users?`$select=displayName,userPrincipalName, mail, department, jobTitle, extension_1fe7973b28e74213b897d62528e614c7_extensionAttribute14, extension_1fe7973b28e74213b897d62528e614c7_extensionAttribute15, accountEnabled, signInActivity, UserType, id&`$expand=manager(`$select=displayName,userPrincipalName)&`$top=999"
$SignInData = (Invoke-RestMethod -Uri $URI -Headers $Headers -Method Get -ContentType "application/json") 
$Report = [System.Collections.Generic.List[Object]]::new() 

# 处理初始数据
Foreach ($User in $SignInData.Value) {  
    if ($Null -ne $User.SignInActivity) {
        $LastSignIn = Get-Date($User.SignInActivity.LastSignInDateTime) -format g
        $DaysSinceSignIn = (New-TimeSpan $LastSignIn).Days
    }
    else {
        $LastSignIn = "Never" 
        $DaysSinceSignIn = "N/A"
    }

    $ReportLine = [PSCustomObject] @{
        UPN                = $User.UserPrincipalName
        DisplayName        = $User.DisplayName
        Email              = $User.Mail
        SignInStatus       = $User.accountEnabled
        Department         = $User.department
        AADLastSignIn      = $LastSignIn
        DaysInactive       = $DaysSinceSignIn
        JobTitle           = $User.jobTitle
        JobCode            = $User.extension_1fe7973b28e74213b897d62528e614c7_extensionAttribute14
        License            = $User.extension_1fe7973b28e74213b897d62528e614c7_extensionAttribute15
        Manager            = if($User.manager) { $User.manager.userPrincipalName } Else {$null}
    }
    $Report.Add($ReportLine) 
}

# 处理分页数据（原脚本逻辑不变）
While ($NextLink -ne $Null) {
    Write-Host "Still processing..."
    $SignInData = Invoke-WebRequest -Method GET -Uri $NextLink -ContentType "application/json" -Headers $Headers
    $SignInData = $SignInData | ConvertFrom-JSon
    ForEach ($User in $SignInData.Value) {  
        if ($Null -ne $User.SignInActivity) {
            $LastSignIn = Get-Date($User.SignInActivity.LastSignInDateTime) -format g
            $DaysSinceSignIn = (New-TimeSpan $LastSignIn).Days
        }
        else {
            $LastSignIn = "Never" 
            $DaysSinceSignIn = "N/A"
        }
        
        $ReportLine = [PSCustomObject] @{  
            UPN                = $User.UserPrincipalName
            DisplayName        = $User.DisplayName
            Email              = $User.Mail
            SignInStatus       = $User.accountEnabled  
            Department         = $User.department
            AADLastSignIn      = $LastSignIn
            DaysInactive       = $DaysSinceSignIn
            JobTitle           = $User.jobTitle
            JobCode            = $User.extension_1fe7973b28e74213b897d62528e614c7_extensionAttribute14
            License            = $User.extension_1fe7973b28e74213b897d62528e614c7_extensionAttribute15
            Manager            = if($User.manager) { $User.manager.userPrincipalName } Else {$null}
        }
        $Report.Add($ReportLine) 
    } 
    $NextLink = $SignInData.'@Odata.NextLink'
}

# 客户端侧筛选授权用户+非活跃用户
$InactiveAuthorizedUsers = $Report | Where-Object {
    ($_.License -match "^E|^f|^k") -and 
    ($_.DaysInactive -ge 90 -or $_.AADLastSignIn -eq "Never")
}

# 输出结果
$InactiveAuthorizedUsers | Sort DisplayName | Out-GridView

补充说明 #

• 方法一的Split-Chunk函数用于拆分用户ID列表，适配Graph批量查询的数量限制（最多20个ID）。
• 用户量较大时优先选择方法一，避免客户端侧过滤占用过多本地资源。

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