How to return sign in activity for specific multiple users in office365 using Microsoft graph Api with powershell

Question

I need to return lastsignindatetime from signinactivity for specific users who are stored inside a csv file using microsoft graph api. Have noticed two weird behaviors with graph api explorer and PowerShell which has become a challenge to retreive the data correctly.

1. when i make request to graph api using graph explorer with this uri i get results returned without a problem. https://graph.microsoft.com/beta/users/7d32b8e5-1bbc-414b-a9ce-afa743fb8753?$select=signInActivity

when i make the request using PowerShell script i get the error below, have tried to put tilde between question mark and select but still i get the same error returned$signInActivityUri = Invoke-RestMethod -Uri "https://graph.microsoft.com/beta/users/$userId?`$select=signInActivity"

2. Visual studio code tells me $userId variable is assigned yet not used warning. This is a false warning since have used $userId in the $signInActivityUri request endpoint. My app app registration has directory.read.all and auditlog.Read.all permission assigned. Can i get assistance to know what is wrong.

this is the script

# Set your authentication details
$appId = ""
$appSecret = ""
$tenantId = ""


$authEndpoint = "https://login.microsoftonline.com/$tenantId/oauth2/v2.0/token"
$body = @{
    client_id = $appId
    client_secret = $appSecret
    scope = "https://graph.microsoft.com/.default"
    grant_type = "client_credentials"
}

# Obtain access token
$tokenResponse = Invoke-RestMethod -Method Post -Uri $authEndpoint -Body $body
$accessToken = $tokenResponse.access_token


$requestHeaders = @{
    'Authorization' = "Bearer $($accessToken)"
    'Accept' = 'application/json'
}


$csvContent = Import-Csv -Path "C:\temp\users.csv"


foreach ($memberUser in $csvContent) {
    # Get user information
    $member = $memberUser.userPrincipalName
    $userResponse = Invoke-RestMethod -Uri "https://graph.microsoft.com/v1.0/users?$filter=userPrincipalName eq '$member'&$select=userPrincipalName, id, displayName" -Headers $requestHeaders -Method Get
    
    foreach ($user in $userResponse.value) {
        $userId = $user.id 
     
        $signInActivityUri = Invoke-RestMethod -Uri "https://graph.microsoft.com/beta/users/$userId?$select=signInActivity" -Headers $requestHeaders -Method Get
        $lastSignInDateTime = $signInActivityUri.signInActivity.lastSignInDateTime
        $lastSignInDateTime
      
    }
}

In my csv, i have only two user account userprincipalnames

Answer 1

In your second screenshot syntax highlighting is showing that $filter and $select are being interpreted as variables instead of as literal, which is why the first call to Invoke-RestMethod is failing, you need to escape the $ with a backtick `.

In the second call, the ? right after $userId is being interpreted as part of the variable name, you need to use ${...} in this case and again, $select has to be escaped with `:

foreach ($memberUser in $csvContent) {
    # Get user information
    $member = $memberUser.userPrincipalName
    $userResponse = Invoke-RestMethod -Uri "https://graph.microsoft.com/beta/users?`$filter=userPrincipalName eq '$member'&`$select=userPrincipalName, id, displayName" -Headers $requestHeaders -Method Get

    foreach ($user in $userResponse.value) {
        $userId = $user.id
        $signInActivityUri = Invoke-RestMethod -Uri "https://graph.microsoft.com/beta/users/${userId}?`$select=signInActivity" -Headers $requestHeaders -Method Get
        $lastSignInDateTime = $signInActivityUri.signInActivity.lastSignInDateTime
        $lastSignInDateTime
    }
}

As for improving your current code, it's unclear why you're doing this in 2 separated loops, you could add signInActivity to your first $select:

foreach ($memberUser in $csvContent) {
    # Get user information
    $member = $memberUser.userPrincipalName
    $invokeRestMethodSplat = @{
        Uri     = "https://graph.microsoft.com/beta/users?`$filter=userPrincipalName eq '$member'&`$select=userPrincipalName, id, displayName, signInActivity"
        Headers = $requestHeaders
        Method  = 'Get'
    }

    Invoke-RestMethod @invokeRestMethodSplat
}

And, a further improvement would be to use the in operator in your filter, this would improve the query speed by great amount but you should note that the number of expressions is limited to 15 and the URL length is limited to 2,048 characters.

$csvContent = Import-Csv -Path 'C:\temp\users.csv'
[string[]] $upns = $csvContent.userPrincipalName
[System.Linq.Enumerable]::Chunk($upns, 15) | ForEach-Object {
    $chunk = $_ -join "','"
    $invokeRestMethodSplat = @{
        Uri     = "https://graph.microsoft.com/beta/users?`$filter=userPrincipalName in ('$chunk')&`$select=userPrincipalName, id, displayName, signInActivity"
        Headers = $requestHeaders
        Method  = 'Get'
    }

    Invoke-RestMethod @invokeRestMethodSplat
}