How to transform below query in flux

InfluxDB 2
,
1

SELECT sum(“error”) / sum(“all”) FROM (SELECT sum(“count”) AS “all” FROM “$measurement_name” WHERE “transaction” = ‘all’ AND “application” =~ /^$application$/ AND $timeFilter GROUP BY time($__interval) fill(null)), (SELECT sum(“countError”) AS “error” FROM “$measurement_name” WHERE “transaction” = ‘all’ AND “application” =~ /^$application$/ AND $timeFilter GROUP BY time($__interval) fill(null))

2

Hello @Sangeeta_Agarwal,
The following blog might be useful to you:

3

@Sangeeta_Agarwal I think this will get you what you’re looking for:

import "regexp"

baseData = () => from(bucket: "example-bucket")
    |> range(start: v.timeRangeStart, stop: v.timeRangeStop)
    |> filter(fn: (r) => r._measurement == "${measurement}")
    |> filter(fn: (r) => r.transaction == "all" and r.application == =~ regexp.compile(v: "${application}"))
    |> filter(fn: (r) => r._field == "count" or r._field == "errorCount")
    |> aggregateWindow(every: v.windowPeriod, fn: sum)
    |> pivot(rowKey: ["_time"], columnKey: ["_field"], valueColumn: "_value")
    |> map(fn: (r) => ({_time: r._time, _value: r.errorCount / r.count}))
4

@scott thank you! I wrote the translation and then forgot to post :woman_facepalming:

5

Thank you Scott.
But when I try the query I don’t get any results:

In Influx data explorer, I get No results. But there are errors but still it is not calculating

6

Oh, sorry. I iterated on the query and forgot to remove something. Try this:

import "regexp"

from(bucket: "example-bucket")
    |> range(start: v.timeRangeStart, stop: v.timeRangeStop)
    |> filter(fn: (r) => r._measurement == "${measurement}")
    |> filter(fn: (r) => r.transaction == "all" and r.application == =~ regexp.compile(v: "${application}"))
    |> filter(fn: (r) => r._field == "count" or r._field == "errorCount")
    |> aggregateWindow(every: v.windowPeriod, fn: sum)
    |> pivot(rowKey: ["_time"], columnKey: ["_field"], valueColumn: "_value")
    |> map(fn: (r) => ({_time: r._time, _value: r.errorCount / r.count}))
7

Hi Scott,
Thank you for the solution but this is not working. I don’t want to iterate through every row.
I need percentage errors which is
(countError/count).
Count = total number of request
countError = total number of failes request.

8

Ok, try this:

import "regexp"

from(bucket: "example-bucket")
    |> range(start: v.timeRangeStart, stop: v.timeRangeStop)
    |> filter(fn: (r) => r._measurement == "${measurement}")
    |> filter(fn: (r) => r.transaction == "all" and r.application == =~ regexp.compile(v: "${application}"))
    |> filter(fn: (r) => r._field == "count" or r._field == "errorCount")
    |> sum()
    |> duplicate(column: "_stop", as: "_time")
    |> pivot(rowKey: ["_time"], columnKey: ["_field"], valueColumn: "_value")
    |> map(fn: (r) => ({_time: r._time, _value: r.errorCount / r.count}))
9

Thanks Scott. It worked :slight_smile: