Index array/object with _field

wmm · April 27, 2021, 1:39pm

Hi everybody!
Is it possible to use e.g. the _field column for indexing an array/object/dict?

something like this:

hysteresisDict = {
“t996”: 10,
“t997”: 10,
“t998”: 10,
“t999”: 10,
“t1000”: 10,
}

check = (r) => (r[“critCount”] > hysteresisDict[r._field])

scott · April 27, 2021, 3:35pm

@wmm If you’re using a true Flux dict type, then yes:

import "dict"

hysteresisDict = [
  "t996": 10,
  "t997": 10,
  "t998": 10,
  "t999": 10,
  "t1000": 10
]

check = (r) => {
  fieldValue = dict.get(
    dict: hysteresisDict,
    key: r._field,
    default: 0
  )
  return (r.critCount > fieldValue)
}

wmm · April 27, 2021, 3:51pm

I try that before!
Is dict.get working with this:

import "dict"
import "influxdata/influxdb/monitor"

thWarnDict = { "t11": 200.0, "t12": 200.0, "t13": 200.0, "t14": 200.0, "t15": 200.0 }
thAlarmDict = { "t11": 100.0, "t12": 100.0, "t13": 100.0, "t14": 100.0, "t15": 100.0 }
thCritDict = { "t11": 75.0, "t12": 75.0, "t13": 75.0, "t14": 75.0, "t15": 75.0 }
hysteresis = 10
defaultTH = 9999999999

statusData = 
  from(bucket: "oceanTest-short")
    |> range(start: -60m)
	|> limit(n: hysteresis, offset: 0)
    |> filter(fn: (r) => (r["_measurement"] == "trends"))
    |> filter(fn: (r) => (r["ptid"] == "331"))
	|> drop(columns: ["_start", "_stop"])
    |> group(columns: ["_field", "ptid", "_measurement"])
    |> stateCount(fn: (r) => (r._value >= dict.get(dict: thCritDict, key: r._field, default: defaultTH)), column: "critCount")
    |> stateCount(fn: (r) => (r._value >= dict.get(dict: thAlarmDict, key: r._field, default: defaultTH)), column: "alarmCount")
    |> stateCount(fn: (r) => (r._value >= dict.get(dict: thWarnDict, key: r._field, default: defaultTH)), column: "warnCount")
    |> stateCount(fn: (r) => (r._value < dict.get(dict: thWarnDict, key: r._field, default: defaultTH)), column: "normalCount")
    //|> yield(name: "status")

crit = (r) => (r["critCount"] > hysteresis)
alarm = (r) => (r["alarmCount"] > hysteresis)
warn = (r) => (r["warnCount"] > hysteresis)
normal = (r) => (r["normalCount"] > hysteresis)

messageFn = (r) => (
  if r._level == "crit" then "CRITICAL (${string(v: double(v: r._value))}%)" 
  else if r._level == "warn" then "ALARM (${string(v: double(v: r._value))}%)" 
  else if r._level == "info" then "WARNING (${string(v: double(v: r._value))}%)" 
  else if r._level == "ok" then "NORMAL (${string(v: double(v: r._value))}%)" 
  else "UNDEFINED"
)

check = {
  _check_id: "statusTask_00001",
  _check_name: "StatusTask",
  _type: "custom",
  tags: {},
}

statusData
  |> monitor.check(
    crit: crit,
    warn: alarm,
	info: warn,
    ok: normal,
    messageFn: messageFn,
    data: check
  )

I get the following error:

2021-04-27 18_02_43-Window

scott · April 27, 2021, 4:04pm

@wmm A few things:

Your “Dict” variables aren’t dicionaries, they’re records. Use brackets to define a dict:

thWarnDict = [ "t996": 200.0, "t997": 200.0, "t998": 200.0, "t999": 200.0, "t1000": 200.0 ]
thAlarmDict = [ "t996": 100.0, "t997": 100.0, "t998": 100.0, "t999": 100.0, "t1000": 100.0 ]
thCritDict = [ "t996": 75.0, "t997": 75.0, "t998": 75.0, "t999": 75.0, "t1000": 75.0 ]
hysteresisDict = [ "t996": 10.0, "t997": 10.0, "t998": 10.0, "t999": 10.0, "t1000": 10.0 ]

In your stateCount() functions, you aren’t calling dict.get() correctly. They should be:

|> stateCount(fn: (r) => (r._value >= dict.get(dict: thCritDict , key: r._field, default: defaultTH)), column: "critCount")
|> stateCount(fn: (r) => (r._value >= dict.get(dict: thAlarmDict, key: r._field, default: defaultTH)), column: "alarmCount")
|> stateCount(fn: (r) => (r._value >= dict.get(dict: thWarnDict, key: r._field, default: defaultTH)), column: "warnCount")
|> stateCount(fn: (r) => (r._value < dict.get(dict: thWarnDict, key: r._field, default: defaultTH)), column: "normalCount")

