OQL Examples 🚀

Aggregations

Examples of aggregate queries using functions like avg, sum, count, etc.

💡 Example 1: Weekly trend of Availability and Utilization by shift.

aggregate
    aggregation.availability*100 as 'Availability %',
    aggregation.utilization*100 as 'Utilization %'
where time from now()-90D to now()
    and factory.name = 'Factory A'
    and interval.Shift_Group.value not in ('')
    and interval.state.category.id not in (201)
    and line.name not in ('Line 1', 'Line 2')
group by interval.Shift_Group.value AS shift_name, bucket 1W AS week

💡 Example 2: Monthly trend of Production normalized to Pacific Time.

aggregate
    aggregation.output as 'Monthly Production (Lbs)'
where time from now()-6M to now()
    and line.name = ('Line 1')
group by bucket 1M AS month, factory.name AS factory_name
order by bucket desc
settings timezone='US/Pacific'

💡 Example 3: Hourly trend of linespeed metric.

aggregate
    avg(metric_group('group_uuid')) as 'linespeed'
where time from now()-7D to now()
    and interval.state.category.id = 1
    and factory.name = 'Factory A'
    and line.name in ('Line 1')
group by line.name as linename, bucket 1h as hour
order by 'line'

💡 Example 4: Analysis of production per hour of uptime

aggregate
    100 * aggregation.availability as 'Availability %',
    aggregation.output as Production,
    (timeInIf(interval.state.category.name = 'Uptime')/3600 + timeInIf(interval.state.category.name = 'Slowed Uptime')/3600) as 'Uptime Hours', (aggregation.output as Production)/(timeInIf(interval.state.category.name = 'Uptime')/3600 + timeInIf(interval.state.category.name = 'Slowed Uptime')/3600) as 'Lbs per Uptime Hour'
where time from now()-1D to now()
    and factory.name = 'Factory A'
    and line.name not in ('Line 1', 'Line 2')

Joins and multi-step queries

Use from to summarize a helper query. Use join when you need helper columns and new calculations on raw data in the same row.

💡 Example: Daily average linespeed from hourly totals (from pattern):

with hourly (linename, total) as (
    aggregate
        avg(metric_group('group_uuid')) as total
    where time from now()-7D to now()
        and factory.name = 'Factory A'
        and line.name in ('Line 1')
    group by line.name as linename, bucket 1h as hour
)
aggregate
    avg(hourly.total) as avg_linespeed
from hourly
group by linename

See FROM Clause and JOIN Clause for benchmark comparisons and other patterns.

Time Windowing

Queries that illustrate controlling query time ranges and bucketing.

💡 Example 1: Analysis of Scrap/Yield by run with shift adjusted times.

aggregate
    aggregation.output as 'Output',
    aggregation.scrap as 'Scrap',
    aggregation.yield as 'Yield',
where time from adjustTimeToInterval(now()-7D, shift.start) to adjustTimeToInterval(now()-1D, shift.end)
    and factory.name = 'Factory A'
    and line.name = 'Line 1'
group by interval.run.name as runname, product.name as productname, bucket 1D as day

Dashboard Charts

Dashboard charts are built from the table returned by your OQL query. You do not need to configure the chart columns manually. Oden looks at the result table and decides how to draw the chart.

Tip

Start with Table view when building a chart query. If the table has the right columns in the right order, the chart is much more likely to work.

The Main Rule

Think of your query results in two parts:

  1. Labels come first. These are the columns in GROUP BY, such as line, product, or shift.
  2. Numbers come after. These are usually the values in AGGREGATE, such as output, uptime hours, or availability.

For trend charts, include a time column, usually with bucket <time>.

Tip

If a chart looks wrong, first check the column order. Most chart issues happen when the labels, time column, or number columns are not in the expected order.

How Oden Reads Your Query

Query result What it usually means in a chart
GROUP BY columns Labels, categories, or series
Time column X-axis for trend charts
Numeric values from AGGREGATE Chart values
Extra columns Extra details in tooltips or links

