Trying to migrate influxql to flux

Niikhiil1997 · May 13, 2021, 10:12pm

Regarding the first query when i put last stream at the end i receive the following error. “invalid: error @16:1-16:11: undefined identifier lastStream”

And if i completely remove laststream i don’t get an error but i’m not receiving the value i should be receiving as that dashboard visualization is stat so im simply expecting a number which i don’t seem to be receiving which at this time should be “9” as nine booking processes are down.

Regarding the one with the pivot, i double checked all the column names and some were slightly off by their casing which i fixed however im getting the below error:

invalid: compilation failed: error at @11:4-13:90: expected RPAREN, got EOF

from(bucket: “eBondProcess/one_week_only”)
|> range(start: v.timeRangeStart, stop: v.timeRangeStop)
|> filter(fn: (r) => r._measurement == “eBond_ProcessMon”)
|> filter(fn: (r) =>
r._field == “AlarmStatus” or
r._field == “CellName” or
r._field == “CurrentProcessCount” or
r._field == “ExpectedToRun” or
r._field == “CheckStatus”
)
|> filter(fn: (r) => r.“AlarmStatus” == “1”)
|> last()
|> pivot(rowKey: [“ComponentName”, “host”], columnKey: ["_field"], valueColumn: “_value”)

so i removed the quotes from |> filter(fn: (r) => r.“AlarmStatus” == “1”) to |> filter(fn: (r) => r.AlarmStatus == “1”) and now it shows no eror but no data. However the funny thing is as soon as i remove |> filter(fn: (r) => r.“AlarmStatus” == “1”) the data will populate. So i think we can cancel out the column names as i do get data without that line.

scott · May 14, 2021, 1:04pm

When using dot notation to access a column in a row record, you can’t wrap the column name in quotes. You can either use dot notation:

|> filter(fn: (r) => r.AlarmStatus == "1")

or bracket notation:

|> filter(fn: (r) => r["AlarmStatus"] == "1")

I don’t know if this is a product of pasting code into the form on this forum, but it also appears you’re using typographer/curly quoates (“”). This will likely cause parsing errors. You should only use straight quotes ("").

Niikhiil1997 · May 17, 2021, 8:25am

@scott

The curly quotes are due to copy and pasting, i am using the correct quotes.

Any information regarding the first section?

Regarding the first query when i put last stream at the end i receive the following error. “invalid: error @16:1-16:11: undefined identifier lastStream”

And if i completely remove laststream i don’t get an error but i’m not receiving the value i should be receiving as that dashboard visualization is stat so im simply expecting a number which i don’t seem to be receiving which at this time should be “9” as nine booking processes are down.

Niikhiil1997 · May 17, 2021, 10:05am

@scott

The above query was fixed by moving the alarm status line to the bottom line and removing quotes around 1.

from(bucket: “eBondProcess/one_week_only”)
|> range(start: v.timeRangeStart, stop: v.timeRangeStop)
|> filter(fn: (r) => r._measurement == “eBond_ProcessMon”)
|> filter(fn: (r) =>
r._field == “AlarmStatus” or
r._field == “CellName” or
r._field == “CurrentProcessCount” or
r._field == “ExpectedToRun” or
r._field == “CheckStatus”
)
|> last()
|> pivot(rowKey: [“ComponentName”, “host”], columnKey: [“_field”], valueColumn: “_value”)
|> filter(fn: (r) => r.AlarmStatus == 1)

However still unable to fix the below query.

from(bucket: “eBondProcess/one_week_only”)
|> range(start: v.timeRangeStart, stop: v.timeRangeStop)
|> filter(fn: (r) => r._measurement == “eBond_ProcessMon”)
|> filter(fn: (r) =>
r._field == “AlarmStatus” or
r._field == “CellName” or
r._field == “CurrentProcessCount” or
r._field == “ExpectedToRun” or
r._field == “CheckStatus”
)
|> filter(fn: (r) => r.AlarmStatus == “1”)
|> group(columns: [“ComponentName”, “host”])
|> last()
|> filter(fn: (r) => r.CellName == “Quoting”)
|> count()

scott · May 17, 2021, 2:52pm

