Powershell / Graph Output filtering

Hi all! I’m working on filtering data for a script that connects to MS graph. I’m having issues filtering out null values.

For background, we’re filtering out which users are active / inactive in AAD by logon date, exceeding 90 days. Licenses are assigned to users via extension attribute #15. With this syntax, the report includes users with our license values, however, the AADLastSignIn attribute value reports Never.

Does anyone have any insight as to why it’s reporting the last sign in date as only Never? Any insight would be greatly appreciated! :slight_smile:

# 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

# Unpack Access Token
$token = ($tokenRequest.Content | ConvertFrom-Json).access_token

# Base URL
$headers = @{Authorization = "Bearer $token"}

# Get User sign in data
Write-Host "Accessing the Graph to get user sign-in data..."
$URI = "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=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 { #No sign in data for this user account
      $LastSignIn = "Never" 
      $DaysSinceSignIn = "N/A" }
## Report headers and variables 
   $ReportLine  = [PSCustomObject] @{
       
     UPN                = $User.UserPrincipalName
     DisplayName        = $User.DisplayName
     Email              = $User.Mail
     SignInStatus       = $User.accountEnabled
     Department         = $User.department
     AADLastSignIn      = $LastSignIn
     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) 
}

# Check for additional data to obtain
$NextLink = $SignInData.'@Odata.NextLink'

While ($NextLink -ne $Null) { # If so, proceed with processing the additional data.
   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 { #No sign in data for this user account
      $LastSignIn = "Never" 
      $DaysSinceSignIn = "N/A" }
     
   $ReportLine  = [PSCustomObject] @{  
     UPN                = $User.UserPrincipalName
     DisplayName        = $User.DisplayName
     Email              = $User.Mail
     SignInStatus       = $User.accountEnabled  
     Department         = $User.department
     AADLastSignIn      = $LastSignIn
     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) } 

   # Check for more data
   $NextLink = $SignInData.'@Odata.NextLink'
}

$Report | Sort DisplayName | Out-GridView

Edit:

This is what it outputs currently, with the last sign in being Never consecutively:

Hi, welcome to the forum :wave:

You’re missing the backtick which should escape the $ before your $select=displayName in your URI.