Trying to migrate influxql to flux

Hi
I am trying to convert the below InfluxQL to Flux, however i am having some difficulty understand how to do this.

The InfluxQL query:
SELECT last(“Result”) as “Number of Matches” FROM “secure_http_monitoring” WHERE $timeFilter GROUP BY “Server”,“SearchTerm”

@Niikhiil1997 With the variables you’re using, I assume you’re using Grafana. The variables in the query below assume Grafana. If you’re using something else, let me know.

from(bucket: "example-bucket")
  |> range($range)
  |> filter(fn: (r) => r._measurement == "secure_http_monitoring" and r._field == "Result")
  |> group(columns: ["Server", "SearchTerm"])
  |> last()

Hi @scott
I am using grafana, yes. However when i tried the query you sent in “explore” tab it is complaining about the dollar sign for range.
“invalid: compilation failed: error at @2:12-2:18: unexpected token for property key: ILLEGAL ($)”

Ok, I couldn’t remember how Grafana parses variables in Flux. Try this:

from(bucket: "example-bucket")
  |> range(start: v.timeRangeStart, stop: v.timeRangeStop)
  |> filter(fn: (r) => r._measurement == "secure_http_monitoring" and r._field == "Result")
  |> group(columns: ["Server", "SearchTerm"])
  |> last()
1 Like

Hi thanks so much. This worked.

I have another question i have two queries which are slightly different and wanted to know how i can specify specific columns in flux.

Query1:
SELECT last(UniquePublishCount) as UniquePublishCount, last(ExpectedValue) as ExpectedValue, last(Result) as CheckResult FROM “Feed_UniquePublishCount_jmx” WHERE $timeFilter GROUP BY “ComponentName”
Query2
SELECT last(PublishCount) as PublishCount, last(ExpectedValue) as ExpectedValue, last(Result) as CheckResult FROM “Feed_PublishCount_jmx” WHERE $timeFilter GROUP BY “ComponentName”

I thought i got query 1 working with the below conversion to flux however i realised when i tried to convert the second one that i have not specified the column name ie query 1 should be for column “Unique Publish Count” and query 2 should be for “Publish Count”. So both influxql to flux conversions were exactly the same.

from(bucket:“eBondJMX/one_week_only”)
|>range(start: v.timeRangeStart, stop: v.timeRangeStop)
|>filter(fn:(r)=>r._measurement==“Feed_UniquePublishCount_jmx”)
|>pivot(rowKey:[“ComponentName”],columnKey:["_field"],valueColumn:"_value")

Would appreciate your help with the above.

These should give you what you’re looking for:

Query 1
from(bucket: "eBondJMX/one_week_only")
  |> range(start: v.timeRangeStart, stop: v.timeRangeStop)
  |> filter(fn:(r) => r._measurement="Feed_UniquePublishCount_jmx")
  |> filter(fn: (r) => r._field == "UniquePublishCount" or r._field == "ExpectedValue" or r._field == "Result")
  |> last()
  |> pivot(rowKey:["ComponentName"], columnKey:["_field"], valueColumn:"_value")
Query 2
from(bucket: "eBondJMX/one_week_only")
  |> range(start: v.timeRangeStart, stop: v.timeRangeStop)
  |> filter(fn:(r) => r._measurement="Feed_UniquePublishCount_jmx")
  |> filter(fn: (r) => r._field == "PublishCount" or r._field == "ExpectedValue" or r._field == "Result")
  |> last()
  |> pivot(rowKey:["ComponentName"], columnKey:["_field"], valueColumn:"_value")

@scott - legend! it seems to work however the only issue is under the ComponentName column i am only getting one component back, so there is only one row. There should be 4 components that should populate which i was getting from the flux query i sent you above. I’m wondering if it has anything to do with the query.

@scott - i managed to fix it by adding “merge” from the transform tab :slight_smile: thank you!

@scott - Actually in the second query i don’t see the column for PublishCount how i see it for the first query for “UniquePublishCount”

@scott - ok i managed to see where the issue was, for the second one we are querying a different table called “Feed_PublishCount_jmx” compared to the first one which was querying “Feed_UniquePublishCount_jmx”.

@scott

Thanks for your help scott, i have managed to conver alot of dashboards over to influx however i have come across another one which i am struggling to convert.

SELECT last(“CurveName”) as CurveName,last(“CurveEnabled”) as CurveEnabled, last(“LastCurvePublished”) as LastCurvePublished, last(“CurveLogMessage”) as ErrorMessage, last(ExpectedValue) as ExpectedValue, last(Result) as CheckResult FROM “SwapCurve_FFT_jmx” WHERE $timeFilter and CurveName !~ /""/ GROUP BY “MBean”, host

Its the “and CurveName !~ /”"/" that i am struggling to conver to influx.

Any help would be appreciated.

@Niikhiil1997 I think this should work for you:

from(bucket: "example-bucket")
  |> range(start: v.timeRangeStart, stop: v.timeRangeStop)
  |> filter(fn: (r) => r._measurement == "SwapCurve_FFT_jmx")
  |> filter(fn: (r) => r.CurveName !~ /""/)
  |> filter(fn: (r) => 
    r._field == "CurveName" or
    r._field == "CurveEnabled" or
    r._field == "LastCurvePublished" or
    r._field == "CurveLogMessage" or
    r._field == "ExpectedValue" or
    r._field == "Result"
  )
  |> last()
  |> pivot(rowKey: ["MBean", "host"], columnKey: ["_field"], valueColumn: "_value")
  |> rename(columns: {CurveLogMessage: "ErrorMessage", Result: "CheckResult"})