Query Patterns

Goal: Reusable patterns for common industrial data queries.

---

What is a Pattern?

A pattern is a query template that solves a common industrial problem:

Element	Description
Problem	The business question being answered
Query structure	KQL template with placeholders
Customization points	What you'll change for your use case
Example output	What the results look like

---

Time-Series Aggregation

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

Pattern

{TableName}
| where Timestamp > ago({TimeRange})
| summarize
    Avg = avg({MetricColumn}),
    Min = min({MetricColumn}),
    Max = max({MetricColumn})
    by bin(Timestamp, {BinSize})
| order by Timestamp asc

Customization Points

Placeholder	Example
{TableName}	SensorReadings
{TimeRange}	8h, 1d, 7d
{MetricColumn}	Temperature, Pressure
{BinSize}	1h, 15m, 5m

Ask Azi

---

Threshold Alerting

Problem: "Show me all readings that exceeded the threshold."

Pattern

{TableName}
| where Timestamp > ago({TimeRange})
| where {MetricColumn} > {Threshold}
| project Timestamp, DeviceId, {MetricColumn},
          Deviation = {MetricColumn} - {Threshold}
| order by Deviation desc

Customization Points

Placeholder	Example
{Threshold}	25.0 (temperature), 150 (pressure)
{MetricColumn}	Temperature, Vibration

Variations

Below threshold:

| where {MetricColumn} < {LowerThreshold}

Outside range:

| where {MetricColumn} < {LowerThreshold} or {MetricColumn} > {UpperThreshold}

Ask Azi

---

Device Comparison

Problem: "Compare performance across multiple devices."

Pattern

{TableName}
| where Timestamp > ago({TimeRange})
| where DeviceId in ({DeviceList})
| summarize
    AvgValue = avg({MetricColumn}),
    ReadingCount = count(),
    LastReading = max(Timestamp)
    by DeviceId
| order by AvgValue desc

Customization Points

Placeholder	Example
{DeviceList}	"T-01", "T-02", "T-03"
{MetricColumn}	CycleTime, Output

Example Output

DeviceId	AvgValue	ReadingCount	LastReading
T-01	22.4	1,247	2024-01-15 14:32:00
T-02	23.1	1,198	2024-01-15 14:31:45
T-03	21.8	1,256	2024-01-15 14:32:15

Ask Azi

---

Historical Trend Analysis

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

Pattern

{TableName}
| where Timestamp > ago({TimeRange})
| summarize DailyAvg = avg({MetricColumn}) by bin(Timestamp, 1d)
| extend MovingAvg = row_window_session(DailyAvg, Timestamp, 7d, 1d)
| project Timestamp, DailyAvg,
          TrendDirection = iff(DailyAvg > prev(DailyAvg), "Up", "Down")

Customization Points

Placeholder	Example
{TimeRange}	30d, 90d
{MetricColumn}	OEE, FirstPassYield

Variations

Week-over-week comparison:

| extend WeekNumber = week_of_year(Timestamp)
| summarize WeeklyTotal = sum({MetricColumn}) by WeekNumber
| extend PreviousWeek = prev(WeeklyTotal)
| extend Change = (WeeklyTotal - PreviousWeek) / PreviousWeek * 100

---

OEE Calculation

Problem: "Calculate Overall Equipment Effectiveness."

Pattern

let PlannedTime = {PlannedProductionTime};
let IdealCycleTime = {IdealCycleSeconds};
{ProductionTable}
| where Timestamp > ago({TimeRange})
| summarize
    RunTime = sum(iff(Status == "Running", Duration, 0)),
    TotalUnits = sum(Units),
    GoodUnits = sum(iff(Quality == "Pass", Units, 0))
| extend Availability = RunTime / PlannedTime
| extend Performance = (TotalUnits * IdealCycleTime) / RunTime
| extend Quality = GoodUnits / TotalUnits
| extend OEE = Availability * Performance * Quality * 100
| project
    Availability = round(Availability * 100, 1),
    Performance = round(Performance * 100, 1),
    Quality = round(Quality * 100, 1),
    OEE = round(OEE, 1)

Customization Points

Placeholder	Example
{PlannedProductionTime}	28800 (8 hours in seconds)
{IdealCycleSeconds}	60 (1 minute per unit)
{ProductionTable}	ProductionData

---

Downtime Pareto

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

Pattern

{DowntimeTable}
| where Timestamp > ago({TimeRange})
| summarize
    TotalMinutes = sum(DurationMinutes),
    Occurrences = count()
    by Cause
| extend TotalDowntime = toscalar(
    {DowntimeTable}
    | where Timestamp > ago({TimeRange})
    | summarize sum(DurationMinutes))
| extend Percentage = round(TotalMinutes / TotalDowntime * 100, 1)
| top 10 by TotalMinutes desc
| project Cause, TotalMinutes, Occurrences, Percentage

Example Output

Cause	TotalMinutes	Occurrences	Percentage
Material shortage	245	12	28.4%
Tool change	189	34	21.9%
Quality hold	156	8	18.1%

---

Batch Traceability

Problem: "Trace all steps for a specific batch or lot."

Pattern

{ProductionTable}
| where LotNumber == "{LotId}"
| join kind=leftouter {QualityTable} on LotNumber
| project
    Timestamp,
    Step,
    Station,
    Operator,
    ProcessParameters = pack_all(),
    QualityResult
| order by Timestamp asc

Customization Points

Placeholder	Example
{LotId}	"LOT-2024-001234"
{ProductionTable}	BatchOperations
{QualityTable}	QualityInspections

---

Energy Cost Allocation

Problem: "Allocate energy costs to production lines."

Pattern

{EnergyTable}
| where Timestamp > ago({TimeRange})
| summarize TotalkWh = sum(kWh) by Line
| extend CostPerKwh = {RatePerKwh}
| extend TotalCost = TotalkWh * CostPerKwh
| extend TotalEnergy = toscalar(
    {EnergyTable}
    | where Timestamp > ago({TimeRange})
    | summarize sum(kWh))
| extend CostShare = round(TotalkWh / TotalEnergy * 100, 1)
| project Line, TotalkWh, TotalCost, CostShare
| order by TotalCost desc

Customization Points

Placeholder	Example
{RatePerKwh}	0.12 ($/kWh)
{EnergyTable}	EnergyReadings

---

Using Patterns with Azi

Patterns work best when you describe what you need:

1. Start with the pattern type: "I need a threshold alerting query..."
2. Specify your data: "...for temperature readings from cleanroom sensors..."
3. Add constraints: "...that shows violations above 23°C in the last 8 hours."

---

Pattern Library by Journey

Pattern	Pharma	Energy	Manufacturing
Time-series aggregation	✓	✓	✓
Threshold alerting	✓	✓	✓
Device comparison	✓	✓	✓
Historical trend	✓	✓	✓
OEE calculation	-	-	✓
Downtime pareto	-	-	✓
Batch traceability	✓	-	✓
Energy cost allocation	-	✓	✓

---

Next Steps

If you want to...	Go to...
Try pharma patterns	Pharma Journey →
Try energy patterns	Energy Journey →
Try manufacturing patterns	Manufacturing Journey →
Learn query basics	Query Guide →