Export Office 365 Users’ Last Logon Time to CSV

A lot of administrators often ask in the community, “How can I export Office 365 users’ last-logon-time?”. Getting the last-logon-date/time of O365 user is a vital task to track the user’s last logon activity, find Inactive users and remove their licenses. The last-logon-time shows the time a user last accessed their mailbox using Outlook, WebMail, or their mobile phone.

To find inactive users in Office 365, you can use either Exchange admin center or Get-MailboxStatistics PowerShell cmdlet. In both ways, you can’t export or use it to filter result based on Inactive days and mailbox type. To get Inactive days, mailbox type, license detail, administrative role, you need to use multiple command-lets like Get-Mailbox, Get-MailboxStatistics, Get-MsolUser, Get-MsolUserRole.

Don’t worry, we have written the PowerShell script to export Office 365 users’ last logon time with the below functionalities.

  1. Result can be filtered based on inactive days.
  2. Result can be filtered based on user / all mailbox type.
  3. Result can be filtered to lists never logged in mailbox alone.
  4. Export results to CSV file.
  5. Shows result with user’s administrative roles in O365 environment.
  6. The assigned licenses column will show you the user-friendly-name like ‘Office 365 Enterprise E3’ rather than ‘ENTERPRISEPACK’.
  7. The script can be executed with MFA enabled account.

The script generates a CSV file with the following attributes: User Principal Name, Display Name, Last Logon Time, Inactive Days, Mailbox Type, Assigned Licenses (with Friendly Subscription Names), Admin Roles

You can download the PowerShell script from TechNet Gallery.

To run this script, you need to be part of at least anyone of the following roles: Mail recipients, User options, View only recipients.

How can I execute a script with MFA?

To execute script with MFA enabled account, you need to mention -MFA switch during script execution.

To know more about how to connect exchange online PowerShell with MFA, refer our blog Connect Exchange Online PowerShell with MFA.

Use-cases:

Below are a few use-cases for this script. I’m sure you would find many. Please leave them in the comments below to help other Admins.

  • To filter output based on inactive days: Run the script with number of inactive days to filter.
  • To filter output based on user mailbox: Run the script with –UserMailboxOnly param to list user mailboxes alone. Without –UserMailboxOnly param, it lists all types of mailboxes including shared, room, and equipment mailbox.
  • To filter never logged in user: Run the script with –NeverLoggedInMB param to get never loggedin mailbox alone.
  • Multiple filters can be used together. For example, you can generate a list of user mailboxes who are inactive for the past 50 days.
  • You can identify idle/unused mailboxes and proceed for license reconciliation. Hence you end up saving more licenses.
  • To find Admins with inactive mailboxes: Run the script. Open the result with Excel and filter based on your desired role from the ‘Roles’ column.
  • You can schedule a script to run periodically. To schedule script
Prerequisites:
  • Windows PowerShell needs to be configured to run scripts, and by default, it isn’t. You need to configure this setting only once on your computer, not every time you connect.
  • You must have MSOnline PowerShell module installed for this script to work. If you do not have it already, please install by executing the below command-let in PowerShell.

We have bundled prerequisite commands as script. You can execute by navigating to script location and run ./Prerequisites.ps1 in PowerShell (Start PowerShell with the “Run as administrator” option).  Else you can use above command-lets.

 Steps to execute script:
  1. To run script, navigate to script location and execute ./LastLogonTimeReport.ps1. The script can be executed with/without parameters. To run with parameters, refer use-cases. Once you hit enter, you will be prompted to provide your Office 365 admin credentials.                                                                Windows PowerShell credential prompt
  2. It will take some time depends on the number of mailboxes in your environment. After script execution, it will ask whether to open the output file.Office 365 Last logon time report
  3. The exported report will look similar to below screenshot.

If you have any queries, reach us through the comment section.