EI1.8 KQL Fundamentals for Identity Security

Figure — KQL Fundamentals for Identity Security.

The operators you will use most

KQL (Kusto Query Language) is a pipe-based query language — you start with a table name and pipe the data through a sequence of operators that filter, transform, and aggregate. If you have used PowerShell pipelines or Unix command-line tools, the concept is familiar. If not, the mental model is: data flows from left to right (top to bottom in a multi-line query), and each operator transforms the data before passing it to the next.

This subsection covers the operators that appear in identity security queries most frequently. The Mastering KQL for Cybersecurity course covers the full language in depth — if you want comprehensive KQL training, take that course alongside this one. For the purposes of this course, the operators below are sufficient.

where — filtering rows

The where operator filters rows based on a condition. Every identity security query starts with a where clause that scopes the data to the relevant time window and conditions.

// Filter to successful sign-ins in the last 24 hours
SigninLogs
| where TimeGenerated > ago(24h)
| where ResultType == 0

The where operator supports all standard comparison operators: == (equals), != (not equals), >, <, >=, <=. For string matching, == is case-sensitive, =~ is case-insensitive, has checks if a string contains a word (word-boundary-aware), contains checks if a string contains a substring (no word boundary), and startswith / endswith check prefixes and suffixes.

// has vs contains — practical difference
SigninLogs
| where AppDisplayName has "Exchange"
// Matches: "Office 365 Exchange Online" (word boundary match)
// Does NOT match: "DataExchange" (no word boundary)

SigninLogs
| where AppDisplayName contains "Exchange"
// Matches both: "Office 365 Exchange Online" AND "DataExchange"
// Usually not what you want — use has instead

// Filter for specific applications
SigninLogs
| where AppDisplayName in ("Office 365 Exchange Online", 
    "Office 365 SharePoint Online", "Microsoft Teams")

project — selecting columns

The project operator selects which columns to include in the output and optionally renames them. Use it to reduce output to the fields you need and create readable column names.

SigninLogs
| where TimeGenerated > ago(24h)
| where ResultType == 0
| project 
    Time = TimeGenerated,
    User = UserPrincipalName,
    App = AppDisplayName,
    IP = IPAddress,
    Risk = RiskLevelDuringSignIn,
    CA = ConditionalAccessStatus

project-away removes specific columns while keeping everything else — useful when you want most fields but need to drop a few verbose ones. project-rename renames columns without dropping any.

extend — adding calculated columns

The extend operator adds new columns based on expressions. This is how you parse JSON fields, calculate derived values, and create classification labels.

// Parse JSON and create classification columns
SigninLogs
| where TimeGenerated > ago(24h)
| extend 
    Country = tostring(LocationDetails.countryOrRegion),
    City = tostring(LocationDetails.city),
    DeviceOS = tostring(DeviceDetail.operatingSystem),
    IsCompliant = tostring(DeviceDetail.isCompliant)
| extend TrustLevel = case(
    IsCompliant == "true", "High",
    Country in ("US", "GB"), "Medium",
    "Low"
    )

The case() function is the KQL equivalent of if/else — it evaluates conditions in order and returns the value for the first match. Essential for creating security classifications.

The tostring() function converts a value to a string — required when accessing nested JSON fields like LocationDetails.countryOrRegion because KQL treats the extracted value as a dynamic type by default.

summarize — aggregating data

The summarize operator groups rows and calculates aggregate values. This is the operator that turns thousands of sign-in events into actionable summaries.

// Count sign-ins per user, per application
SigninLogs
| where TimeGenerated > ago(24h)
| where ResultType == 0
| summarize 
    SignInCount = count(),
    DistinctApps = dcount(AppDisplayName),
    DistinctIPs = dcount(IPAddress),
    Countries = make_set(tostring(LocationDetails.countryOrRegion), 5)
    by UserPrincipalName
| order by SignInCount desc

The aggregate functions you will use most: count() counts rows in each group, dcount() counts distinct values (approximate), make_set() creates an array of unique values, min() and max() find the earliest/latest timestamp or smallest/largest value, and any() returns an arbitrary value from the group (useful when you just need a sample).

// Hourly sign-in volume — detect spikes or gaps
SigninLogs
| where TimeGenerated > ago(7d)
| where ResultType == 0
| summarize SignInCount = count() by bin(TimeGenerated, 1h)
| order by TimeGenerated asc
// Render as a time chart to visualize the pattern
// | render timechart

parse_json and mv-expand — working with arrays

Many sign-in log fields contain JSON arrays that must be parsed before they can be queried. The two operators you need are parse_json() (converts a string to a JSON object) and mv-expand (expands an array into individual rows).

You have already seen these in EI1.5 (conditional access evaluation):

// Parse and expand the ConditionalAccessPolicies array
SigninLogs
| mv-expand CAPolicy = parse_json(ConditionalAccessPolicies)
| extend PolicyName = tostring(CAPolicy.displayName)
| extend PolicyResult = tostring(CAPolicy.result)

