Convert to from InfluxQL to FLUX

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!

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.

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!

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?