Convert to from InfluxQL to FLUX

InfluxDB 2
, , ,
1

Hello!

Could someone please help me to convert my old InfluxQL query to Flux query?
It’s used to find the total availability for several devices in the ID.

SELECT COUNT("result_code") FROM ( SELECT max("result_code") AS "result_code" FROM "ID"."ID"."ping" WHERE time > :dashboardTime: AND time < :upperDashboardTime: AND "ID"=~ /^*ID1*-/ GROUP BY time(1m) ) WHERE time > :dashboardTime: AND time < :upperDashboardTime: AND "result_code" = 1 

SELECT COUNT("result_code") FROM ( SELECT max("result_code") AS "result_code" FROM "ID"."ID"."ping" WHERE time > :dashboardTime: AND time < :upperDashboardTime: AND "ID"=~ /^*ID1*-/ GROUP BY time(1m) ) WHERE time > :dashboardTime: AND time < :upperDashboardTime: AND "result_code" = 0

In this ID group, there are 3 devices and 2 devices that have been online 100% and one 90%, so it should show 100% for the full availability of the group since 2 hosts were online, group but it shows 98%.

I tried below, but it’s not working as intended and i may be way off.
Online:

from(bucket: "PING")
  |> range(start: v.timeRangeStart, stop: v.timeRangeStop)
  |> filter(fn: (r) => r["_measurement"] == "ping")
  |> filter(fn: (r) => r["ID"] =~ /^*ID$RemoteID$*/)
  |> filter(fn: (r) => r["_field"] == "result_code")
  |> filter(fn: (r) => r._value == 0)
  |> aggregateWindow(every: 1m, fn: max)
  |> count()
  |> group(columns: ["_time"])

Offline:

  from(bucket: "PING")
  |> range(start: v.timeRangeStart, stop: v.timeRangeStop)
  |> filter(fn: (r) => r["_measurement"] == "ping")
  |> filter(fn: (r) => r["ID"] =~ /^*ID$RemoteID$*/)
  |> filter(fn: (r) => r["_field"] == "result_code")
  |> filter(fn: (r) => r._value == 1)
  |> aggregateWindow(every: 1m, fn: max)
  |> count()
  |> group(columns: ["_time"])

Thank you kindly for any help or tips!

2

Hello @Skywalker,
I think you’re close. If I understand you correctly you want to group by 1 min intervals, find the max, and then count the number of values for all ID tags. Try:

from(bucket: "PING")
  |> range(start: v.timeRangeStart, stop: v.timeRangeStop)
  |> filter(fn: (r) => r["_measurement"] == "ping")
  |> filter(fn: (r) => r["ID"] =~ /^*ID$RemoteID$*/)
  |> filter(fn: (r) => r["_field"] == "result_code")
  |> filter(fn: (r) => r._value == 0)
  |> aggregateWindow(every: 1m, fn: max)
  |> group()
  |> count()

Does that work for you? A empty group() effectively ungroups the tables so you can find the total count across tags.

3

Hello @Anaisdg

Thanks for your help!
It’s “almost working”

from(bucket: "PING")
  |> range(start: v.timeRangeStart, stop: v.timeRangeStop)
  |> filter(fn: (r) => r["_measurement"] == "ping")
  |> filter(fn: (r) => r["ID"] == "RemoteID1" or r["ID"] == "RemoteID2" or r["ID"] == "RemoteID3")
  |> filter(fn: (r) => r["_field"] == "result_code")
  |> filter(fn: (r) => r._value == 0)
  |> aggregateWindow(every: 1m, fn: max)
  |> group()
  |> count()

from(bucket: "PING")
  |> range(start: v.timeRangeStart, stop: v.timeRangeStop)
  |> filter(fn: (r) => r["_measurement"] == "ping")
  |> filter(fn: (r) => r["ID"] == "RemoteID1" or r["ID"] == "RemoteID2" or r["ID"] == "RemoteID3")
  |> filter(fn: (r) => r["_field"] == "result_code")
  |> filter(fn: (r) => r._value == 1)
  |> aggregateWindow(every: 1m, fn: max)
  |> group()
  |> count()

It will still give me 98% uptime when it should be 100%. As one has been online when the others were offline.

Do I need to do any pivot stuff?

Thanks!

5

Hello @Skywalker,
I’m not sure where that discrepancy is coming from since I don’t have your data. Perhaps you’re not including a point in your range? Do you need to expand your range time?
Where are you calculating percentages?
Can you maybe share some of your data?