Audit Sign-in Failures in Microsoft 365 with Entra Workbooks  

Among all, one of the most crucial tasks admins need to do is keeping tabs on failed login attempts in Microsoft 365. 💯Why? Because it might be a sneaky move by attackers trying to get into Microsoft 365 user accounts. Or maybe it’s just strict Conditional Access policies in Entra causing login issues in the organization. As a whole, it could signal bigger security problems in Microsoft 365. 🙄 So, you’ve got to figure out what’s causing these login failures in Office 365 and fix it ASAP. 

Well, to audit sign-in failures easily, Microsoft gives you many methods. 😎Let’s dig into them and decide which one is the best to analyze Office 365 users’ login failures. But before that, you need to have an overview of the reasons causing the unsuccessful sign-ins. So, let’s see it first. 👇 

Reason for Sign-in Failures in Microsoft 365 

Here are some of the reasons for sign-in failures in Microsoft 365. 

✅Brute force attacks or password-related attacks: Attackers may attempt to compromise user accounts through brute force attacks or by password spray attacks. Each unsuccessful login attempt by attackers contributes to the count of sign-in failures in Microsoft 365. 

✅ Expired passwords or failed MFA challenges: Users may face login failures due to expired passwords or failure to pass multi-factor authentication challenges. Additionally, failing to meet conditions set by Conditional Access policies, such as device or location requirements, can also result in login failures. 

✅ Connectivity issues or user cancelling sign-in process: Login failures can occur due to connectivity issues, such as network disruptions or server unavailability. Users may also inadvertently cancel the sign-in process, leading to failed login attempts in Microsoft 365. 

How to Audit Sign-in Failures in Microsoft 365? 

Here are some methods to understand failed login audit logs in Microsoft 365: 

📍 Audit Sign-in Failures Using Microsoft 365 Audit Logs:  

The Security and Compliance center acts as a one-stop shop for admins to monitor all login attempts across the Microsoft 365 tenant. You can check this Office 365 audit logs and get the history of failed attempts. 

Drawback:  

👎While you can use filters to identify unsuccessful login attempts, exporting the Office 365 users’ failed login attempts report is not possible. 

📍 Find Login Failures in Microsoft 365 with PowerShell:  

Another method is to track user login failures using PowerShell. You can use the cmdlet “Search-UnifiedAuditLog” to audit Microsoft 365 user login history. 

Drawbacks: 

👎 The “Search-UnifiedAuditLog” cmdlet retrieves a maximum of 5000 records only.  
👎 Additionally, it exports both successful and failed login attempts together, requiring filter parameters to isolate failed sign-ins. 

📍 Audit Sign-in Logs in Microsoft Entra ID:  

You can also track user sign-in failures by monitoring Azure AD sign-in logs. These logs can be accessed in the Microsoft Entra admin center →   Monitoring & health → Sign-in logs. 

Drawback:  

👎 While you can view sign-in error codes, specific details such as the number of failed sign-in attempts for a particular user or top sign-in failed users may not be available. 

What is the major drawback of these methods? 

The lack of visuals and in-depth insights into user sign-in failures is a significant limitation. However, this limitation can be addressed using the “Entra workbook”. 

The Entra workbook helps you to build an interactive personalized dashboard of Entra ID sign-in and audit logs. You can get eye-catching charts and insightful reports all in one simplified view of Entra workbooks. 

Entra workbooks bring you a set of built-in templates like, 

• Authentication prompts analysis workbook  
• Conditional Access gap analyzer workbook  
• Sign-in analysis workbook  
• Cross-tenant access settings, and more. 

One of the most helpful built-in templates for troubleshooting login issues is the “Sign-in failure analysis”. Let’s explore that in detail. 

Sign-in Failure Analysis Workbook in Entra ID 

This sign-in failure analysis workbook helps you diagnose and understand the reasons behind user sign-in failures in Microsoft 365. It helps identify patterns such as whose sign-in failed, the frequency of login failures, and the reasons for the unsuccessful attempt clearly. By identifying the root cause, admins can take corrective actions to ensure smooth user access. 

To access the sign-in failure analysis workbook in Entra ID, follow the steps below. 

1. Log in to Microsoft Entra admin center.  
2. Navigate to the Monitoring & health section and select “Workbooks” options.  
3. Select the “Sign-in Failure Analysis” workbook from the Troubleshoot section. 

This workbook contains three sections. 

1. Total summary of sign-ins in Microsoft 365. 
2. Summary of failed sign-in attempts. 
3. Summary of interrupted sign-ins in Office 365. 

1. Monitor Login Activity in Microsoft 365 

This section provides a comprehensive overview of the sign-in logs for users in Microsoft 365. Here’s what you can find: 

• All Sign-ins: Displays the total count of all logins attempts in Microsoft 365. 
• Success: Indicates the total number of users who successfully signed in to Microsoft 365. 
• Pending User Action: Shows the total count of pending sign-in attempts, interrupted by password reset, MFA processes. 
• Failure: Gives the count of unsuccessful sign-in attempts in Microsoft 365. 

2. Summary of Top Sign-in Errors in Microsoft 365 

The sign-in analysis workbook provides comprehensive insights beyond just tallying unsuccessful attempts; it facilitates the identification of primary failure reasons.  

