Grafana: Evaluate date name (Flux): Unterschied zwischen den Versionen

Aktuelle Version vom 26. November 2021, 14:44 Uhr

Goal: Add two columns (namedMonth and namedDay) with name of the month and day.
Steps:

• Aggregate the data with an window of 1d and the function last.
• Group the data by _time.
• Calculate the sum for each timeseries (foreach grouped _time).
• Truncate the data to whole days.
• Unique remove multiple instances (deduplication).
• Evaluate the month and the day and add these as new columns.
• Add pivot function to make the format working with Grafana.

Flux Query v2 (recommended)

For more simplification the namedDay is remove in this example.

import "dict"
import "date" //import library to add date functions

months = [1:"January",2:"February",3:"March",4:"April",5:"May",6:"June",7:"July",8:"August",9:"September",10:"October",11:"November",12: "December"]
timewindow = 1d //timewindow size

from(bucket: <YOUR_BUCKET>)
  |> range(start: v.timeRangeStart, stop: v.timeRangeStop)
  |> filter(fn: (r) => 
    r._measurement == "<YOUR_MEASUREMENT>"
  )
  |> aggregateWindow(every: timewindow, fn: last, createEmpty: false)
  |> truncateTimeColumn(unit: timewindow) //truncate to whole days
  |> group(columns: ["_time"])
  |> unique(column: "<FIELD-NAME_YOU_WANT_TO_DEDUPLCATE>") //prevent multiple instances (VMs)
  |> sum()
  |> map(fn: (r) => ({ r with //Valuemapping of monthnumber to monthname
    namedMonth:
      dict.get(dict: months, key:date.month(t: r._time), default: "")
  })) 
  |> pivot(rowKey: ["_time"], columnKey: ["namedMonth", "namedDay"], valueColumn: "_value") //formatting for Grafana

Flux Query v1

import "date" //import library to add date functions
timewindow = 1d //timewindow size

from(bucket: <YOUR_BUCKET>)
  |> range(start: v.timeRangeStart, stop: v.timeRangeStop)
  |> filter(fn: (r) => 
    r._measurement == "<YOUR_MEASUREMENT>"
  )
  |> aggregateWindow(every: timewindow, fn: last, createEmpty: false)
  |> truncateTimeColumn(unit: timewindow) //truncate to whole days
  |> group(columns: ["_time"])
  |> unique(column: "<FIELD-NAME_YOU_WANT_TO_DEDUPLCATE>") //prevent multiple instances (VMs)
  |> sum()
  |> map(fn: (r) => ({ r with //Valuemapping of monthnumber to monthname
    namedMonth:
      if (date.month(t: r._time)) == 1 then "January" 
      else if (date.month(t: r._time)) == 2 then "February"
      else if (date.month(t: r._time)) == 3 then "March"
      else if (date.month(t: r._time)) == 4 then "April"
      else if (date.month(t: r._time)) == 5 then "May"
      else if (date.month(t: r._time)) == 6 then "June"
      else if (date.month(t: r._time)) == 7 then "July"
      else if (date.month(t: r._time)) == 8 then "August"
      else if (date.month(t: r._time)) == 9 then "September"
      else if (date.month(t: r._time)) == 10 then "October"
      else if (date.month(t: r._time)) == 11 then "November"
      else if (date.month(t: r._time)) == 12 then "December"
      else "error",
    namedDay:
      if (date.weekDay(t: r._time)) == 0 then "Sunday" 
      else if (date.weekDay(t: r._time)) == 1 then "Monday"
      else if (date.weekDay(t: r._time)) == 2 then "Tuesday"
      else if (date.weekDay(t: r._time)) == 3 then "Wednesday"
      else if (date.weekDay(t: r._time)) == 4 then "Thursday"
      else if (date.weekDay(t: r._time)) == 5 then "Friday"
      else if (date.weekDay(t: r._time)) == 6 then "Saturday"
      else "error"
  }))
  |> pivot(rowKey: ["_time"], columnKey: ["namedMonth", "namedDay"], valueColumn: "_value") //formatting for Grafana

Grafana - How does it looks like

In the image above I use following settings:

• Visualization: Stat
• Display:
  • Show: All values
  • Fields: Numeric Fields
  • Orientation: Vertical
  • Text mode: Auto
  • Color mode: Value
  • Alignment mode: auto

More information's about the Flux library "date": https://docs.influxdata.com/influxdb/v2.0/reference/flux/stdlib/date/
More information's about the Flux library "dict": https://docs.influxdata.com/flux/v0.x/stdlib/dict/get/