CODEWITHSUNDEEP
influxdbgrafana
Score_Under
Score_Under

Reputation: 1216

Can I call an aggregate function over multiple dimensions in influxdb?

I have a query in Grafana:

SELECT mean(value) FROM "queues_value" WHERE "host" =~ /$host/ AND "type" = 'rabbitmq_messages' AND $timeFilter GROUP BY time($interval), "host", "instance"

Currently this graphs the average number of messages in a rabbitmq queue over a given time period, showing separate graphs per queue per host.

I would like to do essentially the same thing, but resulting in only one graph per host adding together all the queues. This would mean taking the mean of all message counts over the specified time period (keeping different instances, hosts, and time intervals separate), then summing them together (this time keeping only different hosts and time intervals separate).

The problem is, each aggregate function like mean or sum will only affect an entire query, and there doesn't seem to be a way to apply GROUP BY statements to just one aggregate function.

If it were possible, I'd have done something like this:

SELECT sum(mean(value) GROUP BY "instance") FROM "queues_value" WHERE "host" =~ /$host/ AND "type" = 'rabbitmq_messages' AND $timeFilter GROUP BY time($interval), "host"

Or this:

SELECT sum(value) FROM (SELECT mean(value) FROM "queues_value" WHERE "host" =~ /$host/ AND "type" = 'rabbitmq_messages' AND $timeFilter GROUP BY time($interval), "host", "instance") GROUP BY time($interval), "host"

But neither of those are valid syntax.

In essence, I'm trying to first work on an aggregate containing one specific queue on one specific host over a given time period, and then trying to work on an aggregate formed of the results of that calculation per host over the same time period.

Is there a way to accomplish what I want in either influxdb or grafana?

Upvotes: 1

Views: 2250

Answers (1)

Michael Desa
Michael Desa

Reputation: 4747

There is a way to do this with InfluxDB, but its a two step process.

First, create continuous query that computes the mean

CREATE CONTINUOUS QUERY <name> ON <database>
BEGIN
  SELECT mean(value) AS value
  INTO "mean_queue_value" FROM "queues_value"
  WHERE "host" =~ /<host>/
    AND "type" = 'rabbitmq_messages'
  GROUP BY time(<interval>), "host"
END

Then simply issue a query 

SELECT sum(value) FROM "mean_queue_value" GROUP BY time(<interval>), "host"

Upvotes: 2

Related Questions