5

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

1
  • I just skimmed thru the docs but can't find any info on union or usage examples. My apologies. I've very new to Influx. Building these simple network stats is my first foray into it.
    – Mike
    Jan 27, 2017 at 14:10

3 Answers 3

6

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)
2
  • 2
    This just throws a syntax error on the comma separating the subqueries for me. Influx 1.5.2 Each subquery needs t be in parenthesis, there is no parenthesis around the subqueries as a whole. github.com/influxdata/influxdb/issues/… Sep 30, 2019 at 22:02
  • @DouglasGaskell Yep- looks like I dropped the parens while editing the answer. Added them back now. Thanks for spotting.
    – Morphit
    Oct 12, 2019 at 22:19
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)
2
  • Is it possible to parenthesis the WHERE conditions, so that the precedence is clear? Feb 3, 2017 at 13:48
  • 1
    are you sure about that? I have 3 hosts that I tried this on...while the individual graphs show ~75Mbps each, when I try your SELECT, I get ~200Tbps aggregate... I think because of the sum
    – Evan R.
    Feb 9, 2017 at 20:20
0

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

1
  • Doesn't address the issue: some of the series are in inoctets1, some are in incotets4. This answer depends on all series being in a single measurement.
    – Jason
    Jun 18, 2020 at 2:52

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.