Agent Foskett Academy • Microsoft Sentinel • Module 7 • Lesson 62

Lesson 62 — Building Advanced KQL Hunting Queries

Build reusable, evidence-driven KQL hunts with time-bounded filtering, baselines, joins, time-series analysis and carefully validated pivots.

Module 7 — Advanced Threat Hunting and Investigation • Advanced KQL
Agent Foskett advanced Microsoft Sentinel KQL threat hunting
What you will learn

Turn a hunting hypothesis into a robust, explainable KQL investigation.

✓ Scope queries and check coverage
✓ Build behavioural baselines
✓ Join identity and audit evidence
✓ Investigate time-series deviations

Agent Foskett case file — The query that accused everyone

A first-pass query flags hundreds of accounts as suspicious. Agent Foskett suspects the logic, not hundreds of simultaneous intrusions. The case is about building defensible KQL that separates a useful lead from ordinary activity.

Learning objectives

  • Build parameterised and time-bounded hunting queries.
  • Establish telemetry completeness before interpreting anomalies.
  • Use summarize, join, arg_max() and make-series appropriately.
  • Validate baseline deviations and identity pivots.
  • Explain query limits, performance and evidence confidence.

Prerequisites and query surface

Use the Microsoft Sentinel Logs experience backed by a Log Analytics workspace with Microsoft Entra SigninLogs. The audit join later also requires AuditLogs. These examples are Log Analytics KQL, not Microsoft Defender XDR Advanced Hunting queries. Run them only against authorised telemetry.

Hunting logic starts with a question

Hypothesis: “An account is authenticating from more source IPs than usual and may have related administrative changes.” Alternative explanations include VPN rotation, approved service migration and shared infrastructure. Write both the hypothesis and competing explanations before querying.

The advanced KQL workflow

QUESTION → DATA COVERAGE → SCOPED FILTERS → BASELINE → CANDIDATES → CORRELATION → TIMELINE → VALIDATION → HUNT RECORD

Step 1 — Set explicit parameters

Use let variables for time windows, thresholds and target entities. This makes hunts repeatable and makes assumptions visible to reviewers. Never hide hard-coded thresholds in unexplained query fragments.

Step 2 — Verify ingestion and field coverage

Before trusting results, verify event counts and data freshness. Check whether identity, IP and application fields are populated. A missing table, field or time period means the hunt cannot fully answer its hypothesis.

KQL editor 1 — Baseline account and IP activity

Sentinel Logs — seven-day identity baseline
  1. 1
  2. 2
  3. 3
  4. 4
  5. 5
  6. 6
  7. 7
  8. 8
  9. 9
  10. 10
  11. 11
  12. 12
  13. 13
let Lookback = 7d;
SigninLogs
| where TimeGenerated between (ago(Lookback) .. now())
| where isnotempty(UserPrincipalName)
| summarize SignIns = count(),
            UniqueIPs = dcount(IPAddress),
            Failed = countif(ResultType != "0"),
            FirstSeen = min(TimeGenerated),
            LastSeen = max(TimeGenerated)
    by UserPrincipalName
| where SignIns >= 5
| order by UniqueIPs desc, Failed desc

A high unique-IP count is a lead, not proof of compromise. dcount() is approximate; interpret it as a screening statistic, not an exact forensic count.

Step 3 — Filter early and project intentionally

Start with TimeGenerated and a specific account, app or event type. Use project to retain only relevant fields. Avoid unbounded scans and unnecessarily wide joins.

Step 4 — Understand summarize and distinct counts

summarize reduces events into a behavioural picture. countif() tracks a condition; dcount() estimates distinct values; make_set() captures examples but may be truncated. Keep raw events available for validation.

Step 5 — Build comparable baselines

Compare like with like: weekdays with weekdays, service accounts with service accounts and UTC timestamps with the correct local context. A short lookback can miss normal weekly patterns.

Step 6 — Distinguish outliers from threats

Unusual behaviour may be benign. Ask whether an account is a user, workload identity, test account or administrator. Validate expected VPN egress, travel, maintenance and approved change windows.

Step 7 — Correlate carefully across tables

Use a join only when the keys and semantics align. AuditLogs records identity operations, but its InitiatedBy field is dynamic and may contain user or application actors. Extract the actor defensively and note that some records lack a user principal name.

KQL editor 2 — Correlate sign-ins with audit operations

Sentinel Logs — scoped identity and audit join
  1. 1
  2. 2
  3. 3
  4. 4
  5. 5
  6. 6
  7. 7
  8. 8
  9. 9
  10. 10
  11. 11
  12. 12
  13. 13
  14. 14
  15. 15
  16. 16
  17. 17
  18. 18
  19. 19
  20. 20
  21. 21
  22. 22
  23. 23
let TargetUPN = "analyst@example.com";
let Window = 3d;
let Signins = SigninLogs
    | where TimeGenerated >= ago(Window)
    | where UserPrincipalName =~ TargetUPN
    | project SignInTime = TimeGenerated, UserPrincipalName,
              IPAddress, AppDisplayName, ResultType;
let Changes = AuditLogs
    | where TimeGenerated >= ago(Window)
    | extend ActorUPN = tostring(InitiatedBy.user.userPrincipalName)
    | where ActorUPN =~ TargetUPN
    | project AuditTime = TimeGenerated, ActorUPN,
              OperationName, Result;
Signins
| join kind=leftouter Changes on $left.UserPrincipalName == $right.ActorUPN
| where isnull(AuditTime) or abs(datetime_diff('minute', SignInTime, AuditTime)) <= 60
| project SignInTime, IPAddress, AppDisplayName,
          ResultType, AuditTime, OperationName, Result