Ok, this makes sense. You have to pivot the data before filtering by the AlarmStatus field. When loaded from InfluxDB, your data looks something like:

_time	_field	_value
2021-01-01T00:00:00Z	AlarmStatus	1

After the pivot, it will look like:

_time	AlarmStatus
2021-01-01T00:00:00Z	1

In the initial example, I was thinking AlarmStatus was a tag, not a field. You need to pivot the data before you’re able to filter by the AlarmStatus column. OR, if you don’t want to pivot the data, you can do:

// ...
  |> filter(fn: (r) => r._field == "AlarmStatus" and r._value == "1")
// ...

Niikhiil1997 · May 17, 2021, 3:34pm

@scott

I was able to fix this query with the below query:

import “influxdata/influxdb/schema”

from(bucket: “eBondProcess/one_week_only”)
|> range(start:v.timeRangeStart,stop:v.timeRangeStop)
|> schema.fieldsAsCols()
|> group(columns:[ “ComponentName”,“CellName”,“host”],mode: “by”)
|> last(column: “AlarmStatus”)
|> filter(fn:(r)=> r.AlarmStatus==1 and r.CellName== “Quoting”)
|> keep(columns: ["_time",“AlarmStatus”])
|> count(column: “AlarmStatus”)

Regarding the below query the issue i am having now, is that there are 358 processes currently running but the output of the below query only shows about 50 processes in the table. Same thing occurs with this flux query on the explore tab too. However with the old influxQL query all 358 processes show up in the output table.
InfluxQL Query:
SELECT last(“AlarmStatus”) as “AlarmStatus”, “CellName”, “CurrentProcessCount”, “ExpectedToRun”, “CheckStatus” FROM “eBond_ProcessMon” WHERE $timeFilter and AlarmStatus = 0 GROUP BY “ComponentName”, “host”

from(bucket: “eBondProcess/one_week_only”)
|> range(start: v.timeRangeStart, stop: v.timeRangeStop)
|> filter(fn: (r) => r._measurement == “eBond_ProcessMon”)
|> filter(fn: (r) =>
r._field == “AlarmStatus” or
r._field == “CellName” or
r._field == “CurrentProcessCount” or
r._field == “ExpectedToRun” or
r._field == “CheckStatus”
)
|> last()
|> pivot(rowKey: [“ComponentName”, “host”], columnKey: ["_field"], valueColumn: “_value”)
|> filter(fn: (r) => r.AlarmStatus == 1)

Niikhiil1997 · May 25, 2021, 1:47pm

@scott

Hi Scott are you able to help with the conversion of the below influxql query to flux? It has a nested select statement within a select statement.

SELECT COUNT(LastVal) as foundRowsPast, COUNT(currentVal) as foundRowsNow FROM (SELECT last(InternalInfluxData) as LastVal FROM “ebond_AmpsConnections” GROUP BY ClientName),(SELECT last(InternalInfluxData) as currentVal FROM “ebond_AmpsConnections” WHERE time > NOW() - 5m GROUP BY ClientName) WHERE “ClientName”=~/AmpsRFQPriceEnricher/ OR “ClientName”=~/AmpsRFQFieldsEnricher/ OR “ClientName”=~/AmpsRFQPublisher/ OR “ClientName”=~/AmpsRFQStaticEnricher/ OR “ClientName”=~/AmpsRFQEnrichmentPublisher/ GROUP BY ClientName fill(0)

scott · May 25, 2021, 2:41pm

I think this should give you what you’re looking for:

LastVal = from(bucket: "example-bucket")
  |> range(start: -2d)
  |> filter(fn: (r) => r._measurement "ebond_AmpsConnections" and r._field == "InternalInfluxData")
  |> group(columns: "ClientName")
  |> last()
  |> set(key: "_field", as: "LastVal")

currentVal = from(bucket: "example-bucket")
  |> range(start: -5m)
  |> filter(fn: (r) => r._measurement "ebond_AmpsConnections" and r._field == "InternalInfluxData")
  |> group(columns: "ClientName")
  |> last()
  |> set(key: "_field", as: "currentVal")

