Quelltext der Seite Grafana: Evaluate date name (Flux)

'''Goal:''' Add two columns (''namedMonth'' and ''namedDay'') with name of the month and day. <br>
'''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.''
<pre>
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
</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
  |> 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
</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'''



''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/






[[Kategorie:TIG-Stack]]