Helpful Column Names

Some column names have special behavior:

Column name What it does
link for <column> Adds a clickable link for that column
hide_<column> or hide <column> Hides the column from the chart and tooltip
% in the name Formats the value as a percentage

Tip

Use hide_ for helper columns that the chart needs but users do not need to see.

If a Chart Looks Wrong

Switch to Table view and check:

  • Are the label columns first?
  • Are the value columns numbers?
  • Does a trend chart include a time column?
  • Are any columns hidden with hide_?
  • Are percentage columns named with %?

Table

Use a table when you want to see the raw query result, or when you are checking that a chart query is shaped correctly.

Tables can show any valid OQL result.

Oden shows:

  • Each query column as a table column
  • Each query result as a table row
  • GROUP BY columns first

Tip

Use Table view as your first stop when debugging. Once the table looks right, switch to the chart you want.

AGGREGATE
    aggregation.output as output,
    aggregation.scrap as scrap,
    aggregation.yield as yield
WHERE TIME FROM now()-7D TO now()
AND factory.name = 'Factory A'
GROUP BY line.name AS linename, product.name AS productname

Overview (Single Value)

Use an overview chart for a KPI or summary number, such as total output, availability, or uptime hours.

The query should ideally return one row. Oden displays the first row as large, simplified values.

Tip

For one overall KPI, do not use GROUP BY. Adding GROUP BY creates multiple rows, which is usually not what you want for an overview.

AGGREGATE
    aggregation.output as output,
    aggregation.availability * 100 as 'Availability %',
    timeInIf(interval.state.category.name = 'Uptime') / 3600 as uptime_hours
WHERE TIME FROM now()-1D TO now()
AND factory.name = 'Factory A'

Bar / Column / Stacked Charts

Use these charts to compare values across categories, such as uptime by line or output by product.

Your query should return:

  • Optional label columns from GROUP BY
  • At least one numeric value

Oden draws the chart like this:

  • No GROUP BY: one total bar
  • One GROUP BY: categories, such as one bar per line
  • Two GROUP BY columns: categories plus series, such as one product series per line

Column charts use vertical bars. Bar charts use horizontal bars. Stacked charts place multiple series in the same category.

Tip

Keep these charts simple. One or two GROUP BY columns are usually easiest for viewers to understand.

AGGREGATE
    timeInIf(interval.state.category.name = 'Uptime') / 3600 as uptime_hours
WHERE TIME FROM now()-90D TO now()
AND factory.name = 'Factory A'
AND line.name in ('Line 1', 'Line 2')
GROUP BY line.name AS linename, product.name AS productname

Line Trend (Time Series)

Use a line trend when you want to see how a value changes over time.

Your query should return:

  • A time column, usually bucket <time>
  • At least one numeric value
  • Optional labels, such as line or product

Oden draws the chart like this:

  • Time becomes the X-axis
  • Numeric values become the Y-axis
  • Labels become separate lines

Solid lines usually mean continuous data. Dotted lines usually mean there are gaps or invalid values.

Tip

Use one time bucket and one optional label, such as line.name. Too many labels can create too many lines.

AGGREGATE
    avg(metric_group('performance')) as performance,
    aggregation.availability * 100 as 'Availability %'
WHERE TIME FROM now()-14D TO now()
AND factory.name = 'Factory A'
AND line.name in ('Line 1', 'Line 2')
GROUP BY line.name AS linename, bucket 4h AS time

Combo Chart (Column + Line)

Use a combo chart when you want bars and lines together, such as output as bars and availability as a line.

Your query should return:

  • A time column, usually bucket <time>
  • At least two numeric values
  • Optional labels, such as line or product

Oden draws the chart like this:

  • The first numeric value becomes bars
  • The remaining numeric values become lines
  • Labels become separate series

Tip

Put the value you want as bars first in AGGREGATE. Put line values after it.

AGGREGATE
    aggregation.output as output,
    aggregation.availability * 100 as 'Availability %',
    avg(metric_group('performance')) as performance
