KQL for Threat Hunting Guide: Queries and Hunts Across Microsoft Logs

Most hunts in Microsoft environments fail not for lack of data but because the query looks in the wrong table, filters the wrong field or silently drops the rows that matter. Kusto Query Language (KQL) is the language behind Microsoft Sentinel, Defender XDR advanced hunting, Azure Monitor and Azure Data Explorer, and the identity logs it queries are where cloud intrusions most often leave their traces. This guide covers the patterns hunters use, the Entra ID and Defender identity tables and their key columns, short defensive queries for risky service principal, workload identity and user sign-in activity, and how to turn a hunt into a tested detection. It is for threat hunters, detection engineers and SOC analysts who already know the basics of KQL. Platform setup, roles and automation are covered in the Microsoft Sentinel Guide, and hunting methodology in the Threat Hunting Guide.

Key takeaways

• Know which engine you are in. Sentinel tables use TimeGenerated, Defender XDR tables use Timestamp, and advanced hunting keeps Defender data for 30 days unless it is streamed to Sentinel with longer retention.
• Workload identities have their own tables. Service principal and managed identity sign-ins live in AADServicePrincipalSignInLogs and AADManagedIdentitySignInLogs, not in SigninLogs, so a hunt that only reads SigninLogs never sees them.
• AuditLogs carries persistence. New credentials, consent grants and app role assignments have exact activity names; match them exactly and parse InitiatedBy and TargetResources.
• Baseline, then compare. The strongest identity hunts are "new for this identity" patterns built with let and leftanti joins over a lookback window.
• Write for speed and correctness. Filter on time first, prefer has and == over contains and =~, put the smaller table on the left, and use kind=inner when you need every matching row.

Where KQL runs and what changes between engines

• Streamed Defender tables: Microsoft advises using Timestamp for event time, because TimeGenerated can be overwritten with now() if data reaches Log Analytics more than 48 hours late.
• Time picker: in advanced hunting, Microsoft recommends the time filter in the user interface rather than in the query when tables are filtered at ingestion, to avoid incomplete results. In rules, always set time in the query.
• Livestream is gone: Microsoft lists Sentinel livestream as no longer available and points to KQL jobs, analytics rules and playbooks instead.

Query patterns every hunt uses

Identity tables and the columns that matter

• Result codes: in Entra sign-in tables a ResultType of "0" means success; anything else is a failure or interrupt, with detail in ResultDescription.
• Licensing and latency: Microsoft Graph activity logs require Entra ID P1 or P2 and usually arrive within 30 minutes, sometimes up to 2 hours. They cover only the resource tenant, so a multitenant app's actions in other tenants are not visible.
• Coverage gaps: IdentityLogonEvents does not include non-interactive Entra logons, and returns nothing if Defender for Identity or Defender for Cloud Apps is not deployed.
• Agent sign-ins: AADServicePrincipalSignInLogs now has an Agent column for details of agentic sign-ins, worth including in AI agent identity hunts. See the Agentic AI Identity Guide.

Hunts for risky service principal and workload identity activity

These short queries are illustrative starting points for Sentinel (TimeGenerated). Test them against your own data, tune thresholds and allow-lists, and treat results as leads, not verdicts. The techniques they look for include T1098.001 Additional Cloud Credentials, T1078.004 Cloud Accounts and T1528 Steal Application Access Token.

1. New credentials added to apps and service principals

AuditLogs
| where TimeGenerated > ago(7d)
| where OperationName in ("Add service principal credentials",
    "Update application - Certificates and secrets management")
| extend Actor = coalesce(tostring(InitiatedBy.user.userPrincipalName),
    tostring(InitiatedBy.app.displayName))
| extend Target = tostring(TargetResources[0].displayName)
| project TimeGenerated, OperationName, Actor, Target, Result, CorrelationId

Expect pipeline and rotation automation here. Flag credentials added by a user rather than the usual automation, outside change windows, or to apps that hold high-privilege Graph roles.

2. Credential added, then used within two hours

let creds = AuditLogs
| where TimeGenerated > ago(7d)
| where OperationName == "Add service principal credentials"
| extend ServicePrincipalId = tostring(TargetResources[0].id)
| project CredTime = TimeGenerated, ServicePrincipalId,
    Actor = tostring(InitiatedBy.user.userPrincipalName);
creds
| join kind=inner (
    AADServicePrincipalSignInLogs
    | where TimeGenerated > ago(7d) and ResultType == "0"
    | project SignInTime = TimeGenerated, ServicePrincipalId,
        ServicePrincipalName, IPAddress, ServicePrincipalCredentialKeyId
  ) on ServicePrincipalId
| where SignInTime between (CredTime .. CredTime + 2h)

The smaller table (credential events) sits on the left and kind=inner keeps every matching sign-in. A new key used from an IP address the app has never used is a strong lead.

3. Service principal signing in from a new IP address

