02 API Queries

Query the ENLYZE API from Grafana dashboards.

What You'll Learn

• Use Grafana's time range in API queries

• Query production runs with OEE metrics

• Transform API responses with JSONata

• Query downtimes for analysis

• Debug API queries

Prerequisites

• Completed Tutorial 4.1 (Introduction to Infinity)

• Understanding of GET requests and JSONPath

---

Overview

In the previous tutorial, you learned to make basic GET requests. Now we'll cover:

1. Dynamic time ranges – Connect Grafana's time picker to API queries

2. Production runs – Query runs with built-in OEE metrics

3. Response transformation – Extract and reshape nested data with JSONata

4. Downtimes – Query machine downtime records

---

Dynamic Time Ranges

Hard-coded dates aren't useful for real dashboards. Let's connect queries to Grafana's time picker.

Grafana Time Variables

Grafana provides built-in variables for the selected time range:

Variable	Description	Example Value
${__from}	Start time (milliseconds)	1701388800000
${__to}	End time (milliseconds)	1701475199999
${__from:date:iso}	Start time (ISO format)	2024-12-01T00:00:00.000Z
${__to:date:iso}	End time (ISO format)	2024-12-01T23:59:59.999Z

These variables update automatically when users change the dashboard time picker.

---

Production Runs

The production-runs endpoint returns not just run information, but also OEE metrics for each run.

What's in a Production Run?

Each production run includes:

Category	Fields
Basic Info	uuid, machine, product, production_order
Timing	start, end
Quantity	quantity_total, quantity_yield, quantity_scrap
OEE Metrics	productivity, availability, performance, quality
Speed	average_throughput, maximum_run_speed

Endpoint

Setting	Value
Method	GET
URL	production-runs
Root selector	$.data

Query Parameters

Parameter	Description
machine	Filter by machine UUID
start	Filter runs starting from (ISO format)
end	Filter runs up to (ISO format)
product	Filter by product UUID
product-external-id	Filter by product external ID

---

Your First Time-Filtered Query

Let's create a panel showing production runs for the selected time range.

Step 1: Create a Panel

1. Create a new panel with Table visualization

2. Select Infinity data source

Step 2: Configure the Query

Setting	Value
Type	JSON
Parser	Backend
Source	URL
Format	Table
Method	GET

Step 3: Add Time-Filtered URL

Use Grafana's time variables in the URL query parameters:

production-runs?machine=141e0927-62b3-4e76-8398-ad82d20f397f&start=${__from:date:iso}&end=${__to:date:iso}

[SCREENSHOT: Infinity query with time variables in URL]

Step 4: Set Root Selector

$.data

Step 5: Test Time Filtering

1. Set dashboard time range to "Last 7 days"

2. Check that the panel shows production runs for that period

3. Change to "Last 30 days"

4. Verify more runs appear

[SCREENSHOT: Production runs table with time picker]

---

Understanding the Response

The production-runs response includes nested OEE data:

{
  "data": [
    {
      "uuid": "abc123...",
      "machine": "141e0927-62b3-4e76-8398-ad82d20f397f",
      "production_order": "PO-2024-001",
      "start": "2024-12-01T08:00:00.000Z",
      "end": "2024-12-01T16:00:00.000Z",
      "average_throughput": 125.5,
      "quantity_total": { "value": 1000, "unit": "kg" },
      "quantity_yield": { "value": 950, "unit": "kg" },
      "quantity_scrap": { "value": 50, "unit": "kg" },
      "productivity": { "score": 0.72, "time_loss": 3600 },
      "availability": { "score": 0.85, "time_loss": 1800 },
      "performance": { "score": 0.90, "time_loss": 1200 },
      "quality": { "score": 0.95, "time_loss": 600 }
    }
  ]
}

Notice that OEE metrics and quantities are nested objects. We'll use JSONata to extract these values.

---

Extracting Nested Values with JSONata

JSONata is a query language for transforming JSON. It goes beyond JSONPath and lets you reshape data.

Basic Extraction

To extract OEE scores from production runs, use this root selector:

$.data.{
  "production_order": production_order,
  "start": start,
  "end": end,
  "productivity": productivity.score,
  "availability": availability.score,
  "performance": performance.score,
  "quality": quality.score
}

Result:

production_order	start	end	productivity	availability	performance	quality
PO-2024-001	2024-12-01T08:00:00Z	2024-12-01T16:00:00Z	0.72	0.85	0.90	0.95

