← Sentinel query library

Identity · Microsoft Sentinel

Compare successful sign-ins with a prior IP baseline

Find recent user/IP pairs absent from the preceding 14 days, requiring prior history for that user.

Before you run

Data & scope

Required tables
SigninLogs
Time window
15 days total: 14-day baseline followed by 1-day review
  • Microsoft Entra ID connector with interactive SigninLogs and at least 15 days of accessible, reasonably complete history.
  • Microsoft Entra ID P1 or P2 for ingestion; an Analytics table/query experience supporting joins and query access to SigninLogs.

Documentation review by Codex (AI). No live-tenant execution or detection-performance claim. Validate schema, data coverage and expected results in your workspace.

Kusto Query Language
Download .kql
// Hunting starter. Documentation checked 2026-11-13; not tenant-tested.
let recentStart = ago(1d);
let baselineStart = ago(15d);
let BaselineIPs = SigninLogs
    | where TimeGenerated >= baselineStart and TimeGenerated < recentStart
    | where IsInteractive == true and ResultType == "0"
    | where isnotempty(UserId) and isnotempty(IPAddress)
    | distinct AADTenantId, UserId, IPAddress;
let EstablishedUsers = BaselineIPs
    | summarize BaselineDistinctIPs = count() by AADTenantId, UserId;
SigninLogs
| where TimeGenerated >= recentStart and TimeGenerated <= now()
| where IsInteractive == true and ResultType == "0"
| where isnotempty(UserId) and isnotempty(IPAddress)
| summarize SignInRecords = count(), FirstSeenInRecentWindow = min(TimeGenerated),
    LastSeen = max(TimeGenerated), Apps = make_set(AppDisplayName, 10)
    by AADTenantId, UserId, UserPrincipalName, IPAddress
| join kind=inner (EstablishedUsers) on AADTenantId, UserId
| join kind=leftanti (BaselineIPs) on AADTenantId, UserId, IPAddress
| project AADTenantId, UserId, UserPrincipalName, IPAddress,
    SignInRecords, FirstSeenInRecentWindow, LastSeen, BaselineDistinctIPs, Apps
| order by LastSeen desc
| take 200

Read-only query. Run in your own authorized workspace after checking prerequisites.

Use this hunt to find changes in observed sign-in networks for users who already have successful interactive sign-in history. It compares the latest day with the preceding fourteen days. A new address is an investigation lead, not proof of an attacker, impossible travel or a compromised account.

Read the comparison

BaselineIPs contains distinct tenant/user/IP combinations from the historical interval. The user ID provides a more stable join key than the display name. The first join requires prior history for that user; the second, a leftanti join, removes recent pairs already seen in the baseline. Anti-join semantics.

The intervals do not overlap. FirstSeenInRecentWindow means first seen in the review day, not first seen ever. BaselineDistinctIPs describes the user’s observed historical variety. Applications are a bounded sample. Only successful interactive events with nonempty user IDs and addresses participate, so this deliberately omits new users with no baseline and other sign-in categories.

Triage and tune

Confirm the user and application context, then compare the address with expected VPN, proxy, mobile-provider or office egress. Check relevant device, access-policy and risk evidence in the original records. Ask whether the user changed location or connectivity through an established contact path rather than treating geolocation as reliable attribution.

Legitimate roaming and rotating infrastructure can create many matches; an attacker using a familiar egress may produce none. Adjust the two time boundaries together and document the reason. For busy tenants, narrow both baseline and review inputs consistently to the population under investigation. Do not sample or truncate the baseline before comparison: that would manufacture apparently new addresses.

Limits and validation

Set the query interface time range to cover at least fifteen days, and verify retention and ingestion continuity. An empty or newly connected baseline excludes users through the first join and can therefore yield no candidates; it does not prove all recent addresses were familiar. Comparison uses the logged address string, not IP ranges or a location model. Join cost grows with history volume.

This is a bounded, read-only hunting starter checked against table and operator documentation. It has not been run against a tenant or validated as a detection. Test known familiar and newly introduced addresses, missing-history cases and ordinary network changes before operational adoption.

Verify the source

Microsoft references

  1. Microsoft Learn — SigninLogs schema ↗
  2. Microsoft Learn — Kusto leftanti join ↗
  3. Microsoft Learn — Microsoft Entra ID connector requirements ↗
  4. Microsoft Learn — Kusto make_set aggregation ↗
Before turning a hunt into an alert

Measure normal activity, validate known test cases, account for ingestion delays, choose a threshold and entity mapping, and assign an owner. Results require investigation.

Follow the detection learning path →