let known = AADServicePrincipalSignInLogs
| where TimeGenerated between (ago(14d) .. ago(1d)) and ResultType == "0"
| distinct AppId, IPAddress;
AADServicePrincipalSignInLogs
| where TimeGenerated > ago(1d) and ResultType == "0"
| join kind=leftanti known on AppId, IPAddress
| summarize FirstSeen = min(TimeGenerated), SignIns = count(),
    Resources = make_set(ResourceDisplayName, 10)
    by ServicePrincipalName, AppId, IPAddress, ClientCredentialType

Workload identities usually run from a small, stable set of addresses, which makes this hunt far quieter than the same pattern on users. Tune out apps that run from dynamic cloud ranges, or compare by AutonomousSystemNumber instead of IP address.

4. Consent and app role grants to watch

AuditLogs
| where TimeGenerated > ago(30d)
| where OperationName in ("Consent to application",
    "Add app role assignment to service principal",
    "Add delegated permission grant", "Add owner to application")
| where tostring(TargetResources) has_any ("full_access_as_app",
    "RoleManagement.ReadWrite.Directory", "AppRoleAssignment.ReadWrite.All",
    "Application.ReadWrite.All", "Mail.Read")
| project TimeGenerated, OperationName,
    Actor = tostring(InitiatedBy.user.userPrincipalName), TargetResources

Searching the serialised TargetResources with has_any is a quick first pass; for production rules, expand modifiedProperties with mv-expand and match the permission value exactly. See the Entra ID Applications and Consent Guide.

5. Managed identity reaching a new resource

let known = AADManagedIdentitySignInLogs
| where TimeGenerated between (ago(30d) .. ago(1d))
| distinct ServicePrincipalId, ResourceDisplayName;
AADManagedIdentitySignInLogs
| where TimeGenerated > ago(1d)
| join kind=leftanti known on ServicePrincipalId, ResourceDisplayName
| project TimeGenerated, ServicePrincipalName, ResourceDisplayName, ResultType

A managed identity on a web app that suddenly requests tokens for Key Vault or Microsoft Graph may mean code or host compromise on that resource. See the Cloud Workload Identity Guide.

6. What an app did with Microsoft Graph

MicrosoftGraphActivityLogs
| where TimeGenerated > ago(3d)
| where isnotempty(ServicePrincipalId) and isempty(UserId)
| where RequestMethod in ("POST", "PATCH", "DELETE")
| extend Path = tostring(split(RequestUri, "?")[0])
| summarize Requests = count(), Paths = make_set(Path, 20),
    IPs = make_set(IPAddress, 10) by AppId, ServicePrincipalId
| order by Requests desc

Application-only writes to Graph are rare for most apps; a jump in volume or new paths such as servicePrincipals or users is worth a look. Correlate to the sign-in with SignInActivityId, noting Microsoft's warning that some first-party activity has no matching sign-in record.

Hunts on user sign-ins and on-premises identity

Password spray from one source

SigninLogs
| where TimeGenerated > ago(1d)
| where ResultType == "50126"
| summarize Users = dcount(UserPrincipalName), Attempts = count()
    by IPAddress, bin(TimeGenerated, 1h)
| where Users > 10

Error 50126 is an invalid username or password. Slow sprays rotate through residential proxies, so also summarise by AutonomousSystemNumber and across a longer window, and run the same pattern on AADNonInteractiveUserSignInLogs. Maps to T1110.003 Password Spraying.

Successful sign-ins that skipped strong authentication

SigninLogs
| where TimeGenerated > ago(7d) and ResultType == "0"
| where AuthenticationRequirement == "singleFactorAuthentication"
| where ConditionalAccessStatus == "notApplied"
| summarize SignIns = count(), Apps = make_set(AppDisplayName, 10)
    by UserPrincipalName, ClientAppUsed

This surfaces accounts and apps that fall outside Conditional Access, including legacy test accounts. See the Conditional Access Guide.

Service accounts logging on from new devices (advanced hunting)

let svc = dynamic(["svc-backup", "svc-sql", "svc-deploy"]);
IdentityLogonEvents
| where Timestamp > ago(7d)
| where AccountName in~ (svc) and ActionType == "LogonSuccess"
| summarize Devices = dcount(DeviceName), DeviceList = make_set(DeviceName, 20),
    Protocols = make_set(Protocol) by AccountName
| where Devices > 3

Replace the inline list with a watchlist or an inventory table of service accounts. A service account that normally authenticates from one application server and now appears on workstations is a classic lateral movement signal. See the Service Account Security Guide.

From hunt to tested detection

