List distinct values with timestamp from InfluxDB via InfluxQL

Question

I have a time-series with semantic version strings of a monitored component.

E.g.:

name: app
time                firmware_version
----                ----------------
1651658809605790508 5.8.19
1651658811390991812 5.8.19
1651658813328778435 5.8.19
1651658815153688294 5.8.19
1651658817041644439 5.8.19
...

I would like to display a table with the distinct versions and their timestamps (best would be a time-series, but as far as I understand the transformation from the string sem-ver to a numeric via Grafana isn't supported and so not plot-able?)

If I query with

SELECT  distinct("firmware_version") FROM "app" WHERE ("host" =~ /^something$/)

I get the lower time-bound in the series (in case where it isn't limited 0):

name: app
time distinct
---- --------
0    5.8.19
0    5.10.1
0    5.11.0

Is there some magic query to squeeze the time info out of influx when it does the reduction /filter over the firmware column?

Answer 1

From about a year, in new versions of Grafana (I think from v7.0) you should be able to do some transformations in Grafana. It may be useful for you when you use older query language: InfluxQL instead of Flux. I made some tests with float type field values but this idea should work in your case as well.

In my case, I have a time series with counter values collected every 10s, most of values are the same, but every ~30min value is incremented. In your case there will be a new version number.

Similar to you, I can get raw data in Grafana by query:

SELECT "firmware_version" 
FROM "test" 
WHERE $timeFilter

Which returns a simple plot like this:

Now it is worth to mention that aggregation may be very useful if you have wide time periods in short intervals. You may want to aggregate data, for example as a first value in 1min intervals (it will reduce amount of data send from InfluxDB):

SELECT first("firmware_version") 
FROM "test" 
WHERE $timeFilter 
GROUP BY time(1m)

Now you can transform your data by Grafana engine, go to "Transform" tab under your plot. Then add transformation named "Goup by" like this:

It will filter out first appearance of each value. This way you can also get last values to know till when each firmware version were used.

Your result should present like this:

Transform tab also allows you to parse data to different types. In this case I cannot help because I do not have example string data like yours but you can try it on your own.

Try one of these types of transform option in Grafana:

But I cannot test it now on my own.

Answer 2

With screenshots of the reply to @Crashtein:

Here the state-timeline plot which works as a hack. (Hack because I cannot see up/downgrade visually and it isn't color-graded because the sem-ver isn't supported as unit. (So less-then isn't defined I guess) The only way I could introduce coloring, is to define RegEx-based value-mapping, but this of course isn't scalable.