Querying influxdb for version changes and provide it to grafana

Fluxlang
,
1

Hi,

I tried the last days to create a flux query to show the count of reported versions per timeframe, before showing my awful try of a query I would provide the data structure.

bucket: cms
_measurement: (always the same) census
tags:
unique_id - a unqiue id per installation
fields:
cms_version: semVer string x.y.z
cms_major: x - only the major version
cms_minor: x.y - only the minor version
db_version - same structure like cms
db_server - string with mysql, postgresql, mssql,…
…

We get this information about every 6 hours depending on the triggered cronjobs for each installation.

My try to create a query for grafana which at least works a kind of

from(bucket: "cms")
  |> range(start: v.timeRangeStart, stop: v.timeRangeStop)
  |> filter(fn: (r) => r._field == "cms_major" and r._value == "3")
  |> sort(columns: ["_time"], desc: true)  
  |> aggregateWindow(every: 1w, fn: first, column: "unique_id", createEmpty: false)
  |> group(columns: ["unique_id", "_value", "_field"])  
  |> map(fn: (r) => ({ _time: r._time, _field: "value", count: 1 }))
  |> aggregateWindow(every: 1w, fn: sum, column: "count", createEmpty: false)
  |> pivot(rowKey:["_time"], columnKey: ["_field"], valueColumn: "count")
  |> group(columns: ["_time"])
  |> sum(column: "value")
  |> duplicate(column: "_time", as: "_stop")

The query works more or less, it returns a count per week without duplicated unique ids for major version 3, at least in influxdb. Using this query in grafana ends in many columns with the name “value timestamp”. Using a transform in grafana to reduce rows based on last and replace all fields shows a graph. But I think that’s not right since the data should be already correct right?

The next issue is to show multiple versions in comparison on one chart (version 3 4 5 +++), I tried to using union but that doesn’t look quite well and doesn’t work with transformation.

any advice would be helpful.

thanks

2

@hleithner This is definitely possible and I think could be optimized to improve performance over what you already have. Could you give me an example of what you want/expect the output to look like? Grafana kind of does it’s own thing when it comes to rendering results sometimes, but there are ways you can structure the data to make Grafana render it better without much customization to the visualization settings.

3

thanks for your answer @scott

I would expect something like the following data

Period Version 3 Version 4 Version 5
2024-01-01 1000 2000 500
2024-02-01 900 1900 700
2024-03-01 800 1800 900
2024-04-01 700 1700 1100

not sure if grafana can use this type of table or if it needs multiple rows with only one value and one version column. Something like thi

Period Version Count
2024-04-01 3 700
2024-04-01 4 1700
2024-04-01 5 1100
2024-03-01 3 800
2024-03-01 4 1800
2024-03-01 5 900

in the end it should output something like that:

stats_example
stats_example1352×991 75 KB

4

@hleithner When using Grafana and Flux to graph results, Grafana expects unpivoted data similar to the 2nd format you showed above. I think to get the graph to look like how you’re hoping it will, the following query will work. It duplicates the cms_major field value into a new major_version column, appends "Version " to the major_version column while converting it to a string, groups by the major_version column, then generates monthly counts per version.

from(bucket: "cms")
    |> range(start: v.timeRangeStart, stop: v.timeRangeStop)
    |> filter(fn: (r) => r._field == "cms_major")
    |> duplicate(column: "_value", as: "major_version")
    |> map(fn: (r) => ({ r with major_Version: "Version ${r.major_version}" }))
    |> group(columns: ["major_version"])
    |> aggregateWindow(every: 1mo, fn: count, timeSrc: "_start")

This should return a stream of tables that will look something like the following, which should graph correctly in Grafana:

_time major_version _value
2024-01-01T00:00:00Z Version 3 1000
2024-02-01T00:00:00Z Version 3 900
2024-03-01T00:00:00Z Version 3 800
2024-04-01T00:00:00Z Version 3 700
_time major_version _value
2024-01-01T00:00:00Z Version 4 2000
2024-02-01T00:00:00Z Version 4 1900
2024-03-01T00:00:00Z Version 4 1800
2024-04-01T00:00:00Z Version 4 1700
_time major_version _value
2024-01-01T00:00:00Z Version 5 500
2024-02-01T00:00:00Z Version 5 700
2024-03-01T00:00:00Z Version 5 900
2024-04-01T00:00:00Z Version 5 1100

Now, if you wanted to visualize this as a table, rather than a graph (similar to the first example you gave), you can use the same query and then pivot on _time with major_version as the column key:

from(bucket: "cms")
    |> range(start: v.timeRangeStart, stop: v.timeRangeStop)
    |> filter(fn: (r) => r._field == "cms_major")
    |> duplicate(column: "_value", as: "major_version")
    |> map(fn: (r) => ({ r with major_Version: "Version ${r.major_version}" }))
    |> group(columns: ["major_version"])
    |> aggregateWindow(every: 1mo, fn: count, timeSrc: "_start")
    |> pivot(rowKey: ["_time"], columnKey: ["major_version"], valueColumn: "_value")

This will give you something similar to:

_time Version 3 Version 4 Version 5
2024-01-01T00:00:00Z 1000 2000 500
2024-02-01T00:00:00Z 900 1900 700
2024-03-01T00:00:00Z 800 1800 900
2024-04-01T00:00:00Z 700 1700 1100
5

awesome that works pretty much as expected. Thank you

I only added the unique_id filter after the _field filter so only get one entry per period

  |> sort(columns: ["_time"], desc: true)  
  |> aggregateWindow(every: 1mo, fn: first, column: "unique_id", createEmpty: false)
  |> group(columns: ["unique_id"])