union(tables: [lastVal, currentVal])
  |> filter(fn: (r) =>
    "ClientName" =~ /AmpsRFQPriceEnricher/ or
    "ClientName" =~ /AmpsRFQFieldsEnricher/ or
    "ClientName" =~ /AmpsRFQPublisher/ or
    "ClientName" =~ /AmpsRFQStaticEnricher/ or
    "ClientName" =~ /AmpsRFQEnrichmentPublisher/
  )
  |> group(columns: "ClientName")
  |> count()
  |> pivot(rowKey:["_time"], columnKey: ["_field"], valueColumn: "_value")
  |> rename(columns: {"LastVal": "foundRowsPast", "currentVal": "foundRowsNow"})

Niikhiil1997 · May 25, 2021, 3:02pm

@scott

I have checked all the spelling and spaces and it seems fine, however i’m getting the following error when trying to run the query:

invalid: error @3:67-3:68: undefined identifier r

currentVal = from(bucket: “ebondAMPSReplications/autogen”)
|> range(start: -5m)
|> filter(fn: (r) => r._measurement “ebond_AmpsConnections” and r._field == “InternalInfluxData”)
|> group(columns: “ClientName”)
|> last()
|> set(key: “_field”, as: “currentVal”)

union(tables: [lastVal, currentVal])
|> filter(fn: (r) =>
“ClientName” =~ /AmpsRFQPriceEnricher/ or
“ClientName” =~ /AmpsRFQFieldsEnricher/ or
“ClientName” =~ /AmpsRFQPublisher/ or
“ClientName” =~ /AmpsRFQStaticEnricher/ or
“ClientName” =~ /AmpsRFQEnrichmentPublisher/
)
|> group(columns: “ClientName”)
|> count()
|> pivot(rowKey:["_time"], columnKey: ["_field"], valueColumn: “_value”)
|> rename(columns: {“LastVal”: “foundRowsPast”, “currentVal”: “foundRowsNow”})

scott · May 25, 2021, 3:29pm

doh. Sorry. Try this:

LastVal = from(bucket: "example-bucket")
  |> range(start: -2d)
  |> filter(fn: (r) => r._measurement == "ebond_AmpsConnections" and r._field == "InternalInfluxData")
  |> group(columns: ["ClientName"])
  |> last()
  |> set(key: "_field", value: "LastVal")

currentVal = from(bucket: "example-bucket")
  |> range(start: -5m)
  |> filter(fn: (r) => r._measurement == "ebond_AmpsConnections" and r._field == "InternalInfluxData")
  |> group(columns: ["ClientName"])
  |> last()
  |> set(key: "_field", value: "currentVal")

union(tables: [lastVal, currentVal])
  |> filter(fn: (r) =>
    "ClientName" =~ /AmpsRFQPriceEnricher/ or
    "ClientName" =~ /AmpsRFQFieldsEnricher/ or
    "ClientName" =~ /AmpsRFQPublisher/ or
    "ClientName" =~ /AmpsRFQStaticEnricher/ or
    "ClientName" =~ /AmpsRFQEnrichmentPublisher/
  )
  |> group(columns: ["ClientName"])
  |> count()
  |> pivot(rowKey:["_time"], columnKey: ["_field"], valueColumn: "_value")
  |> rename(columns: {"LastVal": "foundRowsPast", "currentVal": "foundRowsNow"})

Niikhiil1997 · May 25, 2021, 3:50pm

@scott

Now getting invalid: type error @4:6-4:34: expected [string] but found string (argument columns)

scott · May 25, 2021, 4:03pm

Edited the codeblock in my last response. The columns parameter of group() requires an array of strings.

Niikhiil1997 · May 25, 2021, 4:25pm

@scott

Sorry scott, now getting a new issue on line 6.

invalid: type error @6:6-6:39: found unexpected argument as

scott · May 26, 2021, 8:03pm

Updated the codeblock above. Set requires a value parameter, not an as parameter.

Niikhiil1997 · May 27, 2021, 3:54pm

Cheers scott, that worked

Niikhiil1997 · June 1, 2021, 8:16am

@scott

Hi Scott, apologies, are you able to help with the below conversion to flux?