And in EI1.4 (authentication details):

// Parse the AuthenticationDetails array
SigninLogs
| extend AuthDetail = parse_json(AuthenticationDetails)
| extend PrimaryMethod = tostring(AuthDetail[0].authenticationMethod)
| extend MFAMethod = tostring(AuthDetail[1].authenticationMethod)

The key difference: mv-expand creates one row per array element (expanding the result set), while array indexing (AuthDetail[0]) extracts a specific element by position without expanding. Use mv-expand when you want to analyze each array element individually (like each CA policy evaluation). Use array indexing when you know the position of the element you want (like the first and second authentication steps).

let — reusable variables and subqueries

The let operator defines named variables or subqueries that can be referenced later in the query. Essential for building complex detection queries that compare current activity against a baseline.

// Define a time range and a list of high-risk apps
let timeRange = 24h;
let sensitiveApps = dynamic(["Azure Portal", "Microsoft Graph Explorer", 
    "Azure Active Directory PowerShell"]);
SigninLogs
| where TimeGenerated > ago(timeRange)
| where AppDisplayName in (sensitiveApps)
| where ResultType == 0
| project TimeGenerated, UserPrincipalName, AppDisplayName, IPAddress

let is also used to define subqueries whose results are joined with the main query — the pattern used in the token replay detection query in EI1.2 and the service principal baseline query in EI1.3.

Time functions

Identity security queries constantly work with time — comparing sign-in timestamps, calculating durations, and detecting time-based anomalies.

ago() calculates a time relative to now: ago(24h) is 24 hours before the current time, ago(7d) is 7 days, ago(30m) is 30 minutes.

// Calculate time between consecutive sign-ins for the same user
// Useful for detecting rapid sign-in attempts (spray or brute force)
SigninLogs
| where TimeGenerated > ago(1h)
| where ResultType != 0  // Failed sign-ins
| order by UserPrincipalName, TimeGenerated asc
| extend PrevTime = prev(TimeGenerated, 1)
| extend PrevUser = prev(UserPrincipalName, 1)
| where UserPrincipalName == PrevUser
| extend GapSeconds = datetime_diff('second', TimeGenerated, PrevTime)
| where GapSeconds < 5  // Less than 5 seconds between failures
| project TimeGenerated, UserPrincipalName, IPAddress, GapSeconds
// Results: rapid-fire failed sign-ins — likely automated attack

Data type handling and common pitfalls

KQL is strongly typed, and the sign-in log fields contain a mix of types that trip up new query writers. Here are the most common issues and how to handle them:

Dynamic type fields — LocationDetails, DeviceDetail, ConditionalAccessPolicies, and AuthenticationDetails are all dynamic type (JSON). You must use tostring() to extract values for comparison: tostring(LocationDetails.countryOrRegion) not just LocationDetails.countryOrRegion. Without tostring(), comparisons against string literals may fail silently — the query returns no results without an error.

Complete worked example: investigating a suspicious sign-in

Here is a complete multi-step query that demonstrates how the operators combine in a real investigation scenario. The scenario: you received an alert for a medium-risk sign-in for a user. You need to quickly assess the sign-in, determine what the user did afterward, and decide whether to escalate.

// Complete investigation query: risky sign-in + post-auth activity
// Step 1: Find the risky sign-in
let targetUser = "casey.finance@yourdomain.onmicrosoft.com";
let riskySignIn = SigninLogs
| where TimeGenerated > ago(24h)
| where UserPrincipalName =~ targetUser
| where RiskLevelDuringSignIn in ("medium", "high")
| where ResultType == 0
| project SignInTime = TimeGenerated, IPAddress, 
    Country = tostring(LocationDetails.countryOrRegion),
    DeviceOS = tostring(DeviceDetail.operatingSystem),
    AppDisplayName, RiskLevelDuringSignIn,
    RiskEventTypes_V2,
    AuthMethod = tostring(parse_json(AuthenticationDetails)[0].authenticationMethod),
    CAStatus = ConditionalAccessStatus;
// Step 2: Get the sign-in context
riskySignIn
| extend InvestigationNotes = case(
    Country !in ("US", "GB"), strcat("ALERT: Unexpected country - ", Country),
    AuthMethod == "Password", "WARNING: Password auth used (phishing-capable)",
    CAStatus == "notApplied", "CRITICAL: No CA policy evaluated",
    "Context appears normal — review additional activity"
    )
// This produces the initial assessment in a single query
// Run the INVEST-02 and INVEST-03 queries from EI1.12 for follow-up

This query combines filtering, JSON parsing, conditional classification, and projection into a single investigation step. The case() function at the end produces an automated initial assessment note — not a replacement for human judgment, but a starting point that highlights the most concerning aspects of the sign-in.