PowerShell AzureAD cmdlet filter not working with variable

Question

My PowerShell script to retrieve the last sign in date of Azure AD users doesn't work when using variables within the filter for UPN. This is a well discussed topic when it comes to AzureAD PowerShell cmdlets, with the most common answer being to wrap the variable in additional quotes. However, none of the examples are for this exact cmdlet. In any case, this does not work for me. To break things down I am now using the approach of building my filter as a variable ($filter).

If I use $filter = "UserPrincipalName eq 'user123@domain.com'" the Get-AzureADAuditSignInLogs cmdlet passes me a result. If I use $filter = "UserPrincipalName eq '$($row.UserPrincipalName)'", Get-AzureADAuditSignInLogs gives a $null result.

In BOTH cases, Write-Host $filter outputs the exact same string so there is no problem with retrieving the entry from the CSV file. There is something "special" about the way MS's Azure AD cmdlets implement filters.

Has anyone come across this before? Is there a way I can force my variable to be as if I "hand typed" it?

Connect-AzureAD

$Result = $null
$output = $null
$filter = $null

$csv = Import-csv -Path c:\temp\UPNs.csv 

ForEach($row in $csv)
{
   
    $filter = "UserPrincipalName eq '$($row.UserPrincipalName)'"
    #$filter = "UserPrincipalName eq 'user123@domain.com'"
    
    $filter = $filter.ToString()
    Write-Host $filter
    
    $Result = Get-AzureADAuditSignInLogs -Filter $filter -Top 1 | Select-Object CreatedDateTime, UserPrincipalName

    $output = ($Result.UserPrincipalName.ToString() + "," + $Result.CreatedDateTime.ToString())
    $output | Out-File C:\temp\HighwaysSignInInfo.txt -Append
    Write-host $output
       
}

CSV file:

UserPrincipalName
user123@domain.com
user1234@domain.com
user12345@domain.com
user12356@domain.com
user1234567@domain.com

Answer 1

This filter works just fine:

Get-AzureADAuditSignInLogs -Filter "startsWith(userPrincipalName,'name@Contoso.com')"

Although some websites say you can use Get-AzureADAuditSignInLogs -Filter "UserPrincipalName eq 'name@Contoso.com'" it doesn't seem to work for me.

The full script (with handling for bandwidth throttling)

#GetAADUserSignIns v1.0
#This script will obtain the last sign in date for each supplied UPN via a source csv file. The biggest issue with such queries is bandwidth throttling. This is handled within the script using a simple
#Try, Catch combined with a Function that can be used to make nested calls.

#IMPORTANT: Review the below 3 options to generate a source file (Option 3 is recommended)

#1. This attempts dump the entire audit log to text file. However you are likely to be throttled before the export completes so don't bother
#Get-AzureADAuditSignInLogs -Filter -All:$true | Out-File C:\Temp\AuditLogsAll.txt

#2. To get a list of all AAD accounts exported to csv (This will capture guest accounts too, the list will be huge, therefore not recommended to run Get-AzureADAuditSignInLogs with)
#Get-AzureADUser -All $true | Export-Csv C:\Temp\AADUsers.csv -NoTypeInformation

#3. Obtain a list of on-prem AD accounts that have a valid UPN
#Get-ADUser -Filter {userPrincipalName -like "*@customdomain.com"} | Select-Object userPrincipalName | Export-Csv C:\Temp\UPNs.csv -NoTypeInformation

Connect-AzureAD

Function GetLastSignInDate {
    param (
        [string]$upn
    )

    $filter = "startsWith(userPrincipalName,'" + $upn + "')"
    Try
    {
        $Result = Get-AzureADAuditSignInLogs -Filter $filter -Top 1 | Select-Object CreatedDateTime, UserPrincipalName
        $output = ($upn + "," + $Result.CreatedDateTime.ToString())
        $output | Out-File C:\temp\SignInInfo.txt -Append
        Write-host $output -ForegroundColor Green
    }
    Catch
    {
        $message = $_

        if ($message -like "*Too Many Requests*")
        {
            Write-host "Sleeping for 10 seconds due to throttling limitations..." -ForegroundColor Yellow
            sleep 10
            #Nested function call to retry the entry that was throttled
            GetLastSignInDate $upn
        }
        elseif ($message -like "*This request is throttled*")
        {
            Write-host "Sleeping for 10 seconds due to throttling limitations..." -ForegroundColor Yellow
            sleep 10
            #Nested function call to retry the entry that was throttled
            GetLastSignInDate $upn
        }        
        elseif ($message -like "*null-valued*")
        {
            $output = ($upn + ", Not Found")
            $output | Out-File C:\temp\SignInInfo.txt -Append
            Write-host $output -ForegroundColor Gray           
        }
        elseif ($message -like "*Invalid filter clause*")
        {
            $output = ($upn + ", Invalid character")
            $output | Out-File C:\temp\SignInInfo.txt -Append
            Write-host $output -ForegroundColor Gray           
        }
        elseif ($message -like "*Error reading JToken*")
        {
            $output = ($upn + ", Script stopped due to authentication token timeout")            
            Write-host $output -ForegroundColor White -BackgroundColor Red
            exit           
        }
        else
        {
            $output = ($upn + ",Error - " + $message.ToString().SubString(0,15))
            $output | Out-File C:\temp\SignInInfo.txt -Append
            Write-host $output -ForegroundColor Red
           
        }
    }

}

$csv = $null
$Result = ""
$output = ""
$filter = ""
$i = $null

$csv = Import-csv -Path C:\temp\upns.csv 

ForEach($row in $csv)
{             
    $upn = $row.UserPrincipalName.ToLower().Replace('`r`n','').Replace('`r','').Replace('`n','').Trim()    
    GetLastSignInDate $upn          
}