Trying to migrate influxql to flux

@scott

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.

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 ("").

@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.

@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()

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")
// ...

@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)

@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)

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"})

@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

LastVal = from(bucket: “ebondAMPSReplications/autogen”)
|> 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: “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”})

:man_facepalming: 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"})

@scott

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

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

@scott

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

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

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

1 Like

Cheers scott, that worked :slight_smile:

@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

@Niikhiil1997 Is FullCommand a tag or a field?

@scott
it is a field.

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
  )
1 Like

@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
)