[SCREENSHOT: Clean production runs table with OEE scores]

JSONata Basics

Expression	Description
$.data	Access the data array
$.data[0]	First element
field.subfield	Access nested property
{ "name": value }	Create new object
$map(array, fn)	Transform each element

Common Patterns

Extract single value:

$.data[0].productivity.score

Extract quantity values:

$.data.{
  "total": quantity_total.value,
  "yield": quantity_yield.value,
  "scrap": quantity_scrap.value,
  "unit": quantity_total.unit
}

Handle missing data:

$.data.{
  "quality": $exists(quality) ? quality.score : null
}

Calculate values:

$.data.{
  "scrap_rate": quantity_scrap.value / quantity_total.value
}

---

Adding Columns for Better Display

Instead of using JSONata to reshape everything, you can also use Columns in the Infinity query to select specific fields.

Step 1: Add Columns

Scroll to the Columns section and add:

Selector	Title	Type
production_order	Order	String
start	Start	Timestamp
end	End	Timestamp
productivity.score	OEE	Number
availability.score	Availability	Number
performance.score	Performance	Number
quality.score	Quality	Number
quantity_yield.value	Yield	Number

[SCREENSHOT: Columns configuration]

Step 2: Format Display

In Field overrides, add formatting:

1. Override fields matching /score$/ (regex):

   • Unit: Percent (0.0-1.0)

   • Min: 0, Max: 1

2. Override "Yield" field:

   • Unit: Use the unit from your data (e.g., kg, m)

---

Visualizing OEE in Tables

Gauge Cells

For OEE scores, gauge cells provide great visualization:

1. Go to Field overrides

2. Add override for fields matching /score$/

3. Set Cell type to Gauge

4. Set Min to 0, Max to 1

5. Add thresholds:

   • Red: 0

   • Yellow: 0.6

   • Green: 0.8

[SCREENSHOT: Production runs table with gauge cells]

Colored Background

Alternatively, use colored backgrounds:

1. Set Cell type to Colored background

2. Add the same thresholds

3. Set Color mode to Background (gradient)

---

Stat Panels for Single Values

Show the latest production run's OEE as a stat panel:

Step 1: Create Stat Panel

1. Add new panel with Stat visualization

2. Configure Infinity query for production-runs

Step 2: Extract Single Value

Use JSONata to get just the productivity score from the most recent run:

Root selector:

$.data[0].productivity.score

Step 3: Configure Display

Setting	Value
Unit	Percent (0.0-1.0)
Color mode	Background
Thresholds	Red < 0.6, Yellow < 0.8, Green >= 0.8

[SCREENSHOT: OEE stat panel]

---

Downtimes

Query machine downtime records for analysis.

Endpoint

Setting	Value
Method	GET
URL	downtimes?machine={uuid}&start=${__from:date:iso}&end=${__to:date:iso}
Root selector	$.data

Query Parameters

Parameter	Description
machine	Filter by machine UUID(s)
start	Filter downtimes starting from
end	Filter downtimes up to

Example: Recent Downtimes

downtimes?machine=141e0927-62b3-4e76-8398-ad82d20f397f&start=${__from:date:iso}&end=${__to:date:iso}

Calculating Downtime Duration

Use JSONata to calculate duration in minutes:

$.data.{
  "start": start,
  "end": end,
  "duration_minutes": ($toMillis(end) - $toMillis(start)) / 60000
}

---

Combining Production Runs from Multiple Machines

Using Computed Columns

When querying multiple machines, add a machine name column:

Query A (Kiefel):

• URL: production-runs?machine=141e0927-62b3-4e76-8398-ad82d20f397f&start=${__from:date:iso}&end=${__to:date:iso}

In Computed columns, add:

• Selector: "Kiefel" (literal string in quotes)

• Title: Machine

• Type: String

Query B (Macchi):

• URL: production-runs?machine=cc0d2dcb-564b-48cd-a342-71765a536058&start=${__from:date:iso}&end=${__to:date:iso}

In Computed columns, add:

• Selector: "Macchi"

• Title: Machine

Merging Results

Add a Merge transformation to combine both queries into one table.

[SCREENSHOT: Multi-machine production runs table]

---

Joining with Product Data

Production runs contain a product field with a UUID, but not the product name or external ID. To display the product's external ID (from your ERP/MES), you need to join with the products endpoint.