WHERE TIME FROM now()-30D TO now()
AND factory.name = 'Factory A'
AND line.name in ('Line 1', 'Line 2')
GROUP BY line.name AS linename, bucket 1D AS day

Scatter Plot

Use a scatter plot to compare two numeric values, such as output compared with performance.

Your query should return at least two numeric values.

Oden draws the chart like this:

  • The first numeric value becomes the X-axis
  • The second numeric value becomes the Y-axis
  • GROUP BY labels create separate point groups
  • Extra columns can add more detail to the tooltip

Tip

Use GROUP BY only when you want to compare groups. For one combined view, leave GROUP BY out.

Tip

Add extra columns when tooltips need more context. Use hide_ if the extra value should not be shown.

AGGREGATE
    aggregation.output as output,
    avg(metric_group('performance')) as performance,
    aggregation.availability * 100 as 'Availability %'
WHERE TIME FROM now()-30D TO now()
AND factory.name = 'Factory A'
AND line.name in ('Line 1', 'Line 2')
GROUP BY line.name AS linename, product.name AS productname

Key Takeaways

  • Build the table shape first, then choose the chart.
  • Put labels first with GROUP BY.
  • Put values after the labels with AGGREGATE.
  • Use bucket <time> for trend charts.
  • Use simple, clear column names so charts are easier to read.

Common Dashboard Queries

Tip

  • Macros are reusable OQL expressions.
  • Using performance label instead of an uuid resolves to the a line’s performance metric.

Adherence Scores

This query calculates the adherence across all process metrics used in Process AI recommendations. 1. Value → Adherence Score across the run 2. Start_value → Adherence Score at the start of the run 3. End_value → Adherence Score at the end of the run

AGGREGATE
    macro_adherence_value as value,
    macro_adherence_start_value as start_value,
    macro_adherence_end_value as end_value
WHERE TIME FROM now()-7D to now()
AND line.name = 'Line 1'
GROUP BY interval.run.name as run, interval.process_ai_active_recommendation.start as rec_id
ORDER BY rec_id DESC

Adherence Score for one metric

This query calculates the adherence for individual process metric used in Process AI recommendation.

AGGREGATE
    macro_metric_adherence_score('uuid1') as x,
    macro_metric_adherence_score('uuid2') as y,
    macro_metric_adherence_score('uuid3') as z,
    (x+y+z)/3 as score,
    timeinif()/60 as rec_duration
WHERE TIME FROM now()-7D to now()
    AND line.name = 'Line 1'
    AND interval.state.category.name='Uptime'
GROUP BY interval.run.name as run, interval.process_ai_active_recommendation.start as rec_id
HAVING rec_duration>2

Identify product target issues

This query identifies products with target issues.

with product_stats as (
    aggregate
        cast(interval.run.performance_target.value, 'Float64') as target,
        cast(interval.run.performance_target.usl, 'Float64') as usl,
        p90(metric_group('performance')) as p90,
        p75(metric_group('performance')) as p75,
        p50(metric_group('performance')) as median,
        avg(metric_group('performance')) as bench_avg,
        if(p75 > 1.0 * usl, 1, 0) as candidate_for_usl_change,
        (p75 - usl) as diff,
        timeinif(interval.state.category.name = 'Uptime') / 3600 as duration
    where time from now()-30D to now()
        and interval.state.category.name = 'Uptime'
        and interval.run.performance_target.value is not null
        and interval.run.performance_target.usl is not null
    group by line.name as linename, product.name as productname
)
aggregate
    product_stats.target as target,
    product_stats.usl as usl,
    product_stats.p90 as p90,
    product_stats.p75 as p75,
    product_stats.median as median,
    product_stats.bench_avg as bench_avg,
    product_stats.candidate_for_usl_change as candidate_for_usl_change,
    product_stats.diff as diff,
    product_stats.duration as duration
from product_stats
order by diff desc, duration desc, linename asc, productname asc
having candidate_for_usl_change = 1