What query do I need to sum these field values across tags?

InfluxDB 1
1

I am trying to get the total shard sizes from the _internal InfluxDB database. The field is called diskBytes, and it is stored every 10 seconds by default. It is queried by something like

SELECT diskBytes FROM "shard" WHERE "database"='_internal' AND time>t1 AND time<t2

There is a value for each of the paths (labelled by a tag), but I need to get the total size across all paths and am struggling to get the right query to sum them.

The data looks like this:

Clipboard08
Clipboard081577×817 235 KB

If I try to group them by a small time interval (less than the 10s collection rate), then it works correctly using the query

SELECT sum(diskBytes) FROM "shard" WHERE "database"='_internal' AND time>t1 AND time<t2 GROUP BY time(5s)

However, I would now like to query the data at a lower rate, let’s say once every 10 minutes or something. Then have it return the first/mean value over those 10 minutes. If I group by anything longer than 10s, then you can see below that it doesn’t work (it just sums across all values in the time interval, rather than summing over paths only).

In this screenshot you can see the one that works (top) and the one that doesn’t (bottom):

Clipboard09
Clipboard09579×628 144 KB

What’s the right way to query this?

(I am using InfluxDB v1.8)

2

You need a subquery.
The inner one will be the one grouped by 10s (that you already have), on top of that you can a different query and get the min/max/last/whatever
something like this

SELECT last(value) AS LastSize, mean(value) as AvgSize
FROM (
  SELECT sum(value) AS value FROM __ GROUP BY time(10s)
) GROUP BY time(_interval_)
3

Excellent, thanks that was exactly what I needed (just didn’t know the terminology “subquery”).

Cheers!