Flux query: _field has slash in name, cannot get value

InfluxDB 1 InfluxQL
1

Hello,
I am wondering how I can get the value out of a _field which has slashes in his name, ie:

r._field == “status_information/last_state_change/days”

my query looks like the following and I want to calculate a new column based on days * 86400 + hours * 3600, etc to get the number of seconds and graph it. As you may notice, I am not able to get the value out of >> r.status_information/last_state_change/days

Any Ideas? I am hunting cisco telemetry data, bfdsessionuptime…
Thanks in advance, best regards, Tom

from(bucket: "telemetry")
  |> range(start: -5m)
  |> filter(fn: (r) => r._measurement == "Cisco-IOS-XR-ip-bfd-oper:bfd/session-details/session-detail")
  |> filter(fn: (r) => r.destination_address =="192.168.1.3")
  |> filter(fn: (r) => (
          r._field == "status_information/last_state_change/days" or
          r._field == "status_information/last_state_change/hours"
         )
  |> pivot(rowKey:["_time"], columnKey: ["_field"], valueColumn: "_value")
  |> map(fn: (r) => ({ r with
      uptimeseconds: float(v: r.status_information/last_state_change/days * 86400.0) + float(v: 
      r.status_information/last_state_change/hours * 3600.0)
  }))
2

@ToWo You can’t use dot notation if there are special characters or white space characters in your column name. You need to use bracket notation:

from(bucket: "telemetry")
  |> range(start: -5m)
  |> filter(fn: (r) => r._measurement == "Cisco-IOS-XR-ip-bfd-oper:bfd/session-details/session-detail")
  |> filter(fn: (r) => r.destination_address =="192.168.1.3")
  |> filter(fn: (r) => (
          r._field == "status_information/last_state_change/days" or
          r._field == "status_information/last_state_change/hours"
         )
  |> pivot(rowKey:["_time"], columnKey: ["_field"], valueColumn: "_value")
  |> map(fn: (r) => ({ r with
      uptimeseconds: float(v: r["status_information/last_state_change/days"] * 86400.0) + float(v: r["status_information/last_state_change/hours"] * 3600.0)
  }))
3

@scott exactly what I was looking for, have had looked in to the samples for hours and the beginners guide fits best. thank you. Please allow one more question on this, I want to extend the filter r.destination_ipaddress == “192.168.xx” to a variable which than can be chosen at runtime from an ip-list ( ip address values to choose can be picked up by a distinct query to the destination_address). How would one start?
thanks in advance,
best regards,
Tom

4

@ToWo If you’re using InfluxDB Cloud or InfluxDB OSS 2.0, you can create a custom dashboard variable that lists all of the potential IPs.

The InfluxDB stores all dashboard variables in a v record that get’s append to all dashboard cells, so to use a variable in a dashboard query, you just use the v.variableName (or v["Variable Name"]) syntax:

from(bucket: "telemetry")
  |> range(start: -5m)
  |> filter(fn: (r) => r._measurement == "Cisco-IOS-XR-ip-bfd-oper:bfd/session-details/session-detail")
  |> filter(fn: (r) => r.destination_address == v.IP)
  |> filter(fn: (r) => (
          r._field == "status_information/last_state_change/days" or
          r._field == "status_information/last_state_change/hours"
         )
  |> pivot(rowKey:["_time"], columnKey: ["_field"], valueColumn: "_value")
  |> map(fn: (r) => ({ r with
      uptimeseconds: float(v: r["status_information/last_state_change/days"] * 86400.0) + float(v: r["status_information/last_state_change/hours"] * 3600.0)
  }))
5

@scott Perfect! Thank you very much for this, now it fits all together,
cheers,
Tom

6

@ToWo No problem. Happy to help!

7

Hello Scott ,

I have the below query but seems i dont take any percentage value.

from(bucket: “telegraf”)

|> range(start: v.timeRangeStart, stop: v.timeRangeStop)

|> filter(fn: (r) => r[“_measurement”] == “Cisco-IOS-XR-ip-daps-oper:address-pool-service/nodes/node/vrfs/vrf/ipv4”)

|> filter(fn: (r) => r[“_field”] == “pools/pool_name” or r[“_field”] == “pools/free” or r[“_field”] == “pools/used” or r[“_field”] == “pools/total”)

|> filter(fn: (r) => r[“source”] == “CR-BNG-01”)

|> filter(fn: (r) => r[“vrf_name”] == “GREEN”)

|> filter(fn: (r) => r[“pools/pool_name”] == “EAD20_GREEN” or r[“pools/pool_name”] == “EAD21_GREEN”)

|> aggregateWindow(every: v.windowPeriod, fn: last, createEmpty: false)

|> map(

    fn: (r) => ({

        r with \_value: float(v: r\["pools/free"\]  / float(v: r\["pools/total"\] ) \* 100.0)

        }))

|> yield(name: “last”)

|> pivot(rowKey:[“_time”], columnKey: [“_field”], valueColumn: “_value”)

Thank you in advance for your help,

Minas

8

@Minas_Balaskas You need to pivot the data before you try to operate on multiple fields in map():

from(bucket: "telegraf")
    |> range(start: v.timeRangeStart, stop: v.timeRangeStop)
    |> filter(fn: (r) => r["_measurement"] == "Cisco-IOS-XR-ip-daps-oper:address-pool-service/nodes/node/vrfs/vrf/ipv4")
    |> filter(fn: (r) => r["_field"] == "pools/pool_name" or r["_field"] == "pools/free" or r["_field"] == "pools/used" or r["_field"] == "pools/total")
    |> filter(fn: (r) => r["source"] == "CR-BNG-01")
    |> filter(fn: (r) => r["vrf_name"] == "GREEN")
    |> filter(fn: (r) => r["pools/pool_name"] == "EAD20_GREEN" or r["pools/pool_name"] == "EAD21_GREEN")
    |> aggregateWindow(every: v.windowPeriod, fn: last, createEmpty: false)
    |> pivot(rowKey:["_time"], columnKey: ["_field"], valueColumn: "_value")
    |> map(
        fn: (r) => ({
            r with _value: float(v: r["pools/free"] / float(v: r["pools/total"] ) * 100.0)
            }))
    |> yield(name: "last")
9

Thank you Scott , Yes i corrected that part and everything works fine !!

Thanks for your help!!