How to Export Exchange Online Mailbox Last Logon Data to CSV
Question details
An administrator needs to extract Exchange Online mailbox information, including email addresses, last logon times, and assigned Microsoft 365 licenses, into a consolidated CSV report.
- Product
- Exchange Online / PowerShell
- Device & OS
- not provided
- Scenario
- IT administrators frequently need to audit Microsoft 365 environments to identify inactive mailboxes, reclaim unused licenses, or generate compliance reports.
- Observed behavior
- Extracting last logon time requires Exchange Online modules, while license information requires Microsoft 365 modules. The challenge is running the correct PowerShell commands and combining the outputs into a single CSV file.
Ensure you are running PowerShell as an Administrator and have the ExchangeOnlineManagement and MSOnline (or AzureAD) PowerShell modules installed and authenticated.
Export Mailbox Statistics and Last Logon Time to CSV
Use this method to quickly generate a CSV file containing user display names, email addresses, and their most recent logon times using the Exchange Online module.
This approach uses the standard Exchange Online PowerShell cmdlets to retrieve mailbox statistics across your entire organization.
Open PowerShell as Administrator and run 'Connect-ExchangeOnline'. Sign in with your global or exchange administrator credentials when prompted.
Execute the following command to retrieve the data and export it: Get-Mailbox -ResultSize Unlimited | Get-MailboxStatistics | Select-Object DisplayName, UserPrincipalName, LastLogonTime | Export-Csv 'C:\lastlogon.csv' -NoTypeInformation.
Navigate to the C:\ directory on your computer, locate the 'lastlogon.csv' file, and open it in your preferred spreadsheet software to verify the data.
Combine Exchange Logon Data with Microsoft 365 License Information
Since license data is stored in Azure AD, you must combine commands from two different modules to get a comprehensive report.
Analyze Exchange Online CSV Reports with WPS Spreadsheet
After exporting your Exchange Online last logon and license data via PowerShell, you need a reliable spreadsheet tool to review the CSV files. WPS Office provides a free, lightweight alternative to Microsoft Excel, offering seamless format compatibility and a familiar UI for data analysis.
- 1. Install WPS Office: Download and install WPS Office on your computer.
- 2. Open Your CSV Export: Right-click the 'lastlogon.csv' file generated by PowerShell and select 'Open with WPS Spreadsheet'.
- 3. Analyze Data: Apply data filters and conditional formatting to quickly spot accounts that have not logged in recently.
Frequently Asked Questions
Why is the LastLogonTime blank for some of my users?
A blank LastLogonTime typically means the user has never logged into their mailbox. It can also occur if a mailbox was recently migrated and hasn't been accessed in the new Exchange Online environment yet.
Can I filter the PowerShell export for specific departments?
Yes. You can modify the Get-Mailbox command by adding filters, such as Get-Mailbox -Filter {Department -eq 'Sales'}, before piping the results into Get-MailboxStatistics.
How do I fix formatting issues when opening the exported CSV?
If all your data appears squished into a single column, your system's default list separator might differ from the comma used by PowerShell. You can fix this by opening the file in WPS Spreadsheet, selecting the column, and using the 'Text to Columns' feature under the Data tab to split the text by commas.
