June 18, 2019

Export Office 365 Users’ Real Last Logon Time Report to CSV

by Kathy Cooper

6 min read

No Comments

LastLogonTime retrieved from Get-MailboxStatistics is not accurate but most of the administrators/scripts use this to identify inactive mailboxes.

Nowadays, I see most forums have questions like “Why the Last Login Date reported by the Get-MailboxStatistics cmdlet is not correct”,” Some users’ mailbox have very recent last logon date/time even though the account has been blocked/disabled few months ago”.

To solve this problem, we have written PowerShell script to get users’ real last logon time(based on user’s last activity time). In this script, we have used mailbox’s LastUserActionTime insteadof LastLogonTIme to identify user’s last activity. Before jumping into script, let’s take a look at why we shouldn’t trust LastLogonTime attribute.

Why Getting Office 365 User Last-Logon-Time Using Get-MailboxStatistics is bad Practice?

Get-MailboxStatistics PowerShell cmdlet is so popular for getting Office 365 users’ last-logon-time. Currently, many (All ​​​​​​​) PowerShell scripts exports inaccurate LastLogonTime using Get-MailboxStatistics cmdlet. Here are some key things you should have in mind when you’re using LastLogonTime

  • Mailbox’s last logon time is not accurate because the LastLogonTime that is retrieved from Get-MailboxStatistics does not reflect the user’s last logon time alone, but also when a process accesses the mailbox.
  • Microsoft Exchange Mailbox Assistant performs background processing of mailboxes in mailbox databases on the server. It keeps on processing mailboxes even if their owners ignore them. So, the last logon time often means the last time an assistant accessed the mailbox.

Most people don’t realize that the LastLogonTime gives inaccurate data to identify the mailbox’s inactivity.

The solution for Getting the LastLogonTime

As LastLogonTime also gives the last time a mailbox assistant has accessed the mailbox, we should go for some other attribute which shows the last time a ‘real’ user accessed the mailbox.

Finally, the change many were waiting for.

It’s LastUserActionTime. Yes, Getting Office 365 Mailbox user’s last activity using LastUserActionTime is the best solution.

Still, no clarity? Please refer dedicated blog post on Why Office 365 Users’ Last logon Time Reported by Get-MailboxStatistics is inaccurate.

Note: If you don’t want to restrict the last activity within Exchange, you can utilize the Get-MgUser cmdlet to retrieve details of inactive Microsoft 365 users.

Update Dec 2023:

Microsoft introduces ‘last successful sign-in date time’ attribute to accurately identify inactive users. You can download the script to find user inactivity based on successful sign-ins.

Export Office 365 Users’ Real Last Logon Time (Last Activity Time) Report using PowerShell

If you search online for how to get Office 365 users last logon time, you will get a plethora of results, which is inaccurate and misleading. Fixing your script is the best thing but if you are not a programmer or not familiar with PowerShell, you may find a bit difficult in getting users’ real last login time. This is where we come in. We have written a PowerShell script to export Office 365 users’ last activity time(Real last logon time) report.

Script Highlights:

  • Reports the user’s activity time based on the user’s last action time(LastUserActionTime).
  • Exports result to CSV file.
  • Result can be filtered based on inactive days.
  • You can filter the result based on user/mailbox type.
  • Result can be filtered to list never logged in mailboxes alone.
  • You can filter the result based on licensed user.
  • Shows result with the user’s administrative roles in the Office 365.
  • The assigned licenses column will show you the user-friendly-name like ‘Office 365 Enterprise E3’ rather than ‘ENTERPRISEPACK’.
  • The script can be executed with MFA enabled account.
  • The script is scheduler friendly. i.e., credentials can be passed as a parameter instead of saving inside the script.

Download Script: UserLastActivityTimeReport

Sample Output:

Pre-requisites and script execution steps are available in our earlier blog. Please refer the blog.

The exported report will look similar to below screenshot.

Export Office 365 users last activity Time report