The Challenge

The production-runs response only contains:

{
  "product": "4646d168-a98b-447e-834a-d36dd1f33eb4"
}

But you want to display:

Product ID: 6009400032

Solution: Join Transformation

Use multiple queries and Grafana's Join transformation.

Step 1: Create Two Queries

Query A (Production Runs):

Setting	Value
URL	production-runs?machine=141e0927-62b3-4e76-8398-ad82d20f397f&start=${__from:date:iso}&end=${__to:date:iso}
Root selector	$.data

Add columns:

Selector	Title	Type
production_order	Order	String
product	product_uuid	String
start	Start	Timestamp
productivity.score	OEE	Number

Query B (Products):

Setting	Value
URL	products
Root selector	$.data

Add columns:

Selector	Title	Type
uuid	product_uuid	String
external_id	Product ID	String
name	Product Name	String

Important: The join field must have the same column name in both queries (product_uuid).

[SCREENSHOT: Two queries configured for join]

Step 2: Add Join Transformation

1. Go to Transform tab

2. Add Join by field transformation

3. Configure:

Setting	Value
Mode	OUTER
Field	product_uuid

[SCREENSHOT: Join transformation configuration]

Step 3: Clean Up with Organize

Add Organize fields transformation to:

• Hide the duplicate product_uuid column

• Reorder columns as desired

Result

Order	Product ID	Product Name	Start	OEE
PO-2024-001	6009400032	Film 50um Clear	2024-12-01T08:00:00Z	72%
PO-2024-002	6009400045	Film 75um Blue	2024-12-01T16:00:00Z	85%

[SCREENSHOT: Final joined table with product information]

Join Modes

Mode	Description
INNER	Only rows with matches in both queries
OUTER	All rows from both queries
LEFT	All rows from first query, matches from second
RIGHT	All rows from second query, matches from first

For production runs + products, use OUTER or LEFT to keep all runs even if product data is missing.

---

Error Handling and Debugging

Common Errors

Error	Cause	Solution
404 Not Found	Wrong URL or UUID	Verify endpoint and machine UUID
422 Unprocessable	Invalid date format	Use ISO 8601 format
Empty data	Time range has no data	Expand time range
Nested objects in table	Missing column extraction	Add Columns or use JSONata

Debugging Tips

1. Check Network Tab

   • Open browser DevTools (F12)

   • Go to Network tab

   • Look for the API request

   • Check request URL and response

2. Test with Table First

   • Always start with Table visualization

   • Easier to see raw data structure

   • Switch to other visualizations after data works

3. Simplify Root Selector

   • Start with just $.data

   • Verify data structure

   • Then add JSONata transformations

4. Use Debug Transformation

   • Add Debug transformation

   • See exactly what data Grafana receives

---

Practical Exercise

Build a production overview dashboard:

1. Production Runs Table

   • Query production-runs for Kiefel machine

   • Filter by dashboard time range

   • Extract: order, start, end, OEE scores, yield

   • Display OEE as gauge cells

2. Latest Run Stats

   • Four stat panels: Productivity, Availability, Performance, Quality

   • Extract from most recent run with $.data[0]

   • Use thresholds for color coding

3. Downtimes Table

   • Query downtimes for same machine and time range

   • Show start, end, calculated duration

---

Summary

You've learned to query production data with dynamic time ranges:

• Use ${__from:date:iso} and ${__to:date:iso} in URL parameters

• Query production runs with built-in OEE metrics

• Extract nested values with JSONata

• Visualise OEE with gauge cells and stat panels

• Query and analyse downtimes

---

What's Next?

Continue with:

• Working with Variables – Create dropdowns for site and machine selection

---

Quick Reference

Time Variables

Variable	Format	Use Case
${__from:date:iso}	ISO 8601	API datetime parameters
${__to:date:iso}	ISO 8601	API datetime parameters
${__from}	Milliseconds	Calculations

JSONata Cheat Sheet

Expression	Result
$.data	Data array
$.data[0]	First element
field.subfield	Nested access
{ "a": x, "b": y }	New object
$exists(field)	Check if exists
condition ? a : b	Conditional
$toMillis(date)	Date to milliseconds

Key Endpoints

Endpoint	Method	Description
production-runs	GET	Production history with OEE
downtimes	GET	Downtime records
machines	GET	Machine list
sites	GET	Site list