JMeter, Influx2, and InfluxQL

InfluxDB 2
, ,
1

Hiya,
I am running into issues with Influxql and the time field. I tried to make a subquery using the time field, but I’ve run into errors (because time cannot be selected as a scalar?).

I am using the JMeter influx driver to capture data. I would like to display the two most recent benchmarks runs for an application in a single grafana window. I would like to filter using an input variable for the application name. I tried creating an influxql statement with a subquery, but I have only run into errors.

There are two measurements event and jmeter. The event measurement denotes a benchmark start and stop time. I was hoping to use the events to filter the jmeter measurement. But I cannot get time as a scalar value in a subquery. :cry:

SELECT * FROM "events" ORDER BY "time" DESC LIMIT 2
┏━━━━━━━┳━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━┳━━━━━━━━━━━━━┳━━━━━━━━━━━━━━━━━┳━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━┳━━━━━━━━━━━━━━┓
┃ index ┃              time              ┃ application ┃      tags       ┃                         text                         ┃    title     ┃
┣━━━━━━━╋━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━╋━━━━━━━━━━━━━╋━━━━━━━━━━━━━━━━━╋━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━╋━━━━━━━━━━━━━━┫
┃      1┃  1729700490540000000.0000000000┃APP1         ┃version=v2.1.0   ┃APP1-r1000000-t10-g1-l100000-d10-m100001 ended        ┃ApacheJMeter  ┃
┃      2┃  1729699890484000000.0000000000┃APP1         ┃version=v2.1.0   ┃APP1-r1000000-t10-g1-l100000-d10-m100001 started      ┃ApacheJMeter  ┃
┣━━━━━━━┻━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━┻━━━━━━━━━━━━━┻━━━━━━━━━━━━━━━━━┻━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━┻━━━━━━━━━━━━━━┫

Most recent benchmark for APP1
How can I query the latest benchmark results for a given application?

SELECT "jmeter".* 
FROM "jmeter" 
WHERE 
  time >= (
    SELECT time 
    FROM "events" 
    WHERE "text" =~ /started$/ 
      AND "application" = 'APP1' 
    ORDER BY time DESC 
    LIMIT 1
  ) 
  AND
  time <= (
    SELECT time 
    FROM "events" 
    WHERE "text" =~ /ended$/ 
      AND "application" = 'APP1' 
    ORDER BY time DESC 
    LIMIT 1
  )

Previous benchmark for APP1
How can I query the previous benchmark results for a given application?

SELECT "jmeter".* 
FROM "jmeter" 
WHERE 
  time >= (
    SELECT time 
    FROM "events" 
    WHERE "text" =~ /started$/ 
      AND "application" = 'APP1' 
    ORDER BY time DESC 
    OFFSET 1
    LIMIT 1
  ) 
  AND
  time <= (
    SELECT time 
    FROM "events" 
    WHERE "text" =~ /ended$/ 
      AND "application" = 'APP1' 
    ORDER BY time DESC 
    OFFSET 1
    LIMIT 1
  )
2

To put it another way, I’d like to convert this to SQL

first_start_time =
from(bucket:"jmeter")
  |> range(start: -1w)
  |> filter(fn: (r) => r._measurement == "events" and r._field == "text")
  |> filter(fn: (r) => r.application == "APP1" and r._value =~ /started$/)
  |> sort(desc: true, columns: ["time"])
  |> limit(n: 1)
  |> findRecord(fn: (key) => key.application=="APP1", idx:0)

first_end_time =
from(bucket:"jmeter")
  |> range(start: -1w)
  |> filter(fn: (r) => r._measurement == "events" and r._field == "text")
  |> filter(fn: (r) => r.application == "APP1" and r._value =~ /ended$/)
  |> sort(desc: true, columns: ["time"])
  |> limit(n: 1)
  |> findRecord(fn: (key) => key.application=="APP1", idx:0)

from(bucket: "jmeter")
  |> range(start: first_start_time._time, stop: first_end_time._time)
  |> filter(fn: (r) => r._measurement == "jmeter")
3

@peecee
Would you like it converted to SQL or InfluxQL?
Just checking because in v3 you can do both.

I dont see the difference between your previous and current influxql queries. Can you help me spot it?

Is your Flux query complete? I don’t see you doing anything with the findrecord value…

Thanks!

4

Hi @Anaisdg
Thanks for helping me clarify my request. I see now there’s a difference between InfluxQL and SQL.
We’re running influx 2, so I think that means we need an InfluxQL statement, not SQL. Right?

Is your Flux query complete? I don’t see you doing anything with the findrecord value

In the flux query example I use the findRecord results as the range start and stop of the third query.

I dont see the difference between your previous and current influxql queries. Can you help me spot it?

The InfluxQL SELECT statement returns an error:

> SELECT * FROM "jmeter" WHERE time >= ( SELECT time FROM "events" WHERE "text" =~ /started$/ AND "application" = 'APP1' ORDER BY time DESC LIMIT 1 ) AND time <= ( SELECT time FROM "events" WHERE "text" =~ /ended$/ AND "application" = 'APP1' ORDER BY time DESC LIMIT 1 )
ERR: 400 Bad Request: failed to parse query: found SELECT, expected identifier, string, number, bool at line 1, char 40