| order by SignInTime desc
| take 200

Replace the example UPN. This is a time-window correlation, not proof that a specific sign-in caused an audit operation. Many-to-many joins can duplicate rows; inspect event identifiers and raw records.

Step 8 — Recognise join explosion

A single account can have many sign-ins and many audit operations. Joining only on account can multiply rows dramatically. Restrict time windows, select columns and inspect join cardinality before interpreting totals.

Step 9 — Preserve event context

Keep event timestamps, account identity, source IP, application and operation details for follow-up. Summaries help identify candidates, but the underlying event records are needed to establish what actually happened.

Step 10 — Choose the right join strategy

inner returns matched rows; leftouter preserves the left-side events; leftanti identifies left-side rows without matches. Choose based on the hypothesis and explicitly document what unmatched data means.

Step 11 — Use arg_max for latest evidence

arg_max(TimeGenerated, *) selects the most recent record within each group. It is useful for last-seen context, but it cannot replace a timeline when sequence matters.

Step 12 — Look for trends, not single spikes

Time-series analysis can show activity bursts and quiet periods. It requires sensible bins and enough history. Empty bins or missing ingestion can resemble behavioural changes.

KQL editor 3 — Hunt time-series spikes

Sentinel Logs — sign-in activity by hour
  1. 1
  2. 2
  3. 3
  4. 4
  5. 5
  6. 6
  7. 7
  8. 8
  9. 9
  10. 10
  11. 11
  12. 12
  13. 13
let Window = 7d;
SigninLogs
| where TimeGenerated >= ago(Window)
| where isnotempty(UserPrincipalName)
| summarize Attempts = count() by bin(TimeGenerated, 1h), UserPrincipalName
| summarize PeakHourly = max(Attempts),
            AverageActiveHour = avg(Attempts),
            ActiveHours = count()
    by UserPrincipalName
| where ActiveHours >= 5 and PeakHourly >= 10
| extend PeakToAverage = round(PeakHourly / AverageActiveHour, 2)
| order by PeakToAverage desc

This baseline uses active hours only, not zero-activity hours. It is a candidate-ranking technique; verify each account’s normal pattern and whether ingestion gaps affected the observation.

Step 13 — When to use make-series

make-series creates regular time bins and can support anomaly functions such as series_decompose_anomalies(). Those functions depend on meaningful seasonality and sufficient data; do not interpret their scores as attacker confidence.

Step 14 — Investigate the most interesting candidate

Choose one authorised test account. Compare raw sign-ins with the aggregate output, check IP ownership and approved activity, and pivot into audit records only where relevant.

Step 15 — Time zones and event ordering

Log timestamps are generally UTC. Convert only for reporting or business-hours interpretation, and retain the original UTC timestamp in evidence. Consider ingestion delay and clocks when comparing data sources.

Step 16 — Performance and cost

  • Apply time filters at the source.
  • Restrict columns before joins.
  • Aggregate where appropriate, but retain access to raw evidence.
  • Limit exploratory results without mistaking truncation for completeness.
  • Document query runtime and data coverage.

Step 17 — Common KQL investigation mistakes

  • Assuming an approximate distinct count is exact.
  • Comparing incomparable account populations.
  • Joining large tables without a meaningful time relationship.
  • Using take before calculating a complete baseline.
  • Treating failed authentication as confirmed malicious activity.
  • Assuming no results means no threat.

Step 18 — Explain evidence confidence

Classify findings as supported, not supported or inconclusive. Identify whether conclusions rely on one source or independent corroboration. Preserve alternative explanations and coverage gaps.

Agent Foskett case file — The query was the suspect

Agent Foskett discovers that a wide join duplicated sign-in records and made normal accounts look unusually active. After narrowing the time window and inspecting raw events, most candidates disappear. One account still warrants a documented owner check. Lesson: query correctness is part of evidence integrity.

Hands-on lab — Build a defensible hunt

  1. Confirm SigninLogs and, if used, AuditLogs ingestion.
  2. State a hypothesis and two benign alternatives.
  3. Run the baseline query and choose a candidate.
  4. Adjust thresholds and document why.
  5. Use the join query on an authorised account.
  6. Inspect raw events for any apparent correlation.
  7. Run the time-series candidate query.
  8. Record findings, limitations and the next investigative pivot.

Knowledge check

  1. Why is dcount() not an exact forensic count?
  2. How can a join inflate an apparent activity total?
  3. What is the difference between an outlier and a confirmed threat?
  4. Why does an active-hour average differ from an all-hours baseline?
  5. What must be recorded before promoting a hunt into a detection?

Lesson 62 quality checklist

  • Canonical matches published filename.
  • Opening HTML comment, background, header, footer and scripts preserved.
  • Three full-width numbered KQL editors.
  • Examples explicitly use Sentinel Log Analytics KQL.
  • Query assumptions, telemetry requirements and limitations stated.
  • Previous lesson linked; unpublished Lesson 63 not linked.

Agent Foskett takeaway

The strongest hunting query is not the one that returns the most suspicious rows. It is the one whose results can be explained, reproduced and checked against raw evidence.

The Logs Already Knew.

Lesson 62 complete — build hunts you can defend!
You can now construct scoped KQL hunts, validate baselines, correlate identity evidence and investigate candidate anomalies.
Sentinel Academy Home

Related Agent Foskett learning

Develop advanced hunting queries from the hypothesis-driven foundations of Lesson 61.