Prepare for SC-200 with a reference that remembers your progress.

Ingested security logs often store important values inside free-form messages, JSON objects, arrays, or external files. KQL can extract and normalize those values into reusable tabular schemas.

extract() uses a regular expression and capture-group number to return one substring, optionally converted to a type. It returns null if no match exists or conversion fails.

SecurityEvent
| extend AccountName = extract(
    @"^(.*\\)?([^@]*)(@.*)?$", 2,
    tolower(Account))

parse matches a larger string pattern and creates several calculated columns at once. Its modes are simple, regex, and relaxed; fields that cannot be parsed become null.

The dynamic type stores arrays and property bags. Access a property with dot or bracket notation and convert it to a scalar when an operator requires a specific type.

SigninLogs
| extend OS = tostring(DeviceDetail.operatingSystem),
         StatusCode = tostring(Status.errorCode)

externaldata defines a table schema inside the query and reads rows from an external storage artifact such as Azure Blob Storage or Data Lake Storage. It is suited to relatively small reference datasets, not high-volume continuous ingestion.

let KnownIPs = externaldata(IPAddress:string)
[
  h@"https://storage.example/known.csv?...SAS..."
]
with (format="csv", ignoreFirstRecord=true);
SigninLogs
| where IPAddress in (KnownIPs)

Protect storage credentials and SAS tokens; avoid placing secrets in shared query text. This operator is unavailable in the public demo environment used by the lesson.

A parser is a KQL function that converts source-specific unstructured data into a stable virtual table. In the Logs window, create and test the query, select Save, choose Save as function, and assign a function name and optional parameters.

// Saved as function: PrivLogins
SecurityEvent
| where EventID == 4672
| where AccountType == "User"

// Reuse later
PrivLogins
| summarize Events = count() by Account

Functions centralize normalization logic so analytics rules, workbooks, and hunting queries share the same schema and corrections.

Distinguish extract from parse, scalar values from dynamic fields, mv-expand from mv-apply, external reference data from ingested logs, and an ad hoc query from a saved parser function.

Normalize security data at query time by extracting stable fields, expanding structured arrays carefully, enriching with governed external data, and saving repeated parsing logic as functions.