How to Get the Last Microsoft 365 Sign-In Date with PowerShell
Question details
The user needs to retrieve the last sign-in date for all Microsoft 365 users, including those who only access applications and lack a mailbox.
- Product
- Microsoft 365 / PowerShell
- Device & OS
- not provided
- Scenario
- Auditing user activity and generating comprehensive sign-in reports for a Microsoft 365 tenant.
- Observed behavior
- Per-user Azure AD audit log requests frequently time out or get throttled when processing large date ranges, and standard Exchange cmdlets miss application-only users.
Ensure you have the necessary administrative privileges (such as Global Reader or Reports Reader) in your Microsoft 365 tenant and that the required PowerShell modules for Microsoft Graph or Exchange Online are installed and authenticated.
Use Microsoft Graph API for Comprehensive Sign-In Logs
The most reliable method to get sign-in data for all users (including those without mailboxes) without severe throttling.
Microsoft Graph provides a unified endpoint to query Azure AD sign-in logs. Instead of querying per user, which causes throttling, you can pull the logs collectively and apply server-side filters.
Open PowerShell as an administrator and connect to Microsoft Graph using the command: Connect-MgGraph -Scopes "AuditLog.Read.All", "Directory.Read.All".
Use the Get-MgAuditLogSignIn cmdlet to retrieve sign-in records across the tenant.
Apply server-side filtering to limit the date range and avoid timeouts. For example, append: -Filter "createdDateTime ge 2023-01-01T00:00:00Z".
Implement a pagination loop or use the -All parameter carefully to handle large datasets. Add controlled delays using Start-Sleep if throttling limits are reached.
Use Get-MailboxStatistics for Mailbox Users
A quick method to find the last logon time for users who have active Exchange Online mailboxes.
Analyze Your Exported Audit Reports with WPS Office
While PowerShell handles the extraction of Microsoft 365 sign-in logs, analyzing the resulting CSV and Excel reports is effortless with WPS Office. It is a free, lightweight, and highly compatible Office suite that perfectly handles large data exports.
- 1. Install WPS Office: Download and install the free WPS Office suite on your computer.
- 2. Open the exported report: Launch WPS Spreadsheet and open the CSV file you exported via PowerShell.
- 3. Analyze the data: Use the Data tab to apply filters or create PivotTables to organize the last sign-in dates effectively.
Frequently Asked Questions
Why doesn't Get-MailboxStatistics show all Microsoft 365 users?
The Get-MailboxStatistics cmdlet only queries Exchange Online. Users who utilize Microsoft 365 applications (like Teams, SharePoint, or OneDrive) without an assigned Exchange mailbox will not appear in these results.
How can I prevent Azure AD PowerShell queries from timing out?
To prevent timeouts, reduce the date range of your query, use server-side filters (such as the -Filter parameter in Microsoft Graph) instead of client-side piping (like Where-Object), and implement controlled retries with delays in your script.
Do I need special permissions to run Microsoft Graph sign-in queries?
Yes, reading sign-in logs via Microsoft Graph typically requires the AuditLog.Read.All permission, as well as an administrator role such as Global Reader, Security Reader, or Reports Reader.
How do I handle '429 Too Many Requests' errors in PowerShell?
When you receive a 429 error, it means you are being throttled by the server. You should use a try-catch block to catch the error, read the 'Retry-After' header if available, and use Start-Sleep to pause the script before re-attempting the request.
