Select part of a string for single stat

Grafana
,
1

Have a query that returns this data.

2024-08-21T18:59:15.501932Z,2024-08-22T18:59:15.501932Z,2024-08-22T18:43:49.65919Z,1:4 2:22 3:0 4:0 5:0 6:0 7:0 8:0 9:0

Each value pair is a “Parameter”:“Value”. I would like to report the second value after the : to a single stat. Example I would like to report just the 22 after the 2: How can I parse down this string to multiple values that can be used individually? Regex? Arrays?

2

@fastxl,
So it’s returning this array?

arr = ["1st", "2nd", "3rd"]

arr[0]

In flux. So you could do:

import "experimental/array"
queryThatYieldsArrayYouGave = ....
itemYouWant = queryThatYieldsArrayYouGave[4]

To get that long string. Then you could do:

import "array"
import "strings"

myItem = ["1:4 2:22 3:0 4:0 5:0 6:0 7:0 8:0 9:0"] 
spacesplit = strings.split(v: myItem[0], t: " ")
colonsplit = strings.split(v: spacesplit[1], t: ":")

array.from(rows: [{value: colonsplit[1]}])
3

@Anaisdg Thanks for the reply. I am a bit new and trying to learn. Here is my query that gives me the data I need to separate.

from(bucket: "LB3000")
  |> range(start: today(), stop: now())
  |> filter(fn: (r) => r.deviceId == "Okuma-LB3000" and r.dataItemId == "Lp1CommonVariable")
  |> aggregateWindow(every: 1d, fn: last, createEmpty: false)

It returns this

FluxString
FluxString1077×225 14.3 KB

Beyond that I am a bit lost on the additional query.

4

@fastxl Do you only want the value associated with 2 parameter or do you want to be able to select the parameter and return the value?

5

@scott Select the parameter and return the value.

6

@fastxl This is going to get a little crazy, but I included comments in the query to explain what is happening:

The dashboard cell query

import "array"
import "dict"
import "strings"

// Extract the _value of the last record as a scalar string.
myItem =
    (from(bucket: "LB3000")
        |> range(start: today())
        |> filter(fn: (r) => r.deviceId == "Okuma-LB3000" and r.dataItemId == "Lp1CommonVariable")
        |> last()
        |> findRecord(fn: (key) => true, idx: 0))._value

// Split the scalar string by the space and return an array of strings.
spacesplit = strings.split(v: myItem, t: " ")

// Iterate over the spacesplit array and split each element by colon.
// For each item in the arry, return a record with two properties: key and value.
paramList =
    array.map(
        arr: spacesplit,
        fn: (x) => {
            _split = strings.split(v: x, t: ":")

            return {key: _split[0], value: _split[1]}
        },
    )

// Structure the paramList array as a dictionary to allow for dynamic access at
// query time.
paramDict = dict.fromList(pairs: paramList)

// Define what parameter to select from the paramDict dictionary.
paramKey = "1"

// Build an ad hoc table with only a _value column that is populated by selecting
// the paramKey from the paramDict dictionary.
array.from(rows: [{_value: dict.get(key: paramKey, dict: paramDict, default: "No value")}])

Now, since you’re going to be using this in a dashboard, I’d recommend defining the paramKey variable in this query with a dashboard variable. So first you need to define a dashboard variable that lets you select from the available parameters in the string (See Create a dashboard variable). Use a “Query” variable type and the following query to return all of the parameters

Dashboard variable query to get all the parameters

import "array"
import "strings"

// Extract the _value of the last record as a scalar string.
myItem =
    (from(bucket: "LB3000")
        |> range(start: today())
        |> filter(fn: (r) => r.deviceId == "Okuma-LB3000" and r.dataItemId == "Lp1CommonVariable")
        |> last()
        |> findRecord(fn: (key) => true, idx: 0))._value

// Split the scalar string by the space and return an array of strings.
spacesplit = strings.split(v: myItem, t: " ")

// Iterate over the spacesplit array and split each element by colon.
// For each item in the array, return a record with a _value property.
paramList =
    array.map(
        arr: spacesplit,
        fn: (x) => {
            _split = strings.split(v: x, t: ":")

            return {_value: _split[0]}
        },
    )

// Build an ad hoc table using the array of values
array.from(rows: paramList)

I’ll just assume the name of the variable is parameter. Now update your dashboard cell query to use the parameter dashboard variable and you’ll be able to select which parameter’s value to show in the single stat cell:

Final dashboard cell query with selectable parameter variable

import "array"
import "dict"
import "strings"

// Extract the _value of the last record as a scalar string.
myItem =
    (from(bucket: "LB3000")
        |> range(start: today())
        |> filter(fn: (r) => r.deviceId == "Okuma-LB3000" and r.dataItemId == "Lp1CommonVariable")
        |> last()
        |> findRecord(fn: (key) => true, idx: 0))._value

// Split the scalar string by the space and return an array of strings.
spacesplit = strings.split(v: myItem, t: " ")

// Iterate over the spacesplit array and split each element by colon.
// For each item in the arry, return a record with two properties: key and value.
paramList =
    array.map(
        arr: spacesplit,
        fn: (x) => {
            _split = strings.split(v: x, t: ":")

            return {key: _split[0], value: _split[1]}
        },
    )

// Structure the paramList array as a dictionary to allow for dynamic access at
// query time.
paramDict = dict.fromList(pairs: paramList)

// Define what parameter to select from the paramDict dictionary.
paramKey = v.parameter

// Build an ad hoc table with only a _value column that is populated by selecting
// the paramKey from the paramDict dictionary.
array.from(rows: [{_value: dict.get(key: paramKey, dict: paramDict, default: "No value")}])
2 Likes