Microsoft 365 security isn’t just about configuring MFA, Conditional Access policies, or enabling Entra smart lockout. 💯 It also depends on how effectively you monitor Azure AD user sign-in logs in Microsoft 365. Microsoft 365 offers three ways to audit risky sign-ins: Entra ID sign-in logs, PowerShell, and unified audit logs. 💡

But for in-depth analysis including AD FS sign-ins and better visualized sign-in data, Entra workbook joins the list. 😎 Let’s see how it helps you to effectively do sign-in analysis in Microsoft 365.

What is Entra Workbook?

Workbooks help you monitor Entra ID sign-in and audit logs with a personalized interactive dashboard. You can gain eye-catching, insight-rich, comprehensive reports on Entra data with workbooks.

Microsoft provides you with a bundle of built-in workbook templates, where you don’t need to create a new one from scratch. Here are some of the readymade workbooks available in Microsoft Entra ID.

  1. Authentication prompts analysis workbook
  2. Conditional Access gap analyzer workbook
  3. Multifactor authentication gaps workbook
  4. Risk-based access policies workbook
  5. Sign-in failure analysis workbook
  6. Conditional access insights and reporting workbook, and more.

One such workbook, which is now in preview, is the – “Sign-in analysis” workbook. This is your assistance workbook that helps you to track unusual sign-in activity in Microsoft 365. Let’s see about it in detail below.

Microsoft 365 Sign-in Analysis Workbook in Entra ID

The sign-in analysis workbook provides comprehensive insights into sign-ins for both Azure AD and AD FS. It offers detailed breakdowns based on user location, time, and device, and even helps identify reasons for failed sign-ins.

Monitoring Microsoft 365 sign-in activity is just easy now!

To access and see the sign-in activity of Microsoft 365 users using this workbook, follow the steps below.

  1. Sign in to the Microsoft Entra admin center.
  2. Navigate to the “Monitoring & health” section and select “Workbooks”.
  3. Now, select the “Sign-In Analysis (Preview)” workbook under the Usage section.

In the very first of the workbook, you will be given two options:

  • Show help: You can use this option if you want guiding sentences for better navigation in the workbook.
  • AD FS Data Enabled: As this workbook covers both AAD and ADFS sign-ins, you can decide which data should be shown based on your requirements. By default, it is set to “No“, showing only Azure AD sign-in data. If you want a combined view of both AAD and AD FS, you can toggle it to “Yes“.

But note the below:

  • To see AAD sign-in data, you should configure the diagnostics settings to export the “SignInLogs” setting to the log analytics workspace.
  • At the same time, if you need to analyze AD FS data, you need to export the “ADFSSignInLogsfrom diagnostics settings to log analytics workspace. Also, make sure Connect Health agent for AD FS is installed.

The fact you need to know: The workbook uses the “AD FS Data Enabled” parameter with two values, “4” and “5.” (You can get it by looking into the “Advanced Editor” of this parameter). When the parameter is toggled to Yes, ➔ value is set to 4 ➔ this means you can get a combined view of AAD and AD FS sign-in data. If you want the opposite, like analyzing only AAD specifically, the value will be set to 5 when you select No in the toggle. This selection determines how the reports and insights are populated.

AD FS parameter value to monitor Microsoft 365 sign-ins
AD FS parameter value to monitor Microsoft 365 sign-ins

For clarity, I’ve set the parameter to “No,” which means the workbook will display only Azure AD sign-in data. After the two options, the workbook will be shown in two tabs:

📢Audit Sign-ins Activity in Microsoft 365

The “General analysis” section provides a comprehensive overview of all successful, pending, and failed user sign-ins on Microsoft 365. Additionally, it helps you break down sign-ins by device, time, and location, offering detailed insights into user sign-in activity.

It consists of three sections:

1. Monitor Login Activity in Microsoft 365

This sign-in summary section displays the exact count of successful, failed, and interrupted sign-ins for Azure AD, providing an overview of your organization’s sign-in status. This allows you to quickly assess whether your organization is functioning as expected, without any risky sign-ins or failure issues.

Total summary of sign-ins in Microsoft 365
Total summary of sign-ins in Microsoft 365

By clicking the respective tiles, the “Summary of Sign-ins by Time” section will show you a count of sign-ins for a particular status on specific dates.

