How can I combine two different measurements?

influxdb

#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 (https://github.com/alexcesaro/statsd) 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