1. Write a hypothesis: name the behaviour and ATT&CK technique, for example "an attacker adds a secret to an existing app and uses it from new infrastructure (T1098.001)". Sentinel hunts (preview) track the hypothesis, queries, bookmarks and outcome.
2. Size the query: start with count or take on a short window, then widen. Record result counts per day so the expected volume is known.
3. Bookmark evidence: keep interesting rows as bookmarks with entities and technique mapped; they remain queryable in HuntingBookmark. In the Defender portal, link results to an incident instead, since bookmarks are not supported in advanced hunting.
4. Tune with allow-lists: move known automation identities and IP ranges into a watchlist rather than hard-coding them, so the list has an owner and an audit trail.
5. Promote: from the hunting query use New alert rule to build a scheduled rule, or a custom detection in the Defender portal. Microsoft notes that near-real-time frequency is not available for custom detections that include Sentinel data.
6. Test and roll back: run at low severity without automation for a fortnight, replay a benign simulation of the behaviour, then enable response. Keep the previous version in source control so a noisy change can be reverted. See the Detection Engineering Guide.

Choosing between hunt, rule and job

• Keep it a hunt: when results need human judgement every time, such as consent grants in a tenant with many app owners.
• Make it a rule: when a precise condition has low volume, such as credentials added by a non-automation user to an app with directory write roles.
• Make it a KQL job: when the hunt spans months of lake data, such as first-seen baselines; the job writes a compact result back to the analytics tier for rules to use.
• Small versus large estates: a small tenant can review weekly hunt output by hand; a large one needs baselines per identity, watchlists and scoring before anything reaches an analyst.

Measuring the hunting programme

• Hunts completed per month with a recorded outcome (validated, refuted, inconclusive).
• Number of hunts converted into scheduled rules or custom detections.
• Share of workload identities (service principals and managed identities) covered by at least one baseline hunt.
• False positive rate of promoted rules in their first 30 days.
• Median query resource usage, keeping hunts out of the High band shown in advanced hunting.

Writing fast, correct queries

Framework mapping

Common failure patterns

• Hunting only SigninLogs: service principal, managed identity and non-interactive sign-ins sit in separate tables, so workload identity misuse is never queried.
• Wrong success test: filtering ResultType == "Success" or comparing it as a number, so every row or no row matches. Entra uses the string "0" for success.
• Join that drops evidence: using the default join flavour and losing all but one event per key, or forgetting the time condition so unrelated events correlate.
• Retention shorter than the hunt: running a 90-day baseline in advanced hunting where Defender data stops at 30 days, or against tables that were never connected.
• Hard-coded allow-lists: automation accounts excluded by name inside rules, which attackers can mimic and nobody reviews.
• Exact-name drift: AuditLogs activity names copied with typographic characters, such as a dash in "Update application - Certificates and secrets management", so the filter never matches.

Lessons from incidents

• Apps as the attack path: in the Microsoft Midnight Blizzard breach, a password spray against a legacy test account led to a legacy OAuth app, new malicious apps, a consent grant by a newly created user and the full_access_as_app permission for mailbox access. Hunts 1, 2 and 4 above and the spray query look for exactly those steps in AuditLogs and sign-in tables. See also the APT29 (Midnight Blizzard) Threat Actor Profile.
• Logs make the hunt possible: in the Microsoft Storm-0558 key breach, forged tokens produced no failed sign-ins to hunt; detection came from a customer with premium audit logging and a custom alert on MailItemsAccessed. Mail access telemetry has to be collected and queryable before KQL can find anomalies in it (T1114.002 Remote Email Collection).

Practitioner checklist

• Confirm SigninLogs, AADNonInteractiveUserSignInLogs, AADServicePrincipalSignInLogs, AADManagedIdentitySignInLogs, AuditLogs and MicrosoftGraphActivityLogs are populated.
• Check retention for each table covers your longest baseline window, using the lake tier and KQL jobs for long baselines.
• Use TimeGenerated in Sentinel and Timestamp in Defender tables consistently.
• Build first-seen baselines for every privileged service principal: IP address, resource and credential key.
• Hunt weekly for new credentials, consent grants and app role assignments, matched by exact activity name.
• Keep automation identities and approved ranges in owned watchlists, not inside queries.
• Apply time filters first, has over contains, smaller table left and kind=inner in every shared query.
• Record each hunt's hypothesis, ATT&CK technique and outcome, and promote repeatable hunts to tested rules.

Standards and references

• Microsoft Learn: Best practices for Kusto Query Language queries
• Microsoft Learn: Advanced hunting query best practices
• Microsoft Learn: Threat hunting in Microsoft Sentinel
• Microsoft Learn: AADServicePrincipalSignInLogs table reference
• Microsoft Learn: Microsoft Entra audit log activity reference
• Microsoft Learn: Access Microsoft Graph activity logs
• Microsoft Learn: IdentityLogonEvents table in advanced hunting
• MITRE ATT&CK: Additional Cloud Credentials

Related NHI Mgmt Group resources: Threat Hunting Guide · Microsoft Sentinel Guide · ITDR Guide · Azure RBAC and Service Principals Guide · SIEM Guide