Sign-in activity by time to monitor Microsoft 365 sign-ins
Sign-in activity by time to monitor Microsoft 365 sign-ins

2. Check Sign-in Locations in Microsoft 365

Based on the sign-in summary tile you have selected; you can view the counts of the sign-in status classified based on country. For example, if you select the tile “Failed”, you can view the failed sign-in counts classified by country.

  • This gives you an easy way to spot sign-ins from unknown locations. This information can be used to create location-based Conditional Access policies that target these unknown locations.
Sign-in by location
Sign-in by location

A parallel section provides a detailed breakdown by both country and city, including various metrics such as total sign-ins, failures, and interruptions.

Location sign-in details
Location sign-in details

3. Analyze Microsoft 365 Sign-ins Based on Devices

In this section, you can view sign-in counts based on the devices used. This report is useful to audit the devices users utilize during sign-ins. It is particularly helpful for analyzing the reasons behind sign-in failures or interruptions.

For instance, if you have configured device-based Conditional Access policies that might block access or require additional information, you can troubleshoot these issues by examining the devices (OS) involved.

Sign-in by devices
Sign-in by devices

Once you click the respective device tile from the above section, you can view the detailed device details displayed in the parallel section. This includes insights like username, sign-in status, and sign-in time.

Device Sign-in details
Device Sign-in details

📢Audit Sign-in Failures in Microsoft 365

As an admin, it is crucial to stay vigilant about sign-in failures in Microsoft 365. These failures could indicate issues such as brute force attacks, expired passwords, MFA challenges, problems caused by Conditional Access policies, and more. To help you stay informed about sign-in failures, the “Error analysis” section provides detailed insights.

It consists of three sections:

1. Summary of Failed Sign-in Attempts in Microsoft 365

  • Total Sign-in Failures in Microsoft 365-This subsection shows the total count of failed sign-in attempts within a specified time range.
    • This summary section helps you get the security status of the organization. A higher number of failed sign-ins indicates a greater security vulnerability.
  • Admins can use this information to take further security actions, such as reviewing existing policies and configurations to grab the failure reasons.
    • Identify Microsoft 365 Applications with Bad Password Attempts- The subsection highlights the top applications where users experienced bad password attempts, which are often due to brute force attacks.
  • By identifying these applications, you can determine if your organization is targeted by such attacks and take necessary measures to protect vulnerable applications.

2. Top Sign-in Errors by Users and IP

Understanding overall sign-in failure counts is not enough to troubleshoot and remediate issues effectively. This section provides details on the top users experiencing sign-in failures and the errors based on IP addresses. By identifying risky users and sign-ins, you can configure Conditional Access policies and other security measures to prevent data breaches.

At the beginning of this workbook, you will see an option “View Errors By.” This allows you to decide whether to view the top sign-in errors based on users or IP addresses. Once selected, the workbook displays detailed information such as error codes, descriptions, respective users, locations, and total failure counts.

Top sign-in errors by IP and user
Top sign-in errors by IP and user

3. Audit AD FS Sign-in Failures in Microsoft 365

This section focuses on analyzing and investigating “Account locked” failures in AD FS sign-in logs. It filters the data to highlight these specific failures, sorts them by time, and displays relevant information such as username, alternate sign-in name, and IP address for each attempt.

  • This detailed view helps you pinpoint and address the causes of account lockouts like brute force or password spray attacks.
  • Also, you can identify the risky users in Microsoft 365 and take proactive steps like configuring MFA and risk-based access policies.

Edit or Create a New Workbook in Entra ID

Microsoft is not handcuffing you with the pre-built templates alone! It allows you to create your own Entra workbook with 5 main components. Yes, you can use the Text, Query, Parameter, and Groups components to customize the workbook based on your requirements. For example, you can create a workbook to

  • Export Office 365 users’ failed login attempts
  • View the sign-in log for specific Microsoft 365 users
  • Get Azure sign-in logs older than 30 days
  • Check the recent sign-in activity of Microsoft 365 admins
  • Get the login history of all Microsoft 365 users.

That’s it! A complete Microsoft 365 login analyzer is just lying in Entra ID! Make use of it to check, assess login activity on Microsoft 365, and troubleshoot Microsoft sign-in failures if any! I hope this workbook helps you track user logins in Microsoft 365 effectively. Furthermore, if you have any queries regarding examining this sign-in analytics report, you can reach us through the comments section. We are happy to help!