How to Generate User Last Login Report in Office 365

Tracking user activity and monitoring sign-ins is crucial for maintaining security and ensuring efficient management within Office 365. One valuable aspect of user management is generating a last login date report.

This report provides administrators with insights into when users last accessed their accounts, aiding in security assessments and identifying potential issues.

In this article, we’ll explore different methods to generate an Office 365 last login report using various tools and PowerShell modules.

Office 365 Last Login Report in Microsoft Entra Admin Center

The first option to generate an Office 365 last login report is through the Microsoft Entra Admin Center (formerly Azure Active Directory admin center).

1. Log in to the Microsoft Entra admin center.
2. Navigate to Identity → All Users → Sign-in logs.
   
3. You can customize the reporting period (date range) and add filters from here.
   

   Note that the Azure AD Sign-in Logs only go as far back as 30 days.

   

4. Click Download and choose JSON or CSV. You can then select which report to download. The relevant ones are the “InteractiveSignIns_” and “NonInteractiveSignIns_” reports.
   • “InteractiveSignIns_” — Where the users entered their credentials interactively, like, like, signing in to SharePoint Online or Outlook Web App.
   • “NonInteractiveSignIns_” — Where the users logged in using their access tokens, usually cached in clients that support modern authentication, such as Outlook desktop or mobile.
     
5. After downloading, you can now open the file for further processing.
   The report includes all login dates of each user and not just the last. You must perform further processing to clean up the report.

Office 365 Last Login Report using Azure AD PowerShell

For more advanced users who prefer PowerShell, the AzureADPreview module provides the necessary cmdlet to access sign-in logs programmatically. Here’s how to do it:

Open PowerShell and run the following command to install the AzureADPreview module:

Note. The AzureADPreview module only works in Windows PowerShell and not PowerShell Core (7x).

Install-Module -Name AzureADPreview -Force -AllowClobber

Run the following command to connect to Azure AD:

Import-Module AzureADPreview 
Connect-AzureAD

Copy the following script and save it to your computer as Get-AzAdUserLastLogin.ps1.

# Get all users (excluding Guest and disabled accounts) 
$azureAdUsers = Get-AzureADUser -Filter "AccountEnabled eq true and userType eq 'Member'" -All:$true 
"Found [$($azureAdUsers.Count)] users." | Out-Default 

for ($i = 0 ; $i -lt $azureAdUsers.Count ; $i++) { 
"Processing $($i+1) of $($azureAdUsers.Count): [$($azureAdUsers[$i].UserPrincipalName)]" | Out-Default 
# Filter only the successful login attempts. 
$filter = "UserId eq '$($azureAdUsers[$i].ObjectId)' and Status/ErrorCode eq 0" 
if ($signInLogs = (Get-AzureADAuditSignInLogs -Filter $filter -Top 1)) { 
" -> OK." | Out-Default 
# Return the last login time 
[PSCustomObject]$([ordered]@{ 
Name = $azureAdUsers[$i].DisplayName 
Username = $azureAdUsers[$i].UserPrincipalName 
LastLogInDate = (Get-Date ($signInLogs.CreatedDateTime)) 
}) 
} 
else { 
" -> No login within the last 30 days." | Out-Default 
} 
}

Execute the script to generate the Office 365 Last Login Report and store it in a variable.

$LastSignInLogs = .\Get-AzAdUserLastLogin.ps1

Display the result on the screen.

Or export the Office 365 Last Login Report to a CSV file.

$LastSignInLogs | Export-Csv ".\office 365 last login report.csv"

Office 365 Last Login Report using Microsoft Graph PowerShell

There are two ways to get the last login date of users using Microsoft Graph PowerShell.

• Using the Get-MgAuditSignInLogs cmdlet to retrieve the Azure AD sign-in logs. This method is similar to running Get-AzureADAuditSignInLogs in the AzureADPreview module we discussed in the previous section. The Azure AD sign-in logs are limited to 30 days.
• Using the Get-MgAuditLogSignIn cmdlet to get the user’s SignInActivity property. This property stores the last login date of the user.

This method requires installing Microsoft.Graph PowerShell module on your computer. If you haven’t done so, here’s the command to install it.

Install-Module Microsoft.Graph

Using the Get-MgAuditSignInLogs Command

Open PowerShell and connect to Microsoft Graph.

# Import the module 
Import-Module -Name Microsoft.Graph.Authentication 

# Authenticate to Microsoft Graph (you will be prompted for credentials) 
Connect-MgGraph -Scopes "AuditLog.Read.All"

Copy the following script and save it to your computer as Get-GraphAdUserLastLogin.ps1.

# Get all users (excluding Guest and disabled accounts) 
$azureAdUsers = Get-AzureADUser -Filter "AccountEnabled eq true and userType eq 'Member'" -All:$true 
"Found [$($azureAdUsers.Count)] users." | Out-Default 

for ($i = 0 ; $i -lt $azureAdUsers.Count ; $i++) { 
"Processing $($i+1) of $($azureAdUsers.Count): [$($azureAdUsers[$i].UserPrincipalName)]" | Out-Default 
# Filter only the successful login attempts. 
$filter = "UserId eq '$($azureAdUsers[$i].ObjectId)' and Status/ErrorCode eq 0" 
if ($signInLogs = (Get-AzureADAuditSignInLogs -Filter $filter -Top 1)) { 
" -> OK." | Out-Default 
# Return the last login time 
[PSCustomObject]$([ordered]@{ 
Name = $azureAdUsers[$i].DisplayName 
Username = $azureAdUsers[$i].UserPrincipalName 
LastLogInDate = (Get-Date ($signInLogs.CreatedDateTime)) 
}) 
} 
else { 
" -> No login within the last 30 days." | Out-Default 
} 
}

Execute the script to generate the Office 365 Last Login Report and store it in a variable.

$LastSignInLogs = .\Get-GraphAdUserLastLogin.ps1

Display the result on the screen.

Or export the Office 365 Last Login Report to a CSV file.

$LastSignInLogs | Export-Csv ".\office 365 last login report.csv"

Based on testing using the same set of users, the Microsoft Graph method performs faster than AzureADPreview.

Using the Get-MgUser Command

The Get-MgUser that comes with the Microsoft.Graph PowerShell module retrieves the Azure AD user account and optionally returns the SignInActivity property. This property contains the LastSignInDateTime property that stores the last recorded login time of the user.

# Import the Authentication module 
Import-Module -Name Microsoft.Graph.Authentication 

# Authenticate to Microsoft Graph (you will be prompted for credentials) 
Connect-MgGraph -Scopes "User.Read.All"

# Import the required module 
Import-Module Microsoft.Graph.Users 

# Get all users (excluding Guest and disabled accounts) 
Get-MgUser -Filter "AccountEnabled eq true and UserType eq 'Member'" -ErrorAction Stop -Property ` 
'DisplayName', 'UserPrincipalName', 'SignInActivity' -All| 
Select-Object ` 
@{n = 'Name' ; e = { $_.DisplayName } }, 
@{n = 'Username' ; e = { $_.UserPrincipalName } }, 
@{n = 'LastLoginDate'; e = { 
$( 
if (!$_.SignInActivity.LastSignInDateTime) { 
# If no LastSignInDateTime value, set to $null 
$null 
} 
else { 
$_.SignInActivity.LastSignInDateTime 
} 
) 
} 
}

$LastSignInLogs = .\Get-GraphAdUserLastSignin.ps1 
$LastSignInLogs

$LastSignInLogs | Export-Csv ".\office 365 last login report.csv"