Last login details

Question

Hi All,

I am running an Exchange 2016 Hybrid environment in my test Azure Tenant. I create remote mailboxes from on-premises and have 150 Microsoft 365 E5 licenses. My users test M365 features and integrations in this tenant. Since I only have 150 licenses, I frequently receive requests to assign licenses to new users. To manage this, I check the Azure AD sign-in logs for users who have not logged in for 30 days, revoke their M365 licenses, and assign the licenses to new users.

Is there a way to check the last login details using Azure AD PowerShell, rather than manually checking each user's sign-in logs? I usually check the Azure AD sign-in logs for the last 30 days. Is that sufficient to identify users who haven't logged in during this period?

For example, if I have a list of users in a CSV file in the following format, how can I import these users and check their last Azure AD sign-in date?

users
user1@contoso.com 
user2@contoso.com

Answer 1

Hi，@Glenn Maxwell

Thanks for posting your question in the Microsoft Q&A forum.

1. Install the Azure AD PowerShell Module if you haven't already. You can install it using the following command:

Install-Module -Name AzureAD 

2. Connect to your Azure AD with your admin account using:

Connect-AzureAD 

3. Import your list of users from the CSV file:

$users = Import-Csv -Path "path_to_your_csv.csv" 

4. Loop through each user in the CSV and retrieve their last sign-in date:

foreach ($user in $users) { $signInLogs = Get-AzureADAuditSignInLogs -Filter "userPrincipalName eq '$($user.UserPrincipalName)'" -Top 1 | Sort-Object CreatedDateTime -Descending if ($signInLogs) { $lastSignIn = $signInLogs.CreatedDateTime # Output user and last sign-in date Write-Output "$($user.UserPrincipalName) last signed in on $lastSignIn" } else { Write-Output "$($user.UserPrincipalName) has no sign-in logs" } } 

Please replace "path_to_your_csv.csv" with the actual path to your CSV file and ensure that your CSV has a column named "UserPrincipalName" for the user's principal name.

If my answer is helpful to you, please mark it as the answer so that other users can refer to it. Thank you for your support and understanding.

Answer 2

That would depend on your criteria. Login doesn't necessarily indicate that the user is taking advantage of any license assigned to them, for that purpose the "activity" data per workload is more useful. You can get it via the built-in "Active users" report: https://admin.microsoft.com/#/reportsUsage/LicenseActivity

Or if you want to do it via PowerShell/Graph API:

Get-MgReportOffice365ActiveUserDetail -Period d90 -OutFile C:\reports\blabla.csv

Answer 3

Hi Glenn Maxwell,

Thank you for posting in the Q&A Forums.

$users = Import-Csv -Path “C:\path\to\your\users.csv” # Assuming the CSV file contains the “UPN” columns

foreach ($user in $users) {

$upn = $user.UPN  

$uri = “https://graph.microsoft.com/v1.0/users/$upn/signInActivity”  

$token = GetAccessToken() # Here you need to implement a function to get the OAuth access token  



$headers = @{  

    Authorization = “Bearer $token”  

}  



$response = Invoke-RestMethod -Uri $uri -Method Get -Headers $headers  



if ($response.value.Count -gt 0) {  

    $lastSignInDateTime = $response.value[0].lastSignInDateTime  

    Write-Host “User $upn last signed in at $lastSignInDateTime”  



    # Here you can add logic to check if the last login was more than 30 days ago  

    # and revoke or reassign the license as needed  

} else {  

    Write-Host “No sign-in activity found for user $upn”  

}  

}

This is a sample function to get an OAuth access token, which you will need to configure for your environment

function GetAccessToken() {

# Here you need to implement the logic to get the OAuth access token.  

# Typically involves a client ID, client key, resource URI (for Microsoft Graph it's https://graph.microsoft.com/)  

# Use the ADAL or MSAL library to get the token  

return “your_access_token_here”  

}

Best regards

NeuviJ

============================================

If the Answer is helpful, please click "Accept Answer" and upvote it.