Influxdb and Grafana combine multiple SELECT

Question

I have some network counters in Influx that I graph with Grafana.

I'm trying to combine stats from a couple ethernet interfaces and combine them to show aggregate bandwidth.

These two separate queries work on their own, but I can't figure out how to write it to combine these two.

SELECT 8 * non_negative_derivative(mean("value"), 1s ) 
FROM "inoctets" 
WHERE "host" = 'myhost1' AND $timeFilter 
GROUP BY time(1s) fill(null)

SELECT 8 * non_negative_derivative(mean("value"), 1s ) 
FROM "inoctets" 
WHERE "host" = 'myhost4' AND $timeFilter 
GROUP BY time(1s) fill(null)

Edit: FWIW I'm running Influx 1.1.1 and Grafana 4.1.1

Answer 1

To combine aggregated measurements under different tags is a bit tricky. I managed to find this post which has the answer.

The key is to use sub-queries (InfluxDB 1.2+) to capture each tag's measurements separately. Then, since the field names are the same, they need to be renamed. Finally you can manipulate them as needed. Here's your example:

SELECT 8 * (non_negative_derivative(mean("inoctets1"), 1s )
          + non_negative_derivative(mean("inoctets4"), 1s ))
AS "inbytestotal"
FROM (
    SELECT "value"
    AS "inoctets1"
    FROM "inoctets"
    WHERE "host" = 'myhost1' AND $timeFilter
), (
    SELECT "value"
    AS "inoctets4"
    FROM "inoctets"
    WHERE "host" = 'myhost4' AND $timeFilter
)
GROUP BY time(1s) fill(null)

Answer 2

Figured it out after weeks of trial and error. I was trying to use AND instead of OR. But OR wouldn't give me the right values until I added SUM into the mix.

So for future searchers, this works..

SELECT 8 * non_negative_derivative(sum("value"), 1s) 
FROM "inoctets" 
WHERE "host" = 'myhost1' OR "host" = 'myhost4' AND $timeFilter 
GROUP BY time($interval)

Answer 3

It is also possible to use wildcards for the tag values provided that "host" is in fact a tag key by using the =~ operator

select * from "inoctets" where host =~ /myhost1/myhost4/ limit 10

I use this quite frequently in influx / grafana setups