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. 

 

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. 

 

You can Download the script from TechNet Gallery 

 

Script Highlights: 
  • 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. 
  • 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.

 

Script Execution: 

Since the script supports both MFA enabled account and non-MFA account, it requires a different approach for each method. 

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

For non-MFA account, you can run the script as you run normally. 

 

How to: Execute Office 365 Users’ Logon History PowerShell Script with MFA: 

To execute the 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 to our blog Connect Exchange Online PowerShell with MFA. 

 

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 admin@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. 

 

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.