Plotting metrics from OpenTelemetry's Host Metrics Receiver

[alexveecle]

I have the Host Metrics Receiver configured so that I can query metrics in ClickHouse like:

SELECT TimeUnix, Value
FROM "otel_metrics_sum"
WHERE MetricName = 'system.memory.usage'
  AND Attributes['state'] = 'free'
ORDER BY TimeUnix DESC

This displays a table with reasonable values. However, I'm struggling to chart in the Chart Explorer. What I do is:

• Select the data source
• In the form:
  • Average
  • system.memory.usage
  • Where: Attributes['state'] = 'free'

But this plots a bizarre chart with some spikes, whereas the table displays what looks like a "smooth" time series.

Trying the SQL builder with queries such as:

SELECT TimeUnix, Value
FROM "otel_metrics_sum"
WHERE MetricName = 'system.memory.usage'
  AND Attributes['state'] = 'free'
  AND (TimeUnix BETWEEN fromUnixTimestamp64Milli ({startDateMilliseconds:Int64}) AND fromUnixTimestamp64Milli ({endDateMilliseconds:Int64}))
ORDER BY TimeUnix DESC

Does not produce anything either.

Probably I'm missing something obvious?

[alexveecle]

I needed FROM $__sourceTable(sum) instead.

[alexveecle]

For the record, handling granularity (hopefully correctly?):

SELECT toStartOfInterval(TimeUnix, INTERVAL {intervalSeconds:Int64} second) AS ts, avg(Value)
FROM $__sourceTable(sum)
WHERE MetricName = 'system.memory.usage'
  AND Attributes['state'] = 'free'
  AND TimeUnix BETWEEN fromUnixTimestamp64Milli ({startDateMilliseconds:Int64}) AND fromUnixTimestamp64Milli ({endDateMilliseconds:Int64})
GROUP BY ts