Obtaining a total of two series of data from InfluxDB in Grafana

Question

I am perplexed at this point. I spent a day or three in the deep end of Influx and Grafana, to get some graphs plotted that are crucial to my needs. However, with the last one I need to total up two metrics (two increment counts, in column value). Let's call them notifications.one and notifications.two. In the graph I would like them displayed, it would work well as a total of the two, a single graph line, showing (notifications.one + notifications.two) instead of two separate ones.

I tried with the usual SELECT sum(value) from the two, but I don't get any data from it (which does exist!). There is also merge() mentioned in the documentation of Influx, but I cannot get this to work either.

The documentation for merge requires something like:

SELECT mean(value) FROM /notifications.*/ WHERE ...

This also, comes back as a flat zero line.

I hope my question carries some weight, since I have far from enough knowledge to convey the problem as good as possible.

Thank you.

Solution 1

In InfluxDB 0.9 there is no way to merge query results across measurements. Within a measurement all series are merged by default, but no series can be merged across measurements. See https://influxdb.com/docs/v0.9/concepts/08_vs_09.html#joins for more detail.

A better schema for 0.9 is instead of two measurements: notifications.one and notifications.two, have one measurement notifications with foo=one and foo=two as tags on that single measurement. Then the query for the merged values is just SELECT MEAN(value) FROM notifications and the per-series query is then SELECT MEAN(value) FROM notifications GROUP BY foo

Solution 2

With InfluxDB 0.12 you can write:

SELECT MEAN(usage_system) + MEAN(usage_user) + MEAN(usage_irq) AS cpu_total 
  FROM cpu
  WHERE time > now() - 10s 
  GROUP BY host;

These features are not really documented yet, but you can have a look at supported mathematical operators.

Solution 3

I think as per the question its possible to club queries together just like nested queries in RDBMS. This can be achieved using Continous Queries in influxdb. This documentation explains it clearly. Basically you need to create a query from other queries and then use this newly created query to fetch the series.

https://docs.influxdata.com/influxdb/v1.1/query_language/continuous_queries/#substituting-for-nested-functions

Comments

[Marlon van der Linde]

Thank you, I marked your response as correct, since it seems to be a logical approach. It will work for me. I guess it also cleans things up a bit. I initially didn't know that 0.8 and 0.9 had this difference (I usually try to steer clear of old docs).

[davidA]

Do you know if there's a way to do this when the values are from the same measurement? I.e. use a WHERE clause to select two series based on a tag value, and add/subtract those? Your command above seems to require multiple measurements.

[Tombart]

@meowsqueak That would require Meta Queries that are on long term roadmap. Recently the feature has been removed from 5.0 milestone, so it's hard to tell when it'll be available.