Trying to migrate influxql to flux

@scott

Could you please help with the below conversion to flux

SELECT last("%CPU") as “%CPU”,last(“VMSIZE”) as VMSize, last(“USER”) as “Owner” ,last(“ARGS”) as Args FROM “eBond_TopCPU” WHERE “%CPU” > 10.0 and $timeFilter GROUP BY “PID”, “host”

So far i have got this working flux query:
from(bucket: “eBondTopCPU/one_day_only”)
|> range(start: v.timeRangeStart, stop: v.timeRangeStop)
|> filter(fn: (r) => r._measurement == “eBond_TopCPU”)
|> filter(fn: (r) =>
r._field == “VMSIZE” or
r._field == “USER” or
r._field == “%CPU” or
r._field == “ARGS”
)
|> last()
|> pivot(rowKey: [“PID”, “host”], columnKey: ["_field"], valueColumn: “_value”)

Not sure how i add the part in where “%CPU” > 10.0

i tried adding |> filter(fn: (r) => r.%CPU > 10) however this came up with the following error:

invalid: compilation failed: error at @12:25-12:28: expected IDENT, got MOD (%!)(MISSING) at 12:24

That’s a really good start. Because of the percentage character in the column name, you need to use bracket notation to reference the column value. This should give you what you need:

from(bucket: "example-bucket")
  |> range(start: v.timeRangeStart, stop: v.timeRangeStop)
  |> filter(fn: (r) => r._measurement == "eBond_TopCPU")
  |> filter(fn: (r) =>
      r._field == "%CPU" or
      r._field == "VMSIZE" or
      r._field == "USER" or
      r._field == "ARGS"
  )
  |> last()
  |> pivot(rowKey:["_time"], columnKey: ["_field"], valueColumn: "_value")
  |> filter(fn: (r) => r["%CPU"] > 10.0)
  |> rename(columns: {VMSIZE: "VMSize", USER: "Owner", ARGS: "Args"})
1 Like

@scott
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”})

Regarding the query above i tried a different approach please see below, however i think there might be something wrong with the join as when i try to join the tables I’m only getting 4/5 of the client names that i have put into expectedComponents. Do you think it could be a different join ?

import"experimental/array"
import"influxdata/influxdb/schema"

expectedComponents=array.from(rows:[
{ClientName: “AmpsRFQPriceEnricher”},
{ClientName: “AmpsRFQFieldsEnricher”},
{ClientName: “AmpsRFQPublisher”},
{ClientName: “AmpsRFQStaticEnricher”},
{ClientName: “AmpsRFQEnrichmentPublisher”},
])
mostRecentData=from(bucket:“ebondMonitorAMPS/one_week_only”)
|>range(start: -5m, stop:now())
|>filter(fn:(r)=>r._measurement==“ebond_AmpsConnections” and r._field==“InternalInfluxData”)

join(
tables: {a:expectedComponents,b:mostRecentData},
on:[“ClientName”]
)
|>schema.fieldsAsCols()

@scott

Any update regarding the query mentioned above?