double() isn’t the correct function for converting values to a float. float() is:

messageFn = (r) => (
  if r._level == "crit" then "CRITICAL (${string(v: float(v: r._value))}%)"
  else if r._level == "warn" then "ALARM (${string(v: float(v: r._value))}%)"
  else if r._level == "info" then "WARNING (${string(v: float(v: r._value))}%)"
  else if r._level == "ok" then "NORMAL (${string(v: float(v: r._value))}%)"
  else "UNDEFINED"
)

This is what the full task should look like:

import "dict"
import "influxdata/influxdb/monitor"

option task = {
  name: "StatusTask",
  every: 1s,
  offset: 0
}

thWarnDict = [ "t996": 200.0, "t997": 200.0, "t998": 200.0, "t999": 200.0, "t1000": 200.0 ]
thAlarmDict = [ "t996": 100.0, "t997": 100.0, "t998": 100.0, "t999": 100.0, "t1000": 100.0 ]
thCritDict = [ "t996": 75.0, "t997": 75.0, "t998": 75.0, "t999": 75.0, "t1000": 75.0 ]
hysteresisDict = [ "t996": 10.0, "t997": 10.0, "t998": 10.0, "t999": 10.0, "t1000": 10.0 ]
defaultTH = 9999999999

statusData =
  from(bucket: "oceanTest")
    |> range(start: -1m)
    |> filter(fn: (r) => (r["_measurement"] == "trends"))
    |> filter(fn: (r) => (r["ptId"] == "331"))
    |> drop(columns: ["_start", "_stop"])
    |> group(columns: ["_field", "ptid", "_measurement"])
    |> stateCount(fn: (r) => (r._value >= dict.get(dict: thCritDict , key: r._field, default: defaultTH)), column: "critCount")
    |> stateCount(fn: (r) => (r._value >= dict.get(dict: thAlarmDict, key: r._field, default: defaultTH)), column: "alarmCount")
    |> stateCount(fn: (r) => (r._value >= dict.get(dict: thWarnDict, key: r._field, default: defaultTH)), column: "warnCount")
    |> stateCount(fn: (r) => (r._value < dict.get(dict: thWarnDict, key: r._field, default: defaultTH)), column: "normalCount")
    //|> yield(name: "status")

crit = (r) => (r["critCount"] > dict.get(dict: hysteresisDict, key: r._field, default: defaultTH))
alarm = (r) => (r["alarmCount"] > dict.get(dict: hysteresisDict, key: r._field, default: defaultTH))
warn = (r) => (r["warnCount"] > dict.get(dict: hysteresisDict, key: r._field, default: defaultTH))
normal = (r) => (r["normalCount"] > dict.get(dict: hysteresisDict, key: r._field, default: defaultTH))

messageFn = (r) => (
  if r._level == "crit" then "CRITICAL (${string(v: float(v: r._value))}%)"
  else if r._level == "warn" then "ALARM (${string(v: float(v: r._value))}%)"
  else if r._level == "info" then "WARNING (${string(v: float(v: r._value))}%)"
  else if r._level == "ok" then "NORMAL (${string(v: float(v: r._value))}%)"
  else "UNDEFINED"
)

check = {
  _check_id: "statusTask_00001",
  _check_name: "StatusTask",
  _type: "custom",
  tags: {},
}

statusData
 |> monitor.check(
   crit: crit,
   warn: alarm,
   info: warn,
   ok: normal,
   messageFn: messageFn,
   data: check
 )

wmm · April 27, 2021, 4:06pm

Thank you very much!
I also found a few errors before - sorry for that!
Now the task is working!

scott · April 27, 2021, 4:07pm

No problem. Happy to help!

wmm · April 27, 2021, 4:08pm

One more thing - when the task is scheduled it completed with FAILED!
2021-04-27 18:07:00 +0200

could not execute task run: runtime error @55:6-62:4: check: found column “_field” in the group key; experimental.to() expects pivoted data

wmm · April 27, 2021, 4:12pm

And the next funny thing is - now i get a error message when i try to edit the task!

2021-04-27 18_09_39-Window

scott · April 27, 2021, 4:19pm

Hmm, ok, these seem like bugs. You may want to create an issue on the InfluxDB repo.

wmm · April 28, 2021, 6:08am

The issue "unknown type “DictExpression” disappeard with v2.0.5.

The “found column “_field” in the group key” is still open!

Topic		Replies	Views
Influxdb 1.x Filter by field value Fluxlang	7	999	July 18, 2019
Flux query: Problem with array filter inside map InfluxDB 2 query	1	897	April 26, 2023
Is this possible in InfluxDB? (lookup value in an array) Fluxlang	9	506	December 15, 2022
Calulating fields from multiple tags InfluxDB 2 query , flux	5	157	December 22, 2023
Help with query needed InfluxDB 2	2	495	April 30, 2021

Index array/object with _field

Related Topics