SELECT * FROM “ebond_ProcsTakingLotsThreads” WHERE $timeFilter
AND “FullCommand” !~ /.activemq./
AND
(“FullCommand” !~ /.ebond-jtimeseries-server./ OR “NoOfThreads” >=1000)
AND
(“FullCommand” !~ /.ebond-swapcurvebuilder./ OR “NoOfThreads” >=3000)
AND
(“FullCommand” !~ /.ebond-amps-server./ OR “NoOfThreads” >=1000)
AND “NoOfThreads” > 3000

scott · June 1, 2021, 3:03pm

@Niikhiil1997 Is FullCommand a tag or a field?

Niikhiil1997 · June 1, 2021, 4:03pm

@scott
it is a field.

scott · June 1, 2021, 10:54pm

from(bucket: "example-bucket")
  |> range(start: v.timeRangeStart, stop: v.timeRangeStop)
  |> filter(fn: (r) => r._measurement == "ebond_ProcsTakingLotsThreads")
  |> filter(fn: (r) => r._field == r.FullCommand or r._field == r.NoOfThreads)
  |> pivot(rowKey:["_time"], columnKey: ["_field"], valueColumn: "_value")
  |> filter(fn: (r) => 
    r.FullCommand !~ /.activemq./ and
    (r.FullCommand !~ /.ebond-jtimeseries-server./ or r.NoOfThreads >= 1000) and
    (r.FullCommand !~ /.ebond-swapcurvebuilder./ or r.NoOfThreads >= 3000) and
    (r.FullCommand !~ /.ebond-amps-server./ or r.NoOfThreads >= 1000) and
    r.NoOfThreads > 3000
  )

Niikhiil1997 · June 3, 2021, 10:25am

@scott

From the original influxQL query i reduced some of the NoOfThreads thresholds to produce some data in the dashboard which worked, however when i tried to make the reduction in the flux query you sent the same data is not showing up. The reason why i did this was to test the flux query is working correctly as we have had no data come into the dashboard for some time so wanted to reduce thresholds to populate the dashboard with some kind of data.

Reduced threshold influxQL query, showing data:

SELECT * FROM “ebond_ProcsTakingLotsThreads” WHERE $timeFilter
AND “FullCommand” !~ /.activemq./
AND
(“FullCommandName” !~ /.ebond-jtimeseries-server./ OR “NoOfThreads” >=1000)
AND
(“FullCommand” !~ /.ebond-swapcurvebuilder./ OR “NoOfThreads” >=3000)
AND
(“FullCommand” !~ /.ebond-amps-server./ OR “NoOfThreads” >=1000)
AND “NoOfThreads” > 1000

Equivalent reduction made in threshold for flux query but not showing the same data:
from(bucket: “ebondProcsTakingLotsThreads/one_week_only”)
|> range(start: v.timeRangeStart, stop: v.timeRangeStop)
|> filter(fn: (r) => r._measurement == “ebond_ProcsTakingLotsThreads”)
|> filter(fn: (r) => r._field == r.FullCommand or r._field == r.NoOfThreads)
|> pivot(rowKey:["_time"], columnKey: ["_field"], valueColumn: “_value”)
|> filter(fn: (r) =>
r.FullCommand !~ /.activemq./ and
(r.FullCommand !~ /.ebond-jtimeseries-server./ or r.NoOfThreads >= 1000) and
(r.FullCommand !~ /.ebond-swapcurvebuilder./ or r.NoOfThreads >= 3000) and
(r.FullCommand !~ /.ebond-amps-server./ or r.NoOfThreads >= 1000) and
r.NoOfThreads > 1000
)

Topic		Replies	Views
Convert from InfluxQL to FLUX query (Please Help) Fluxlang	9	798	December 17, 2021
InfluxQL query to Flux Fluxlang influxql , flux	1	520	April 17, 2023
InfluxQL count with subquery in Flux InfluxDB 2 flux	3	1408	December 14, 2020
Problem: Can't find a solution for this query in Flux (Migrating from InfluxQL to Flux) Grafana influxql , flux	2	572	August 26, 2021
Convert influxQL to flux with count() in select statement InfluxDB 2 influxql , flux	0	380	September 9, 2022

Trying to migrate influxql to flux

Related topics