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()
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:
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")
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 
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”.
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"})
Hi @scott
select count(CellName) from (SELECT last(“AlarmStatus”) as “AlarmStatus”, “CellName”, “CurrentProcessCount”, “ExpectedToRun”, “CheckStatus” FROM “eBond_ProcessMon” WHERE $timeFilter and AlarmStatus = 1 GROUP BY “ComponentName”, “host”) WHERE CellName = ‘Booking’
I have not done a count conversion yet and also the “AlarmStatus = 1”
Your help would be much appreciated as always
I think this should give you what you’re looking for:
lastStream = from(bucket: "example-bucket")
|> range(start: v.timeRangeStart, stop: v.timeRangeStop)
|> filter(fn: (r) => r._measurment == "eBond_ProcessMon")
|> filter(fn: (r) =>
r._field == "AlarmStatusor" or
r._field == "CellName" or
r._field == "CurrentProcessCount" or
r._field == "ExpectedToRun" or
r._field == "CheckStatus"
)
|> filter(fn: (r) => r.AlarmStatus == "1")
|> group(columns: ["CompnentName", "host"])
|> last()
|> filter(fn: (r) => r.CellName == "Booking")
|> count()
im getting the below error when inserting the flux query you sent above.
invalid: error in query specification while starting program: this Flux script returns no streaming data. Consider adding a “yield” or invoking streaming functions directly, without performing an assignment
So the query i sent above was specific for “Booking” and was simply just a count, however we also have a table which gives us all processes that are expected to run but not running which then sends an alert using the below query.
SELECT last(“AlarmStatus”) as “AlarmStatus”, “CellName”, “CurrentProcessCount”, “ExpectedToRun”, “CheckStatus” FROM “eBond_ProcessMon” WHERE $timeFilter and AlarmStatus = 1 GROUP BY “ComponentName”, “host”
I converted the influxQL to flux using 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”
)
|> last()
|> pivot(rowKey: [“ComponentName”, “host”], columnKey: ["_field"], valueColumn: “_value”)
The above flux query gives me all data as there is no filter for alarm status which is not what this table should have, it should only have processes that are missing and that is does by adding the alarmstatus = 1 filter, however as soon as i add the filter for alarm status it returns no data to me.
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”)
This then returns no data.
Hi @scott
Any update?
@Niikhiil1997 Try removing the pivot and see if that returns data. If it does, then the pivot call needs to be updated.
Hi
tried to remove the pivot line, and this time got no error but no data is showing up for 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”)
|> last()
|> pivot(rowKey: [“ComponentName”, “host”], columnKey: ["_field"], valueColumn: “_value”)
Also regarding the first query you sent which i will paste below, i am still receiving the error.
invalid: error in query specification while starting program: this Flux script returns no streaming data. Consider adding a “yield” or invoking streaming functions directly, without performing an assignment
lastStream = from(bucket: “example-bucket”)
|> range(start: v.timeRangeStart, stop: v.timeRangeStop)
|> filter(fn: (r) => r._measurment == “eBond_ProcessMon”)
|> filter(fn: (r) =>
r._field == “AlarmStatusor” or
r._field == “CellName” or
r._field == “CurrentProcessCount” or
r._field == “ExpectedToRun” or
r._field == “CheckStatus”
)
|> filter(fn: (r) => r.AlarmStatus == “1”)
|> group(columns: [“CompnentName”, “host”])
|> last()
|> filter(fn: (r) => r.CellName == “Booking”)
|> count()
This is because I assigned the stream of tables to a variable, but never called the variable. Sorry. You can either remove lastStream = or just put lastStream at the end of the query.
If that’s the case, then the filters aren’t returning any data. Double check the filters to make sure all the field names are correct.