In the ‘Summary of Top Signs with Errors’ section, you can gain access to a detailed breakdown of Entra ID sign-in error codes, including the failure reason and respective counts. This feature empowers you to swiftly pinpoint common issues like below by referencing the top sign-in error codes. 

• Sign-ins using invalid username or password. 
• Conditional Access policy requires a compliant device. 
• Access has been blocked by Conditional Access policies.  
• Access has been blocked by security defaults.  

Once you click on the specific error code listed in the “Summary of top error” section, it dynamically you the relevant users’ sign-in in the parallel part with error codes. This allows you to view the frequent login failed users and the reason behind it. Moreover, you can also retrieve the timing information associated with these sign-in failures, providing insights into when these incidents occurred. 

Is it possible to get additional details on the failed sign-ins of the user? 

Yes, it’s possible. Continuing from the previous section, if you select the hyperlinked error code, you can get into a ‘details’ part showing up comprehensive Microsoft 365 logon failure report. 

This includes stuff like whether Conditional Access policies were involved, where the sign-in attempt came from (like the state and city), and the reason for the failure (like the error code description). With all this info, admins can really dig into what went wrong and take the right steps to fix things! 

3. Summary of Interrupted Sign-ins in Microsoft 365 

Besides keeping tabs on failed sign-in attempts, the identity workbook also sheds light on interrupted sign-ins in Microsoft 365. This feature offers valuable insights into sign-in attempts that were halted midway. 

Similarly to the previous section, you can pinpoint the primary reasons for interruptions by referring to the corresponding error codes. Some of the common interrupted sign-in reasons and their error codes are, 

Error Code	Error Description
50140	Sign-in interrupted due to the “Keep me signed in” prompt.
51006	Forced users to reauthenticate due to insufficient authentication.
50059	Tenant information was not found in either the request or implied by any provided credentials.
65001	The user or administrator hasn’t consented to use the application with ID X.
52004	The user has not provided consent for access to LinkedIn resources.
50055	The password expired, and the session ended.
50144	The user’s Active Directory password has expired (on-premises password).
50072	The user needs to enroll for second-factor authentication.
50074	Strong authentication is required, and the user did not pass the MFA challenge.
16000	The user account doesn’t exist in the tenant and can’t access the application.
50127	The user needs to install a broker app to gain access to this content.
50125	Sign-in was interrupted because of a password reset or password registration entry.
50129	Device is not workspace joined.
50143	Session is invalid because the user tenant doesn’t match the domain hint due to different resources.
81010	Seamless SSO failed because the user’s Kerberos ticket has expired or is invalid.
81012	The user trying to sign in to Microsoft Entra ID is different from the user signed into the device.
50076	Retry with a new authorized request for the resource due to the user moved to a new location.
50097	Device authentication is required.

Furthermore, you can check the number of interrupted sign-ins for the respective error codes in the “Interrupt Count” column. 

Next Step: Identifying Users with Interrupted Sign-ins 

To identify users who experienced interrupted sign-ins, click on the error code row in this section. A neighboring section will automatically populate with details of the user and the sign-in time associated with the respective error code. Like the previous section, administrators can get more details on interrupted sign-in users by clicking the error code link. 

Create a New Microsoft Entra Workbook for Sign-in failure Analysis 

If the pre-built sign-in failure analysis workbook doesn’t meet your specific needs, you can edit it to extract the desired Entra insights. Alternatively, you can create a new workbook in Entra ID, tailored to your requirements. 

In a custom workbook, admins can utilize the six main components to craft a personalized interactive dashboard. For example, they can employ the “Query” component to query the log analytics workspace, filtering failed/interrupted logins for a single user and extracting relevant information such as error codes and failure reasons. 

Here’s an example query: 

SigninLogs  
| where UserDisplayName == '<Username>' 
| extend ErrorCode = tostring(Status.errorCode)  
| extend FailureReason = tostring(Status.failureReason)  
| where ErrorCode != "0" // Exclude successful sign-ins 
| summarize errCount = count() by ErrorCode, tostring(FailureReason) 
| sort by errCount 
| project ['❌ Error Code'] = ErrorCode, ['Reason']= FailureReason, ['Error Count'] = toint(errCount)

This query retrieves data on failed or interrupted sign-in attempts for a specific user (‘Authy’), categorizing them by error code and failure reason.  

Create Workbooks and Drill Down Essential Sign-in Logs Now! 

Admins can use this data in conjunction with other components in the Entra workbook to create a personalized dashboard tailored to their needs. Yes, like retrieving logs using KQL (query – component), you can use other components to add explanations, links to useful resources for guidance, or include visuals for extra clarity – it’s all up to you!  

So, like the above example, create your own Entra workbook and get specific insights like below at ease.  

• Admin login failures in Microsoft 365 
• Failed sign-in attempts occurred in the last 24 hours 
• Office 365 users who failed to pass MFA 
• Daily login failure count 

Based on the error codes, take further actions like tweaking the strict Conditional Access policies, MFA requirements only when it is necessary. Finally, I hope this blog brings you information into how to audit sign-in failures in Microsoft 365 with workbook. Furthermore, if you have any queries regarding this workbook, you can reach out to us in the comment section. Also, another set of workbooks is waiting for you in the queue, so stay tuned!