How can I combine two different measurements?

Telegraf
1

I am trying to achieve something that seems simple and, I would guess, possible in InfluxDB.

I have two measurements (both counters): MessagesReceived and MessagesProduced. What I want is to calculate the ratio in between produced and received. So I am looking for a query that looks like this:

select A.value / B.value from MessagesProduced as A, MessagesReceived as B

Naively speaking, seems to be simple and straightforward. But I couldn’t find a way to make it work with InfluxDB. So, my questions are:

  1. Is it possible to do something like this in InfluxDB?
  2. If not, is there a workaround?
  3. If yes, how?

Thanks

2

Hello @ricardoatsouza,

I was able to do this with this query SELECT "usage_guest"/"usage_system" FROM "telegraf"."autogen"."cpu" limit 10 from the default telegraf database which gathers metrics from my local machine.

Could you maybe share your schema and exact query, if that syntax doesn’t work for you?

Best,

Anais

3

Hi @Anaisdg,
Thanks for the answer.

In your example, you are making the division of two fields from the same measurement. The problem is that I want to do it with the value of two different measurements: MessagesProduces and MessagesReceived. And, to make it more fun, they have both a field named value.

I know I can select both values with this query:

select value from MessagesReceived, MessagesProduced

But I cannot get what I expect running:

select value/value from MessagesReceived, MessagesProduced

The reason of why it doesn’t work is because InfluxDB doesn’t know to whom value belongs to, which is very clear to me. So, I would expect influxDB to able to make references to them in the select clause, like:

select MessagesProduced.value/MessagesReceived.value from MessagesReceived, MessagesProduced

Or to have aliases, like:

select A.value/B.value from MessagesReceived as B, MessagesProduced as A

This nails down the question to: is it possible to make references in the select clause to different measurements in the from clause? Even if they have the same field name?

Thanks

Ricardo

4

Hello @ricardoatsouza,

Unfortunately, there is no way to perform cross-measurement math or grouping with influxql. All data must be under a single measurement to query it together. This issue has been raised. With Flux you will be able to do this.

Best,

Anais

5

If you can change your series format, here’s a suggestion.

measurement : Messages
fields :

  • Produced
  • Received

So the query becomes

SELECT "Produced" / "Received" FROM Messages

6

Hi @samaust,

That’s what I am considering doing.
The problem is that I am using a Golang library statsd (GitHub - alexcesaro/statsd: An efficient Statsd Go client.) that doesn’t give the option to create more fields under the same measurement (or I am really missing something). As a result, every measurement goes under the one field value in influxDB.

@Anaisdg I will give a check on Flux. But, either way, the feature cross-measurement math sounds like something not so difficult to do. Are there any plans in the pipeline to include it in InfluxDB?

Thanks

Ricardo

7

Hi @ricardoatsouza,

Yes, Flux is our new query engine and it will allow you to perform cross-measurement math.

Best,

Anais

8

If you cannot change your measurement schema, you can Continuous Query and downsample your records to achieve this. ie.,
Select sum(value) as value_produced INTO Messages from MessagesReceived where …
Select sum(value) as value_consumed INTO Messages from MessagesProduced where …

Now, select value_produced/value_consumed from Messages where…

9

Welcome to InfluxData Community — thanks for contributing!

10

Hey,I am getting the same problem, I want to subtract the time of different measurement but the thing is we cannot add 3 time column in a measurement and there is no JOIN clause in InfluxQL. Now I want to write queries in Flux but it is not supporting it in 1.8 version of Influxdb whereas there is no any download link available for windows of InfluxDB2.0 version.I am using grafana latest stable version(7.2.1).

11

Hello @amanmishra1321,
Are you not able to enable Flux in 1.8?