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 using PowerShell?”. 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.

 

Export Office 365 User Last Logon Time Using PowerShell

The script exports Office 365 users’ last-logon-time to CSV with the following attributes: User Principal Name, Display Name, Last Logon Time,Creation Time, Inactive Days, Mailbox Type, Assigned Licenses (Friendly Name), Admin Roles.

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.

 

You can download the PowerShell script from TechNet Gallery.

To run ‘Export Office 365 users last logon time to CSV using PowerShell’ 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 ‘Export Office 365 User last login report’ script. I’m sure you would find many. Please leave them in the comments below to help other Admins.

Export Inactive Mailboxes in Office 365 to CSV using PowerShell

To get Inactive mailboxes, you can use -InactiveDays param which will returns mailboxes that are inactive for given days. Also exports Inactive mailbox to CSV file.

 Using this param, you can get a list of users who haven’t logged in for over ‘N’ days

 
Export Office 365 User Mailbox Last Logon Time to CSV

Often you are in the situation to get last logon time for only user mailboxes and eliminates other types like shared mailbox, room mailbox. In that case, you can use -UserMailboxOnly param which return user mailboxes alone in the results.

 
Export Never-Logged-In Office 365 Users to CSV

Run the script with -NeverLoggedInMB param to get never logged-in mailbox alone. Using this report, you can identify idle/unused mailboxes and proceed for license reconciliation. Hence you end up saving more licenses.

 
Export Office 365 Mailbox to CSV

You can export Office 365 Mailbox with attributes like Assigned License, Admin Roles,Last logon time, etc. To export Office 365 Mailbox report, execute the script without any param. This will exports all mailbox in Office 365 tenant.

 

Export Inactive Users in Office 365 to CSV using PowerShell

Multiple filters can be used together to get more granular report. For example, you can generate a list of user mailboxes who are inactive for the past 50 days.

To find inactive admins: Run the script. Open the result with Excel and filter based on your desired role from the ‘Roles’ column.

 

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.

Export office 365 users last logon time to CSV

 

How can I Schedule this script?

You can schedule this script in task scheduler by explicitly mentioning the credential.

To know more about scheduling Powershell script, refer our blog: Schedule PowerShell script using Task Scheduler.

 

Note:

As LastLogonTime attribute also updated by some background tasks like Mailbox Assistant, this report(LastLogonTime retrieved from Get-MailboxStatistics) might give inaccurate data. If you want to get users’ last logon time based on ‘real’ user’s last activity, you can refer Export Office 365 Users Real Last Logon Time Report to CSV blog.

 

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