Unlock the Full Potential of this Script

This script has many built-in parameters to filter/customize the report based on your requirement. We have listed some of the primary use cases below.

Export Office 365 Inactive Mailboxes based on Inactive Days:

To get the Inactive mailboxes, you can use-InactiveDays param which will return mailboxes that are inactive for a given number of days. It also exports the inactive mailboxes to a CSV file.

 Using this param, you can get a list of mailboxes that are inactive for over ‘N’ days.

Export Office 365 User Mailbox Last Activity Time to CSV:

Often you are in the situation to get last logon time report for only user mailboxes and eliminate other types like shared mailbox, room mailbox, etc. In that case, you can use UserMailboxOnlyparam, which return user mailboxes alone in the result.

Export Never-Logged-In Office 365 Users to CSV:

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

Export Inactive Licensed Users to CSV:

To get a list of inactive licensed users, you can use -LicensedUsersOnly param. Using this report, you can regain the licenses from inactive users.

Export Last Logon Time for a list of users:

You can use –MBNamesFile param to get last activity of a mailbox from the input list called “MBIdentity.txt” and exports last activity into CSV.

To get user’s real last activity of mailbox, pass an input file with a Name/ Alias/ Distinguished Name/ Canonical DN/ Email Address/UPN/SamAccountName or GUID of mailboxes.

The MBNamesFile must follow the format below: Mailbox identity separated by new line without header.

Export User Last Activity time for a list of users

Export Office 365 Mailboxes 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 export all mailboxes in your Office 365 tenant.

Export Office 365 Inactive Mailboxes with User-Friendly Time

If you want to view users’ last action time with a friendly-time like 2 mins ago, 3 hours ago, 3 days ago, you can use -FriendlyTime param. If you use this param, it will install the PowerShell Humanizer module if the module is not installed already.

Get more Granular Report

You can use multiple filters together to get a more granular last logon time report. For example, you can generate list of licensed users who are inactive for more than 90 days.

Execute “Office 365 Users’ Real Last Logon Time Report” with MFA

To execute the script with MFA enabled account,

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

To execute the script with non-MFA account (It will prompt once to enter the credential. Using that credential, the script will create AzureAD and EXO PowerShell session), run the script with -NoMFA switch param.

Schedule “Office 365 Users’ Real Last Logon Time PowerShell 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:

LastUserActionTime doesn’t show up-to-date data. There will be some delay like a day or two. You can refer LastUserActionUpdateTime which indicates when the LastUserActionTime was updated.

Get More Detailed Users’ Last Activity Date Report:

If you want to know inactive users based on their,

  • last logon time
  • last activity time
  • last mail read
  • last mail sent
  • last mail received date
  • Exchange Inactive users
  • Teams inactive users based on last team activity date
  • SharePoint inactive users based on the SharePoint activity
  • OneDrive inactive users
  • Skype inactive users
  • Yammer inactive users

I’d suggest you try AdminDroid Microsoft 365 reporting and auditing tool.

Additionally, AdminDroid provides 1500+ pre-built reports and 20 smart visually appealing dashboards to know about your Office 365 environment at a glance. This tool provides reports on Office 365 reporting, auditing, analytics, usage statistics, security & compliance, etc.

AdminDroid Office 365 Reporting Tool AdminDroid Office 365 auditing tool

AdminDroid Office 365 user activity dashboardAdminDroid Mailbox Usage report

Besides, AdminDroid provides over 100+ reports and a handful of dashboards completely for free. It includes reports on Users, Licenses, Groups, Group Members, Devices, Login Activities, Password Changes, License Changes, and more. The free edition doesn’t have any restrictions in reporting functionalities such as customization, scheduling, and exporting. For your Azure AD reporting and auditing needs, you can download Free Office 365 reporting tool by AdminDroid and see how it works for you.

Tracking inactive users would definitely help you with better management. If you have any queries, reach us through the comment section.

Share article