Select from multiple measurements

1

I struggle with a SELECT from multiple measurements computing a single result per time interval.

  • one measurement has resource usage breakdown by user
  • other measurement instruments installed capacity

The number of points per time interval is not the same for both measurements (usage even varies over time). Now I would like to compute utilization like

select sum(usage)/sum(capacity) from usage,capacity group by time(1m)

Unfortunately influx runs the same query against each measurement, finding one of the two sum() values undefined in each case. Did I miss something in the syntax where I can specify which measurement the field should come from?

Rob

2

I don’t know if this help you

SELECT sum(s) FROM (SELECT last("sessions") AS s FROM "win_rdp" GROUP BY time($__interval), "host") GROUP BY time($__interval)

With above query we get the summary of all sessions from win_rdp, grouped by time

3

Thank you for the inspiration, and I learned a bit about nested queries, but I can’t seem to get away from the multiple measurements. What I would really need is expressions using fields from multiple measurements. I can understand something would need to enforce alignment by the same grouping.

4

This is something that has been on the radar for a while and is ultimately set to be addressed with IFQL in future influxdb releases. Unfortunately your only recourse for now is to either using kapacitor or writing into a continuous query.

Trust me, it’s a PITA that a lot of people (including myself!) have come across but the new language will address this pretty much head-on.

5

You should be able to generate this using a Nested query. It will be something like

  1. Align the Sum
  2. Perform the computation

select usage/capacity from (select sum(usage) as usage, sum(capacity) as capacity from usage,capacity group by time(1m)) group by time(1m)

6

I have been beating my head against this, but I don’t get them into a single result. When I select from two measurements, the result of the inner select is returned in two tables as well; one with “capacity” filled in, one with “usage” filled in. Trying to use both in an expression results in two empty answers.