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

Aus Wiki-WebPerfect
Wechseln zu: Navigation, Suche
K
 
(9 dazwischenliegende Versionen des gleichen Benutzers werden nicht angezeigt)
Zeile 5: Zeile 5:
 
*'''Calculate the sum''' for each timeseries (foreach grouped _time).
 
*'''Calculate the sum''' for each timeseries (foreach grouped _time).
 
*'''Truncate''' the data to '''whole days'''.  
 
*'''Truncate''' the data to '''whole days'''.  
−
*Select ''first'' to '''remove the last not completed day'''.
+
*'''Unique''' remove multiple instances (deduplication).  
 
*'''Evaluate the month and the day''' and add these as new columns.
 
*'''Evaluate the month and the day''' and add these as new columns.
 
*Add '''pivot''' function to make the format working with '''Grafana'''.
 
*Add '''pivot''' function to make the format working with '''Grafana'''.
  
−
 
+
=== Flux Query v2 (recommended) ===
 +
''For more simplification the namedDay is remove in this example.''
 
<pre>
 
<pre>
 +
import "dict"
 
import "date" //import library to add date functions
 
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
 
timewindow = 1d //timewindow size
  
Zeile 20: Zeile 24:
 
   )
 
   )
 
   |> aggregateWindow(every: timewindow, fn: last, createEmpty: false)
 
   |> aggregateWindow(every: timewindow, fn: last, createEmpty: false)
 +
  |> truncateTimeColumn(unit: timewindow) //truncate to whole days
 
   |> group(columns: ["_time"])
 
   |> group(columns: ["_time"])
 +
  |> unique(column: "<FIELD-NAME_YOU_WANT_TO_DEDUPLCATE>") //prevent multiple instances (VMs)
 
   |> sum()
 
   |> 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
 +
</pre>
 +
 +
 +
 +
=== Flux Query v1 ===
 +
<pre>
 +
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
 
   |> truncateTimeColumn(unit: timewindow) //truncate to whole days
−
   |> first() //remove the calculated to now()
+
   |> 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
 
   |> map(fn: (r) => ({ r with //Valuemapping of monthnumber to monthname
 
     namedMonth:
 
     namedMonth:
Zeile 49: Zeile 77:
 
       else "error"
 
       else "error"
 
   }))
 
   }))
−
   |> pivot(rowKey: ["_time"], columnKey: ["namedMonth", "namedDay"], valueColumn: "_value") //formating for Grafana
+
   |> pivot(rowKey: ["_time"], columnKey: ["namedMonth", "namedDay"], valueColumn: "_value") //formatting for Grafana
 
</pre>
 
</pre>
  
  
 +
'''Grafana - How does it looks like'''<br>
 +
[[Datei:01-grafana-date.png]] <br>
  
 +
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'''
  
−
== Grafana ==
 
  
  
 +
''More information's about the Flux library "date": https://docs.influxdata.com/influxdb/v2.0/reference/flux/stdlib/date/'' <br>
 +
''More information's about the Flux library "dict": https://docs.influxdata.com/flux/v0.x/stdlib/dict/get/
  
  

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
01-grafana-date.png

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/

Von „http://wiki.webperfect.ch/index.php?title=Grafana:_Evaluate_date_name_(Flux)&oldid=3062“