Export Office 365 Users’ Logon History Report to CSV Using PowerShell

Logons are the one common activity in most attack patterns. Tracking Office 365 user’s login activities is a crucial one in detecting potential security breaches and suspicious behavior.  

To get Office 365 User logon history, you can use either Office 365 Security and Compliance or PowerShell. But in the Security and Compliance Center, you can get a history of successful login attempts alone. It won’t track failed Office 365 user’s login attempts. Even if you use filters to get failed login attempts, you can’t export those failed login attempts alone. 

To audit Office 365 user’s all successful and failed logon attempts, PowerShell is the best solution. PowerShell cmdlet Search-UnifiedAuditLog is used to track all events in a specified range. You need to process all events to filter out successful login attempts and failed login attempts. Getting Audit logs using the Search-UnifiedAuditLog is not an easy task. It has the following challenges. 

Note: If you are relying on the logon history report to check users’ inactivity, we recommend using our dedicated script for generating an inactive users report. This script provides comprehensive details about both interactive and non-interactive sign-ins, ensuring a more comprehensive analysis of user activity.

 

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 have worked for you and made a user-friendly PowerShell script – Office 365 users’ login history report, which contains both successful and failed login attempts. Also, the script has more advanced filtering options to get successful login attempts, failed login attempts, login history of specific user or a list of users, login history within a specific period, etc. 

 

Download Script: O365UserLoginHistory.ps1

 

Script Highlights: 
  • The script uses modern authentication to connect to Exchange Online.
  • Allows you to filter the result based on successful and failed logon attempts. 
  • The exported report has IP addresses from where your office 365 users are login. 
  • This script can be executed with MFA enabled account. 
  • You can export the report to choose either “All Office 365 users’ login attempts” or “Specific Office user’s logon attempts”. 
  • By using advanced filtering options, you can export “Office 365 users Sign-in report” and “Suspicious login report”. 
  • Exports report result to CSV. 
  • Automatically installs the EXO V2 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. 
  • Our Logon history report tracks login events in AzureActiveDirectory (UserLoggedInUserLoginFailed)ExchangeOnline (MailboxLogin) and MicrosoftTeams (TeamsSessionStarted). 

 

Sample Output:

The exported login history report looks similar below screenshot

Export Office 365 Logon history report

Note: Since ‘Microsoft Teams’ login doesn’t have an IP address and Result status column, it shown as empty. Only successful Teams logins are captured in Search-UnifiedAuditLog. So, if the ‘Microsoft Teams’ logins are available in the exported report, consider it as successful login attempts.

 

How to: Execute Office 365 Users’ Login History PowerShell Script: 

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

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

 

Unlock Full Potential of “Export O365 Users Login History Report” Script: 

  • Export Office 365 users’ login history for the past 90 days 
  • Get Office 365 users’ logon history within a particular interval 
  • Export specific Office 365 user’s login history 
  • List Office 365 login history for specific users 
  • Export Office 365 users failed login attempts report 
  • Export Office 365 users’ sign-in report (successful login attempts) 
  • By using multiple filtering params, a more granular report can be exported. 
  • Schedule Office 365 users’ login history PowerShell script

 

Export Office 365 Users’ Logon History for Past 90 Days: 

Since Search-UnifiedAuditLog has past 90 days data, we can get a maximum of last 90 days login attempts using our script. To export Office 365 users past 90 days login attempts, run the script as mentioned below. 

 

Export Office 365 users’ Login History within a given interval: 

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, 2019, to Nov 25, 2019. 

 

Export Single User’s Login History Report: 

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

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

 

Export Office 365 Login History for Specific Users: 

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. 

 

Export Office 365 Users’ Failed Login Attempts Report: 

 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 Office 365 login IP address, you can track from where the user/attacker trying to login to Office 365. 

 

Export Office 365 Users’ Sign-In Report: 

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

 

Export Office 365 Users’ Logon History Report with Multiple Filters: 

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. 

 

Schedule Office 365 Users’ Login History PowerShell Script: 

Since Search-UnifiedAuditLog has the past 90 days of data, you may require old audit logs for analysis. In that case, scheduling plays a significant role.  

You can use the Task Scheduler to ‘automate O365 users’ login history PowerShell script’. 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. 

 

Get More Detailed Office 365 Login Report:

To get more detailed office 365 login reports on

    • 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

you can take a look at 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 user activity dashboardusage_adoption_dashboardOffice 365 email traffic reportPowerShell free Office 365 Reporting Tool

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 helps for you.

 

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.