Lesson 23 — Microsoft Entra Log Analytics and KQL

Learning objectives

After completing this lesson, you should be able to investigate Microsoft Entra activity using Log Analytics and KQL.

• Identify the main Microsoft Entra Log Analytics tables.
• Use core KQL operators to filter, shape and summarize identity data.
• Investigate failed and risky user sign-ins.
• Review audit activity and administrative changes.
• Query service principal and managed identity authentication.
• Build clear, reusable investigation queries.

What you need before starting

Microsoft Entra activity logs must already be routed to a Log Analytics workspace through Diagnostic Settings or the Microsoft Entra data connector for Microsoft Sentinel.

You also need permission to read the workspace and query the relevant tables.

Start with a clear question

A query should answer a specific operational question, such as:

• Why was this user blocked?
• Which applications generated the most failures?
• Who changed the Conditional Access policy?
• Which service principal used this IP address?

Start with a small time range

Time filtering reduces noise, improves performance and helps prevent unrelated events from distorting the investigation.

The where operator

where filters rows that meet a condition.

The case-insensitive equality operator =~ is helpful when matching text values exactly.

The project operator

project selects the fields displayed in the result.

The order by operator

Sort the newest or most important evidence first.

The take operator

take returns a limited sample and is useful while exploring an unfamiliar table.

The distinct operator

Use distinct to identify the unique values present in a field.

The summarize operator

summarize groups events and calculates totals.

The extend operator

extend creates a calculated field without removing the original columns.

Latest user sign-ins

Failed user sign-ins

Failures by user

Failures by application

Conditional Access results

Open the original sign-in event when policy-level evaluation details are needed.

Risky sign-ins

Sign-ins from new countries

Device information

Device details are stored in a dynamic field and can be extracted with tostring().

Authentication requirement

This provides a useful overview, but detailed MFA troubleshooting may require the AuthenticationDetails field and the original event.

Failed audit operations

Search for policy changes

Service principal sign-ins

Service principal failures

Managed identity activity

Non-interactive user sign-ins

Build reusable queries with let

The let statement keeps important values at the top of the query.

Reduce noise early

Filter by time, user, application or result near the beginning of the query. Project only the fields needed to answer the question.

This makes the query easier to read and usually more efficient.

Do not assume every table has identical fields

Interactive, non-interactive, service principal and managed identity tables have related but different schemas.

Use take 10, the schema pane or Microsoft table reference before reusing a query across tables.

Know when to return to the portal

KQL is excellent for finding patterns and isolating events. The Microsoft Entra sign-in details experience may still provide the clearest policy evaluation, authentication step and diagnostic explanation for a single event.

Common KQL mistakes

• Querying the wrong sign-in table.
• Using an excessively large time range.
• Assuming every non-zero result is malicious.
• Ignoring background and workload identity activity.
• Displaying every column instead of projecting useful evidence.
• Failing to validate summarized findings against raw events.

Investigation checklist

• State the question before writing the query.
• Choose the table that represents the identity type.
• Limit the time range.
• Filter early.
• Project useful fields.
• Summarize only after reviewing raw events.
• Preserve correlation IDs and timestamps.
• Document the final evidence and conclusion.

Security best practices

• Save validated investigation queries in a controlled repository.
• Use meaningful query names and comments.
• Protect access to the Log Analytics workspace.
• Review ingestion gaps before relying on a negative result.
• Monitor both human and workload identities.
• Convert repeatable high-value hunts into analytics or scheduled detections where appropriate.