Export Microsoft 365 Users’ Logon History Report Using PowerShell

Logon patterns play a major role in many attacks. Tracking Microsoft 365 users’ login activities is essential for detecting potential security breaches and suspicious behavior. So, how do you generate a logon history report in Microsoft 365? Lets’ dive into detail.

To view M365 users’ logon history, you have two main options: using the Microsoft 365 admin portals or PowerShell. However, it’s important to note the limitations of each method.

M365 admin portals: In the Microsoft 365 admin portal, you can access a history of successful login attempts, but it doesn’t track failed login attempts. Even if you apply filters to retrieve failed login attempts, exporting them is not possible. Additionally, the sign-in logs in the Entra admin center are limited to the past 30 days.

PowerShell: For a comprehensive audit of both successful and failed logon attempts over a longer period, PowerShell is the preferred solution. The PowerShell cmdlet Search-UnifiedAuditLog allows you to track all events within a specified range. However, filtering out successful and failed login attempts from the audit logs can be challenging. There are several challenges associated with using Search-UnifiedAuditLog, including:

Challenges in using Search-UnifiedAuditLog:

• You can get a maximum of 5000 records for each Search-UnifiedAuditLog call. It can be achieved by using ResultSize param. Even if you use ResultSize, you can’t guarantee, the specified range has how many records. You need to call Search-UnifiedAuditLog again and again with SessionId until you get all records.
• Even if you use SessionId, you can get 50,000 records for a given period. If it exceeds the limit, you can’t retrieve all records.
• After getting a result, you need to filter out users’ login attempts (both success and failure login attempts) from tons of audit entry.

Don’t worry! We’ve got you covered. We’ve developed a user-friendly PowerShell script to export Microsoft 365 users’ login activities across various workloads, including Azure Active Directory, Exchange Online, Microsoft Teams, and SharePoint Online.

Download Script: O365UserLoginHistory.ps1

Script Highlights:

• The script uses modern authentication to connect to Exchange Online.
• Allows you filter successful and failed logon attempts separately.
• The exported report has IP addresses to track user’s login location.
• You can export the report to choose either “All users” or “Specific user(s)”.
• This script can be executed with MFA enabled account.
• Exports report result to CSV.
• Automatically installs the EXO module (if not installed already) upon your confirmation.
• This script is scheduler friendly. I.e., credentials can be passed as a parameter instead of saving inside the script.
• Supports Certificate-based Authentication(CBA).
• Tracks login events in various Microsoft 365 workloads, including Azure Active Directory, Exchange Online, SharePoint Online and Microsoft Teams .

Sample Output:

The exported login history report looks similar below screenshot

Note: Only successful logins are captured for some workloads in Search-UnifiedAuditLog. Therefore, if the ‘Result status’ is shown as empty, consider them as successful login attempts.

Method 1: Execute the script with a MFA or non-MFA account

The exported report will contain M365 users’ login activities for the past 90 days.

Method 2: Execute the script by explicitly mentioning credentials (Scheduler friendly).

You can use this method to automate report generation using Task Scheduler.

Method 3: Execute the script using Certificate Based Authentication.

You can use either a CA certificate or create a self-signed SSL certificate

• Export Office 365 users’ login history for the past 90 days
• Get M365 users’ login activities for custom period
• Export specific Office 365 user’s login history
• View login history for a list of users
• Export Office 365 users failed login attempts report
• Export Office 365 users’ sign-in report (successful login attempts)
• More granular login history report
• Schedule Office 365 users’ login history report

The Search-UnifiedAuditLog can retrieve audit logs for up to 180 days. By default, the script can retrieve data from the past 90 days. However, you can customize the date using specialized parameters. If you need to retrieve data for more than 90 days, please reach out to us through the comment section, and we will assist you accordingly.

But tracking users’ last login time using this result is difficult. In such case, you can utilize inactive users report to find last interactive and non-interactive sign-ins. Also, you can track guest users last login time separately.

To get users’ login attempts within a specific period, you need to mention start and end times during script execution. It can be achieved by passing –StartDate and –EndDate params.

Date format should follow the MM/DD/YY format. The above script will export all Office 365 users’ login attempts from Nov 20, 2023, to Nov 25, 2023.

To export a specific user’s logon history, execute the script with –UserName param.

The exported report contains login history of john@contoso.com.

If you want to get Office 365 Login history for multiple users, you can pass usernames using –UserName param as comma separated values.

The exported report contains login history of admin and hr user.

Office 365 users’ failed login attempts report is most useful in analyzing suspicious activities. To export failed logon attempts, execute the script with –Failed switch param.

By default, it will return the past 90 days of audit records. If you want to narrow down the report, you can mention time interval using –StartDate and –EndDate params.

Using Microsoft 365 login IP address, you can track from where the user/attacker trying to login to Office 365.

Office 365 users’ sign-in report contains users’ successful login into Office 365. To export Office 365 users’ successful sign-in attempts, you need to run the script with –Success switch param.

You can also utilize M365 users’ last successful sign-in time to identify inactive users.

By default, this PowerShell script supports multiple advanced filtering options. You can use one or more filters during execution time. I have listed some use-cases here.

• To export specific O365 user’s all successful and failed login attempts that performed last week, you can execute the script as follows.

• To export all Office 365 users’ failed login attempts performed in specific hours.

Additionally, you can easily filter out unsuccessful login attempts in Microsoft 365 using the “Time Range” filter of the sign-in failure analysis workbook.

Since Search-UnifiedAuditLog has data for limited period, you may require old audit logs for analysis. In that case, scheduling plays a significant role.

You can use the Task Scheduler to automate Microsoft 365 login history report. If you schedule the script to run once in 90 days, you can access the exported report at any time you want. So, you can store years of audit logs further analysis.

While scheduling, you can pass credential using –AdminName and –Password params.

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

If you need more customized and advanced reports such as first & last login time of the day, weekly login summary, monthly login summary, etc, you can take a look at Microsoft 365 sign-in reports by AdminDroid

the best part is, these reports are Free. AdminDroid’s Free Microsoft 365 reporting tool offers 150+ pre-built reports to efficiently manager users, groups & membership, licenses, user sign-ins, password changes, license changes, managers & direct reports, etc.

AdminDroid provides 50+ sign-in reports by covering various sign-in use aspects. Specifically,

• • User Logins
    • All user logins
    • Failed user logins
    • Successful user login
    • User’s last logon time
    • Users’ first logon time of a day
    • Users’ monthly login count summary
  • Security
    • Admins logins
    • Guest logins
    • Risky login attempts
    • Failed to pass MFA challenge
    • Legacy/Basic authentication login attempts
  • Office 365 service based logins
    • Outlook login history
    • Mailbox PowerShell logins
    • Teams logins
    • Teams external user login activities

Additionally, AdminDroid provides 1900+ pre-built reports and 30 smart 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.

Download AdminDroid Microsoft 365 management tool now and experience the power of simplified administration!

I hope this blog is useful in analyzing successful and suspicious login attempts. If you have any queries or requirements, share it with us through the comment section.