Query Pattern Library

Goal: Start a query from a shape that already answers a question that keeps coming back.

Query Patterns names the four shapes in plain English. This page is the longer list, with a KQL starting point for each. The queries are examples; adapt them to your tables. They were read over for the KQL and were not run against a workspace. Every pattern is a starting point: change the field names, the window and the numbers to match your data, and run it in the Warm Query Manager before you save it.

What a pattern is

Part	What it is
Problem	The question being answered
Query	A KQL example you can paste and change
What to change	The numbers, windows and field names that are yours

A pattern is not a finished answer. Describe your data and your rule to Azi and she adapts the query; you review the proposal, as with any change. See Working with Azi.

Time-series aggregation

Problem: "What is the average temperature over the last 8 hours?"

Data
| where EnqueuedTime > ago(8h)
| extend Payload = todynamic(RawData)
| summarize
    Avg = avg(todouble(Payload.temperature)),
    Min = min(todouble(Payload.temperature)),
    Max = max(todouble(Payload.temperature))
    by bin(EnqueuedTime, 1h)
| order by EnqueuedTime asc

Change	Example
The window	8h, 1d, 7d
The payload field	temperature, pressure
The bucket	1h, 15m, 5m

Ask Azi: "Create a warm query that shows hourly temperature averages for the cleanroom sensors over the last shift."

Threshold

Problem: "Show me every reading that went over the limit."

Data
| where EnqueuedTime > ago(24h)
| extend Payload = todynamic(RawData)
| extend Value = todouble(Payload.pressure)
| where Value > 145.0
| project EnqueuedTime, DeviceID, Value, Deviation = Value - 145.0
| order by Deviation desc

For a floor, or for a range, use one of these in place of the where Value > 145.0 line:

| where Value < 20.0
| where Value < 20.0 or Value > 145.0

Ask Azi: "Build a query that finds all pressure readings above 145 in the last 24 hours."

Device comparison

Problem: "Compare several devices."

Data
| where EnqueuedTime > ago(7d)
| where DeviceID in ("T-01", "T-02", "T-03")
| extend Payload = todynamic(RawData)
| summarize
    AvgValue = avg(todouble(Payload.cycleTime)),
    ReadingCount = count(),
    LastReading = max(EnqueuedTime)
    by DeviceID
| order by AvgValue desc

Change the device ids and the field. Ask Azi: "Compare all the mixing vessels on average batch time over the last week."

Trend

Problem: "How has this metric changed over time?"

Data
| where EnqueuedTime > ago(30d)
| extend Payload = todynamic(RawData)
| summarize DailyAvg = avg(todouble(Payload.oee)) by Day = bin(EnqueuedTime, 1d)
| order by Day asc
| extend Previous = prev(DailyAvg)
| extend MovingAvg = (DailyAvg + prev(DailyAvg, 1) + prev(DailyAvg, 2)) / 3
| project
    Day,
    DailyAvg,
    MovingAvg,
    TrendDirection = case(isnull(Previous), "", DailyAvg > Previous, "Up", DailyAvg < Previous, "Down", "Flat")

prev() reads the row before it, so the rows must be sorted first: the order by is what makes this work. MovingAvg is a three-day trailing average, empty for the first two days, and TrendDirection is empty for the first day, which has nothing before it to compare with. Widen it by adding more prev terms and dividing by the count.

Week over week, group by the start of the week instead:

Data
| where EnqueuedTime > ago(90d)
| extend Payload = todynamic(RawData)
| summarize WeeklyTotal = sum(todouble(Payload.units)) by Week = startofweek(EnqueuedTime)
| order by Week asc
| extend PreviousWeek = prev(WeeklyTotal)
| extend ChangePercent = round((WeeklyTotal - PreviousWeek) / PreviousWeek * 100, 1)

OEE

Problem: "What is the Overall Equipment Effectiveness of this line?"

let PlannedSeconds = 28800;
let IdealCycleSeconds = 60;
Data
| where EnqueuedTime > ago(8h)
| extend Payload = todynamic(RawData)
| summarize
    RunSeconds = sumif(todouble(Payload.durationSeconds), tostring(Payload.status) == "Running"),
    TotalUnits = sum(todouble(Payload.units)),
    GoodUnits = sumif(todouble(Payload.units), tostring(Payload.quality) == "Pass")
| extend Availability = RunSeconds / PlannedSeconds
| extend Performance = (TotalUnits * IdealCycleSeconds) / RunSeconds
| extend QualityRate = GoodUnits / TotalUnits
| extend OEE = Availability * Performance * QualityRate * 100
| project
    Availability = round(Availability * 100, 1),
    Performance = round(Performance * 100, 1),
    Quality = round(QualityRate * 100, 1),
    OEE = round(OEE, 1)

Change	Example
PlannedSeconds	28800, eight hours in seconds
IdealCycleSeconds	60, one minute per unit
The payload fields	the ones your line sends for status, duration, units and quality

OEE is defined differently from one industry to the next. Change the arithmetic to match the standard you use.

Downtime Pareto

Problem: "What are the biggest causes of downtime?"

let Downtime = Data
    | where EnqueuedTime > ago(7d)
    | extend Payload = todynamic(RawData)
    | where tostring(Payload.event) == "downtime"
    | project Cause = tostring(Payload.cause), Minutes = todouble(Payload.durationMinutes);
let Total = toscalar(Downtime | summarize sum(Minutes));
Downtime
| summarize TotalMinutes = sum(Minutes), Occurrences = count() by Cause
| extend Percentage = round(TotalMinutes / Total * 100, 1)
| top 10 by TotalMinutes desc
| project Cause, TotalMinutes, Occurrences, Percentage

Batch traceability

Problem: "Trace every step of one batch or lot."

Data
| extend Payload = todynamic(RawData)
| where tostring(Payload.lotNumber) == "LOT-2024-001234"
| project
    EnqueuedTime,
    DeviceID,
    Step = tostring(Payload.step),
    Station = tostring(Payload.station),
    Operator = tostring(Payload.operator),
    Parameters = Payload
| order by EnqueuedTime asc

Parameters = Payload carries the whole message beside the named columns. If your quality results live in another source, a warm query built from steps can read both; see Warm Queries. A query that traces a batch reads what was recorded when the batch ran; running the query is not itself written to a record (see what is and is not recorded).

Energy cost

Problem: "Split energy cost across production lines."

let RatePerKwh = 0.12;
let Energy = Data
    | where EnqueuedTime > ago(30d)
    | extend Payload = todynamic(RawData)
    | project Line = tostring(Payload.line), KWh = todouble(Payload.kWh);
let Total = toscalar(Energy | summarize sum(KWh));
Energy
| summarize TotalKwh = sum(KWh) by Line
| extend TotalCost = TotalKwh * RatePerKwh
| extend CostShare = round(TotalKwh / Total * 100, 1)
| project Line, TotalKwh, TotalCost, CostShare
| order by TotalCost desc

Change the rate and the field names. The rate is in your own currency per kWh.

Using patterns with Azi

Describe what you need in three parts:

1. The pattern: "I need a threshold query..."
2. Your data: "...for temperature readings from the cleanroom sensors..."
3. The rule: "...that shows readings above 23 over the last 8 hours."

Azi drafts it into the Warm Query Manager; you Run a preview and save it with the canvas Save icon.

Next steps

• The language these use -> KQL Basics
• The four shapes in plain English -> Query Patterns
• Save one as a shared source of truth -> Warm Queries